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?

Thank you, - MS.

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?