Why Am I Getting \'java.sql.SQLException: Operation not allowed after ResultSet closed\' in Java JDBC?

Java: Error Using ResultSet after Connection Closure

When attempting to retrieve data from a MySQL database using JDBC, users may encounter the error: "java.sql.SQLException: Operation not allowed after ResultSet closed." This occurs when trying to use a ResultSet object after the underlying connection has been closed.

Explanation

JDBC operates on a concept of lazy loading, where the results of a query are not fully retrieved until requested. The ResultSet object provides a cursor over the results, allowing you to iterate through them. However, closing the connection that created the ResultSet also closes the cursor, making it unusable.

Solution

To avoid this error, one must either keep the connection open until the ResultSet is no longer needed or use a different approach.

One recommended approach is to use a row mapper to convert the result set into a collection of objects. This method encapsulates the conversion process and allows you to access the results even after the connection is closed:

<code class="java">public static List<T> sqlquery(String query, RowMapper<T> rowMapper) throws SQLException {
    try (Connection connection = DriverManager.getConnection(...);
         Statement st = connection.createStatement();
         ResultSet rs = st.executeQuery(query)) {
        List<T> list = new ArrayList<>();
        while (rs.next()) {
            list.add(rowMapper.mapRow(rs));
        }
        return list;
    }
}</code>

This modified code uses try-with-resources to ensure that the connection, statement, and result set are automatically closed when the method completes, eliminating the possibility of forgetting to close them manually. Additionally, it uses a row mapper to convert each row in the result set into a populated object, which can then be collected into a list.

Conclusion

By understanding the lazy loading mechanism of JDBC and utilizing row mappers, developers can effectively retrieve data from databases while avoiding errors related to closed ResultSets after connection closure.

The above is the detailed content of Why Am I Getting \'java.sql.SQLException: Operation not allowed after ResultSet closed\' in Java JDBC?. For more information, please follow other related articles on the PHP Chinese website!