DBCP 数据库连接池

DBCP连接池介绍

目前 DBCP 有两个版本分别是 1.3 和 1.4。

DBCP 1.3 版本需要运行于 JDK 1.4-1.5 ，支持 JDBC 3。

DBCP 1.4 版本需要运行于 JDK 1.6 ，支持 JDBC 4。

1.3和1.4基于同一套源代码，含有所有的bug修复和新特性。因此在选择DBCP版本的时候，要看你用的是什么JDK版本。

DBCP1.2版本性能一般，比c3p0差挺多。DBCP1.4和1.3，配合（依赖）commons pool 1.6的jar包,各方面功能、性能推进到新的高峰。相对1.2版本提高不少。超越(或相当)了c3p0.建议使用DBCP1.4或1.3 +  commons pool 1.6

 

Tomcat7 中保留DBCP连接池，以兼容已有应用。并提供了新的Tomcat JDBC pool作为DBCP的可选替代。新出的Tomcat JDBC pool，据说比DBCP 1.4要好，未接触

初始化连接池方法：

	private void setupDriver() throws Exception {
		Class.forName(config.driverName);
		org.apache.commons.pool.impl.GenericObjectPool.Config cfg = new org.apache.commons.pool.impl.GenericObjectPool.Config();
		cfg.maxActive = config.maxActiveConnections;
		cfg.maxIdle = config.maxIdleConnections;
		cfg.testOnBorrow = true;
		if (config.exhaustedPoolAction.equalsIgnoreCase("GROW"))
			cfg.whenExhaustedAction = 2;
		else if (config.exhaustedPoolAction.equalsIgnoreCase("FAIL"))
			cfg.whenExhaustedAction = 0;
		else if (config.exhaustedPoolAction.equalsIgnoreCase("BLOCK")) {
			cfg.whenExhaustedAction = 1;
			cfg.maxWait = config.blockTime;
		}
		ObjectPool connectionPool = new GenericObjectPool(null, cfg);
		org.apache.commons.dbcp.ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
				config.connectionString, config.userName, config.password);
		new PoolableConnectionFactory(connectionFactory, connectionPool, null,
				config.testSql, false, true);
		PoolingDriver driver = new PoolingDriver();
		driver.registerPool(name, connectionPool);
	}

配置类：

public class DBConfig {
	public static final String POOL_ACTION_FAIL = "FAIL";

	public static final String POOL_ACTION_BLOCK = "BLOCK";
	public static final String POOL_ACTION_GROW = "GROW";
	public boolean active = false;
	public String driverName;
	public String connectionString;
	public String userName;
	public String password;
	public String testSql;
	public int maxActiveConnections = 10;
	public int maxIdleConnections = 10;
	public String exhaustedPoolAction = "FAIL";
	public int blockTime = 3000;

	public DBConfig() {
		this.driverName = "";
		this.connectionString = "";
		this.userName = "";
		this.password = "";
		this.testSql = "";
	}
}

获取链接方法：

public Connection getConnection() throws SQLException {
		return DriverManager.getConnection((new StringBuilder(
				"jdbc:apache:commons:dbcp:")).append(name).toString());
	}

插入数据库方法：

public Object executeInsert(String sql, Object params[])
        throws SQLException
    {
        Connection conn;
        PreparedStatement stmt;
        Object id;
        checkState();
        conn = null;
        stmt = null;
        id = null;
        conn = getConnection();
        stmt = conn.prepareStatement(sql, 1);
        if(params != null)
        {
            int index = 1;
            Object aobj[];
            int j = (aobj = params).length;
            for(int i = 0; i < j; i++)
            {
                Object o = aobj[i];
                stmt.setObject(index++, o);
            }


        }
        if(log.isDebugEnabled())
            log.debug((new StringBuilder("ExecuteInser SQL: ")).append(stmt.toString()).toString());
        stmt.executeUpdate();
        ResultSet generatedKeys = stmt.getGeneratedKeys();
        if(generatedKeys.next())
            id = generatedKeys.getObject(1);
        else
            throw new SQLException((new StringBuilder("INSERT failed: ")).append(stmt.toString()).toString());
//        break MISSING_BLOCK_LABEL_214;
//        Exception exception;
//        exception;
        if(stmt != null)
            stmt.close();
        if(conn != null)
            conn.close();
//        throw exception;
        if(stmt != null)
            stmt.close();
        if(conn != null)
            conn.close();
        return id;
    }

更新数据库方法:

public void executeUpdate(String sql, Object params[])
        throws SQLException
    {
        Connection conn;
        PreparedStatement stmt;
        checkState();
        conn = null;
        stmt = null;
        conn = getConnection();
        stmt = conn.prepareStatement(sql);
        if(params != null)
        {
            int index = 1;
            Object aobj[];
            int j = (aobj = params).length;
            for(int i = 0; i < j; i++)
            {
                Object o = aobj[i];
                stmt.setObject(index++, o);
            }

        }
        if(log.isDebugEnabled())
            log.debug((new StringBuilder("ExecuteUpdate SQL: ")).append(stmt.toString()).toString());
        stmt.executeUpdate();
        
        if(stmt != null)
            stmt.close();
        if(conn != null)
            conn.close();

        return;
    }

 查询数据库方法： 

public ISFSArray executeQuery(String sql, Object params[])
			throws SQLException {
		ISFSArray sfsa;
		Connection conn;
		PreparedStatement stmt;
		checkState();
		sfsa = null;
		conn = null;
		stmt = null;
		conn = getConnection();
		stmt = conn.prepareStatement(sql);
		if (params != null) {
			int index = 1;
			Object aobj[];
			int j = (aobj = params).length;
			for (int i = 0; i < j; i++) {
				Object o = aobj[i];
				stmt.setObject(index++, o);
			}

		}
		if (log.isDebugEnabled())
			log.debug((new StringBuilder("ExecuteQuery SQL: ")).append(
					stmt.toString()).toString());
		ResultSet resultSet = stmt.executeQuery();
		if (resultSet != null)
			sfsa = SFSArray.newFromResultSet(resultSet);
		
		if (stmt != null)
			stmt.close();
		if (conn != null)
			conn.close();
		return sfsa;
	}

销毁：

public void destroy(Object o) {
		try {
			PoolingDriver driver = (PoolingDriver) DriverManager
					.getDriver("jdbc:apache:commons:dbcp:");
			driver.closePool(name);
		} catch (SQLException sqle) {
			log.warn(String.format(
					"Failed shutting down DBManager: %s, Reason: %s",
					new Object[] { name, sqle.toString() }));
		}
	}

官网：https://commons.apache.org/proper/commons-dbcp/