java - ResultSet 关闭错误后不允许操作

Question

我有一个代码在处理后将数据插入表中，但我一次又一次地收到错误

Operation not allowed after ResultSet closed

这是我的代码。

 try {
        Connection con = null;
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("Select * from asteriskcdrdb.sp1");
        while (rs.next()) {
            AreaCode = rs.getString("AreaCode");
            //System.out.println(AreaCode);
            String Pulse = rs.getString("Pulse");
            Rate = rs.getInt("Rate/pulse");
            // System.out.println(Rate);
            if (AreaCode.equals(str)) {
                System.out.println("Hii");
                try {
                    Connection conn = null;
                    Class.forName("com.mysql.jdbc.Driver");
                    conn = DriverManager.getConnection(
                            "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
                    Statement stmt = conn.createStatement();
                    rst = stmt.executeQuery("Select * from cdr where src ='9035020090'");
                    while (rst.next()) {
                        calldate = rst.getString("calldate");
                        // System.out.println(calldate);
                        clid = rst.getString("clid");
                        src = rst.getString("src");
                        dst = rst.getString("dst");
                        dcontext = rst.getString("dcontext");
                        channel = rst.getString("channel");
                        dstchannel = rst.getString("dstchannel");
                        lastapp = rst.getString("lastapp");
                        lastdata = rst.getString("lastdata");
                        duration = rst.getString("duration");
                        //System.out.println(duration);
                        dur = Integer.parseInt(duration);
                        //System.out.println(dur);
                        data.add(dur);
                        billsec = rst.getString("billsec");
                        disposition = rst.getString("disposition");
                        amaflags = rst.getString("amaflags");
                        accountcode = rst.getString("accountcode");
                        uniqueid = rst.getString("uniqueid");
                        userfield = rst.getString("userfield");
                        int newcost = checktime(dur, Rate);
                        stmt.executeUpdate("insert into cdrcost (
         calldate,clid,src,dst,dcontext,channel,
         dstchannel,lastapp, lastdata,duration,billsec,
         disposition,amaflags,accountcode,uniqueid,
         userfield,cdrcost) values ('" + calldate + "','" + 
         clid + "','" + src + "','" + dst + "','" + dcontext 
         + "','" + channel + "','" + dstchannel + "','" + 
         lastapp + "','" + lastdata + "','" + duration + "','" + 
         billsec + "','" + disposition + "','" + amaflags 
          + "','" + accountcode + "','" + uniqueid + "','" + userfield  
         + "','" + newcost + "')");
                     }

                } catch (Exception e) {
                    System.out.println(e);
                }
            } else if (AreaCode.equals(str2)) {
                System.out.println("Hii2");
            }
        }

    } catch (Exception e) {
        System.out.println(e);
    }
}

public static int checktime(int dur, int Rate) {
    int cost = 0;

    // System.out.println(c);
    int min = 60;

    int quotient = dur / min;
    // System.out.println(quotient);

    int reminder = dur % min;
    //  System.out.println(reminder);

    if (reminder > 0) {
        quotient = quotient + 1;
        // System.out.println(quotient);

        // System.out.println(cost);
    }
    cost = quotient * Rate;
    return cost;
}

Answer 1

在给你答案之前，你应该了解一些关于数据库访问和JDBC的基本知识:

• 在大型操作中，您不得创建许多连接来访问数据库。如果您需要在单一方法中读取、插入、更新或删除数据，您应该只使用 1 个连接。打开连接是一个成本很高的操作。如果您还没有注意到它，那是因为您处于单用户(您)环境中。

• 每个语句使用一个或多个 ResultSet。由于您是初学者，假设每个 Statement 都有一个 ResultSet。如果你修改了一个Statement中的数据，这个Statement绑定(bind)的ResultSet将被关闭，不能在以后的操作中使用。这就是您遇到问题的原因(如其他答案中所述)。

