BoneCP使用

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.jolbox.bonecp.BoneCPConfig;
import com.jolbox.bonecp.BoneCPDataSource;
import com.wjxjt.smsapi.Conf;

public class JDBCUtil {

	private static BoneCPDataSource ds = null;

	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			BoneCPConfig cfg = new BoneCPConfig();
			// set the JDBC url
	        cfg.setJdbcUrl("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8");
	        cfg.setUsername("root");         
	        cfg.setPassword("root");

	        //分区数量
	        cfg.setPartitionCount(3);
	        //每个分区最大连接数
	        cfg.setMaxConnectionsPerPartition(5);
	        //每个分区最小连接数
	        cfg.setMinConnectionsPerPartition(1);
	        //分区连接数增长大小
	        cfg.setAcquireIncrement(1);
	        //空闲存活时间，空闲时间超过这个值，连接会被清除
	        cfg.setIdleMaxAge(60,TimeUnit.MINUTES);
	        //空闲连接测试间隔时间
	        cfg.setIdleConnectionTestPeriod(240,TimeUnit.MINUTES);

	        ds = new BoneCPDataSource(cfg);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
	}

	/**
	 * 插入
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int insert(String sql, Object... params) throws SQLException {
		return update(sql, params);
	}

	/**
	 * 插入并返回自动增长的主键
	 * @param sql
	 * @param params
	 * @return
	 */
	public static long insertAndGetKey(String sql, Object... params) {
		long key = 0;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = ds.getConnection();
			stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			ParameterMetaData pmd = stmt.getParameterMetaData();
			if (params.length < pmd.getParameterCount()) {
				throw new SQLException("参数错误:" + pmd.getParameterCount());
			}
			for (int i = 0; i < params.length; i++) {
				stmt.setObject(i + 1, params[i]);
			}
			stmt.executeUpdate();
			rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				key = rs.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) { // 关闭记录集
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) { // 关闭声明
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) { // 关闭连接对象
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return key;
	}

	/**
	 * 更新
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int update(String sql, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner(ds);
		return qr.update(sql, params);
	}

	/**
	 * 删除
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int delete(String sql,Object... params) throws SQLException{
		return update(sql, params);
	}

	/**
	 * 批量插入
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int[] batchInsert(String sql, Object[][] params) throws SQLException {
		return batchUpdate(sql, params);
	}

	/**
	 * 批量更新
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int[] batchUpdate(String sql, Object[][] params) throws SQLException {
		QueryRunner qr = new QueryRunner(ds);
		return qr.batch(sql, params);
	}

	/**
	 * 批量删除
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static int[] batchDelete(String sql,Object[][] params) throws SQLException{
		return batchUpdate(sql, params);
	}

	/**
	 * 使用Map处理单行记录
	 * @return
	 * @throws SQLException 
	 */
	public static Map<String, Object> queryForMap(String sql,Object... params) throws SQLException{
		QueryRunner qr = new QueryRunner(ds);
		Map<String, Object> rowMap = qr.query(sql, new MapHandler(), params); 
		return rowMap;
	}

	/**
	 * 使用Map处理多行记录
	 * @return
	 * @throws SQLException 
	 */
	public static List<Map<String, Object>> queryForMapList(String sql,Object... params) throws SQLException{
		QueryRunner qr = new QueryRunner(ds);
		List<Map<String, Object>> list = qr.query(sql, new MapListHandler(), params);
		return list;
	}

	/**
	 * 使用Bean处理单行记录
	 * @return
	 * @throws SQLException 
	 */
	public static <T> T queryForBean(String sql,Class<T> clazz,Object... params) throws SQLException{
		QueryRunner qr = new QueryRunner(ds);
		T entity = qr.query(sql, new BeanHandler<T>(clazz), params);
		return entity;
	}

	/**
	 * 使用Bean处理多行记录
	 * @return
	 * @throws SQLException 
	 */
	public static <T> List<T> queryForBeanList(String sql,Class<T> clazz,Object... params) throws SQLException{
		QueryRunner qr = new QueryRunner(ds);
		List<T> list = qr.query(sql, new BeanListHandler<T>(clazz), params);
		return list;
	}

	/**
	 * 统计数量
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static long count(String sql, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner(ds);
		Number num = (Number)qr.query(sql, new ScalarHandler<Object>(){
			@Override
			public Object handle(ResultSet rs) throws SQLException {
				Object obj = super.handle(rs);
				if (obj instanceof BigInteger)
					return ((BigInteger) obj).longValue();
				return obj;
			}
		}, params);
		return num == null?0:num.longValue();
	}

}