C3P0 causing threads to close

Question

I have a weird problem. I have an utility class that holds the connection and has function that prepares the statements. The statements are then ran, closed, and everything works fine.

However, when I tried adding a connection pool using ComboPooledDataSource, my threads close. I debugged it and I can see that some queries are executed successfully, but then all of a sudden everything closes and only the c3p0 threads remain running. No exception is thrown.

I tried setting the pool to a single connection to mimic the working code as closely as possible but that also fails. If I set the connection member to the connection from the pool everything works fine, but if I try to using directly from the pool I get the behavior I outlined above.

Here is some sample code:

class DBUtilityClass
{
   private java.sql.Connection connection;
   private ComboPooledDataSource connectionPool;

   public void DBUtilityClass()
   {
      connect();
   }

   private void connect()
   {
      connectionPool = new ComboPooledDataSource();
      connectionPool.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver   
      connectionPool.setJdbcUrl(urlString.toString());
      connectionPool.setUser(user);
      connectionPool.setPassword(password);
      connectionPool.setAutoCommitOnClose(true);

      connectionPool.setInitialPoolSize(1);
      connectionPool.setMinPoolSize(1);
      connectionPool.setAcquireIncrement(1);
      connectionPool.setMaxPoolSize(1);


   }

   //Version 1 - this works if I set the connection in the constructor 
   //or if I connect the connection manually without using the pool
   public Connection getConnection()
   {
      connection.setAutoCommit(true);
      return connection;
   }

   //Version 2 - This fails
   public Connection getConnection()
   {
      Connection temp = connectionPool.getConnection();
      temp.setAutoCommit(true);
      return temp;
   }

   public PreparedStatement getPreparedStatement(String sql)
   {
      Connection temp = getConnection();
      return temp.prepareStatement(sql);
   }
}

Answer 1

The function public PreparedStatement getPreparedStatement(String sql) in your class is a Connection leak. Each time it is called, a Connection gets taken from the pool, but the the reference is dropped, so it is never close()ed and returned to the pool.

(I'm sorry in our comment thread above it took me so long to see this!)

When there is one shared Connection, it's no problem, one Connection stays checked out. But when, as you should with a pool, you checkout Connections just-in-time and don't cache them, you have to be sure to close() them when you are done.

Make sure that every call to getConnection() is matched by a call to close(). The easiest way to do this is

1. Don't use a getPreparedStatement(...)` function here, just work with Connections
2. Use try-with-resources

try( Connection conn = myDBUtilityInstance.getConnection ) {
  try( PreparedStatement ps = conn.prepareStatement( sql ) ) {
    // do work here
  }
}