How to force reuse of connections in jdbc pool?

Question

I have a Runnable that gets a connection from the connection pool as shown 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 expires after 60 seconds, I assumed that the connection is returning to the pool, and when a new thread is created, the connection can be reused. But when I list my network connections, the list continues to grow as more threads are created. Are the connections created as described above created in the pool correctly, and if so, how can I get the connections to be reused?

Answer 1

You are not actually using a connection pool. A ConnectionPoolDataSource not intended for direct use. It is intended as a (special) DataSource for PooledConnection objects, which are then stored in the connection pool using a (regular) DataSource implementation that provides pooling.

A typical developer should not use ConnectionPoolDataSource directly; it is intended for use with connection pools provided by application servers, or for wrapping in a common DataSource that provides pooling.

When Connection is requested in the connection pool, it checks for an existing PooledConnection (or requests a new one from its ConnectionPoolDataSource ), retrieves the Connection and returns it to the user. When the user closes Connection , PooledConnection will signal the connection pool, which he will be available again.

In this case, you create a PooledConnection , extract the Connection from it, and then drop the PooledConnection . This means that the PooledConnection is denied, and its physical connection to the database cannot be reused and will be closed / dropped when it finally collects garbage (usually when the connection pool wants to close the physical connection, it calls close() on PooledConnection ) .

You need to either use the connection pool as provided by your application server, or use a common connection pool, such as DBCP, c3p0 or BoneCP.

Answer 2

You are not saying that you are using the connection pool, so the answer is "possible."

However, most pools have a way to detect abandoned connections. For example, DBCP provides the removeAbandoned and removeAbandonedTimeout configuration removeAbandonedTimeout . This will not lead to an immediate connection return to the pool, so you will still see the number of connections increasing before the timeout expires (and, hopefully, since you are in a tight loop, you set the maximum on the number of open connections).