Database Connection Pool API.

Overview in Dialog Form

Q: How do I use the DBCP package?

A: There are two primary ways to access the DBCP pool, as a {@link java.sql.Driver Driver}, or as a {@link javax.sql.DataSource DataSource}. You'll want to create an instance of {@link org.apache.commons.dbcp.PoolingDriver} or {@link org.apache.commons.dbcp.PoolingDataSource}. When using one of these interfaces, you can just use your JDBC objects the way you normally would. Closing a {@link java.sql.Connection} will simply return it to its pool.

Q: But {@link org.apache.commons.dbcp.PoolingDriver PoolingDriver} and {@link org.apache.commons.dbcp.PoolingDataSource PoolingDataSource} both expect an {@link org.apache.commons.pool.ObjectPool ObjectPool} as an input. Where do I get one of those?

A: The {@link org.apache.commons.pool.ObjectPool ObjectPool} interface is defined in the {@link org.apache.commons.pool} package (Commons-Pool). The {@link org.apache.commons.pool.impl} package has a couple of implementations, and you can always create your own.

Q: Ok, I've found an {@link org.apache.commons.pool.ObjectPool ObjectPool} implementation that I think suits my connection pooling needs. But it wants a {@link org.apache.commons.pool.PoolableObjectFactory PoolableObjectFactory}. What should I use for that?

A: The DBCP package provides a class for this purpose. It's called {@link org.apache.commons.dbcp.PoolableConnectionFactory}. It implements the factory and lifecycle methods of {@link org.apache.commons.pool.PoolableObjectFactory} for {@link java.sql.Connection}s. But it doesn't create the actual database {@link java.sql.Connection}s itself, it uses a {@link org.apache.commons.dbcp.ConnectionFactory} for that. The {@link org.apache.commons.dbcp.PoolableConnectionFactory} will take {@link java.sql.Connection}s created by the {@link org.apache.commons.dbcp.ConnectionFactory} and wrap them with classes that implement the pooling behaviour.

Several implementations of {@link org.apache.commons.dbcp.ConnectionFactory} are provided--one that uses {@link java.sql.DriverManager} to create connections ({@link org.apache.commons.dbcp.DriverManagerConnectionFactory}), one that uses a {@link java.sql.Driver} to create connections ({@link org.apache.commons.dbcp.DriverConnectionFactory}), one that uses a {@link javax.sql.DataSource} to create connections ({@link org.apache.commons.dbcp.DataSourceConnectionFactory}).

Q: I think I'm starting to get it, but can you walk me though it again?

A: Sure. Let's assume you want to create a {@link javax.sql.DataSource} that pools {@link java.sql.Connection}s. Let's also assume that that those pooled {@link java.sql.Connection}s should be obtained from the {@link java.sql.DriverManager}. You'll want to create a {@link org.apache.commons.dbcp.PoolingDataSource}.

The {@link org.apache.commons.dbcp.PoolingDataSource} uses an underlying {@link org.apache.commons.pool.ObjectPool} to create and store its {@link java.sql.Connection}.

To create a {@link org.apache.commons.pool.ObjectPool}, you'll need a {@link org.apache.commons.pool.PoolableObjectFactory} that creates the actual {@link java.sql.Connection}s. That's what {@link org.apache.commons.dbcp.PoolableConnectionFactory} is for.

To create the {@link org.apache.commons.dbcp.PoolableConnectionFactory}, you'll need at least two things:

1. A {@link org.apache.commons.dbcp.ConnectionFactory} from which the actual database {@link java.sql.Connection}s will be obtained.
2. An empty and factory-less {@link org.apache.commons.pool.ObjectPool} in which the {@link java.sql.Connection}s will be stored.
   When you pass an {@link org.apache.commons.pool.ObjectPool} into the {@link org.apache.commons.dbcp.PoolableConnectionFactory}, it will automatically register itself as the {@link org.apache.commons.pool.PoolableObjectFactory} for that pool.

You can optionally provide a {@link org.apache.commons.pool.KeyedObjectPoolFactory} that will be used to create {@link org.apache.commons.pool.KeyedObjectPool}s for pooling {@link java.sql.PreparedStatement}s for each {@link java.sql.Connection}.