• 如果您要执行一条使用参数的 SQL 语句，请使用 PreparedStatement。否则，您的应用程序将容易受到 SQL 注入(inject)攻击(即黑客可能会关闭您的数据库服务器，您和我都知道这是一件坏事)。

• 您应该在使用后关闭资源。这意味着，您应该关闭 ResultSet、Statement 和 Connection(按此顺序)。

根据所有这些注释，您的代码将更改为:

Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/asteriskcdrdb", "root", "techsoft");
    st = con.createStatement();
    rs = st.executeQuery("Select * from asteriskcdrdb.sp1");
    while (rs.next()) {
        AreaCode = rs.getString("AreaCode");
        String Pulse = rs.getString("Pulse");
        Rate = rs.getInt("Rate/pulse");
        if (AreaCode.equals(str)) {
            Statement stmt = null;
            ResultSet rst = null;
            PreparedStatement insSt = null;
            try {
                //using the first connection
                stmt = con.createStatement();
                rst = stmt.executeQuery("Select * from cdr where src ='9035020090'");
                while (rst.next()) {
                    calldate = rst.getString("calldate");
                    clid = rst.getString("clid");
                    src = rst.getString("src");
                    dst = rst.getString("dst");
                    dcontext = rst.getString("dcontext");
                    channel = rst.getString("channel");
                    dstchannel = rst.getString("dstchannel");
                    lastapp = rst.getString("lastapp");
                    lastdata = rst.getString("lastdata");
                    duration = rst.getString("duration");
                    dur = Integer.parseInt(duration);
                    data.add(dur);
                    billsec = rst.getString("billsec");
                    disposition = rst.getString("disposition");
                    amaflags = rst.getString("amaflags");
                    accountcode = rst.getString("accountcode");
                    uniqueid = rst.getString("uniqueid");
                    userfield = rst.getString("userfield");
                    int newcost = checktime(dur, Rate);
                    //every ? is a parameter in the query
                    insSt = con.prepareStatement(
                        "insert into cdrcost (calldate,clid,src,dst,dcontext,channel, dstchannel, lastapp, lastdata,duration,billsec, disposition,amaflags,accountcode,uniqueid, userfield,cdrcost) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    //setting every parameter
                    insSt.setObject(1, calldate);
                    insSt.setObject(2, clid);
                    insSt.setObject(3, src);
                    insSt.setObject(4, dst);
                    insSt.setObject(5, dcontext);
                    insSt.setObject(6, channel);
                    insSt.setObject(7, dstchannel);
                    insSt.setObject(8, lastapp);
                    insSt.setObject(9, lastdata);
                    insSt.setObject(10, duration);
                    insSt.setObject(11, billsec);
                    insSt.setObject(12, disposition);
                    insSt.setObject(13, amaflags);
                    insSt.setObject(14, accountcode);
                    insSt.setObject(15, uniqueid);
                    insSt.setObject(16, userfield);
                    insSt.setObject(17, newcost);
                    //executing the insert statement
                    insSt.executeUpdate();
                }
            } catch (Exception e) {
                System.out.println(e);
            } finally {
                //closing the resources in this transaction
                try {
                    //the insSt statement doesn't have a resultset
                    if (insSt != null) {
                        insSt.close();
                    }
                    //the rst ResultSet is bounded to stmt Statement, it must be closed first
                    if (rst != null) {
                        rst.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException sqle) {}
            }
        } else if (AreaCode.equals(str2)) {
            System.out.println("Hii2");
        }
    }
} catch (Exception e) {
    System.out.println(e);
} finally {
    //closing the resources in this transaction
    //similar logic than the used in the last close block code
    try {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        //at the last of all the operations, close the connection
        if (con != null) {
            con.close();
        }
    } catch (SQLException sqle) {}
}

---

附带说明一下，您是初学者并不意味着您应该编写代码只是为了让它工作。您应该始终遵循最佳做法。 IMO 最好在这些情况下寻求指导。