在GridDB中使用JDBC时为何出现JDBC_ALREADY_CLOSED错误？

我正在开发一个Java应用，集成GridDB并使用JDBC与数据库交互，运行时遇到如下错误：

JDBC_ALREADY_CLOSED ERROR
Status check of client request failed. Operation may be wrong.
A method other than isClosed() was invoked for an object that has a close() method,
e.g., a Java.sql.Connection object, etc., after it has been closed.
Check the method invoking sequence.

我的代码如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class GridDBJDBC {
    public static void main(String[] args) {
        Connection connection = null;

        try {
            // Step 1: Establish a JDBC connection
            String url = "jdbc:gs://192.168.0.1:10001/farwa_db";
            String user = "admin";
            String password = "admin786";
            connection = DriverManager.getConnection(url, user, password);

            // Step 2: Execute a query
            String query = "SELECT * FROM Employee WHERE score > ?";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setDouble(1, 50.0);
            ResultSet resultSet = preparedStatement.executeQuery();

            // Step 3: Process results
            while (resultSet.next()) {
                System.out.println("Name: " + resultSet.getString("name") + 
                                   ", Score: " + resultSet.getDouble("score"));
            }

            // Step 4: Close the connection
            connection.close();

            // Step 5: Attempt to reuse the closed connection (causes the error)
            preparedStatement.setDouble(1, 75.0); // Error occurs here
            resultSet = preparedStatement.executeQuery();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null && !connection.isClosed()) {
                    connection.close();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }
}

问题原因 #

代码中提前关闭了Connection对象，之后又尝试调用关联的PreparedStatement方法。在JDBC规范中，当Connection被关闭后，基于该Connection创建的PreparedStatement、ResultSet等对象都会被自动关闭，此时再调用这些对象的方法就会触发JDBC_ALREADY_CLOSED ERROR。

修复方案 #

调整代码逻辑，确保在所有基于Connection的操作完成后再关闭连接：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class GridDBJDBC {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // Step 1: Establish a JDBC connection
            String url = "jdbc:gs://192.168.0.1:10001/farwa_db";
            String user = "admin";
            String password = "admin786";
            connection = DriverManager.getConnection(url, user, password);

            // Step 2: Execute first query
            String query = "SELECT * FROM Employee WHERE score > ?";
            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setDouble(1, 50.0);
            resultSet = preparedStatement.executeQuery();

            // Step 3: Process first results
            while (resultSet.next()) {
                System.out.println("Name: " + resultSet.getString("name") + 
                                   ", Score: " + resultSet.getDouble("score"));
            }

            // Step 4: Execute second query (reuse PreparedStatement before closing connection)
            preparedStatement.setDouble(1, 75.0);
            resultSet = preparedStatement.executeQuery();

            // Step 5: Process second results
            while (resultSet.next()) {
                System.out.println("Name: " + resultSet.getString("name") + 
                                   ", Score: " + resultSet.getDouble("score"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 按顺序关闭资源：ResultSet -> PreparedStatement -> Connection
            try {
                if (resultSet != null) resultSet.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                if (preparedStatement != null) preparedStatement.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            try {
                if (connection != null && !connection.isClosed()) {
                    connection.close();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }
}

额外优化建议 #

• 使用try-with-resources语法，它会自动关闭实现了AutoCloseable接口的资源（JDBC 4.1及以上支持），无需手动在finally块中关闭，代码更简洁安全：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class GridDBJDBC {
    public static void main(String[] args) {
        String url = "jdbc:gs://192.168.0.1:10001/farwa_db";
        String user = "admin";
        String password = "admin786";
        String query = "SELECT * FROM Employee WHERE score > ?";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             PreparedStatement preparedStatement = connection.prepareStatement(query)) {

            // 执行第一个查询
            preparedStatement.setDouble(1, 50.0);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    System.out.println("Name: " + resultSet.getString("name") + 
                                       ", Score: " + resultSet.getDouble("score"));
                }
            }

            // 执行第二个查询
            preparedStatement.setDouble(1, 75.0);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    System.out.println("Name: " + resultSet.getString("name") + 
                                       ", Score: " + resultSet.getDouble("score"));
                }
            }

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

内容的提问来源于stack exchange，提问作者Farwa Waheed