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

Question

Do they need to be closed after each query and initialized at the beginning of each query?

Aretta

Answer 1

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?

Girish

Whyu not to use one connection per application? – Xerography

@Xerography That's answered in "See also" links. I usually never post them for decoration. They usually answer the new questions which pop up during reading the answer. Click through all of them, you'll learn a wealth. – Girish

Isn't it possible to recycle connections? – Pimply

@inf3rno: Yup. That's called a "connection pool". The way how you write JDBC code should absolutely not change. You only need to make sure that database.getConnection() returns a pooled connection. If you're developing a Servlet based application, start here: https://mcmap.net/q/17054/-how-should-i-connect-to-jdbc-database-datasource-in-a-servlet-based-application – Girish

Answer 2

Since you don't want the results of a previous query, you need to initialize the ResultSet, of course.

The statement can be kept, if needed again, and especially PreparedStatements should be kept - they can be precompiled on the first run by the database, which saves some seconds:

"SELECT foo FROM bar WHERE a = ?" 

if only the parameter changes, of course.

Eachelle

If you close a connection -which is mandatory when you want to be able to run your application longer than the DB's default connection timeout, such as webapplications- then all opened statements will be closed as well, thus you cannot keep them open (and for sure not assign as class/instance variable or something). – Girish

If. I didn't read something about webapplications and timeouts. However, in most scenarios you're right. But on single-user machines, databases have their advantages too. – Eachelle