SQLException：ResultSet关闭后不允许操作

I'm using MySQL with Java, trying to make shoppingcartDB, and I'm trying to delete the tuples for which 30 days from ordering have passed.

我正在使用MySQL和Java,尝试制作shoppingcartDB,我正在尝试删除已经过了30天的元组。

But the compiler says:

但是编译器说:

Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed

How can I solve this problem?

我怎么解决这个问题?

Code:

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

The line of code where the Exception is thrown is:

抛出异常的代码行是:

stmt.executeUpdate("DELETE FROM basket WHERE orderdate = '" + Odate + "';");

stmt.executeUpdate(“DELETE FROM basket WHERE orderdate ='”+ Odate +“';”);

2 个解决方案

#1

you can have one statement executing at one moment, the best option for you is to close (stmt.close() ) the first statement and run the second one

你可以在一个时刻执行一个语句,最好的选择是关闭(stmt.close())第一个语句并运行第二个语句

#2

You can simply use two statements, the first to select the records and the second to delete the needed ones

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

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

#1