Java Connection Pooling - garbage collection or call close()?

Question

My fear is that I have a fundamental issue with understanding connection pooling in Java.

I'm using IDBCDataSource as a connection pool. At the entry point of my application I instantiate a BasicDataSource with for instance setMaxActive=50. The instance of that DataSource is than handed into various DAOs that are utilized by some business logic.

Each DAO calls getConnection(), but there is no single close() called. My assumption is that after a DAO is not used the garbage collector closes the connections.

My issue is that Im constantly running out of connections (i.e. code waiting for an available connection).

Now lets say I would add a close() call at the end of each database operation. What happens with thrown Exceptions. I would have to catch every Exception in the DAO, make sure to close the connection and then re-throw the occurred Exception!

Example - Current Approach:

public class MyDAO {
     private Connection con;

     public MyDAO (DataSource ds) {
          con = ds.getConnection();     
     }     

     public MyReturnClass execSomeQuery() throws SQLException {
          String sql = String.format("SELECT * FROM foo");
          PreparedStatement ps = con.prepareStatement(sql);
          ResultSet rs = ps.executeQuery();

          while (rs.next()) {
               …     
               ...
          }          

          return result;
     }
}


public class MyAppLogic() {
     DataSource ds; 

     public MyAppLogic(DataSource ds) {
          this.ds = ds;
     }

     public void doSomeStuff() {
          MyDAO myDAO = MyDAO(ds);
          myDAO.execSomeQuery();
     }
}

Answer 1

You need to close the connections so that they return in the connection pool. GC will not call close on your connections!