Does reusing a Statement and Resultset release resources from its previous usage? Or do I have to explicitly close them before reuse?

Question

Sample code:

        aStmt = aConn.prepareStatement(aQuery);
        aRset = aStmt.executeQuery(cQuery);

        while (cRset.next()) {
            //stuff to determine value of parm1

            aStmt.setString(1, parm1);                
            aRset = aStmt.executeQuery(); 

            //more stuff
        }

Do I have to close aStmt and aRset after every loop inside the while statement? Or will reusing them in the succeeding loops release the memory/resources used from the previous loops?

Answer 1

The behavior of resultsets and (prepared) statements is explicitly documented in the Java API. I suggest that you read the actual documentation (and the JDBC spec) to get the details.

The Statement API says:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.

(emphasis mine).

In your specific code, when you call aStmt.executeQuery(), the old ResultSet assigned to aRset is implicitly closed by the driver. That said, it would be better to explicitly close it yourself (or use Java 7 try-with-resources), to prevent you from forgetting to close the ResultSet in the last iteration through the loop.

Now to the PreparedStatement: When you prepare a statement (in general, implementation can vary), the query is sent to the server for compilation. On execution the parameters for that specific execution is sent to the server. Calling close() on aStmt would result in the prepared statement being deallocated on the server, that is clearly NOT what you want here as you want to re-use the statement with different values for its parameter.

So in short

1. Closing ResultSet is not technically necessary here (except for the last ResultSet created), but it is better to do it explicitly
2. You should only close the PreparedStatement when you are done with it.

Using try-with-resources is one way to remove part of the confusion on these issues, as your code will automatically release resources when it is done with it (at the end of the scope of use):

try (
    ResultSet cRset = cStmt.executeQuery(cQuery);
    PreparedStatement aStmt = aConn.prepareStatement(aQuery);
) {
    while (cRset.next()) {
        //stuff to determine value of parm1

        aStmt.setString(1, parm1);                
        try (ResultSet aRset = aStmt.executeQuery()) {
            //more stuff
        }
    }
}

At the end of this piece of code all JDBC resources are correctly closed (in the right order, even if exceptions occurred etc)

Answer 2

No, you may not close the ResultSet and Statement inside the while loop.

You have to close them after the loop.

Also if you want to reuse the PreparedStatement then you may not close it until you're ready with your processing.

Best rule is to close such resources in the same block as they're created. In your case the best thing to do is to close the resources in a finally block after catching the SQLException.

E.g.

try {
    aStmt = aConn.prepareStatement(aQuery);
    cRset = cStmt.executeQuery(cQuery);

    while (cRset.next()) {
        //stuff to determine value of parm1

        aStmt.setString(1, parm1);
        try {
            aRset = aStmt.executeQuery();
        } finally {
            aRset.close();
        }

        //more stuff
    }
} catch (SQLException ex) {
    // Do error handling
} finally {
    // Close Resultset
}

In Java 7 you can use try with resources.

Answer 3

PreparedStatement API: A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

But you cannot reuse a ResultSet object. When you call executeQuery on a PreparedStatement object the second time a new ResultSet is created, if you do not close the previous ResultSet you are risking to get a resource leak.