How to use try/catch with db statement and resultSet

Question

public SortedMap<String, TreeMap<String, String>> getCampo(String city, String sport, String 
    data) throws SQLException {
    TreeMap<String, TreeMap<String, String>> campoInfo = new TreeMap<>();
    
    
    Connection connection =null;
    Statement statement = null;
    ResultSet resultSet = null;
    
    try {
     connection = getConnection();
     statement = connection.createStatement();
    String query = String.format("SELECT * FROM CAMPO WHERE COMUNE='%s' AND SPORT='%s' AND DATA >= '%s' AND TORNEO = 0", city, sport, data);
     resultSet = statement.executeQuery(query);
    while (resultSet.next()) {
        String name = resultSet.getString("NOME");
        String id = "" + resultSet.getInt("ID");
        String comune = resultSet.getString("COMUNE");
        String indirizzo = resultSet.getString("INDIRIZZO");
        String desc = resultSet.getString("DESCRIZIONE");
        String renter = "" + resultSet.getInt("RENTER");
        String date = resultSet.getString("DATA");
        String ora = resultSet.getString("ORA");
        String metodo = resultSet.getString("METODODIPAGAMENTO");
        String prezzo = resultSet.getString("PREZZO");
        String isAffittabile = "" + resultSet.getString("AFFITTABILE");
        TreeMap<String, String> info = new TreeMap<>();
        info.put("ID", id);
        info.put("COMUNE", comune);
        info.put("INDIRIZZO", indirizzo);
        info.put("DESC", desc);
        info.put("RENTER", renter);
        info.put("DATA", date);
        info.put("ORA", ora);
        info.put("PREZZO", prezzo);
        info.put("METODODIPAGAMENTO", metodo);
        info.put("AFFITTABILE", isAffittabile);
        campoInfo.put(name, info);

    }
    
    
}
    catch (Exception e) {
        // TODO: handle exception
    }
   finally {
    if (resultSet!=null) {
        resultSet.close();
    }
    if (statement!=null) {
        statement.close();
    }
   }
    if (connection!=null){
       connection.close();}
    return campoInfo;
   }

I'm writing this function in Java with Sonar scanner. I have a problem with the variables 'statement' and 'resultSet', because when I try to close them, in the finally block, Sonar tells me that there's a bug and use try-with resources or close it in a finally block. It happens just for one of these variables, the second in the block. For example, first i close the 'resulSet' (and it's ok) but the second doesn't close and Sonar highlights it. What can I do?

Why not use a try-with-resources block (I think that's what SonarQube is hinting at). `try (Connection connection = getConnection() { try (Statement statement = connection.createStatement() { /* add your business logic */ } }` It will be automatically closed at the end — knittl, May 19 '21 at 17:01
Don't you think "connection.close();" be in the finally block with a null check ? — Chandan, May 19 '21 at 17:05
No, I can't use a try inside another try, Sonar recognzine it as a code smell. And even if i add 'connection.close()' the error doesn't go away — PaulK, May 19 '21 at 17:29
@knittl You can even combine those into a single try-with-resources — Mark Rotteveel, May 19 '21 at 18:09

score 1 · Answer 1 · answered May 19 '21 at 17:54

You can initialize multiple variables in a try() {} block:

public SortedMap<String, TreeMap<String, String>> getCampo(String city, String sport, String data) throws SQLException {
    TreeMap<String, TreeMap<String, String>> campoInfo = new TreeMap<>();

    String query = String.format("SELECT * FROM CAMPO WHERE COMUNE='%s' AND SPORT='%s' AND DATA >= '%s' AND TORNEO = 0", city, sport, data);

    try (Connection connection = getConnection(); 
        Statement statement = connection.createStatement(); 
        ResultSet resultSet = statement.executeQuery(query)) {
        while (resultSet.next()) {
            String name = resultSet.getString("NOME");
            String id = "" + resultSet.getInt("ID");
            String comune = resultSet.getString("COMUNE");
            String indirizzo = resultSet.getString("INDIRIZZO");
            String desc = resultSet.getString("DESCRIZIONE");
            String renter = "" + resultSet.getInt("RENTER");
            String date = resultSet.getString("DATA");
            String ora = resultSet.getString("ORA");
            String metodo = resultSet.getString("METODODIPAGAMENTO");
            String prezzo = resultSet.getString("PREZZO");
            String isAffittabile = "" + resultSet.getString("AFFITTABILE");
            TreeMap<String, String> info = new TreeMap<>();
            info.put("ID", id);
            info.put("COMUNE", comune);
            info.put("INDIRIZZO", indirizzo);
            info.put("DESC", desc);
            info.put("RENTER", renter);
            info.put("DATA", date);
            info.put("ORA", ora);
            info.put("PREZZO", prezzo);
            info.put("METODODIPAGAMENTO", metodo);
            info.put("AFFITTABILE", isAffittabile);
            campoInfo.put(name, info);
        }

    } catch (Exception e) {
        // TODO: handle exception
    }
    return campoInfo;
}

Another problem I see here is that you should really be using prepared statements (not String.format) to build queries

Yes, but then I can't reach them in finally block, maybe beacuse of java version (1.8). I know that is totally unsafe not using prepared statements but it's quicker for what i need. Thank for warning — PaulK, May 19 '21 at 18:10
@PaulK What do you mean with _"can't reach them in finally block"_? You don't need to, that is what try-with-resources is for. — Mark Rotteveel, May 19 '21 at 18:11
I mean that if I try to do 'resultSet.close()' inside the finally block eclipse tells me that 'resultSet variable cannot be resolved' — PaulK, May 19 '21 at 18:14
You don't need to close it explicitely, that's the point of try-with-resources blocks. The compiler generates the close() call for you. — Guillaume, May 19 '21 at 18:33

How to use try/catch with db statement and resultSet

1 Answers1