java - How to force re-use of connections in jdbc pool?

Question

I've got a Runnable which gets a connection from a connection pool as below and has 60 seconds to do something with the connection:

private static ConnectionPoolDataSource cpds; // MysqlConnectionPoolDataSource

public void run(){
    while((System.currentTimeMillis()-created)<60000){
        try(Connection conn = cpds.getPooledConnection().getConnection()){  
            //do something
        }catch(SQLException sqle){}
    }
}

When the thread dies after 60s, i've assumed the connection is returned to the pool and when a new thread is created the connection can be re-used. But when I list my network connections, the list keeps growing as more threads are created. Are connections created as above being returned to the pool correctly and if so how can I force the connections to be re-used ?

Answer 1

You are not actually using a connection pool. A ConnectionPoolDataSource isn't intended to be used directly. It is intended as a (special) DataSource for PooledConnection objects which are then kept in a connection pool by a (normal) DataSource implementation that provides connection pooling.

A normal developer should not use a ConnectionPoolDataSource directly, it is intended for use with connection pools provided by Application Servers, or to be wrapped into general purpose DataSources that provided connection pooling.

When a Connection is requested from the connection pool, it will checkout an existing PooledConnection (or request a new one from its ConnectionPoolDataSource), retrieve a Connection and return that to the user. When the user closes the Connection, the PooledConnection will signal the connection pool that it is available again.

In this case you are creating a PooledConnection, retrieving a Connection from it and then discarding the PooledConnection. This means that the PooledConnection gets abandoned, and its physical connection to the database cannot be reused and will be closed/discarded when it is finally garbage collected (normally when the connection pool wants to close the physical connection, it will call close() on the PooledConnection).

You either need to use connection pooling as provided by your Application Server, or use a general purpose connection pool like DBCP, c3p0 or BoneCP.