Closing PreparedStatements

Question

Does use of PreparedStatements and ResultSets creates a "new database instance" everytime they are used? Or, whith other words, if I use a PreparedStatement and a ResultSet, should I close them after every use or once I finish?

Example:

while (...){
        p = connection.prepareStatement(...);
        r = p.executeQuery();
        while (r.next()) {
        ....
        }
 }
 // We close at the end. Or there are any other p and r still opened...?
 p.close();
 r.close();

OR

while (...){
        p = connection.prepareStatement(...);
        r = p.executeQuery();
        while (r.next()) {
        ....
        }
        p.close();
        r.close();
 }

NOTE: Of course I would use try and close properly, this is just an example.

Answer 1

You should close every one you open. When you create a prepared statement or result set the database allocates resources for those, and closing them tells the database to free those resources (it's likely the database will reallocate these resources eventually after a timeout period, but calling close lets the database know it can go ahead and clean up). Your second example is better, except I'd close the result set before the prepared statement.

So with try blocks included it would look like:

while (...){
        PreparedStatement p = connection.prepareStatement(...);
        try {
          ResultSet r = p.executeQuery();
          try {
            while (r.next()) {
              ....
            }
          } finally {
            try {
              r.close();
            } catch (SQLException e) {
            // log this or something -- prevent these from masking original exception
            }
          }
        }
        finally {
          try {
            p.close();
          } catch (SQLException e) {
            // log this or something -- prevent these from masking original exception
          }
        }
 }

Catching the exceptions from the close is ugly, but if you have an exception thrown during execution of the prepared statement, or during traversal of the result set, you want to make sure that you see it, and not an exception thrown when closing the prepared statement or result set (which is due to some network glitch you can't do anything about anyway).

Also be aware that using try-with-resources will work, except that if you have a case where the database operation succeeds but calling close results in an exception then the exception will get thrown.

I recommend people use the spring-jdbc library (which handles closing everything for you) instead of cranking out iffy or verbose jdbc by hand.

Answer 2

The first way is better.

However, you should know that you can re-use prepared statements (hence the name "prepared") if the SQL you are using is the same each time. For example:

//Note: try/catch/finally blocks removed for brevity
p = connection.prepareStatement(...);
while (...){
    r = p.executeQuery();
    while (r.next()) {
        ....
    }
    r.close();
}
p.close();