Best Practices from Oracle Development's A‑Team

A convenience method for closing multiple resources

Andy Knight
Principal Solution Architect


Ensuring that resources (e.g. database connections) are properly closed to avoid leaks can make your code cumbersome. This article offers a simplification for this issue.

Main Article

As with all things Java, it's often best to show by example. So that's exactly what I'm going to do. The example that we'll work with is highly contrived but that's usually the case with sample code anyway.

So here's a piece of code that establishes a connection to an Oracle database and acquires the system date from the DB. To do this, we will acquire three resources.

  1. java.sql.Connection
  2. java.sql.PreparedStatement
  3. java.sql.ResultSet

Let's look at how this would traditionally be done. Note that this code uses a DBUtils class that you will see later. At this stage, its use should be quite obvious.

public static String getDateFromDB(Properties p) {         DBUtils utils = new DBUtils();         Connection c = null;         PreparedStatement ps = null;         ResultSet rs = null;         String date = "Unknown";         try {             c = utils.getConnection(p);             ps = c.prepareStatement("SELECT TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') FROM DUAL");             rs = ps.executeQuery();             if (rs.next()) {                 date = rs.getString(1);             }         } catch (SQLException e) {            // Handle the exception         } finally {             if (rs != null) {                 try {                     rs.close();                 } catch (Exception e) {                 }             }             if (ps != null) {                 try {                     ps.close();                 } catch (Exception e) {                 }             }             if (c != null) {                 try {                     c.close();                 } catch (Exception e) {                 }             }         }         return date;     }

The point of this example is not what it does but how it cleans up the aforementioned resources. There's nothing inefficient about it - it's just cumbersome.

I should mention at this point that JDK 7 has the new "try with resources" construct. This is all very well but your code can start to look a bit messy if you have 3 or more resources that are, effectively, nested. You can read more about this here.

What's happening here in the finally block is very important. It is not enough to have your method declared as throwing an Exception and then writing your finally block like this...

finally {         if (rs != null) {             rs.close();         }         if (ps != null) {             ps.close();         }         if (c != null) {             c.close();         }     }

I mention this because I have seen it done. One may argue that the close() methods are unlikely to throw an exception. But "unlikely" will be the edge case that catches you out.

Now what if your finally block could look like this...

finally {     utils.close(rs,ps,c); }

I think that's a lot neater. Here's the underlying code...

public void close(Object... obj) {         final Class[] OC = new Class[]{};         final Object[] OO = new Object[]{};         for (Object o : obj) {             if (o != null) {                 try {                     final Method m = o.getClass().getMethod("close", OC);                     if (!m.isAccessible()) {                         try {                             m.setAccessible(true);                         } catch (SecurityException e) {                             Logger.getLogger(getClass().getName()).log(Level.WARNING, null, e);                             continue; // Ignore this and carry on                         }                     }                     m.invoke(o, OO); // Invoke the close() method                 } catch (Exception e) {                     Logger.getLogger(getClass().getName()).log(Level.WARNING, null, e); // Should just be NoSuchMethodException or if there's an exception during the close                 }             }         }     }

Here's a description of what this method does...

First thing to note is that it takes a variable length argument list. Each argument may refer to any type of Object or may be null (in which case it will be ignored).

The method enumerates the argument list and uses the Reflection API to see if the Object that it refers to contains a method with the signature void close()

It checks to see if the method is accessible. If not it will try to make it accessible.

Finally, if the method was found and was accessible (or was made so), then it is invoked.

Note that this method does not throw any exceptions. If anything goes wrong, the exception is merely logged as a WARNING.

The DBUtils class may be downloaded here

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content