SonarQube keeps complaining about Use try-with-resources or close this "PreparedStatement"-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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