Почему пишет, что ResultSet закрыт?

02 июля 2021, 06:20

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
public class Main {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String connectionURL = "jdbc:postgresql://localhost:5432/test"; 
        String name = "postgres"; 
        String password = "parol22";
        try (Connection connection = DriverManager.getConnection(connectionURL, name, password);
            Statement statement = connection.createStatement()) 
        {
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS products(id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(60), size VARCHAR(10))");
            statement.executeUpdate("INSERT INTO products(id, name, size) VALUES(" + new Random().nextInt() + ", 'Ремень мужской, кожаный', 'XL'),"
                    + "(" + new Random().nextInt() + ", 'Куртка женская', 'S')," + "(" + new Random().nextInt() + ", 'Пенал детский', 'STD')");
            try (ResultSet resultSet1 = statement.executeQuery("SELECT * FROM products");
                ResultSet resultSet2 = statement.executeQuery("SELECT * FROM products WHERE (id % 2 = 0)"))
            {
                while (resultSet1.next()) {
                    System.out.println("id - " + resultSet1.getInt("id") + ", продукт - " 
                        + resultSet1.getString("name") + ", размер - " + resultSet1.getString("size") + ";");
                }
                while (resultSet2.next()) {
                    System.out.println("id - " + resultSet2.getInt("id") + ", продукт - " 
                        + resultSet2.getString("name") + ", размер - " + resultSet2.getString("size") + ";");
                }
            }
        } 
    }
}

Результат:

Exception in thread "main" org.postgresql.util.PSQLException: ResultSet закрыт.
    at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2741)
    at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1829)
    at medeelliinsan.Main.main(Main.java:28)

Answer 1

Я понимаю так: javadoc гласит

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

Следовательно, правильным использованием будет

try (Connection connection = DriverManager.getConnection(connectionURL, name, password);
     Statement statement = connection.createStatement();
     Statement statement2 = connection.createStatement()) {
    try (ResultSet resultSet1 = statement.executeQuery("SELECT * FROM tst_emp");
         ResultSet resultSet2 = statement2.executeQuery("SELECT * FROM tst_emp WHERE mod(emp_id, 2) = 0")) {
        while (resultSet1.next()) {
            System.out.println("id - " + resultSet1.getInt("emp_id") + ", служащий - " + resultSet1.getString("emp_name") + ";");
        }
        while (resultSet2.next()) {
            System.out.println("id - " + resultSet2.getInt("emp_id") + ", служащий - " + resultSet2.getString("emp_name") + ";");
        }
    }
}

Предложенный автором ответ содержит ручное управление ResultSet, что может привести к ошибкам (как в самом ответе - нет закрытия набора данных).

Answer 2

ResultSet закрывается сам при определённых условиях, чтобы работало и закрывалось, нужно и достаточно написать так:

package medeelliinsan;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
public class Main {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String connectionURL = "jdbc:postgresql://localhost:5432/test"; 
        String name = "postgres"; 
        String password = "parol22";
        try (Connection connection = DriverManager.getConnection(connectionURL, name, password);
            Statement statement = connection.createStatement()) 
        {
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS products(id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(60), size VARCHAR(10))");
            statement.executeUpdate("INSERT INTO products(id, name, size) VALUES(" + new Random().nextInt() + ", 'Ремень мужской, кожаный', 'XL'),"
                    + "(" + new Random().nextInt() + ", 'Куртка женская', 'S')," + "(" + new Random().nextInt() + ", 'Пенал детский', 'STD')");
            ResultSet resultSet1 = statement.executeQuery("SELECT * FROM products");
            while (resultSet1.next()) {
                System.out.println("id - " + resultSet1.getInt("id") + ", продукт - " 
                    + resultSet1.getString("name") + ", размер - " + resultSet1.getString("size") + ";");
            }
            ResultSet resultSet2 = statement.executeQuery("SELECT * FROM products WHERE (id % 2 = 0)");
            while (resultSet2.next()) {
                System.out.println("id - " + resultSet2.getInt("id") + ", продукт - " 
                    + resultSet2.getString("name") + ", размер - " + resultSet2.getString("size") + ";");
            }
        }
    }
}