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

Question

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()? If not how can the code be refactored to close the PreparedStatement and the Connection every time?

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

Is the question if the connection gets closed with `try-with-resources` on the `PreparedStatement` or do you want to ask a different question about restructuring your code which closes the connection after running a query? Please [edit] your post to make it clear what single question you want to ask. — Progman, Feb 19 '22 at 21:13
@Progman Yes, that's my basic question. I have edited it to make it a little more clear. — , Feb 19 '22 at 21:22
Which database system and which JDBC client/version/library are you using? — Progman, Feb 19 '22 at 21:25
This doesn't seem to be a question about try-with-resources, but about the `close` method of `PreparedStatement`. — xehpuk, Feb 19 '22 at 21:42

Spyros K · Accepted Answer · 2022-02-20T15:08:50.743

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

That's what I figured. Any ideas on how I could refactor to make this happen? I want to have a query() and execute() function where both takes that param list. I don't want to copy-paste the for loop code in both functions. I'd rather have a method that does it automatically. But the connection needs to be closed after it completes. I guess I could close the connection manually in each function but that looks a little messy. — , Feb 19 '22 at 21:39
Sure I will update the example, but perhaps you can also update the question to reflect that need. So it is easier for future readers to understand the question/answer. — Spyros K, Feb 19 '22 at 21:43
This is what I was looking for. Another way I just thought of was closing the connection manually in the consumer itself. Think I'll use this instead though. Thank you. — , Feb 19 '22 at 21:55
You are welcome, closing the connection manually is also possible. But you can leverage the try-with-resources to ensure both the connection and the prepared statement are closed. — Spyros K, Feb 19 '22 at 21:56

paulsm4 · Answer 2 · 2022-02-19T21:19:17.387

0

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

edited Feb 19 '22 at 21:19

answered Feb 19 '22 at 21:14

paulsm4

114,292
17
138
190

1

I know that the PreparedStatement returned from that function will be closed. However, the connection that is referenced inside the configureStatement function needs to be closed as well. To my knowledge, closing the statement with a try-resource did not close the connection too. I guess they do though. – Feb 19 '22 at 21:26
Your code clearly shows both the connection and your method that creates the prepared statement are in the same try/resource block. Hence both are closed when the block exits. It sounded like your question was just asking about the "configureStatement()" method - hence my reply. No, of *course* closing a prepared statement doesn't necessarily close the connection. Why would you even think that??? – paulsm4 Feb 20 '22 at 19:16

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

2 Answers2