How to Avoid `java.sql.SQLException: Operation not allowed after ResultSet closed` in Java?

Returning a ResultSet

When querying a database, it is necessary to handle the returned results effectively. One common issue arises when attempting to return a ResultSet from a method that queries the database. However, returning a ResultSet directly can lead to resource leaks and improper resource management.

The error "java.sql.SQLException: Operation not allowed after ResultSet closed" indicates that the ResultSet object has been closed before the method could return it. To resolve this, it is recommended to map the ResultSet to a collection of Javabeans and return that instead. This approach ensures that the ResultSet is closed automatically after use, preventing resource leaks.

An example of how to map the ResultSet to a list of Javabeans is provided below:

public List<Biler> list() throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Biler> bilers = new ArrayList<Biler>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
        resultSet = statement.executeQuery();

        while (resultSet.next()) {
            Biler biler = new Biler();
            biler.setId(resultSet.getLong("id"));
            biler.setName(resultSet.getString("name"));
            biler.setValue(resultSet.getInt("value"));
            bilers.add(biler);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    return bilers;
}

In this example, the list() method queries the database, maps the results to a list of Biler objects, and returns the list. This ensures that the ResultSet is closed properly and prevents resource leaks.

Another approach is to use the try-with-resources statement, which automatically closes the resources when the code block completes:

public List<Biler> list() throws SQLException {
    List<Biler> bilers = new ArrayList<Biler>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            Biler biler = new Biler();
            biler.setId(resultSet.getLong("id"));
            biler.setName(resultSet.getString("name"));
            biler.setValue(resultSet.getInt("value"));
            bilers.add(biler);
        }
    }

    return bilers;
}

By following these recommendations, developers can ensure that ResultSets are handled properly, preventing resource leaks and ensuring efficient database access in Java applications.

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