Make sure objects are closed if an exception is thrown

Question

I am creating a JDBC Statement and ResultSet s.

Findbugs rightly indicates that I am not close if an exception occurs.

So now I have:

 Statement stmt = null; ResultSet res = null; try { stmt = ... res = stmt.executeQuery(...); ... } finally { try { if(res != null) res.close(); // <-- can throw SQLException } finally { if(stmt != null) stmt.close(); } } 

(Only I rather have more result sets and prepared statements, etc. openly ... so my finally nesting is pretty much deeper)

Is there a better way to close a large number of result sets?

(Also, on Symbian, they never allow the destructor / close / release / delete -type methods to cause any errors. I think this is a very good design decision. The fact that all close methods in JDBC can throw SQLException makes things unnecessarily complicated in my opinion.)

Answer 1

If you are using Java 7, you can take advantage of the fact ResultSet extends AutoCloseable and use try-with-resources .

 try (Statement sql = <WHATEVER>; ResultsSet res = sql.executeQuery(<WHATEVER>)) { // Use results } 

At least you are avoiding finally clauses.

Answer 2

In code where I can rely on Java 7, I would probably use try-with-resources as suggested by Duncan Jones .

There are two approaches in my previous code that I used:

The first is a set of helper methods in a static helper class, in this form:

 public static final Statement quietClose(Statement s) { if (s != null) { try { s.close(); } catch (Exception e) { // Do some useful logging here } } return null; } 

Then in the finally block:

 stmt = Helper.quietClose(stmt); 

The second approach was to use LinkedList , add things to it in the order that I opened them, and then have an assistant who would loop in the reverse order and basically do it.

---

In all cases, I try to keep the methods short enough so that in the end there are no 18 different JDBC objects that I need to close. (I say that I "aspire" ... I do not always succeed.)

Answer 3

Instead, write Apache Commons DbUtils.closeQuietly () .

This will close the combinations of ResultSets , Statements and Connections , handling zeros along the way. Note that it does not handle multiple ResultSets , but is useful otherwise.

Answer 4

David M. Lloyd's blog post from a few years ago covers this well, exploring options and establishing a nesting scheme new try / finally directly above each resource created. In our example, something like this:

 Statement stmt = null; ResultsSet res = null; try { stmt = ... try { res = stmt.executeQuery(...); ... } finally { try { res.close(); } catch (Throwable t) { t.printStackTrace(); } } } finally { try { stmt.close(); } catch (Throwable t) { t.printStackTrace(); } } 

If you are following this route, it is recommended that you also follow David’s advice and create a secure resource closure method that you can use throughout your project. You can then call this method instead of try / catch blocks in finally blocks.

 // put this anywhere you like in your common code. public static void safeClose(Closeable c) { try { c.close(); } catch (Throwable t) { // Resource close failed! There only one thing we can do: // Log the exception using your favorite logging framework t.printStackTrace(); } } 

Answer 5

To expand the reporter clause, keep a list of items to close. Create a closure method that catches Throwable and returns Throwable . Rename the list to which any returned Throwables will be entered and discarded first.

 private Throwable close(Closable c){ try{ c.cloase(); return null; } catch (Throwable t){ return t; }; } private void workMethod(){ try{ }finally{ List<Throwable> ts = new ArrayList<Throwable>(); for(Closable c : closables){ Throwable T = close(c); if (t != null) ts.add(t); } if (!ts.isEmpty()) throw ts.get(0); } }