What should be in try-with-resources when dealing with databases

Question

I have this small project and I need to connect my Java application to the database. I have succeeded. I have few questions though. I'm not sure how to handle resources. As of now, I defaulted to packing as much as I can into try-with-resources because, it is reliable option for closing them automatically. But I'm not 100% sure if I'm doing it right.

There are 2 cases:

1. I use function ResultSet executeQuery(String sql)

try 
(
    Connection connection = DriverManager.getConnection(url, user, password);
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(query)
) 
{
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int columnsNumber = rsmd.getColumnCount();
    String number = new String();

    while (resultSet.next()) {
        for (int i = 1; i <= columnsNumber; i++) {
            number = resultSet.getString(i);
        }
    }
    retValue = Integer.parseInt(number);
    …
}

2. I use function int executeUpdate(String sql)

try 
(
    Connection connection = DriverManager.getConnection(url, user, password);
    Statement statement = connection.createStatement()
) 
{
    statement.executeUpdate(query);
    …
}

From what I understand, Connection and ResultSet both need to be closed. In first case I am using result set later, so that's why I'm creating a variable in () of try. But in the second case executeUpdate cannot be put in (). I know that it returns int, but I am still unsure. Is this sufficient? Are connection and result set closed properly?

Answer 1

Yes, correct

Yes, you have correctly used try-with-resources syntax to close your JDBC resources appropriately.

You said:

connection and reslutset both need to be closed

Yes.

• Both Connection and ResultSet classes are documented as having a close method that releases resources. So you should close them as early as is practical.
• Both classes are documented as implementing AutoCloseable. So you can use them with try-with-resources syntax.

Ditto for java.sql.Statement.

In contrast, look at the ResultSetMetaData class. That class has no close method, and does not implement AutoCloseable. So that class, unlike a regular ResultSet, does not need to be closed, and cannot be used in try-with-resources syntax.

AutoCloseable

You said:

But in the second case executeUpdate cannot be put in ().

The executeUpdate method does not return an AutoCloseable object. So that method cannot be called with try-with-resources syntax.

That method executeUpdate returns a primitive int rather than a reference to an object. So there is no close method to call.