Closing JDBC connections in the final block only?

Question

Why database connections are often closed in two positions, immediately after use, and secondly, additionally in the final block using a zero check to prevent them from being closed twice. Isn't it enough to use a finally block? The final block must be executed in each case.

Here is the official Apache-Tomcat JNDI Datasource HOW-TO example. They indicate that the connection should be closed under any circumstances. I wonder why it’s not enough to use a finally block, because closed commands at the end of the main try {} block seem redundant.

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 ignore) { } rs = null; } if (stmt != null) { try { stmt.close (); } catch (SQLException ignore) { } stmt = null; } if (conn != null) { try { conn.close (); } catch (SQLException ignore) { } conn = null; } } 

I would like to write a lot shorter:

  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 ... } catch (SQLException e) { // ... deal with errors ... } finally { // Always make sure result sets and statements are closed, // and the connection is returned to the pool try { if (rs != null) rs.close (); if (stmt != null) stmt.close (); if (conn != null) conn.close (); } catch (SQLException ignore) { } } 

Answer 1

You have a good question - I don’t understand the “official example” either. Finally, a block, of course, is enough.

However, your code has more serious errors, namely, if rs.close () throws an exception, you will have a stmt and conn leak, and you will also ignore this exception silently. This is what you should not do. Since Java 7, using the try-with-resources construct, is the preferred way, but if you cannot get there, at least handle each possible exception (rs, stmt, conn) separately so that they do not cause each other to leak.

For example, for this purpose, DbUtils has closeQuietly () just because it was a normal script. Personally, I would go somewhere like Spring's JDBCTemplate , which does similar things behind the scenes.

Edit: try-with-resources is explained by Oracle here . In short, you would do it something like this:

 try (Connection conn = yourCodeToGetConnection(); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query)) { while (rs.next()) { String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); } } catch (SQLException ex) { // log, report or raise } 

If the try statement automatically handles conn , stmt and rs closures in all cases and order (in the reverse order in which you specify them). Possible exceptions that you still need to resolve.

Answer 2

Thanks for the many comments. So, to summarize (especially the EJP comments on my question [that closing a connection closes basic statements and closes an expression, it closes a ResultSet itself]), and since I find the try-with-resource construct a little difficult to read, suggesting to write

 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 ... } catch (SQLException e) { // ... deal with errors ... } finally { // Always make sure result sets and statements are closed, // and the connection is returned to the pool try { if (conn != null) conn.close (); } catch (SQLException ignore) { } } 

and closing only the main connection, leaving the expression and the ResultSet always unaffected and closed by the connection.

Right?

Answer 3

I would rather write a general method for closing a connection. What can be called from the finally block.

Something like that

 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 ... } catch (SQLException e) { // ... deal with errors ... } finally { CloseTheConnection(conn); CloseTheStatement(stmt); } public void closeTheConnection(Connection conn){ try{ if(conn!=null){ conn.close(); } }catch(Exception ex){ } pubic void closeTheStatement(Statement stmt){ try{ if( stmt != null) stmt.close(); } catch(Exception ex){ } } 

Creating various methods and calling them will finally guarantee that even if you get any exception from one method, another method will be called. And it will be reusable.