CODEWITHSUNDEEP
javasql-serverspringsonarqubetry-catch
Erick Finn
Erick Finn

Reputation: 161

Apply Try with Resources correctly in Java 7?

I am developing an application with Angular and Spring with Java 7, however, I was asked to scan my code with SonarQube, so this tool is telling me that I need to apply a try with resources to close my Prepared Statement and Result Set, so I did some research and I implemented it like this:

try (Connection connection = DriverManager.getConnection(connectionUrl);
            Statement statement = connection.createStatement();
            PreparedStatement sentence = connection.prepareStatement(selectSql);
            ResultSet resultSet = sentence.executeQuery();) {
    

        // Create and execute a SELECT SQL statement.
        sentence.setString(1, parametro);
        logger.info(resultSet + " resultSet!----------------------------------------");

        // Print results from select statement
        while (resultSet.next()) {
            logger.info(" Entro al While!----------------------------------------");
            euroList.add(new EuroModel(resultSet.getInt("ID"), resultSet.getString("RFC"),
                    resultSet.getString("NOM")));
            logger.info("recibiendo result-> " + resultSet.getString(2) + " " + resultSet.getString(3));
            logger.info(euroList.toString());

        }

    } catch (SQLException e) {
        logger.info("No se pudo conectar");
        logger.info("EuroService");
        logger.info("INFO: " + e);
        List<EuroModel> empty = null;
        return empty;
    }

However, my code is not working due to the following line is in the wrong place and I don't know where should it be with this try with resources:

sentence.setString(1, parametro);

Could someone help me please?

Upvotes: 0

Views: 80

Answers (1)

VGR
VGR

Reputation: 44355

You are executing your query before you have set your PreparedStatement’s parameter.

You can remove the ResultSet from your try-with-resources, because it will be closed automatically:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

You want to make sure you execute your PreparedStatement’s query, and obtain the corresponding ResultSet, after you have set your parameter:

try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement();
        PreparedStatement sentence = connection.prepareStatement(selectSql)) {


    // Create and execute a SELECT SQL statement.
    sentence.setString(1, parametro);
    ResultSet resultSet = sentence.executeQuery();
    logger.info(resultSet + " resultSet!----------------------------------------");

Update

A useful additional lesson to take away from this: SonarQube is not an authority when it comes to good programming practices. In fact, SonarQube is wrong about things on a semi-regular basis.

This is such a case. Sonar is not smart enough to know that a ResultSet will be automatically closed when its parent Statement is closed. Sonar only knows that there is a closable object which doesn’t appear to be explicitly closed in the code.

As Slaw suggested, you can make Sonar happy by using another try-with-resources for the ResultSet:

try (Connection connection = DriverManager.getConnection(connectionUrl);
        Statement statement = connection.createStatement();
        PreparedStatement sentence = connection.prepareStatement(selectSql)) {


    // Create and execute a SELECT SQL statement.
    sentence.setString(1, parametro);
    try (ResultSet resultSet = sentence.executeQuery()) {
        logger.info(resultSet + " resultSet!----------------------------------------");

        while (resultSet.next()) {
            // etc.
        }
    }

Upvotes: 2

Related Questions