Closing JDBC Connections in Pool

Question

Our standard code section for using JDBC is...

Connection conn = getConnection(...);
Statement  stmt = conn.conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
ResultSet  rset = stmt.executeQuery (sqlQuery);

// do stuff with rset

rset.close(); stmt.close(); conn.close();

Question 1: When using Connection Pool, should one close the Connection at the end? If so, isn't the purpose of pooling lost? And if not, how does the DataSource know when a particular instance of Connection is freed up and can be reused? I am a little confused on this one, any pointers appreciated.

Question 2: Is the following method anything close to standard? Looks like an attempt to get a connection from the pool, and if DataSource cannot be established, use the old fashioned DriverManager. We are not even sure which part is getting executed at runtime. Repeating the question above, should one close the Connection coming out of such a method?

synchronized public Connection getConnection (boolean pooledConnection)
                                                        throws SQLException {
        if (pooledConnection) {
                if (ds == null) {
                        try {
                                Context envCtx = (Context)
                                        new InitialContext().lookup("java:comp/env");
                                ds = (DataSource) envCtx.lookup("jdbc/NamedInTomcat");
                                return ds.getConnection();
                        } catch (NamingException e) {
                                e.printStackTrace();
                }}
                return (ds == null) ? getConnection (false) : ds.getConnection();
        }
        return DriverManager.getConnection(
                "jdbc:mysql://"+ipaddy+":"+dbPort +"/" + dbName, uName, pWord);
}

Edit: I think we are getting the pooled connection since we do not see a stack trace.

Answer 1

When using Connection Pool, should one close the Connection at the end? If so, isn't the purpose of pooling lost? And if not, how does the DataSource know when a particular instance of Connection is freed up and can be reused? I am a little confused on this one, any pointers appreciated.

Yes, certainly you need to close the pooled connection as well. It's actually a wrapper around the actual connection. It wil under the covers release the actual connection back to the pool. It's further up to the pool to decide whether the actual connection will actually be closed or be reused for a new getConnection() call. So, regardless of whether you're using a connection pool or not, you should always close all the JDBC resources in reversed order in the finally block of the try block where you've acquired them. In Java 7 this can be further simplified by using try-with-resources statement.

---

Is the following method anything close to standard? Looks like an attempt to get a connection from the pool, and if DataSource cannot be established, use the old fashioned DriverManager. We are not even sure which part is getting executed at runtime. Repeating the question above, should one close the Connection coming out of such a method?

The example is pretty scary. You just need to lookup/initialize the DataSource only once during application's startup in some constructor / initialization of an applicationwide DB config class. Then just call getConnection() on the one and same datasource throughout the rest of application's lifetime. No need for synchronization nor nullchecks.

See also:

• Is it safe to use a static java.sql.Connection instance in a multithreaded system?
• Am I Using JDBC Connection Pooling?

Answer 2

The pools typically return you a wrapped Connection object, where the close() method is overridden, typically returning the Connection to the pool. Calling close() is OK and probably still required.

A close() method would probably look like this:

public void close() throws SQLException {
  pool.returnConnection(this);
}

For your second question, you could add a logger to show whether the bottom block ever runs. I would imagine though you'd only want one way or the other for the configuration of your database connections. We solely use a pool for our database accesses. Either way, closing the connection would be pretty important to prevent leaks.

Answer 3

Actually, the best approach to connection management is to not farm them out to any code anywhere.

Create a SQLExecutor class that is the one and only location which opens and closes connections.

The entire rest of the application then pumps statements into the executor rather than getting connections from the pool and managing (or mismanaging them) all over the place.

You can have as many instances of the executor as you want, but no one should be writing code that opens and closes connections on its own behalf.

Conveniently, this also lets you log all your SQL from a single set of code.