How Often Should Connection, Statement and Resultset Be Closed in Jdbc

How often should Connection, Statement and ResultSet be closed in JDBC?

Always. You need to acquire and close them in the shortest possible scope to avoid resource leaking, transactional problems and exhausted connection pools. Not doing so would cause the DB to run out of resources sooner or later, resulting in exceptions like "Too many connections".

The normal JDBC idiom is the following, whereby all resources are opened and closed in the very same try-with-resources block:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_LIST);
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    }

    return entities;
}

Or when you're not on Java 7 yet:

public List<Entity> list() throws SQLException {
    List<Entity> entities = new ArrayList<Entity>();
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement(SQL_LIST);
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            entities.add(map(resultSet));
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return entities;
}

Using PreparedStatement will give you the benefit of the DB caching of the statements (next to SQL injection prevention when used properly). Acquiring and closing the connection is the most expensive task, but there the connection pools are invented for. If you want to reuse the same statement to do bulk inserts/updates, then you can use batches.

See also:

• When my app loses connection, how should I recover it?
• Is it safe to use a static java.sql.Connection instance in a multithreaded system?

Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards?

What you have done is perfect and very good practice.

The reason I say its good practice... For example, if for some reason you are using a "primitive" type of database pooling and you call connection.close(), the connection will be returned to the pool and the ResultSet/Statement will never be closed and then you will run into many different new problems!

So you can't always count on connection.close() to clean up.

When to close connection in JDBC/SQL

The 1st approach is correct, It's good programming practice to explicitly close things that you open once you've finished with them.

Generally you should Close the connection in a finally block. All other JDBC resource depend on this so are freed up implicitly...

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

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)

---

Does closing Connection automatically close statement and resultset?

Yes it does, Connection.close API says "Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released". The problem is that applications typically use database connection pools and these may simply return Connection to pool on Connection.close.

In any case, it's a good practice to always close ResultSet and Statement explicitly and not to rely on Connection.close.

Besides, it's not the best idea to work with JDBC directly. You can use Spring JDBC instead and forget about releasing resources problem.

Does closing ResultSet closes Connection in java

In a word - no.

Closing the ResultSet just closes the cursor associated with it so you can't call next() on it. The Connection object is not affected, and can be used to execute new statements.

ResultSet not closed when connection closed?

One problem with ONLY closing the connection and not the result set, is that if your connection management code is using connection pooling, the connection.close() would just put the connection back in the pool. Additionally, some database have a cursor resource on the server that will not be freed properly unless it is explicitly closed.

Close result set in loop using Java

I suggest to use Java 7 try-with-resources

(the code below assumes that the loop var dealId is used in the query)

Class.forName(<driver class>);                
try (Connection con = DriverManager.getConnection(
        "IP", "username", "password")) {
    for(String dealId : items) {
        String sql= "SQL Query with " + dealId;
        // resources are opened by order 
        try (PreparedStatement preparedStatement = con.prepareStatement(sql);
             ResultSet rs = preparedStatement.executeQuery()) {
            while(rs.next()) {
                count += rs.getInt("total");                    
            }
        } // resources are implicitly closed in reverse order of open
    }
} catch (Exception e) {
    e.printStackTrace();
}

System.out.println(count);
if(items.size() == count) {
    dealsBelongToTheParty = true;
}

---

Related Topics