SonarQube keeps complaining about Use try-with-resources or close this "PreparedStatement" in a "finally" clause

Question

I'm trying to get an email from a table based on the user's id. However, SonarQube keeps complaining about Use try-with-resources or close this "PreparedStatement" in a "finally" clause which I already have. I've looked at the answer here and other places but the solutions don't work for me. Here's the code:

public String getUserEmail(String id) throws SQLException {
        String emailAddress = null;
        String sql = "select email from my_table where id=?";
        PreparedStatement preparedStatement = this.connection.prepareStatement(sql);
        preparedStatement.setString(1, id);
        try {
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                emailAddress = rs.getString("email");
            }
        } catch(SQLException e) {
            throw new TuringClientException("Failed to getUserEmail. ", e);
        } finally {
            preparedStatement.close();
        }
        return emailAddress;
    }

I've also try wrapping PreparedStatement preparedStatement = this.connection.prepareStatement(sql) in another try catch or do nested try-catch but none of the attempts works.

score 1 · Answer 1 · answered Oct 22 '21 at 19:11

You can try (try with resources)

public String getUserEmail(String id) throws SQLException {
        String emailAddress = null;
        String sql = "select email from my_table where id=?";
        try(PreparedStatement preparedStatement = this.connection.prepareStatement(sql)) {
            preparedStatement.setString(1, id);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                emailAddress = rs.getString("email");
            }
        } catch(SQLException e) {
            throw new TuringClientException("Failed to getUserEmail. ", e);
        }
        return emailAddress;
    }

score 1 · Accepted Answer · answered Oct 22 '21 at 19:13

setString may throw an SQLException. If the statement is successfully prepared and then setString throws an exception, it will do so outside of the try block and the statement will never be closed. Moving the prepareStatement into a nested try should be OK, as long as you have the clsoe in the right finally block (and without seeing what you did it's hard to say why SonarQube is complaining about it), but honestly, using the try-with-resource syntax would just be so much neater. Note, by the way, that the ResultSet should also be closed:

public String getUserEmail(String id) throws SQLException {
    String emailAddress = null;
    String sql = "select email from my_table where id=?";
    try (PreparedStatement preparedStatement = this.connection.prepareStatement(sql) {
        preparedStatement.setString(1, id);
        try (ResultSet rs = preparedStatement.executeQuery()) {
            while (rs.next()) {
                emailAddress = rs.getString("email");
            }
        } catch(SQLException e) {
            throw new TuringClientException("Failed to getUserEmail. ", e);
        }
    }
    return emailAddress;
}

SonarQube keeps complaining about Use try-with-resources or close this "PreparedStatement" in a "finally" clause

2 Answers2