Where to close java PreparedStatements and ResultSets?

Question

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?

Answer 1

In Java 7, you should not close them explicitly, but use automatic resource management to ensure that resources are closed and exceptions are handled appropriately. Exception handling works like this:

Exception in try | Exception in close | Result
-----------------+--------------------+----------------------------------------
      No         |        No          | Continue normally
      No         |        Yes         | Throw the close() exception
      Yes        |        No          | Throw the exception from try block
      Yes        |        Yes         | Add close() exception to main exception
                 |                    |  as "suppressed", throw main exception

Hopefully that makes sense. In allows pretty code, like this:

private void doEverythingInOneSillyMethod(String key)
  throws MyAppException
{
  try (Connection db = ds.getConnection()) {
    db.setReadOnly(true);
    ...
    try (PreparedStatement ps = db.prepareStatement(...)) {
      ps.setString(1, key);
      ...
      try (ResultSet rs = ps.executeQuery()) {
        ...
      }
    }
  } catch (SQLException ex) {
    throw new MyAppException("Query failed.", ex);
  }
}

---

Prior to Java 7, 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. With this approach, exceptions from a close() will mask an exception from inside the try block. That can be overcome, but it results in even more messy code, and requires a custom exception class that provides the "suppressed" exception chaining present in Java 7.

Connection db = ds.getConnection();
try {
  PreparedStatement ps = ...;
  try {
    ResultSet rs = ...
    try {
      ...
    }
    finally {
      rs.close();
    }
  } 
  finally {
    ps.close();
  }
} 
finally {
  db.close();
}

Answer 2

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(SQLException e)
        {
            logger.error("The result set cannot be closed.", e);
        }
    }
    if (ps != null)
    {
        try
        {
            ps.close();
        } catch (SQLException e)
        {
            logger.error("The statement cannot be closed.", e);
        }
    }
    if (conn != null)
    {
        try
        {
            conn.close();
        } catch (SQLException e)
        {
            logger.error("The data source connection cannot be closed.", e);
        }
    }

}

and then,

finally {
    close(rs, ps, null); 
}

Answer 3

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.

Answer 4

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.

Answer 5

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).

Answer 6

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.

Answer 7

Building on @erickson's answer, why not just do it in one try block like this?

private void doEverythingInOneSillyMethod(String key) throws MyAppException
{
  try (Connection db = ds.getConnection();
       PreparedStatement ps = db.prepareStatement(...)) {

    db.setReadOnly(true);
    ps.setString(1, key);
    ResultSet rs = ps.executeQuery()
    ...
  } catch (SQLException ex) {
    throw new MyAppException("Query failed.", ex);
  }
}

Note that you don't need to create the ResultSet object inside the try block as ResultSet's are automatically closed when the PreparedStatement object is closed.

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Reference: https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

Answer 8

If your are using Java 7 you can use the improvements in the exception handling mechanisms in those classes that implement AutoCloseable (i.e. PreparedStatement, Resultset)

You might also find this question interesting: Closing ResultSet in Java 7

Answer 9

I know this is an old question, but just in case someone is looking for the answer, java now has the try-with-resouce solution.

static String readFirstLineFromFile(String path) throws IOException {
      try (BufferedReader br =
                   new BufferedReader(new FileReader(path))) {
        return br.readLine();
    }
}

Answer 10

focus finally clause,

finally {
   try {
      rs.close();
      ps.close();
   } catch (Exception e) {
      // Do something
   }
}

I think you have to modify 2 points.

First, use try & catch again in fainlly clause.

Second, do rs.close() before doing ps.close().

fly1997@naver.com

Answer 11

Probably an old (though simple) way to do things, but it still works:

public class DatabaseTest {

    private Connection conn;    
    private Statement st;   
    private ResultSet rs;
    private PreparedStatement ps;

    public DatabaseTest() {
        // if needed
    }

    public String getSomethingFromDatabase(...) {
        String something = null;

        // code here

        try {
            // code here

        } catch(SQLException se) {
            se.printStackTrace();

        } finally { // will always execute even after a return statement
            closeDatabaseResources();
        }

        return something;
    }

    private void closeDatabaseResources() {
        try {
            if(conn != null) {
                System.out.println("conn closed");
                conn.close();
            }

            if(st != null) {
                System.out.println("st closed");
                st.close();
            }

            if(rs != null) {
                System.out.println("rs closed");
                rs.close();
            }

            if(ps != null) {
                System.out.println("ps closed");
                ps.close();
            }

        } catch(SQLException se) {
            se.printStackTrace();
        }               
    }
}

Answer 12

Do no omit calling close. It may cause problems.

I prefer adding try/catch block to the finally.

Answer 13

I use this..

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