采用JDBC连接池遇到的问题

最近采用多线程使用JDBC连接数据库时，遇到如下问题，当连接次数达到一定次数后，就出现了如下错误提示

17:18:14,569 DEBUG BasicResourcePool:1831 - An exception occurred while acquiring a poolable resource. Will retry.

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException : Too many connections

    at com.mysql.jdbc.SQLError.createSQLException( SQLError.java:921 )

    at com.mysql.jdbc.MysqlIO.checkErrorPacket( MysqlIO.java:2822 )

    at com.mysql.jdbc.MysqlIO.checkErrorPacket( MysqlIO.java:777 )

    at com.mysql.jdbc.MysqlIO.secureAuth411( MysqlIO.java:3216 )

    at com.mysql.jdbc.MysqlIO.doHandshake( MysqlIO.java:1147 )

    at com.mysql.jdbc.Connection.createNewIO( Connection.java:2595 )

    at com.mysql.jdbc.Connection.<init>( Connection.java:1509 )

    at com.mysql.jdbc.NonRegisteringDriver.connect( NonRegisteringDriver.java:266 )

    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection( DriverManagerDataSource.java:135 )

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection( WrapperConnectionPoolDataSource.java:182 )

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection( WrapperConnectionPoolDataSource.java:171 )

    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource( C3P0PooledConnectionPool.java:137 )

    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire( BasicResourcePool.java:1014 )

    at com.mchange.v2.resourcepool.BasicResourcePool.access$800( BasicResourcePool.java:32 )

    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run( BasicResourcePool.java:1810 )

    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run( ThreadPoolAsynchronousRunner.java:547 )

17:18:14,569 DEBUG BasicResourcePool:404 - incremented pending_acquires: 3

17:18:14,584 DEBUG BasicResourcePool:1831 - An exception occurred while acquiring a poolable resource. Will retry.

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException : Data source rejected establishment of connection,  message from server: "Too many connections"

    at com.mysql.jdbc.SQLError.createSQLException( SQLError.java:921 )

    at com.mysql.jdbc.MysqlIO.doHandshake( MysqlIO.java:962 )

    at com.mysql.jdbc.Connection.createNewIO( Connection.java:2595 )

    at com.mysql.jdbc.Connection.<init>( Connection.java:1509 )

    at com.mysql.jdbc.NonRegisteringDriver.connect( NonRegisteringDriver.java:266 )

    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection( DriverManagerDataSource.java:135 )

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection( WrapperConnectionPoolDataSource.java:182 )

    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection( WrapperConnectionPoolDataSource.java:171 )

    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource( C3P0PooledConnectionPool.java:137 )

    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire( BasicResourcePool.java:1014 )

    at com.mchange.v2.resourcepool.BasicResourcePool.access$800( BasicResourcePool.java:32 )

    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run( BasicResourcePool.java:1810 )

    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run( ThreadPoolAsynchronousRunner.java:547 )

这个是已经达到了数据库得最大连接个数，解决的方法可以设置数据库得max_connections的个数。另外一种就是可以采取连接池得方式，从连接池中获取Connection对象，由连接池来管理和数据库得连接。

c3p0-0.9.1.1就是提供了一个连接池得管理，c3p0 - JDBC3 Connection and Statement Pooling。

下栽网址   http://sourceforge.net/projects/c3p0/

使用得时候，把解压后lib/c3p0-0.9.1.1.jar 加入到ClASSPATH中中就行了。在里面提供了几个Example

线程和非线程的

DataSource ds_unpooled = DataSources.unpooledDataSource("jdbc:postgresql://localhost/testdb", "swaldman", "test-password");

DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled ); 你还可以自己设置数据源的一些属性

Map overrides = new HashMap();

overrides.put("maxStatements", "200");

DataSource ds_pooled = DataSources.pooledDataSource( ds_unpooled, overrides );

获取Connection对象

Connection conn = ds_pooled.getConnection();