views:

6468

answers:

8

Consider the code:

PreparedStatement ps = null;
ResultSet rs = null;
try {
  ps = conn.createStatement(myQueryString);
  rs = ps.executeQuery();
  // process the results...
} catch (java.sql.SQLException e) {
  log.error("an error!", e);
  throw new MyAppException("I'm sorry. Your query did not work.");
} finally {
  ps.close();
  rs.close();
}

The above does not compile, because both PreparedStatement.close() and ResultSet.close() throw a java.sql.SQLException. So do I add a try/catch block to the finally clause? Or move the close statements into the try clause? Or just not bother calling close?

+2  A: 

For file I/O, I generally add a try/catch to the finally block. However, you must be careful not to throw any exceptions from the finally block, since they will cause the original exception (if any) to be lost.

See this article for a more specific example of database connection closing.

Michael Myers
+1  A: 

Do no omit calling close. It may cause problems.

I prefer adding try/catch block to the finally.

asalamon74
+5  A: 

If you're really hand-rolling your own jdbc it definitely gets messy. The close() in the finally needs to get wrapped with its own try catch, which, at the very least, is ugly. You can't skip the close, although the resources will get cleared when the connection is closed (which might not be right away, if you're using a pool). Actually, one of the main selling points of using a framework (e.g. hibernate) to manage your db access is to manage the connection and result set handling so you don't forget to close.

YOu can do something simple like this, which at least hides the mess, and guarantees that you don't forget something.

public static void close(ResultSet rs, Statement ps, Connection conn)
{
 if (rs!=null)
 {
  try
  {
   rs.close();

  }
  catch(Exception e)
  {
   logger.error("The result set cannot b closed.",e);
  }
 }
 if (ps != null)
 {
  try
  {
   ps.close();
  } catch (Exception e)
  {
   logger.error("The statement cannot be closed.",e);
  }
 }
 if (conn != null)
 {
  try
  {
   conn.close();
  } catch (Exception e)
  {
   logger.error("The data source connection cannot be closed.",e);
  }
 }

}

and then,

finally{ close(rs, ps, null);
Steve B.
This solution works very well if you're rolling your own, because the SQLExceptions thrown by the close methods are unrecoverable anyway. Another neat way to go about it, is to throw a RuntimeException subclass from this method which bubbles up to a code layer that can manage the DB failure.
Spencer K
Note that according to the JavaDoc for Statement, the ResultSet is closed as a side-effect of closing the statement so it's not strictly necessary in this example (but doesn't hurt).http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#close()
hallidave
I wrote it this way because it's useful to be able to flexibly close with some of the objects as null. So you can use the same function to close any subset of (statement, resultset, connection) objects.
Steve B.
Always close statements, leaving them open after connection is closed, might produce problems: see more at http://stackoverflow.com/questions/321418/where-to-close-java-preparedstatements-and-resultsets.
jb
A: 

I use this..

finally
{
    if (ps != null) ps.close();
    if (rs != null) rs.close();
}
Chris Nava
That doesn't answer the question - because ps.close() and rs.close() can both throw SqlException.
Jon Skeet
+3  A: 

I usually have a utility method which can close things like this, including taking care not to try to do anything with a null reference.

Usually if close() throws an exception I don't actually care, so I just log the exception and swallow it - but another alternative would be to convert it into a RuntimeException. Either way, I recommend doing it in a utility method which is easy to call, as you may well need to do this in many places.

Note that your current solution won't close the ResultSet if closing the PreparedStatement fails - it's better to use nested finally blocks.

Jon Skeet
+3  A: 

Don't waste your time coding low-level exception management, use an higher-level API like Spring-JDBC, or a custom wrapper around connection/statement/rs objects, to hide the messy try-catch ridden code.

BraveSirFoobar
I also agree with this. Spring will wrap checked exception into unchecked, because in most cases application cannot recover from DB exceptions.
dma_k
+3  A: 

It's best to use nested finally blocks, rather than testing references for null.

The example I'll show might look ugly with the deep nesting, but in practice, well-designed code probably isn't going to create a connection, statement, and results all in the same method; often, each level of nesting involves passing a resource to another method, which uses it as a factory for another resource.

Connection db = ds.getConnection();
try {
  PreparedStatement ps = ...;
  try {
    ResultSet rs = ...
    try {
      ...
    }
    finally {
      rs.close();
    }
  } 
  finally {
    ps.close();
  }
} 
finally {
  db.close();
}
erickson
It'd be less ugly if you put the finally on the same line as the closing brace. ;)
Tom Hawtin - tackline
Ctrl-Shift-F to your heart's content! ;)
erickson
A: 

Also note:

"When a Statement object is closed, its current ResultSet object, if one exists, is also closed. "

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#close()

It should be sufficient to close only the PreparedStatement in a finally, and only if it is not already closed. If you want to be really particular though, close the ResultSet FIRST, not after closing the PreparedStatement (closing it after, like some of the examples here, should actually guarantee an exception, since it is already closed).

According to the documentation you reference, calling Statement.close() on an already-closed Statement has no effect. Closing a Statement multiple times doesn't throw an exception, so it stands to reason that doing the same for a ResultSet should be equally innocuous. The ResultSet documentation doesn't explicitly say this, but it also doesn't say that you shouldn't close it more than once...The whole point of this pedantic rant is that it doesn't GUARANTEE an exception. Although it would be good to close the ResultSet first just in case some implementation behaves that way.
A. Levy