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;
    }

CodePudding user response：

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;
    }

CodePudding user response：

Q: What makes you think returning an object from one of your own methods won't allow the object to be "closed" in a Java try with resources?

From the Java documentation:

https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html

Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

The key issue: the object returned from your method must implement java.lang.AutoCloseable. In your case, it does:

https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html

Interface PreparedStatement

• All Superinterfaces:

  AutoCloseable, Statement, Wrapper