如何在db语句和结果集[重复]中使用try/cat

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

我正在用Java编写这个函数，使用Sonar scanner。我对变量“statement”和“resultSet”有问题，因为当我试图在finally块中关闭它们时，Sonar告诉我有一个bug，请使用try with resources或在finally块中关闭它。这只发生在这些变量中的一个，块中的第二个。例如，首先我关闭“结果集”（这没问题），但第二个没有关闭，Sonar将其高亮显示。我能做什么？

Answer 1

可以在try（）{}块中初始化多个变量：

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

我在这里看到的另一个问题是，您应该真正使用准备好的语句（而不是String.format）来构建查询