OSChina 使用的是 dbutils 这个JDBC的封装类库来进行数据库操作。
而 QueryHelper 则是在 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
|
package
my.db;import
java.io.Serializable;import
java.math.BigInteger;import
java.sql.*;import
java.util.*;import
my.cache.CacheManager;import
net.oschina.Configurations;import
org.apache.commons.dbutils.QueryRunner;import
org.apache.commons.dbutils.handlers.*;import
org.apache.commons.lang.ArrayUtils;/** *
数据库查询助手 *
@author Winter Lau<br> */@SuppressWarnings("unchecked")public
class
QueryHelper { private
final
static
QueryRunner _g_runner = new
QueryRunner(); private
final
static
ColumnListHandler _g_columnListHandler = new
ColumnListHandler(){ @Override protected
Object handleRow(ResultSet rs) throws
SQLException { Object
obj = super.handleRow(rs); if(obj
instanceof
BigInteger) return
((BigInteger)obj).longValue(); return
obj; } }; private
final
static
ScalarHandler _g_scaleHandler = new
ScalarHandler(){ @Override public
Object handle(ResultSet rs) throws
SQLException { Object
obj = super.handle(rs); if(obj
instanceof
BigInteger) return
((BigInteger)obj).longValue(); return
obj; }
}; private
final
static
List<Class<?>> PrimitiveClasses = new
ArrayList<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); }}; private
final
static
boolean
_IsPrimitive(Class<?> cls) { return
cls.isPrimitive() || PrimitiveClasses.contains(cls) ; } /** *
获取数据库连接 *
@return */ public
static
Connection getConnection() { try{ return
Configurations.getConnection(); }catch(SQLException
e){ throw
new
DBException(e); } } /** *
读取某个对象 *
@param sql *
@param params *
@return */ @SuppressWarnings("rawtypes") public
static
<T> T read(Class<T> beanClass, String sql, Object...params) { try{ return
(T)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_scaleHandler:new
BeanHandler(beanClass), params); }catch(SQLException
e){ throw
new
DBException(e); } } public
static
<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); } return
obj; } /** *
对象查询 *
@param <T> *
@param beanClass *
@param sql *
@param params *
@return */ @SuppressWarnings("rawtypes") public
static
<T> List<T> query(Class<T> beanClass, String sql, Object...params) { try{ return
(List<T>)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_columnListHandler:new
BeanListHandler(beanClass), params); }catch(SQLException
e){ throw
new
DBException(e); } } /** *
支持缓存的对象查询 *
@param <T> *
@param beanClass *
@param cache_region *
@param key *
@param sql *
@param params *
@return */ public
static
<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); } return
objs; } /** *
分页查询 *
@param <T> *
@param beanClass *
@param sql *
@param page *
@param count *
@param params *
@return */ public
static
<T> List<T> query_slice(Class<T> beanClass, String sql, int
page, int
count, Object...params) { if(page
< 0
|| count < 0)
throw
new
IllegalArgumentException("Illegal
parameter of 'page' or 'count', Must be positive."); int
from = (page - 1)
* count; count
= (count > 0)
? return
query(beanClass, sql + "
LIMIT ?,?",
ArrayUtils.addAll(params, new
Integer[]{from,count})); } /** *
支持缓存的分页查询 *
@param <T> *
@param beanClass *
@param cache *
@param cache_key *
@param cache_obj_count *
@param sql *
@param page *
@param count *
@param params *
@return */ public
static
<T> List<T> query_slice_cache(Class<T> beanClass, String cache, Serializable cache_key,
int
cache_obj_count, String sql, int
page, int
count, 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) return
objs; int
from = (page - 1)
* count; if(from
< 0) return
null; if((from+count)
> cache_obj_count)//超出缓存的范围 return
query_slice(beanClass, sql, page, count, params); int
end = Math.min(from + count, objs.size()); if(from
>= end) return
null; return
objs.subList(from, end); } /** *
运行统计查询语句。语句的运行结果必须仅仅返回一个数值 *
@param sql *
@param params *
@return */ public
static
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){ throw
new
DBException(e); } } /** *
运行统计查询语句。语句的运行结果必须仅仅返回一个数值 *
@param cache_region *
@param key *
@param sql *
@param params *
@return */ public
static
long
stat_cache(String cache_region, Serializable key, String sql, Object...params) { Number
value = (Number)CacheManager.get(cache_region, key); if(value
== null){ value
= stat(sql, params); CacheManager.set(cache_region,
key, value); } return
value.longValue(); } /** *
运行INSERT/UPDATE/DELETE语句 *
@param sql *
@param params *
@return */ public
static
int
update(String sql, Object...params) { try{ return
_g_runner.update(getConnection(), sql, params); }catch(SQLException
e){ throw
new
DBException(e); } } /** *
批量运行指定的SQL语句 *
@param sql *
@param params *
@return */ public
static
int[]
batch(String sql, Object[][] params) { try{ return
_g_runner.batch(getConnection(), sql, params); }catch(SQLException
e){ throw
new
DBException(e); } }} |