commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nishant Ranjan <>
Subject [dbcp] is statement pool working?
Date Sun, 02 Jan 2011 14:05:12 GMT

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:$9794bf20$0302a8c0@vikram%3E).
 A reference implementation on how to activate connection pooling has been 

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

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

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 

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