DBUtils QueryRunner.query 的使用

十月的星空 发布于 2014/05/07 16:28
阅读 5K+
收藏 1

@mnisummer 你好,想跟你请教个问题:

大家都說使用DButils 的QueryRunner.query 會自動關閉ResultSet 和 Statement (當然源碼中有ResultSet 和Statement的關閉)

但沒有看到程序顯式進行關閉,那麼大家是如何判斷QueryRunner.query 自動關閉ResultSet 和 Statement的呢?

加载中
0
鸟人会飞

用Spring框架,不用管JDBC对象的开闭,Spring会自动管理的。

0
黄昏武士
黄昏武士


JDBC对象,如ResultSet和Statement的开闭手动去做,一般我的做法是:封装。关于数据库层面的交互,全部封装到一个类里面去,像OSC的源代码里面封装了一个QueryHelper类,这个类里面把所有的对象开闭操作都做好了;一旦有问题,我只要改这个类就好了。

另外,QueryRunner基本上不需要你去做ResultSet和Statement的开闭啊,源代码附上(我做过少许修改):  

package com.mugu.framework.data;

import java.io.Serializable;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.ArrayUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import com.mugu.framework.cache.CacheManager;
import com.mugu.framework.core.BaseEntity;



/**
 * 数据库查询帮助类,主要执行数据库操作
 * @author 
 * @Email 
 * @creation 2011-4-8
 */
@SuppressWarnings("unchecked")
public class QueryHelper {
	private final static QueryRunner _g_runner = new QueryRunner();
	private final static Logger log = LogManager.getLogger(QueryHelper.class);
	
	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<?>>(){/**
		 * 
		 */
		private static final long serialVersionUID = 1L;

	{
		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) ;
	}
	
	/**
	 * 读取某个对象
	 * @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,new ModelProcessor()), 
						params);
		}catch(SQLException e){
			log.debug(e);
			return null;
		}
	}
	
	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,new ModelProcessor()), 
					params);
		}catch(SQLException e){
			log.error(e);
			return null;
		}
	}

	/**
	 * 支持缓存的对象查询
	 * @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) ? count : Integer.MAX_VALUE;
		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
	 * @throws DBException 
	 */
	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
	 * @throws DBException 
	 */
	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();
	}
	
	public static Map<String,Object> queryMap(String sql,Object...params){
		try{
			return (Map<String,Object>)_g_runner.query(getConnection(), 
					sql, 
					new MapHandler(new ModelProcessor()), 
					params);
		}catch(SQLException e){
			log.error(e);
			return null;
		}
	}
	
	public static List<Map<String,Object>> queryMapList(String sql,Object...params){
		try{
			return (List<Map<String,Object>>)_g_runner.query(getConnection(), 
					sql, 
					new MapListHandler(new ModelProcessor()), 
					params);
		}catch(SQLException e){
			log.error(e);
			return null;
		}
	}
	

	public static List<Map<String,Object>> queryMapList_Cache(String cache_region,Serializable key,String sql,Object...params){
		List<Map<String,Object>> value = (List<Map<String,Object>>)CacheManager.get(cache_region, key);
		if(value == null){
			value = queryMapList(sql, params);
			CacheManager.set(cache_region, key, (Serializable)value);
		}
		
		return value;
	}
	
	/**
	 * 插入一个对象,该对象必须继承自{@link BaseEntity}类
	 * @param obj
	 * @return
	 */
	public static Serializable insert(BaseEntity obj){
		StringBuffer sql = new StringBuffer();
		EntityRegistry er = EntityMapped.getFromEntity(obj.getClass());
		
		int tableColumnsCount = er.getPropertyMap().size();
		if(er.getIdProperty() != null && er.getIdProperty().isAutoCreate()){
			tableColumnsCount -= 1;
		}
		
		String cols = EntityMapped.getTableColumns(er,true);
		sql.append("insert into " + er.getTableName());
		sql.append("(" + cols + ")");
		sql.append(" values(" + outParams(tableColumnsCount) + ")");
		
		String[] fields = getFields(cols);
		Object[] params = new Object[fields.length];
		for(int i = 0;i<fields.length;i++){
			String f = fields[i];
			
			params[i] = getFieldValue(er,f,obj);
		}
		
		Serializable pk = insert(sql.toString(),params);
		setIdProperty(er.getIdProperty(),obj,pk);
		return pk;
	}
	
	public static Serializable insert_cache(BaseEntity obj,String cache,String key){
		Serializable i = insert(obj);
		
		if(i != null){
			BaseEntity cachedObj = (BaseEntity) CacheManager.get(cache, key);
			if(cachedObj != null){
				CacheManager.justEvict(cache, key);
				CacheManager.set(cache, key, obj);
			}
		}
		
		return i;
	}
	
