java.sql.SQLException: Operation not allowed after ResultSet closed Problem

Question

I am trying to run the following code but getting an error:

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

I want to run while (rs.next()) inside while (row.next()).

try {
    Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank_db", "root","");
    Statement stmt = conn.createStatement();
    ResultSet row = stmt.executeQuery("SELECT * FROM user WHERE id=(SELECT MAX(id) FROM user)");

    while (row.next()) {
        int rows = row.getInt("id");

        ResultSet rs = stmt.executeQuery("select * from user where account_number=" + acc + "");

        while (rs.next()) {
            // do something with rs
        }
        rs.close();
    }
    row.close();
    stmt.close();
    conn.close();

} catch (Exception e){
    ...
}

Answer 1

It is not possible to have two queries open on the same Statement at the same time (by default).

The JavaDoc of Statement states:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects.

and similar from the JavaDoc of ResultSet (less specific):

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

---

Unfortunately we are not able to know what is being done with both ResultSets - eventually a better solution would be to use one single, more adequate query (e.g. using JOIN or similar).