JDBC connection lifecycle for Connection Pool (Hikari) reuse

Question

Hikari: 2.4.7

PostgreSQL JDBC driver: 9.4-1201-jdbc41

I'm trying to understand what must be done to a java.sql.Connection object for it to be available again in the connection pool?

I've just introduced connection pooling to a multi threaded application that was previously standing up / tearing down connections with each SQL statement.

What I have noticed, after introducing Hikari, is that as soon as I hit maximumPoolSize every attempt thereafter to HikariDataSource.getConnection will fail due to connectionTimeout. So it seems like I'm not "releasing" this connection somehow.

The typical use of the Connection object is:

# omits Exception handling, parameter substitution, result evaluation. 
PreparedStatement preparedStatement = hikariDataSource.getConnection().prepareStatement(sql);
preparedStatement.executeQuery();
preparedStatement.close();

Is there anything else that is expected to be done on this connection to get it eligible for reuse in the connection pool?

Autocommit is on. Connection.close(), unless doing something special when provided by Hikari, seemed like the exact thing I wanted to avoid.

Answer 1

I don't know Hikari specifically, but for every connection you take out of a connection pool, you have to return that connection when you are done with it.

Typically this is done using Connection.close() - the pool hands out a wrapper function where close() doesn't physically close the connection, only returns it.

So your code should look like this:

Connection con = hikariDataSource.getConnection();
PreparedStatement preparedStatement = con.prepareStatement(sql);
preparedStatement.executeQuery();
preparedStatement.close();
con.close(); // this returns the connection to the pool

Of course the two close() methods should be called in a finally block.