Closing several JDBC statements cleanly
It's been a while since I wrote some raw JDBC code. I didn't remember that it was so tedious to manually close a series of PreparedStatement objects and make sure that any exception was properly handled and reported.
Note that the ARM blocks or BGGA closures proposals don't make this easier since this cleanup should be done after the prepared statements have been used for a while in various other methods, it doesn't automatically have to be done at the end of a lexical scope.
This is what I came up with.
Of course, you could write an alternative implementation that creates some kind of repository for the prepared statements in a map and then provide a method that closes them all by going over the entries of the map while preserving the exceptions in a similar manner. Any other suggestions or comments for this to be done better?
private PreparedStatement psStmt1;
private PreparedStatement psStmt2;
private PreparedStatement psStmt3;
public void cleanup() throws SQLException {
SQLException exception = null;
if (psStmt1 != null) {
try {
psStmt1.close();
} catch (SQLException e) {
exception = e;
} finally {
psStmt1 = null;
}
}
if (psStmt2 != null) {
try {
psStmt2.close();
} catch (SQLException e) {
if (exception != null) e.setNextException(exception);
exception = e;
} finally {
psStmt2 = null;
}
}
if (psStmt3 != null) {
try {
psStmt3.close();
} catch (SQLException e) {
if (exception != null) e.setNextException(exception);
exception = e;
} finally {
psStmt3 = null;
}
}
if (exception != null) {
throw exception;
}
}
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Comments
Mike P(Okidoky) replied on Wed, 2008/01/30 - 1:35pm
Could normal try/finally nesting work, eg:
(I'm having a hard time taming this editor)
Almost all code I've ever seen in companies, people were declaring things at the top, attempting to open and create stuff, then closing stuff, in ways that causes all kinds of null pointer exceptions and resources not getting cleaned up. I think that the nested approach is foolproof in that you can not ever get null pointer exceptions (declare locals as late as possible and never set them to null), and all resources are guaranteed to get cleaned up.
~Mike
Geert Bevin replied on Wed, 2008/01/30 - 1:36pm
Mike P(Okidoky) replied on Wed, 2008/01/30 - 1:42pm
in response to:
Geert Bevin
But when an error occurs at a given nested level, usually, when it makes it way back out again, when no other exceptions occur, you end up with the exception you want, usually.
For instance, you open a file, then write information to it, and you get a format conversion error. The exception is throws, and on its way out, it closes the file. Closing the file is unlikely going to cause another exception and the outside caller sees the format conversion exception.
Jose Smith replied on Wed, 2008/01/30 - 3:17pm
Ronald Miura replied on Wed, 2008/01/30 - 5:10pm
Mark Hillary replied on Fri, 2008/02/01 - 4:30am
in response to:
Jose Smith
SQLUtils.closeResources( Statement ... stmts );
SQLUtils.closeResources( ResultSet ... rss );
then just looped over them closing the resource, and logging any exceptions. I didn't rethrow because generally an exception thrown when closing a resource isn't that intresting. Also it avoids hiding an previously thrown exception. Creating util methods like this reducded the size of my JDBC classes quite a bit.
Michael Duffy replied on Fri, 2008/02/01 - 7:09am
I can think of many ways to improve this code. "Don't Repeat Yourself" is a great rule. You've violated it here. I'd have a utility class with static methods to close methods, like this:
[code]
public class DatabaseUtils
{
public static final void close(Statement s)
{
try
{
if (s != null)
{
s.close();
}
}
catch (SQLException e)
{
// log it, but don't rethrow it
e.printStackTrace();
}
}
// same for ResultSet & Connection
}
[/code]