null check in try-with-resources

Question

I've got the following code:

try (Connection connection = getConnection();

    PreparedStatement preparedStatement = connection.prepareStatement(someSql)) {//stuff}

How do I check that connection is not null here?

Also, I got a method which returns a PreparedStatement like this:

private PreparedStatement getPreparedStatement(Connection connection)

  throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement(SQL);

preparedStatement.setString(1, "someString");

return preparedStatement;

}

this gets called within a method that uses the following try with resources:

try (Connection connection = getConnection();
    PreparedStatement preparedStatement =
        getPreparedStatement(connection)) {//stuff}

Now I would assume the prepared statement will be autoclosed, because it gets initiated in the try with resources. But SonarCloud says that i should use try with resources in the getPreparedStatement-method or close that PreparedStatement in a finally block. Is this a wrong finding by SonarCloud or is there a better way to do it?

Answer 1

getConnection should throw an exception instead of returning null. Returning null is not nice. SonarCloud seems to be wanting you to put a try-with-resource opening ingetPreparedStatement(must include thesetString`) and closing in the calling method, which of course you can't do as such.

The best approach is the Execute Around idiom. Instead of getPreparedStatement returning a PreparedStatement pass in a lambda (typically) to be executed. The resource can then be closed in a try-with-resource statement cleanly.

/*** NICE ***/
// Function instead of Consumer would allow the method to return a value.
private void prepared(
    Connection connection, Consumer<PreparedStatement> op
) throws SQLException {
    // Might want to add getConnection in here too, perhaps.
    try (
        PreparedStatement statement =
             connection.prepareStatement(SQL)
    ) { 
        statement.setString(1, "someString");
        op.accept(statement);
    }
}

Used as:

    try (Connection connection = getConnection()) {
        prepared(connection, statement -> {
            // blah, blah, blah
        });
    }

The hacky alternative is to include a try statement within getPreparedStatement that only closed in error conditions.

/*** HACKY ***/
private PreparedStatement prepared(
    Connection connection
) throws SQLException {
    boolean success = false;
    PreparedStatement statement =
        connection.prepareStatement(SQL);
    try { 
        statement.setString(1, "someString");
        success = true;
        return preparedStatement;
    } finally {
        if (!success) {
            statement.close();
        }
    }
}

If you desperately want getConnection to return null (don't) and use a single two-entry try-with-resource, then the conditional operator works.

try (
    Connection connection = getConnection();
    PreparedStatement statement =
        connection==null ? null : connection.prepareStatement(SQL)
) {