java - 从数据库中删除时，ResultSet 关闭后不允许进行操作

Question

我目前正在尝试使用 JDBC 从数据库中删除项目，但收到一个错误，我不知道如何摆脱该错误。错误是:

Caused by: java.sql.SQLException: Operation not allowed after ResultSet closed

这是java:

    System.out.println("Connecting database for Delete...");
    Integer deletedCount = 0;

    Statement deleteStatement = null;
    try (Connection conn = DriverManager.getConnection(url, username, password)) {
        System.out.println("Database connected!");
        deleteStatement = conn.createStatement();
        String selectDelete = "SELECT id FROM table WHERE end <= (NOW() - INTERVAL 1 HOUR)";

        ResultSet rs = deleteStatement.executeQuery(selectDelete);

            while (rs.next()) {

                String eventid = rs.getString("id");
                String deleteSQL = "DELETE FROM table WHERE id = " + eventid;
                deleteStatement.executeUpdate(deleteSQL);
                deletedCount++;
                System.out.println(deletedCount);
            }
       System.out.println("Completed Delete! "+ deletedCount +" deleted!");
    } catch (SQLException e) {
        throw new IllegalStateException("Cannot connect the database!", e);
    }

我在这里所做的是首先选择日期已经过去的所有项目并将其设置为结果集。然后我经历一个 while 循环，试图将它们从数据库中删除。它运行一次并得到错误，我将在下面完整地说明这一点。每次执行循环时是否都需要创建一个新的删除语句？我无法找到正确执行此操作的方法。

这是完整的错误:

Connecting database for Delete...
Database connected!
1
Exception in thread "main" java.lang.IllegalStateException: Cannot connect the database!
at JDBC.deleteOverDueEvents(JDBC.java:56)
at EventJSON.main(EventJSON.java:31)
Caused by: java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:743)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6313)
at JDBC.deleteOverDueEvents(JDBC.java:45)
... 1 more

Answer 1

为什么要选择、循环和删除

String deleteSQL= "DELETE FROM table WHERE end <= (NOW() - INTERVAL 1 HOUR)";
Statement deleteStatement = conn.createStatement();
deleteStatement.executeUpdate(deleteSQL);

工作效率更高