OSChina 使用的是 dbutils 这个JDBC的封装类库来进行数据库操作。而 QueryHelper 则是在 dbutils 的基础上进行一级简单的封装，提供一些常用的数据库操作方法和对数据缓存的支持。
红薯...QueryHelper 代码如下：
1package my.db; 2 3import java.io.Serializable; 4import java.math.BigInteger; 5import java.sql.*; 6import java.util.*; 7 8import my.cache.CacheManager; 9import net.oschina.Configurations; 10 11import org.apache.commons.dbutils.QueryRunner; 12import org.apache.commons.dbutils.handlers.*; 13import org.apache.commons.lang.ArrayUtils; 14 15/** 16 * 数据库查询助手 17 * @author Winter Lau<br> */ 18@SuppressWarnings("unchecked") 19public class QueryHelper { 20 21 private final static QueryRunner _g_runner = new QueryRunner(); 22 private final static ColumnListHandler _g_columnListHandler = new ColumnListHandler(){ 23 @Override 24 protected Object handleRow(ResultSet rs) throws SQLException { 25 Object obj = super.handleRow(rs); 26 if(obj instanceof BigInteger) 27 return ((BigInteger)obj).longValue(); 28 return obj; 29 } 30 31 }; 32 private final static ScalarHandler _g_scaleHandler = new ScalarHandler(){ 33 @Override 34 public Object handle(ResultSet rs) throws SQLException { 35 Object obj = super.handle(rs); 36 if(obj instanceof BigInteger) 37 return ((BigInteger)obj).longValue(); 38 return obj; 39 } 40 }; 41 42 private final static List<Class<?>> PrimitiveClasses = new ArrayList<Class<?>>(){{ 43 add(Long.class); 44 add(Integer.class); 45 add(String.class); 46 add(java.util.Date.class); 47 add(java.sql.Date.class); 48 add(java.sql.Timestamp.class); 49 }}; 50 51 private final static boolean _IsPrimitive(Class<?> cls) { 52 return cls.isPrimitive() || PrimitiveClasses.contains(cls) ; 53 } 54 55 /** 56 * 获取数据库连接 57 * @return 58 */ 59 public static Connection getConnection() { 60 try{ 61 return Configurations.getConnection(); 62 }catch(SQLException e){ 63 throw new DBException(e); 64 } 65 } 66 67 /** 68 * 读取某个对象 69 * @param sql 70 * @param params 71 * @return 72 */ 73 @SuppressWarnings("rawtypes") 74 public static <T> T read(Class<T> beanClass, String sql, Object...params) { 75 try{ 76 return (T)_g_runner.query(getConnection(), sql, _IsPrimitive(beanClass)?_g_scaleHandler:new BeanHandler(beanClass), params); 77 }catch(SQLException e){ 78 throw new DBException(e); 79 } 80 } 81 82 public static <T> T read_cache(Class<T> beanClass, String cache, Serializable key, String sql, Object...params) { 83 T obj = (T)CacheManager.get(cache, key); 84 if(obj == null){ 85 obj = read(beanClass, sql, params); 86 CacheManager.set(cache, key, (Serializable)obj); 87 } 88 return obj; 89 } 90 91 /** 92 * 对象查询 93 * @param <T> 94 * @param beanClass 95 * @param sql 96 * @param params 97 * @return 98 */ 99 @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, int count, 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, new Integer[]{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}
该类主要提供包括查询列表、查询列表分页、查询单条记录、统计等方法的封装。