Why am I getting java.sql.SQLException: Operation not allowed after ResultSet closed

Question

So I've been looking around about this problem and it seems that the problem arises when a statement tries to get multiple ResultSets.

But in the following code I get the exception, even though the executeUpdate just returns an int.

ResultSet resultsRS = statement.executeQuery("select distinct snum from shipments where quantity >= 100");
int rowCount=0;

while(resultsRS.next()){
    statement.executeUpdate("UPDATE suppliers SET status = status + 5 WHERE snum = "+"\""+resultsRS.getString(1)+"\"");
    rowCount++;  
}

It runs one time fine, after that it gives the exception. How would I be able to fix this?

Answer 1

All execution methods in the Statement interface implicitly close a statment's current ResultSet. See docs So you need to store resultSet data in a temporary array and loop through it.

Or try to use another statement for executeUpdate.

Try something like this:

    ArrayList<String> tmp = new ArrayList<String>();
    while(resultsRS.next()){
        tmp.add(resultsRS.getString(1));
    }

    for (String s: tmp) {
        statement.executeUpdate("UPDATE suppliers SET status = status + 5 WHERE snum = "+"\""+s+"\"");
        rowCount++;
    }