When to Close Connection, Statement, Preparedstatement and Resultset in Jdbc

Which should I close first, the PreparedStatement or the Connection?

The statement. I would expect you to close (in order)

1. the result set
2. the statement
3. the connection

(and check for nulls along the way!)

i.e. close in reverse order to the opening sequence.

If you use Spring JdbcTemplate (or similar) then that will look after this for you. Alternatively you can use Apache Commons DbUtils and DbUtils.close() or DbUtils.closeQuietly().

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...

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 :)

Relationship between close for PreparedStatement and Connection?

Closing a Statement doesn't close a Connection. However, closing a Connection will close a Statement.

Think of it like so:

• Connection -> creates and automatically closes -> Statement
• Statement -> creates and automatically closes -> ResultSet

So closing a Connection will automatically close any Statements and any ResultSets it contains.

However, it's still considered best practice to close all three manually (ResultSet followed by Statement followed by Connection) if possible.

Do I need to close 'PreparedStatement'?

Yes, you have to close the prepared statements (PreparedStatement Object) and result sets as they may cause memory leakage.

For more information, see Using Prepared Statements.

Try-with-resources - Does it automatically close the connection? Java

No, the try with resources does not close the Connection that is used inside the PreparedStatement.
Only the PreparedStatement and its ResultSet are closed.

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

It is possible to reuse a connection to execute many PreparedStatements. Each of which is closed after usage. When the connection is no longer needed it can be closed as well.

You could perhaps check it like this:

public void query(String queryString, List<String> queryParams, Consumer<ResultSet> sqlConsumer)
{
    Connection connection;
    try (PreparedStatement preparedStatement = this.configureStatement(queryString, queryParams))
    {
        connection=preparedStatement.getConnection();
        sqlConsumer.accept(preparedStatement.executeQuery());
    } catch(SQLException exception)
    {
        exception.printStackTrace();
    }
    if(connection!=null){
        System.out.println("Is Connection closed:"+connection.isClosed());
    }
}

private PreparedStatement configureStatement(String query, List<String> queryParams) throws SQLException
{
    PreparedStatement preparedStatement = this.getConnection().prepareStatement(query);
    for (int i = 0; i < queryParams.size(); ++i)
        preparedStatement.setString(i, queryParams.get(i));

    return preparedStatement;
}

A refactoring that closes connections by using the try-with-resources with multiple statements:

public void query(String queryString, List<String> queryParams, Consumer<ResultSet> sqlConsumer)
    {

        try ( Connection connection=this.getConnection(); 
              PreparedStatement preparedStatement = this.configureStatement(connection, queryString, queryParams);)
        {
            sqlConsumer.accept(preparedStatement.executeQuery());
        } catch(SQLException exception)
        {
            exception.printStackTrace();
        }
        if(connection!=null){
           connection.close();
        }
    }

private PreparedStatement configureStatement( Connection connection,String query, List<String> queryParams) throws SQLException
    {
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        for (int i = 0; i < queryParams.size(); ++i)
            preparedStatement.setString(i, queryParams.get(i));

        return preparedStatement;
    }

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?

Closing Connection, PreparedStatement, and ResultSet all in one call

Practice the Best Practice

Yes, their is a problem in closing connection the way you have closed.

Suppose, an exception occurred while closing ResultSet object the rest things would not be closed

Second, suppose if everything goes fine, still you are holding other connection (etc) you are not using, it adds to the burden to the JVM, Database, Memory manager etc

It is recommended to use " try(){} with resource " feature available in JAVA 7 or if you are using JAVA 6 close them when it is no longer needed.

---

Related Topics