JDBC Statement.closeOnCompletion() should close statement on next execution?

The JDBC API has the following note related to ResultSet:

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.

In the JDBC 4.3 Specification:

13.1.4 Closing Statement Objects

Closing a Statement object will close and invalidate any instances of ResultSet produced by that Statement object.

At this point it’s clear, closing a statement object, should close the ResultSet

The JavaDoc of Statement has this note:

All execution methods in the Statement interface implicitly close a current ResultSet object of the statement if an open one exists.

Now, the question is how should behave Statement.closeOnCompletion()?

Specifies that this Statement will be closed when all its dependent result sets are closed. If execution of the Statement does not produce any result sets, this method has no effect.

Note: … However, a call to closeOnCompletion does effect both the subsequent execution of statements, and statements that currently have open, dependent, result sets.

The Statement should allow re-executions? or the re-execution should close the statement on the second execution?

To exemplify with a test:

  @Test
  public void testCloseOnCompletionMultipleExecutionResultSets() throws SQLException {
    Statement statement = conn.createStatement();
    ResultSet rs1 = statement.executeQuery("SELECT 1");
    assertFalse("rs1 should be open", rs1.isClosed());

    statement.closeOnCompletion();
    // Should the second execution throw an SQLException with "Statement closed"?
    // or it should work and the statement be closed until the second rs is closed?
    ResultSet rs2 = statement.executeQuery("SELECT 2"); // fail or not?

    assertTrue("rs1 should be closed by rs2", rs1.isClosed());
    assertFalse("rs2 should be open", rs2.isClosed());
    assertFalse("statement should be open", statement.isClosed());
    rs2.close(); // Only close the statement here since is the last rs open.
    assertTrue("statement should be closed", statement.isClosed());
  }

Related discussion: https://github.com/pgjdbc/pgjdbc/pull/1905

Answer

With calling closeOnCompletion() you signal that you want the statement to close after you’ve closed its last result set (which ‘completes’ the execution), so the statement will close itself once execution completes. For non-result-set producing statements, it has no effect (you should call close() yourself). For result-set-producing statements, execution completes once you close the last result set. The primary use-case for this is when you return a result set for processing, but don’t want to keep track of its statement.

Given another execution on the same statement will close any open result set from that statement, having enabled closeOnCompletion, will automatically close the statement as a result.

It would be a user error to signal closeOnCompletion, but then continue to use the statement anyway by executing another statement, so raising a SQLException is appropriate.

As an aside, the text

However, a call to closeOnCompletion does effect both the subsequent execution of statements, and statements that currently have open, dependent, result sets.

means that if you call closeOnCompletion it will affect the next execution that produces a result set, or if you currently have a result set open, the current execution.

This is also supported by the replies in the discussion on jdbc-spec-discuss mailing list by Lance Andersen (JDBC specification leader) and Douglas Surber (JSR-221 JDBC Expert Group member on behalf of Oracle), specifically:

The overall intent was to deal with code similar to:

ResultSet rs = foo();
while(rs.next() {
     /*do something */
}
rs.close();

public ResultSet foo() {

   Statement stmt = con.createStatement();
   stmt.closeOnCompletion();
   ResultSet rs = stmt.executeQuery(aQuery);
   return rs
}

We did spend a lot of time on this back in 2009 and took quite a bit of time to reach agreement on the current wording. However, it does look like there is the potential for some additional word smithing.

(Lance Andersen, https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2021-February/000542.html)

---

I wrote the original proposal. The intent was to handle the case Lance described. It was not intended to allow multiple executions of the Statement. So while the language may not be as clear as it needs to be, the case Filipe described should throw on the second execution of the Statement. At least that was my intent.

(Douglas Surber, https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2021-February/000543.html)

---

As Douglas points out at the time of the discussion, the JDBC EG consensus was the Statement would be closed. If applications did not want this behavior they should not call Statement::closeOnCompletion and if they were not sure if the method had been invoked, they could always call Statement::isCloseOnCompletion to make a decision programatically.

This area as you are aware is messy enough and the intent was to address a common use case which lead to common issues for applications and not further complicate things.

(Lance Andersen, https://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2021-February/000548.html)

---