首页 > 代码库 > OSChina底层数据库操作的类(QueryHelper)源码

OSChina底层数据库操作的类(QueryHelper)源码

2024-08-09 03:09:56   221人阅读

OSChina 使用的是 dbutils 这个JDBC的封装类库来进行数据库操作。而 QueryHelper 则是在 dbutils 的基础上进行一级简单的封装，提供一些常用的数据库操作方法和对数据缓存的支持。
数据库连接的释放方法请看这里。
标签： OSCHINA JDBC 数据库 DbUtils

[1].[代码] QueryHelper.java 跳至 [1]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
packagemy.db;
 
importjava.io.Serializable;
importjava.math.BigInteger;
importjava.sql.*;
importjava.util.*;
 
importmy.cache.CacheManager;
importnet.oschina.Configurations;
 
importorg.apache.commons.dbutils.QueryRunner;
importorg.apache.commons.dbutils.handlers.*;
importorg.apache.commons.lang.ArrayUtils;
 
/**
 * 数据库查询助手
 * @author Winter Lau<br> */
@SuppressWarnings("unchecked")
publicclass QueryHelper {
     
    privatefinal static QueryRunner _g_runner = newQueryRunner();
    privatefinal static ColumnListHandler _g_columnListHandler = newColumnListHandler(){
        @Override
        protectedObject handleRow(ResultSet rs) throwsSQLException {
            Object obj = super.handleRow(rs);
            if(objinstanceofBigInteger)
                return((BigInteger)obj).longValue();
            returnobj;
        }
         
    };
    privatefinal static ScalarHandler _g_scaleHandler = newScalarHandler(){
        @Override
        publicObject handle(ResultSet rs) throwsSQLException {
            Object obj = super.handle(rs);
            if(objinstanceofBigInteger)
                return((BigInteger)obj).longValue();
            returnobj;
        }      
    };
     
    privatefinal static List<Class<?>> PrimitiveClasses = newArrayList<Class<?>>(){{
        add(Long.class);
        add(Integer.class);
        add(String.class);
        add(java.util.Date.class);
        add(java.sql.Date.class);
        add(java.sql.Timestamp.class);
    }};
     
    privatefinal static boolean _IsPrimitive(Class<?> cls) {
        returncls.isPrimitive() || PrimitiveClasses.contains(cls) ;
    }
     
    /**
     * 获取数据库连接
     * @return
     */
    publicstatic Connection getConnection() {
        try{
            returnConfigurations.getConnection();
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
 
    /**
     * 读取某个对象
     * @param sql
     * @param params
     * @return
     */
    @SuppressWarnings("rawtypes")
    publicstatic <T> T read(Class<T> beanClass, String sql, Object...params) {
        try{
            return(T)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_scaleHandler:newBeanHandler(beanClass), params);
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
     
    publicstatic <T> T read_cache(Class<T> beanClass, String cache, Serializable key, String sql, Object...params) {
        T obj = (T)CacheManager.get(cache, key);
        if(obj == null){
            obj = read(beanClass, sql, params);
            CacheManager.set(cache, key, (Serializable)obj);
        }
        returnobj;
    }
     
    /**
     * 对象查询
     * @param <T>
     * @param beanClass
     * @param sql
     * @param params
     * @return
     */
    @SuppressWarnings("rawtypes")
    publicstatic <T> List<T> query(Class<T> beanClass, String sql, Object...params) {
        try{
            return(List<T>)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_columnListHandler:newBeanListHandler(beanClass), params);
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
 
    /**
     * 支持缓存的对象查询
     * @param <T>
     * @param beanClass
     * @param cache_region
     * @param key
     * @param sql
     * @param params
     * @return
     */
    publicstatic <T> List<T> query_cache(Class<T> beanClass, String cache_region, Serializable key, String sql, Object...params) {      
        List<T> objs = (List<T>)CacheManager.get(cache_region, key);
        if(objs == null){
            objs = query(beanClass, sql, params);
            CacheManager.set(cache_region, key, (Serializable)objs);
        }
        returnobjs;
    }
     
    /**
     * 分页查询
     * @param <T>
     * @param beanClass
     * @param sql
     * @param page
     * @param count
     * @param params
     * @return
     */
    publicstatic <T> List<T> query_slice(Class<T> beanClass, String sql, intpage, intcount, Object...params) {
        if(page < 0|| count < 0)
            thrownew IllegalArgumentException("Illegal parameter of ‘page‘ or ‘count‘, Must be positive.");
        intfrom = (page - 1) * count;
        count = (count > 0) ? count : Integer.MAX_VALUE;
        returnquery(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, newInteger[]{from,count}));     
    }
     
    /**
     * 支持缓存的分页查询
     * @param <T>
     * @param beanClass
     * @param cache
     * @param cache_key
     * @param cache_obj_count
     * @param sql
     * @param page
     * @param count
     * @param params
     * @return
     */
    publicstatic <T> List<T> query_slice_cache(Class<T> beanClass, String cache, Serializable cache_key,intcache_obj_count, String sql, intpage, intcount, Object...params) {
        List<T> objs = (List<T>)CacheManager.get(cache, cache_key);
        if(objs == null) {
            objs = query_slice(beanClass, sql, 1, cache_obj_count, params);
            CacheManager.set(cache, cache_key, (Serializable)objs);
        }
        if(objs == null|| objs.size()==0)
            returnobjs;
        intfrom = (page - 1) * count;
        if(from < 0)
            returnnull;
        if((from+count) > cache_obj_count)//超出缓存的范围
            returnquery_slice(beanClass, sql, page, count, params);
        intend = Math.min(from + count, objs.size());
        if(from >= end)
            returnnull;
        returnobjs.subList(from, end);
    }
     
    /**
     * 执行统计查询语句，语句的执行结果必须只返回一个数值
     * @param sql
     * @param params
     * @return
     */
    publicstatic long stat(String sql, Object...params) {
        try{
            Number num = (Number)_g_runner.query(getConnection(), sql, _g_scaleHandler, params);
            return(num!=null)?num.longValue():-1;
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
 
    /**
     * 执行统计查询语句，语句的执行结果必须只返回一个数值
     * @param cache_region
     * @param key
     * @param sql
     * @param params
     * @return
     */
    publicstatic long stat_cache(String cache_region, Serializable key, String sql, Object...params) {
        Number value = http://www.mamicode.com/(Number)CacheManager.get(cache_region, key);
        if(value =http://www.mamicode.com/= null){
            value = http://www.mamicode.com/stat(sql, params);
            CacheManager.set(cache_region, key, value);
        }
        returnvalue.longValue();
    }
 
    /**
     * 执行INSERT/UPDATE/DELETE语句
     * @param sql
     * @param params
     * @return
     */
    publicstatic int update(String sql, Object...params) {
        try{
            return_g_runner.update(getConnection(), sql, params);
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
     
    /**
     * 批量执行指定的SQL语句
     * @param sql
     * @param params
     * @return
     */
    publicstatic int[] batch(String sql, Object[][] params) {
        try{
            return_g_runner.batch(getConnection(), sql, params);
        }catch(SQLException e){
            thrownew DBException(e);
        }
    }
}

OSChina底层数据库操作的类(QueryHelper)源码


联系
我们