commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <>
Subject Re: [dbcp] is statement pool working?
Date Sun, 02 Jan 2011 20:02:29 GMT
On Sun, Jan 2, 2011 at 9:05 AM, Nishant Ranjan <> wrote:

> Hi,
> To enable statement pooling using DBCP, one needs to provide a
> KeyedObjectPoolFactory while creating a PoolableConnectionFactory. This
> itself
> is a bit complicated and confusing. (see:
> ).

To avoid the "manual" steps to create factories and pools, you can use
BasicDataSource if that suits your needs.  BasicDataSource can be configured
to pool statements and it will set up both the connection pool and the
statement pools automatically.  See the documentation for BasicDataSource to
see if this suits your needs.

>  A reference implementation on how to activate connection pooling has been
> provided.
> // create your connection pool
> GenericObjectPool pool = new GenericObjectPool();
> // and set its properties
> // ..
> // then your connection factory
> ConnectionFactory cf = new DriverManagerConnectionFactory(.....);
> // then a factory for your prepared statements
> KeyedObjectPoolFactory kpf = new GenericKeyedObjectPoolFactory(null, 8);
> // finally, your overall connection factory
> PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, pool,
> kpf,
> null, false, true);
> So far so good.
> Yes.

> However, the problem starts to happen when one wants to use a statement out
> of
> this statement pool. (You would want to use one if you have created one!)
> The
> only way of doing this seems to be by creating a PoolingConnection. This is
> where the problem actually starts.
> There is no way of getting a PoolingConnection from the classes which were
> used
> to create the statement pool including the PoolableConnectionFactory (where
> an
> instance of PoolingConnection is actually created everytime a new
> connection is
> created if a statement pool factory was provided earlier - the source code
> reveals that). However one can get a Connection from the DataSource that
> was
> created by this PooableConnectionFactory and use this Connection to create
> a
> PoolingConnection.
> // suppose we already have a DataSource called dataSource
> Was this DataSource created using the connection pool you created above,
i.e. with a statement like
PoolingDataSource dataSource = new PoolingDataSource(pool)?

Connection conn = dataSource.getConnection();
> PoolingConnection poolConn  = new PoolingConnection (conn);
>  When a connection is created in this way and then
> poolConn.prepareStatement();
> is called, it gives the following error:
> java.sql.SQLException: Statement pool is null - closed or invalid
> PoolingConnection.
>    at
> org.apache.commons.dbcp.PoolingConnection.prepareStatement(
> Why don't you just use conn.prepareStatement()?  Assuming the dataSource
was created as above and pcf was created with a statement pool, dataSource
will automatically pool statements.   The wrapped connection, poolConn that
you created above does not have a statement pool associated with it, which
is why you get the error above.  What is returned by
dataSource.getConnection is a (differently wrapped) PoolingConnection that
has a statement pool.

> However when I implement a semaphore to keep track of the statement pool
> (an
> instance of KeyedObjectPool) during creating of a Connection and then
> provide
> this statement while creating PoolingConnection, there are no errors and
> everything works fine. This also makes sense when I look at the source
> code.
> This makes me think that there has to be some way of retrieving the
> statement
> pool that is created by the PoolableConnectionFactory whenever a new
> Connection
> is created. Am I coming to the right conclusions?

I am not sure why you would want to be able to retrieve the statement pool
from a PoolingConnection, but that is not currently supported.  If you
either use BasicDataSource with statement pooling enabled or follow the -
admittedly complicated - steps above to manually create a datasource that
creates connections with statement pooling, the pooling happens
transparently, i.e., you just use prepareStatement, etc., similarly to the
way the dataSource pools connections transparently when you use


> If so, then I wonder if in the current implementation of DBCP, is statement
> pooling at all working? Should someone raise an issue in JIRA? Can I
> suggest a
> solution?
> As this is the 3rd day that I am using DBCP, I might be missing something
> completely. I would be happy to be relieved of a blind spot.
>  Thanks & Regards,
> Nishant Ranjan

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message