Standard way to setup JDBC connection pooling?

[Aquila Deus]

Does such a thing exist?? Or any de-facto standard way?

 

[Mario Winterer]

Hi!

That depends upon your environment. If you are using e.g. Tomcat webserver, connection pooling is already included if you use JNDI
datasources.
If you want to do connection pooling without any special environment (e.g. a simple application based on J2SE), you should consider
to take a ready-to-use connection-pool implementation.
Some JDBC-driver already implement connection pooling or at least support it, but many others don't.

An easy-to-use connection pool that is open source can be found at http://jakarta.apache.org/commons/dbcp/. This library requires an
implementation of an object pool which can be found at http://jakarta.apache.org/commons/pool/.

The following code shows how to create a DataSource with DBCP that uses a connection-pool for the connections:

// create object pool (see API docs for configuration details)
GenericObjectPool connectionPool = new GenericObjectPool(null);
// create a factory that 'produces' JDBC connections
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:some:connect:string", "username", "password");
// create a factory that reuses connection from the pool or creates new ones using the connectionFactory
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null,
false, true);
// create a pooling JDBC DataSource (that uses the pool configured above)
PoolingDataSource dataSource = new PoolingDataSource(connectionPool);

// Now get connections from the dataSource. The connections will be pooled!
// Do not forget to close every connection after use to let the pool do its work!

The fine thing is that those four lines above can be modified for maximum flexibility. So you can use the DriverManager for getting
connections (as above), or a DataSource that you've created before. You can create a DataSource that pools connections or you can
create a pooling JDBC Driver and register it with the DriverManager:

// replace the last line above (PoolingDataSource dataSource = ...):
// create pooling driver
PoolingDriver driver = new PoolingDriver();
// register driver with DriverManager
driver.registerPool("example",connectionPool);

// now you can open pooled connections by using the DriverManager:
Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

Tip: Maybe it is wise to use a higher-level API for accessing database (instead of low level JDBC). Hibernate is a very good
Object/Relational layer that also supports connection pooling!


Best regards,
Tex

 

[huy]

Does such a thing exist?? Or any de-facto standard way?

Try

http://jakarta.apache.org/commons/dbcp/

Regards,

Huy

 

[anonymous]

Does such a thing exist?? Or any de-facto standard way?

YMMV.

We have mixed success with Oracle OCI driver and 9i, 10G. We have also
tried their datasource driver, but it seems to be hard to get it right.