In code, that might look like this:

    GenericObjectPool connectionPool = new GenericObjectPool(null);
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:some:connect:string", "username", "password");
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
    PoolingDataSource dataSource = new PoolingDataSource(connectionPool);

To create a {@link org.apache.commons.dbcp.PoolingDriver}, we do the same thing, except that instead of creating a {@link javax.sql.DataSource} on the last line, we create a {@link org.apache.commons.dbcp.PoolingDriver}, and register the `connectionPool` with it. E.g.,:

    GenericObjectPool connectionPool = new GenericObjectPool(null);
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:some:connect:string", "username", "password");
    PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
    PoolingDriver driver = new PoolingDriver();
    driver.registerPool("example",connectionPool);

Since the {@link org.apache.commons.dbcp.PoolingDriver} registers itself with the {@link java.sql.DriverManager} when it is created, now you can just go to the {@link java.sql.DriverManager} to create your {@link java.sql.Connection}s, like you normally would:

    Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:example");

Q: Sounds complicated, is there an easier way?

A: If you're using the {@link org.apache.commons.dbcp.PoolingDriver}, you don't need to do this configuration in code. Instead, you can provide a JOCL document that describes the connection pool, and let the {@link org.apache.commons.dbcp.PoolingDriver} discover it at runtime.

Specifically, if the {@link org.apache.commons.dbcp.PoolingDriver} is asked for a {@link java.sql.Connection} from a pool that has not yet been registered, it will look for a named resource from which to read the pool's configuration, and create that pool.

For example, suppose you create a pool named "`/eg`" from a JOCL document. The "connect string" for this pool will be "`jdbc:apache:commons:dbcp:/eg`". To do this, you'll need a create a resource (just a file in your classpath) containing a JOCL description of the pool. Specifically, this JOCL document should define a {@link org.apache.commons.dbcp.PoolableConnectionFactory} from which the pool will be obtained. For example:

    <object class="org.apache.commons.dbcp.PoolableConnectionFactory" xmlns="http://apache.org/xml/xmlns/jakarta/commons/jocl">
       <!-- the first argument is the ConnectionFactory -->
       <object class="org.apache.commons.dbcp.DriverManagerConnectionFactory">
          <string value="jdbc:some:connect:string"/>
          <object class="java.util.Properties" null="true"/>
       </object>
       <!-- the next argument is the ObjectPool -->
       <object class="org.apache.commons.pool.impl.GenericObjectPool">
          <object class="org.apache.commons.pool.PoolableObjectFactory" null="true"/>
          <int value="10"/> <!-- max active -->
          <byte value="1"/> <!-- when exhausted action, 0 = fail, 1 = block, 2 = grow -->
          <long value="2000"/> <!-- max wait -->
          <int value="10"/> <!-- max idle -->
          <boolean value="false"/> <!-- test on borrow -->
          <boolean value="false"/> <!-- test on return -->
          <long value="10000"/> <!-- time between eviction runs -->
          <int value="5"/> <!-- number of connections to test per eviction run -->
          <long value="5000"/> <!-- min evictable idle time -->
          <boolean value="true"/> <!-- test while idle -->
       </object>
       <!-- the next argument is the KeyedObjectPoolFactory -->
       <object class="org.apache.commons.pool.impl.StackKeyedObjectPoolFactory">
          <int value="5"/> <!-- max idle -->
       </object>
       <string value="SELECT COUNT(*) FROM DUAL"/> <!-- validation query -->
       <boolean value="false"/> <!-- default read only -->
       <boolean value="true"/> <!-- default auto commit -->
    </object>

Simply save that file somewhere in your classpath as `eg.jocl`, and the {@link org.apache.commons.dbcp.PoolingDriver} will find it automatically. You need only register the {@link org.apache.commons.dbcp.PoolingDriver} (for example, using the `jdbc.drivers` property), and use the the {@link java.sql.DriverManager} to create your {@link java.sql.Connection}s, like you normally would:

    Connection conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:/eg");

(Note that without the leading slash, the pool must be located at `org/apache/commons/dbcp/PoolingDriver/eg.jocl` within your classpath. See {@link java.lang.Class#getResource} for details.)