//	private static void generateIdProperty(EntityRegistry er,BaseEntity obj){
//		PropertyRegistry idProperty = er.getIdProperty();
//		
//		try{
//			if(idProperty.isAutoCreate()){
//				PropertyUtils.setProperty(obj, idProperty.getPropertyName(), 0);
//			}
//		}catch(Exception e){
//			log.error(e);
//			e.printStackTrace();
//		}
//	}
	
	private static void setIdProperty(PropertyRegistry pr,BaseEntity obj,Serializable val){
		try{
			if(pr.isAutoCreate()){
				pr.getWriteMethod().invoke(obj, val);
			}
		}catch(Exception e){
			log.error(e);
			log.debug("字段名称:" + pr.getPropertyName() + "|" + pr.getWriteMethod());
			log.debug("数据类型:" + val.getClass() + "|" + val);
			e.printStackTrace();
		}
	}
	
	private static Serializable insert(String sql,Object[] params){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = QueryHelper.getConnection().prepareStatement(sql, 
				PreparedStatement.RETURN_GENERATED_KEYS);		
			for(int i=0;i<params.length;i++){
				ps.setObject(i+1, params[i]);
			}
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			return rs.next()?(Serializable)rs.getObject(1):null;
		}catch(SQLException e){
			//throw new DBException(e);
			return 0;
		}finally{
			DbUtils.closeQuietly(rs);
			DbUtils.closeQuietly(ps);
			sql = null;
		}
	}
	
	public static int update(BaseEntity obj){
		StringBuffer sql = new StringBuffer();
		EntityRegistry er = EntityMapped.getFromEntity(obj.getClass());
		
		int propertiesCount = er.getPropertyMap().size() - 1;
		
		Object[] params = new Object[propertiesCount + 1];
		sql.append("update " + er.getTableName() + " set ");
		int i = 0;
		for(PropertyRegistry pr : er.getPropertyMap().values()){
			if(!pr.isPrimaryKey()){
				sql.append(pr.getColumnName() + "=?");
				if(i < propertiesCount - 1){
					sql.append(",");					
				}
				
				params[i] = getFieldValue(er, pr.getPropertyName(), obj);
				i++;
			}
		}
		
		sql.append(" where " + er.getIdProperty().getColumnName() + "=?");
		params[propertiesCount] = getFieldValue(er,er.getIdProperty().getPropertyName(),obj);
		
		return update(sql.toString(),params);
	}
	
	public static int update_cache(BaseEntity obj,String cache,String key){
		int i = update(obj);
		
		if(i > 0){
			BaseEntity cachedObj = (BaseEntity)CacheManager.get(cache, key);
			
			if(cachedObj != null){
				CacheManager.justEvict(cache, key);
				CacheManager.set(cache, key, obj);
			}
		}
		
		return i;
	}
	
	public static int remove(Class<?> entityClass,Object id){
		StringBuffer sql = new StringBuffer();
		EntityRegistry er = EntityMapped.getFromEntity(entityClass);
		
		if(er.getIdProperty() == null){
			return -1;
		}
		
		sql.append("delete from " + er.getTableName() + " where " + er.getIdProperty().getColumnName() + "=?");
		return update(sql.toString(),id);
	}
	
	public static int remove_cache(Class<?> entityClass,Object id,String cache,String key){
		int i = remove(entityClass,id);
		
		if(i > 0){
			BaseEntity cachedObj = (BaseEntity)CacheManager.get(cache, key);
			
			if(cachedObj != null){
				CacheManager.justEvict(cache, key);
			}
		}
		
		return i;
	}
	
	private static String[] getFields(String cols){
		return cols.split(",");
	}
	
	private static Object getFieldValue(EntityRegistry er,String field,BaseEntity obj){
		try{
			String propertyName = EntityMapped.getBeanProperty(er, field);
			return PropertyUtils.getProperty(obj, propertyName);
		}catch(Exception ex){
			log.error(ex);
			return null;
		}
	}
	
	private static String outParams(int cols){
		String str = "";
		for(int i = 1;i<= cols;i++){
			str += "?,";
		}
		
		return str.substring(0,str.length()-1);
	}
	
	/**
	 * 执行更新数据库语句
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int update(String sql, Object...params){
		try{
			return _g_runner.update(getConnection(),sql, params);
		}catch(SQLException ex){
			log.error(ex);
			return 0;
		}
	}
	
	/**
	 * 批量执行指定的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);
		}
	}

	/**
	 * 打开数据库连接
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws SQLException{
		return DBManager.getConnection();
	}
	
	/**
	 * 释放数据库连接
	 */
	public static void closeConnection(){
		DBManager.closeConnection();
	}
		
	/////===============================Cache Manager========================================
	/**
	 * 读取缓存中的对象
	 */
	public static Object getFromCache(String name,Serializable key){
		return CacheManager.get(name, key);
	}
	
	public final static void setCache(String name,Serializable key,Serializable value){
		CacheManager.set(name, key, value);
	}
	
}




0
m
mnisummer

但沒有看到程序顯式進行關閉,那麼大家是如何判斷QueryRunner.query 自動關閉ResultSet 和 Statement的呢?

判断不了ResultSet和Statement是否关闭,因为这两个对象时query方法的内部的变量,只能确定,在执行query后,ResultSet和Statement这两个对象被关闭了

返回顶部
顶部