java.sql.SQLException: Operation not allowed after

2019-01-20 19:11发布

问题:

I keep getting this whenever I try to use this method.

java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:794)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7077)
    at server.util.Plimus$1.run(Plimus.java:77)

This is on line 77: while (resultSet.next()) {

public static void process(final Player c, final String playerName) {
    if (connection == null && connectionStatus == 0)
        createConnection();
    else if (connectionStatus != 0)
        return;
    new Thread() {
        @Override
        public void run() {
            try {
                String username = playerName.replaceAll(" ", "_");
                String query = "SELECT * FROM donations WHERE username = '" + username + "' AND received = '0' LIMIT 1;";
                ResultSet resultSet = query(query);
                while (resultSet.next()) {
                    int[] contractIds = {3178768, 1}; //put all of your contract ids in here.
                    int contractId = Integer.parseInt(resultSet.getString("contract")), id = Integer.parseInt(resultSet.getString("id"));
                    query("UPDATE donations SET received = '1' WHERE username = '" + username + "' AND id = '" + id + "';");
                    if (contractId == contractIds[0]) { //first contract id in array.
                        c.getItems().addItem(962, 1);
                    } else if (contractId == contractIds[1]) { //second contract id in array.
                        c.getItems().addItem(962, 1);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                processMethod(0, true, true, false);
            }
        }
    }.start();
}

Here is the query method that is requested.

/**
 * Creates query function.
 */
public static ResultSet query(String s) throws SQLException {
    try {
        if (s.toLowerCase().startsWith("select")) {
            ResultSet resultSet = statement.executeQuery(s);
            return resultSet;
        } else {
            statement.executeUpdate(s);
        }
        return null;
    } catch (Exception e) {
        e.printStackTrace();
        processMethod(0, true, true, false);
    }
    return null;
}

回答1:

While iterating through your ResultSet, you are reusing the Statement object for updating the data (inside your query method), which closes the ResultSet from the query.

See the ResultSet documentation:

"A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results."

You should create separate Statements for the query and the update, and pass them to your query method:

public ResultSet query(String s, Statement statement) throws SQLException {
    ...
}

I assume that statement is declared static inside your class - there is usually no need for that: create the two statements for query and update in the constructor of your class, and either pass them to the query() method or use the one or the other depending on the statement.