Tomcat JNDI Connection Pool docs - Random Connections Closed Exceptions

Question

I found this in the Tomcat documentation here

I don’t understand why they close all JDBC objects twice - once in the try {} block and once in the finally {} block. Why not just close them once at the end of the {} sentence?

These are the relevant documents:

Random Connections Closed Exceptions

These can occur when one request gets a db connection from the connection pool and closes it twice. When using a connection pool, closing the connection just returns it to the pool for reuse by another request, it doesn't close the connection. And Tomcat uses multiple threads to handle concurrent requests. Here is an example of the sequence of events which could cause this error in Tomcat: Request 1 running in Thread 1 gets a db connection. Request 1 closes the db connection. The JVM switches the running thread to Thread 2 Request 2 running in Thread 2 gets a db connection (the same db connection just closed by Request 1). The JVM switches the running thread back to Thread 1 Request 1 closes the db connection a second time in a finally block. The JVM switches the running thread back to Thread 2 Request 2 Thread 2 tries to use the db connection but fails because Request 1 closed it. Here is an example of properly written code to use a db connection obtained from a connection pool: Connection conn = null; Statement stmt = null; // Or PreparedStatement if needed ResultSet rs = null; try { conn = ... get connection from connection pool ... stmt = conn.createStatement("select ..."); rs = stmt.executeQuery(); ... iterate through the result set ... rs.close(); rs = null; stmt.close(); stmt = null; conn.close(); // Return to connection pool conn = null; // Make sure we don't close it twice } catch (SQLException e) { ... deal with errors ... } finally { // Always make sure result sets and statements are closed, // and the connection is returned to the pool if (rs != null) { try { rs.close(); } catch (SQLException e) { ; } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ; } stmt = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { ; } conn = null; } } 

Answer 1

Usually I just close my connection once in the finally block, and I did not find any problems.

However, double closure may be required in more complex use cases. For example, if you open several statements or several connections in a try block, you want to close them immediately after using it. Then you need to catch any remaining part due to exceptions in the finally block. For instance,

 try { conn = ds1.getConnection(); ... Do something with datasource 1 ... conn.close(); conn=null; conn = ds2.getConnection(); ... Do something with datasource 2 ... conn.close(); conn = null; } catch (SQLException e) { ... deal with errors ... } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { ; } conn = null; } } 

Answer 2

Or you can use Spring -JDBC and forget about all these complications (Spring -JDBC will do this automatically for you)