Order in closing resources

Question

Should I close the expression before connecting? And the results before the expression? Or is it the other way around?

Connection conn = null; Statement st = null; Resultset rs = null; try { // Do stuff } catch (SQLException e) { // Do stuff } finally { if (rs != null) rs.close(); if (st != null) st.close(); if (conn != null) conn.close(); } 

or

 Connection conn = null; Statement st = null; Resultset rs = null; try { // Do stuff } catch (SQLException e) { // Do stuff } finally { if (conn != null) conn.close(); if (st != null) st.close(); if (rs != null) rs.close(); } 

Answer 1

Close the result set, then the statement, then the connection.

In other words, close everything on a last-in-first basis.

Answer 2

You must close the resources in the reverse order that you opened (as if these resources are on the stack).

In Java 7, try-with-resources is the ideal way:

 try ( Connection conn = somethingThatGetsAConnection(); Statement st = conn.createStatement(); Resultset rs = st.executeQuery("SELECT something"); ) { // Do stuff } catch (SQLException e) { // Do stuff } 

And Java will take care of this for you, and it will close the resource in reverse order. See also Oracle trial resource tutorial :

Note that the methods of "closing" resources are called in the opposite order of their creation.

You can find a more detailed look at try-with-resources in Better Resource Management with Java SE 7: Beyond Syntactic Sugar

The Java language specification for Java 7 is mentioned in section 14.20.3 :

Resources are initialized in order from left to right. If the resource is not initialized (that is, its initializer expression throws an exception), then all resources initialized so far with the try -with-resources operator are closed. If all resources are initialized successfully, the try block executes as usual, and then all non-zero resources of the try -with-resources try are closed.

Resources are closed in the reverse order from the one in which they were initialized. A resource is closed only if it is initialized with a value other than zero. Exclusion from the closure of one resource does not prevent the closure of other resources.

This can also be seen as a clear indication that Java developers are considering closing resources in the reverse order, where they highlight the norm.

Answer 3

ResultSet , Statement , and then Connection . The golden rule for JDBC connections and applications is closing in the reverse order of initiation or opening. In addition, the ResultSet depends on the execution of the Statement , and the Statement depends on the Connection instance. Therefore, closing should occur in this order (ResultSet, Statement, Connection).

Answer 4

The first example is the right way. The problem with any other order is that closing Statement also automatically closes any underlying ResultSet (and the same could happen for Connection ) - so you need to close one of the bottom ones in the hierarchy first.

The close() methods can throw a SQLException , as pointed out by @aubin. One simple solution to this problem is to use the DBUtils closeQuietly() method to close them - then you don’t even need to check for zero

Answer 5

To solve this problem with minimal effort, try using Java 7 new ARM (Automatic Resource Management) Blocks, also known as "Try-With-Resources".

 try (Connection conn = null, Statement st = null, ResultSet rs = null){ // Do stuff } catch (SQLException e) { // Do stuff } 

No ugly Finally or worry about the correct order, Java will take care of this for you.

Additional information regarding ARM / Try-With-Resources blocks: http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html