How to Solve 'java.sql.SQLException: Operation not allowed after ResultSet closed'?

ResultSet Reuse Solution for "java.sql.SQLException: Operation not allowed after ResultSet closed"

When encountering the "java.sql.SQLException: Operation not allowed after ResultSet closed" error, it suggests that multiple result sets are being used concurrently from the same statement object. This violates the default expectation of the Statement class, which allows only one ResultSet to be open at a time.

To resolve this issue in the provided code, the following steps should be taken:

In the getStuff() method:

• The ResultSet object rs must be closed after extracting the necessary data to avoid conflicts with the other ResultSets.

In the calling code:

• The ResultSet object rs must also be closed to release its association with the statement object.
• A new PreparedStatement object should be created for executing the update query. This ensures that the original ResultSet objects remain unaffected.

The modified code with these fixes would look like:

try {
    //Get some stuff
    String name = "";
    String sql = "SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;";
    ResultSet rs = statement.executeQuery(sql);
    if (rs.next()) {
        name = rs.getString("name");
    }
    rs.close();  // Close the ResultSet 'rs'

    String sql2 = "SELECT `id` FROM  `profiles` WHERE `id` =" + profId + ";";
    ResultSet rs2 = statement.executeQuery(sql2);
    String updateSql = "INSERT INTO `blah`............";
    PreparedStatement pst = (PreparedStatement) connection.prepareStatement(updateSql);

    while (rs2.next()) {
        int id = rs2.getInt("id");
        int stuff = getStuff(id);

        pst.setInt(1, stuff);
        pst.addBatch();
    }
    rs2.close();  // Close the ResultSet 'rs2'

    pst.executeBatch();

} catch (Exception e) {
    e.printStackTrace();
}

private int getStuff(int id) {

    try {
        String sql = "SELECT ......;";
        ResultSet rs = statement.executeQuery(sql);

        if (rs.next()) {
            int result = rs.getInt("something");
            rs.close();  // Close the ResultSet 'rs' in 'getStuff' method
            return result;
        }
        return -1;
    }//code continues
}

By implementing these changes, the code ensures that all ResultSets are properly closed, preventing the "java.sql.SQLException: Operation not allowed after ResultSet closed" error.

The above is the detailed content of How to Solve 'java.sql.SQLException: Operation not allowed after ResultSet closed'?. For more information, please follow other related articles on the PHP Chinese website!