CODEWITHSUNDEEP
javamysqlsqldatabase
Kenny
Kenny

Reputation: 9

SQLException : Operation not allowed after ResultSet closed

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

Upvotes: 0

Views: 1214

Answers (2)

BOUALI ALI
BOUALI ALI

Reputation: 230

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

Upvotes: 0

Karim
Karim

Reputation: 8652

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

Upvotes: 2

Related Questions