java - 结果集已关闭(插入之前)

Question

在 SQL 插入之前关闭结果集存在问题(Java/Netbeans)

Connection var2 = DBConnPool.getInstance().getConnection();
Statement var3 = null;
String var5;
ResultSet var8 = null;

try {
    var3 = var2.createStatement();

    var5 = "insert into triggers (ts, nodeip, trapname) values (now(), '"+ipaddress+"', '"+trapname+"')";
    var3.execute(var5);

    var5 = "select asset.id as assetid, node.id as nodeid \n" +
        "from asset, node\n" +
        "where asset.name LIKE 'Trigger%'\n" +
        "and node.ipaddress=\'"+ipaddress+"'";
    var8 = var3.executeQuery(var5);

    while(var8.next()){
        int var31 = var8.getInt("assetid");
        int var32 = var8.getInt("nodeid");

        var5 = "insert into alerts (ts, assetid, alerttypeid, nodeid, ack) values (now(), " + var31 + ", 15, " + var32 + ", 0)";
        var3.execute(var5);
        var5 ="insert into logs (ts, rssi, nodeid, assetid) values (now(), 1000, " + var32 + ", " + var31 + ")";
        var3.execute(var5);
    }
}

选择 asset.id 查询似乎运行正常，但在

while(var8.next()) {
    int var31 = var8.getInt("assetid");
    int var32 = var8.getInt("nodeid");

ResultSet 关闭后不允许进行操作。

我是否必须在 while(var8.next()) 之外进行插入，如果是这样，我如何恢复/引用变量 var31 和 var32？

抱歉，我知道变量名称不是很有帮助，但插入命令应该很简单(或者我是这么认为的)

感谢您的想法

问候活跃

Answer 1

尝试分离每个声明的责任:

Connection var2 = DBConnPool.getInstance().getConnection();
Statement var3 = null;
Statement stmQuery = null;
String var5;
ResultSet var8 = null;

try {
    var3 = var2.createStatement();
    stmQuery = var2.createStatement();

    var5 = "insert into triggers (ts, nodeip, trapname) values (now(), '"+ipaddress+"', '"+trapname+"')";
    var3.execute(var5);

    var5 = "select asset.id as assetid, node.id as nodeid \n" +
        "from asset, node\n" +
        "where asset.name LIKE 'Trigger%'\n" +
        "and node.ipaddress=\'"+ipaddress+"'";
    var8 = stmQuery.executeQuery(var5);

    while(var8.next()){
        int var31 = var8.getInt("assetid");
        int var32 = var8.getInt("nodeid");

        var5 = "insert into alerts (ts, assetid, alerttypeid, nodeid, ack) values (now(), " + var31 + ", 15, " + var32 + ", 0)";
        var3.execute(var5);
        var5 ="insert into logs (ts, rssi, nodeid, assetid) values (now(), 1000, " + var32 + ", " + var31 + ")";
        var3.execute(var5);
    }
} catch (Exception e) {
    //Somthing went wrong!
} finally {
    var3.close();
    stmQuery.close();
    var8.close();
    var2.close();
}

提示:如您所见，关闭finally block 中的资源非常重要。

希望这有帮助!