[dbcp] is statement pool working?

[Nishant Ranjan <ni...@yahoo.com>]

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: 
http://mail-archives.apache.org/mod_mbox/commons-user/200503.mbox/%3C003301c52797$9794bf20$0302a8c0@vikram%3E).
 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.

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

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(PoolingConnection.java:103)


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? 


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

Re: [dbcp] is statement pool working?

[Phil Steitz <ph...@gmail.com>]

On Sun, Jan 2, 2011 at 9:05 AM, Nishant Ranjan <
nishant_kaunve_ranjan@yahoo.com> 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:
>
> http://mail-archives.apache.org/mod_mbox/commons-user/200503.mbox/%3C003301c52797$9794bf20$0302a8c0@vikram%3E
> ).
>

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(PoolingConnection.java:103)
>
> 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
getConnection.

Phil

>
>
> 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
>
>