如何避免'Statement关闭后不允许操作'异常并正确使用ResultSet？

问题背景 #

需要优化代码以避免出现java.sql.SQLException: No operations allowed after statement closed.异常，错误发生在saveData方法的else database.getStatement().execute(data.getDao().getSQLInsert(data));行，该方法用于用户退出时保存数据。

原代码 #

saveData方法代码 #

public void saveData(GenericData data) {
        database.open();

        try {
            ResultSet resultSet = database.getStatement().executeQuery(data.getDao().getSQLQuery(data));

            if (resultSet.next()) {
                if (data.getDao().getSQLUpdate(data) != null) database.getStatement().executeUpdate(data.getDao().getSQLUpdate(data));
            }
            else database.getStatement().execute(data.getDao().getSQLInsert(data));

            resultSet.close();
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }

        database.close();
    }

database.open方法代码 #

public boolean open() {
        if (isConnected()) {
            return true;
        }

        try {
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            if (this.connection == null) {
                this.connection = DriverManager.getConnection(url, user, password);
            }

            if (this.statement == null && this.connection != null) {
                this.statement = this.connection.createStatement();
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return isConnected();
    }

错误栈信息 #

[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]: java.sql.SQLException: No operations allowed after statement closed.
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at it.thedarksword.essentialsvc.database.controller.DataController.saveData(DataController.java:208)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at it.thedarksword.essentialsvc.database.process.DataProcess.lambda$save$0(DataProcess.java:21)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1760)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]: Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:336)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:655)
[09:00:27] [ForkJoinPool.commonPool-worker-14/ERROR]:   ... 10 more

问题原因 #

1. 复用Statement对象导致ResultSet自动关闭：同一个Statement执行新SQL时，之前生成的ResultSet会被JDBC驱动自动关闭，后续操作该Statement会触发状态异常。
2. 多线程并发冲突：database类中的connection和statement是全局共享的，而saveData在ForkJoinPool线程中执行，多线程同时调用时会导致Statement被其他线程提前关闭或占用。
3. 资源关闭逻辑不严谨：手动关闭ResultSet的时机不当，且未确保Statement和Connection的正确关闭顺序，容易引发资源泄漏。

优化方案 #

核心优化点 #

• 不再复用全局Statement，每次SQL操作使用独立的Statement对象。
• 使用Java 7+的try-with-resources语法，自动管理Connection、Statement、ResultSet的生命周期，无需手动调用close。
• 确保多线程环境下资源独立性，避免共享连接和Statement。

优化后的代码 #

方案1：简化版（直接创建独立资源）

public void saveData(GenericData data) {
    String url = database.url;
    String user = database.user;
    String password = database.password;

    // try-with-resources自动按顺序关闭Connection、Statement
    try (Connection connection = DriverManager.getConnection(url, user, password);
         Statement queryStmt = connection.createStatement()) {

        // ResultSet同样用try-with-resources包裹，自动关闭
        try (ResultSet resultSet = queryStmt.executeQuery(data.getDao().getSQLQuery(data))) {
            if (resultSet.next()) {
                String updateSql = data.getDao().getSQLUpdate(data);
                if (updateSql != null) {
                    // 执行更新用独立的Statement
                    try (Statement updateStmt = connection.createStatement()) {
                        updateStmt.executeUpdate(updateSql);
                    }
                }
            } else {
                String insertSql = data.getDao().getSQLInsert(data);
                // 执行插入用独立的Statement
                try (Statement insertStmt = connection.createStatement()) {
                    insertStmt.execute(insertSql);
                }
            }
        }
    } catch (SQLException throwable) {
        throwable.printStackTrace();
    }
}

方案2：优化Database类封装

先修改Database类，移除全局Statement，改为提供安全获取连接的方法：

public class Database {
    private String url;
    private String user;
    private String password;
    private Connection connection;

    public Database(String url, String user, String password) {
        this.url = url;
        this.user = user;
        this.password = password;
    }

    // 获取可用连接，自动初始化或重新连接
    public Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                connection = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                throw new SQLException("数据库驱动加载失败", e);
            }
        }
        return connection;
    }

    // 关闭连接
    public void close() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

再修改saveData方法：

public void saveData(GenericData data) {
    try (Connection connection = database.getConnection();
         Statement queryStmt = connection.createStatement()) {

        try (ResultSet resultSet = queryStmt.executeQuery(data.getDao().getSQLQuery(data))) {
            if (resultSet.next()) {
                String updateSql = data.getDao().getSQLUpdate(data);
                if (updateSql != null) {
                    try (Statement updateStmt = connection.createStatement()) {
                        updateStmt.executeUpdate(updateSql);
                    }
                }
            } else {
                String insertSql = data.getDao().getSQLInsert(data);
                try (Statement insertStmt = connection.createStatement()) {
                    insertStmt.execute(insertSql);
                }
            }
        }
    } catch (SQLException throwable) {
        throwable.printStackTrace();
    } finally {
        // 按需关闭连接，使用连接池时可省略此步骤
        database.close();
    }
}

额外建议 #

如果是高并发场景，建议使用数据库连接池（如HikariCP）代替手动创建连接，连接池会自动管理连接的复用和生命周期，既提升性能，又能避免多线程资源冲突问题。

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