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!

Answer 2

You could create a wrapper or parent class that manages the connection, so that you don't have to replicate the logic in each method. Here's an example (note that I haven't actually compiled or tested this).

public interface DAOClass {
    public void execSomeQuery() throws SQLException;
}

public class MyDAOWrapper {
    private DAOClass dao;
    private DataSource ds;

    public MyDAOWrapper(DataSource ds, DAOClass dao) {
        this.dao = dao;
        this.ds = ds;
    }

    public void exec() throws SQLException {
        Connection con = ds.getConnection();
        try {
            dao.execSomeQuery();
        }
        finally {
            con.close();
        }
    }

}

// usage
    public void doSomeStuff() throws SQLException {
        MyDAOWrapper dao = new MyDAOWrapper(ds, new MyDAO());
        dao.exec();
    }

Regarding error handling, you don't need to rethrow an exception unless you catch it. Your finally clause should close the connection (if it exists) and when that exits, the exception will continue propagating up.

try {
  do_something();
}
finally {
  cleanup();
  // throw is not necessary
}