PreparedStatement和ResultSet上的资源泄漏

Question

Eclipse显示警告消息，表示"资源泄漏，准备好的语句未在以下位置关闭"

在我看来，finally块负责关闭preparedstatement和rs对象。你知道为什么eclipse还在抱怨资源泄漏吗？

String ruleGrpId = null;
        int finalRuleID = 0;

        String saveRule1 = "INSERT INTO ..";
        String saveRule2 = "INSERT INTO ";

        String saveRule3 = "select ..._name = ?";

        PreparedStatement preparedstatement = null;
        ResultSet rs = null;

        try {
            if (objSomeRule.getRule() == 0) {

                preparedstatement = con.prepareStatement(saveRule1);
                preparedstatement.setString(1, ruleId);

                preparedstatement.executeUpdate();

                //Eclipse complains here
                preparedstatement = con.prepareStatement(saveRule2);

                preparedstatement.setInt(1, ruleID_2);
                preparedstatement.setString(2, ruleID_3);

                rs =  preparedstatement.executeQuery();

                if(rs.next()){
                    finalRuleID = rs.getInt(1);
                }else{
                    //complains here
                    preparedstatement = con.prepareStatement(saveRule3);
                    preparedstatement.setString(1, ruleID_4);
                    //complains here
                    rs =  preparedstatement.executeQuery();
                    if(rs.next()){
                        finalRuleID = rs.getInt("rulegroup_id");
                    }
                }
                objSomeRule.setRuleID(finalRuleID);

            }

        } finally {

            if(preparedstatement != null){
                preparedstatement.close();
            }

            if(rs != null){
                rs.close();
            }
        }

        return finalRuleID;
    }

Answer 1

您创建了3个不同的PreparedStatement实例：

preparedstatement = con.prepareStatement(saveRule1);

preparedstatement = con.prepareStatement(saveRule2);

preparedstatement = con.prepareStatement(saveRule3);

您的变量preparedStatement只保存最后一个打开的变量，并且您没有对之前打开的其他变量的引用
因此，在您的finally部分中，您只关闭最后一个PreparedStatement：

preparedstatement.close(); 

ResultSets也存在类似问题。您创建了两个实例，但只关闭了其中的一个。

Answer 2

因此，您的意思是在没有记录时创建另一个preparedStatement。

 if(rs.next())
 {
      finalRuleID = rs.getInt(1);
 }
 else
 {
    //close here first
    if(preparedstatement != null)
    {
       preparedstatement.close();
    }

    if(rs != null){
       rs.close();
    }

    preparedstatement = con.prepareStatement(saveRule3);
    // your code

但是，您还没有关闭上一个。我不太清楚，你为什么要这样做，但是，假设你需要这样做。然后，您应该先关闭上一个。

Answer 3

重复使用变量preparedStatement和rs可防止对变量的旧对象值调用close（）。Try with resources是一个简洁的解决方案，因为close（）是自动调用的，即使在抛出异常或在深度嵌套的Try中执行返回时也是如此。

    String ruleGrpId = null;
    int finalRuleID = 0;
    String saveRule1 = "INSERT INTO ..";
    String saveRule2 = "INSERT INTO ";
    String saveRule3 = "select ..._name = ?";

    if (objSomeRule.getRule() == 0) {
        try (PreparedStatement ps1 = con.prepareStatement(saveRule1)) {
            ps1.setString(1, ruleId);
            ps1.executeUpdate();
        }
        try (PreparedStatement ps2 = con.prepareStatement(saveRule2)) {
            ps2.setInt(1, ruleID_2);
            ps2.setString(2, ruleID_3);

            try (ResultSet rs2 =  ps2.executeQuery()) {

                if (rs2.next()) {
                    finalRuleID = rs2.getInt(1);
                } else {
                    try (Preparedstatement ps3 = con.prepareStatement(saveRule3)) {
                        ps3.setString(1, ruleID_4);
                        //complains here
                        try (ResultSet rs3 =  ps3.executeQuery()) {
                            if (rs3.next()) {
                                finalRuleID = rs3.getInt("rulegroup_id");
                            }
                        }
                    }
                }
            }
            objSomeRule.setRuleID(finalRuleID);
        }
    }

如果最后一个SELECT查询打算检索先前插入的自动增量ID，则可以使用getGeneratedKeys