Must Jdbc Resultsets and Statements Be Closed Separately Although the Connection Is Closed Afterwards

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.

I hope this helps :)

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.

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.

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?

JDBC result set is not closed. consequence?

Closing the statement should allow the resultset to get garbage collected. however, closing the statement will implicitly close the resultset, so how can you continue to use it? Is the whole thing wrapped in a try-catch-finally block, which sets the statement and resultset to null in the finally block?

Otherwise if you throw an exception you may chew up connections, and the statement and resultset may not get connected. I.e. as long as you stay with the happy day scenario everything will be okay, but it could bring everything down if one thing goes wrong as the lack of the finally block resetting to null may prevent garbage collection and chew up all your resources (e.g. connections).

Efficient way to close multiple statements and resultsets?

what luk2302 said - use try-with-resources. this closes the connection automatically after executing that block.

your initial idea of closing all connections in a single finally-block is not really desireable, as you leave connections open that are not being used.
depending on your Database, you can only have X connections open, so that might be the cause of the Errors.

---

Related Topics