SQLException : Operation not allowed after ResultSet closed

Question

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.

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 + "';");

Possible duplicate of [How can I avoid ResultSet is closed exception in Java?](http://stackoverflow.com/questions/935511/how-can-i-avoid-resultset-is-closed-exception-in-java) — px06, Dec 02 '16 at 09:18
It is also worth noting that `rs1.next()` will close the connection if it cannot find anything returned in the `ResultSet`. You should check your db and see if there's data in there. — px06, Dec 02 '16 at 09:21

Karim · Answer 1 · 2016-12-02T09:55:14.010

2

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

edited Dec 02 '16 at 09:55

answered Dec 02 '16 at 09:20

Karim

8,454
3
25
33

score 0 · Answer 2 · answered Dec 02 '16 at 13:14

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

SQLException : Operation not allowed after ResultSet closed

2 Answers2