SQL Zone is brought to you in partnership with:

Geert has posted 3 posts at DZone. View Full User Profile

Closing several JDBC statements cleanly

01.30.2008
| 4503 views |
  • submit to reddit

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;
  }
}
0
Your rating: None
Published at DZone with permission of its author, Geert Bevin.

(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:

res = open();
try
{
stat = res.start();
try
{
do stuff

}
finally
{
stat.close();
}
}
finally
{
res.close();
}

(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

The problem with the nested is making sure that all intermediate exceptions are properly captured and linked together.

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

This topic was already covered pretty heavily on javalobby.  See this thread with 51 replies: http://www.javalobby.org/java/forums/t18930.html

Ronald Miura replied on Wed, 2008/01/30 - 5:10pm

Just use Spring :)

Mark Hillary replied on Fri, 2008/02/01 - 4:30am in response to: Jose Smith

I did something very similar to the linked article. I had a couple of methods like;
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]

 

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.