QueryHelper.java
001 |
package my.db; |
002 |
003 |
import java.io.Serializable; |
004 |
import java.math.BigInteger; |
005 |
import java.sql.*; |
006 |
import java.util.*; |
007 |
008 |
import my.cache.CacheManager; |
009 |
import net.oschina.Configurations; |
010 |
011 |
import org.apache.commons.dbutils.QueryRunner; |
012 |
import org.apache.commons.dbutils.handlers.*; |
013 |
import org.apache.commons.lang.ArrayUtils; |
014 |
015 |
/** |
016 |
* 数据库查询助手 |
017 |
* @author Winter Lau<br> */ |
018 |
@SuppressWarnings("unchecked") |
019 |
public class QueryHelper { |
020 |
|
021 |
private final static QueryRunner _g_runner = new QueryRunner(); |
022 |
private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler(){ |
023 |
@Override |
024 |
protected Object handleRow(ResultSet rs) throws SQLException { |
025 |
Object obj = super.handleRow(rs); |
026 |
if(obj instanceof BigInteger) |
027 |
return ((BigInteger)obj).longValue(); |
028 |
return obj; |
029 |
} |
030 |
|
031 |
}; |
032 |
private final static ScalarHandler _g_scaleHandler = new ScalarHandler(){ |
033 |
@Override |
034 |
public Object handle(ResultSet rs) throws SQLException { |
035 |
Object obj = super.handle(rs); |
036 |
if(obj instanceof BigInteger) |
037 |
return ((BigInteger)obj).longValue(); |
038 |
return obj; |
039 |
} |
040 |
}; |
041 |
|
042 |
private final static List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>(){{ |
043 |
add(Long.class); |
044 |
add(Integer.class); |
045 |
add(String.class); |
046 |
add(java.util.Date.class); |
047 |
add(java.sql.Date.class); |
048 |
add(java.sql.Timestamp.class); |
049 |
}}; |
050 |
|
051 |
private final static boolean _IsPrimitive(Class<?> cls) { |
052 |
return cls.isPrimitive() || PrimitiveClasses.contains(cls) ; |
053 |
} |
054 |
|
055 |
/** |
056 |
* 获取数据库连接 |
057 |
* @return |
058 |
*/ |
059 |
public static Connection getConnection() { |
060 |
try{ |
061 |
return Configurations.getConnection(); |
062 |
}catch(SQLException e){ |
063 |
throw new DBException(e); |
064 |
} |
065 |
} |
066 |
067 |
/** |
068 |
* 读取某个对象 |
069 |
* @param sql |
070 |
* @param params |
071 |
* @return |
072 |
*/ |
073 |
@SuppressWarnings("rawtypes") |
074 |
public static <T> T read(Class<T> beanClass, String sql, Object...params) { |
075 |
try{ |
076 |
return (T)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_scaleHandler:new BeanHandler(beanClass), params); |
077 |
}catch(SQLException e){ |
078 |
throw new DBException(e); |
079 |
} |
080 |
} |
081 |
|
082 |
public static <T> T read_cache(Class<T> beanClass, String cache, Serializable key, String sql, Object...params) { |
083 |
T obj = (T)CacheManager.get(cache, key); |
084 |
if(obj == null){ |
085 |
obj = read(beanClass, sql, params); |
086 |
CacheManager.set(cache, key, (Serializable)obj); |
087 |
} |
088 |
return obj; |
089 |
} |
090 |
|
091 |
/** |
092 |
* 对象查询 |
093 |
* @param <T> |
094 |
* @param beanClass |
095 |
* @param sql |
096 |
* @param params |
097 |
* @return |
098 |
*/ |
099 |
@SuppressWarnings("rawtypes") |
100 |
public static <T> List<T> query(Class<T> beanClass, String sql, Object...params) { |
101 |
try{ |
102 |
return (List<T>)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_columnListHandler:new BeanListHandler(beanClass), params); |
103 |
}catch(SQLException e){ |
104 |
throw new DBException(e); |
105 |
} |
106 |
} |
107 |
108 |
/** |
109 |
* 支持缓存的对象查询 |
110 |
* @param <T> |
111 |
* @param beanClass |
112 |
* @param cache_region |
113 |
* @param key |
114 |
* @param sql |
115 |
* @param params |
116 |
* @return |
117 |
*/ |
118 |
public static <T> List<T> query_cache(Class<T> beanClass, String cache_region, Serializable key, String sql, Object...params) { |
119 |
List<T> objs = (List<T>)CacheManager.get(cache_region, key); |
120 |
if(objs == null){ |
121 |
objs = query(beanClass, sql, params); |
122 |
CacheManager.set(cache_region, key, (Serializable)objs); |
123 |
} |
124 |
return objs; |
125 |
} |
126 |
|
127 |
/** |
128 |
* 分页查询 |
129 |
* @param <T> |
130 |
* @param beanClass |
131 |
* @param sql |
132 |
* @param page |
133 |
* @param count |
134 |
* @param params |
135 |
* @return |
136 |
*/ |
137 |
public static <T> List<T> query_slice(Class<T> beanClass, String sql, int page, intcount, Object...params) { |
138 |
if(page < 0 || count < 0) |
139 |
throw new IllegalArgumentException("Illegal parameter of 'page' or 'count', Must be positive."); |
140 |
int from = (page - 1) * count; |
141 |
count = (count > 0) ? count : Integer.MAX_VALUE; |
142 |
return query(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params, newInteger[]{from,count})); |
143 |
} |
144 |
|
145 |
/** |
146 |
* 支持缓存的分页查询 |
147 |
* @param <T> |
148 |
* @param beanClass |
149 |
* @param cache |
150 |
* @param cache_key |
151 |
* @param cache_obj_count |
152 |
* @param sql |
153 |
* @param page |
154 |
* @param count |
155 |
* @param params |
156 |
* @return |
157 |
*/ |
158 |
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) { |
159 |
List<T> objs = (List<T>)CacheManager.get(cache, cache_key); |
160 |
if(objs == null) { |
161 |
objs = query_slice(beanClass, sql, 1, cache_obj_count, params); |
162 |
CacheManager.set(cache, cache_key, (Serializable)objs); |
163 |
} |
164 |
if(objs == null || objs.size()==0) |
165 |
return objs; |
166 |
int from = (page - 1) * count; |
167 |
if(from < 0) |
168 |
return null; |
169 |
if((from+count) > cache_obj_count)//超出缓存的范围 |
170 |
return query_slice(beanClass, sql, page, count, params); |
171 |
int end = Math.min(from + count, objs.size()); |
172 |
if(from >= end) |
173 |
return null; |
174 |
return objs.subList(from, end); |
175 |
} |
176 |
|
177 |
/** |
178 |
* 执行统计查询语句，语句的执行结果必须只返回一个数值 |
179 |
* @param sql |
180 |
* @param params |
181 |
* @return |
182 |
*/ |
183 |
public static long stat(String sql, Object...params) { |
184 |
try{ |
185 |
Number num = (Number)_g_runner.query(getConnection(), sql, _g_scaleHandler, params); |
186 |
return (num!=null)?num.longValue():-1; |
187 |
}catch(SQLException e){ |
188 |
throw new DBException(e); |
189 |
} |
190 |
} |
191 |
192 |
/** |
193 |
* 执行统计查询语句，语句的执行结果必须只返回一个数值 |
194 |
* @param cache_region |
195 |
* @param key |
196 |
* @param sql |
197 |
* @param params |
198 |
* @return |
199 |
*/ |
200 |
public static long stat_cache(String cache_region, Serializable key, String sql, Object...params) { |
201 |
Number value = (Number)CacheManager.get(cache_region, key); |
202 |
if(value == null){ |
203 |
value = stat(sql, params); |
204 |
CacheManager.set(cache_region, key, value); |
205 |
} |
206 |
return value.longValue(); |
207 |
} |
208 |
209 |
/** |
210 |
* 执行INSERT/UPDATE/DELETE语句 |
211 |
* @param sql |
212 |
* @param params |
213 |
* @return |
214 |
*/ |
215 |
public static int update(String sql, Object...params) { |
216 |
try{ |
217 |
return _g_runner.update(getConnection(), sql, params); |
218 |
}catch(SQLException e){ |
219 |
throw new DBException(e); |
220 |
} |
221 |
} |
222 |
|
223 |
/** |
224 |
* 批量执行指定的SQL语句 |
225 |
* @param sql |
226 |
* @param params |
227 |
* @return |
228 |
*/ |
229 |
public static int[] batch(String sql, Object[][] params) { |
230 |
try{ |
231 |
return _g_runner.batch(getConnection(), sql, params); |
232 |
}catch(SQLException e){ |
233 |
throw new DBException(e); |
234 |
} |
235 |
} |
236 |
} |
