How to Resolve a 'java.sql.SQLException: Operation not allowed after ResultSet closed' Exception in Java?

Operation Not Allowed: ResultSet Closed Exception in Java

When executing certain SQL queries, developers may encounter a "java.sql.SQLException: Operation not allowed after ResultSet closed" exception. This error typically occurs when multiple ResultSets are attempted simultaneously from the same Statement object.

Consider the following code snippet that demonstrates this issue:

// Problem code: using the same statement object to create multiple ResultSets

try {
    // Execute the first query and obtain ResultSet rs
    ResultSet rs = statement.executeQuery("SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;");

    // Execute the second query and obtain ResultSet rs2
    ResultSet rs2 = statement.executeQuery("SELECT `id` FROM  `profiles` WHERE `id` =" + profId + ";");

    // Prepare a new statement and attempt to use rs2
    PreparedStatement pst = (PreparedStatement)connection.prepareStatement("INSERT INTO `blah`............");

    // Process data from rs2 and update the database using pst
    while(rs2.next()) {
        int id = rs2.getInt("id");
        int stuff = getStuff(id);

        pst.setInt(1, stuff);
        pst.addBatch();
    }

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

In this code, two ResultSets (rs and rs2) are created from the same Statement object (statement). However, while rs is not explicitly closed, it is implicitly closed when the second query is executed and rs2 is obtained. This results in the exception because rs2 is attempting to operate on a closed ResultSet.

Resolution:

To resolve this issue, it is crucial to close all ResultSets before using the same Statement object to obtain new ResultSets. This can be achieved by using try-finally blocks to ensure that the ResultSet is closed even if an exception occurs:

// Corrected code: closing the ResultSet objects
try {
    // Execute the first query and obtain ResultSet rs
    ResultSet rs = statement.executeQuery("SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;");

    // Use rs to retrieve data
    if(rs.next()) {
        String name = rs.getString("name");
    }

    // Close rs
    rs.close();

    // Execute the second query and obtain ResultSet rs2
    ResultSet rs2 = statement.executeQuery("SELECT `id` FROM  `profiles` WHERE `id` =" + profId + ";");

    // Use rs2 to retrieve data
    while(rs2.next()) {
        int id = rs2.getInt("id");
        int stuff = getStuff(id);

        pst.setInt(1, stuff);
        pst.addBatch();
    }

    // Close rs2
    rs2.close();

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

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