SQLException：ResultSet关闭后不允许操作

Question

我正在将MySQL与Java结合使用，试图制作shoppingcartDB，并试图删除从订购到现在已经过了30天的元组。

但是编译器说：

我如何解决这个问题？

代码：

public static void checkBasketdate() throws Exception {
    //Connect to MySQL:
    Connection con = makeConnection();
    Statement stmt = con.createStatement();
    ResultSet rs1 = stmt.executeQuery("SELECT * FROM basket ;");
    while (rs1.next()) {
        Date Odate = rs1.getDate("orderdate");
        long diff = datediffOfDate(Odate);
        System.out.println(Odate);
        if (diff > 30) {
            //This is where the Exception is thrown:
            stmt.executeUpdate("DELETE FROM basket WHERE orderdate = '" + Odate + "';");
            System.out.println("=>orderdate has been passed 30 days, so delete it");
        }
    }
}

抛出异常的代码行是：

stmt。executeUpdate（“从篮子中删除，其中orderdate=““Odate”；”）；

Answer 1

您可以简单地使用两个语句，第一个用于选择记录，第二个用于删除所需的记录

public static void checkBasketdate() throws Exception {
    //Connect to MySQL:
    Connection con = makeConnection();
    Statement stmt = con.createStatement();
    Statement stmtDelete = con.createStatement();
    ResultSet rs1 = stmt.executeQuery("SELECT * FROM basket ;");
    while (rs1.next()) {
        Date Odate = rs1.getDate("orderdate");
        long diff = datediffOfDate(Odate);
        System.out.println(Odate);
        if (diff > 30) {
            //This is where the Exception is thrown:
            stmtDelete.executeUpdate("DELETE FROM basket WHERE orderdate = '" + Odate + "';");
            System.out.println("=>orderdate has been passed 30 days, so delete it");
        }
    }
    stmnt.close();
    stmntDelete.close();
}

Answer 2

您可以在同一时刻执行一条语句，最好的选择是关闭（stmt.close（））第一条语句，然后运行第二条语句