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

February 19, 2022

I’ve been working on a SQL utility and I am trying to set the parameters inside a prepared statement in multiple functions.

To lessen the code, I have a function that returns a prepared statement where all the params are set.

My question is:
Does the connection reference in the configureStatement() get closed using the try with resources in the query()?

    public void query(String queryString, List<String> queryParams, Consumer<ResultSet> sqlConsumer)
    {
        try (PreparedStatement preparedStatement = this.configureStatement(queryString, queryParams))
        {
            sqlConsumer.accept(preparedStatement.executeQuery());
        } catch(SQLException exception)
        {
            exception.printStackTrace();
        }
    }
    
    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;
    }

>Solution :

No, the try with resources does not close the connection that is used inside the configureStatement.
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;
    }