java - 获取玩家统计数据时出现 MySQLNonTransientConnectionException

Question

我将迷你游戏的玩家统计数据存储在 SQL 数据库中，但在加载统计数据时遇到以下错误:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
[23:37:09] [Server thread/WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[23:37:09] [Server thread/WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[23:37:09] [Server thread/WARN]:    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[23:37:09] [Server thread/WARN]:    at java.lang.reflect.Constructor.newInstance(Unknown Source)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.Util.getInstance(Util.java:408)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1187)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1182)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4035)
[23:37:09] [Server thread/WARN]:    at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4004)

代码:

public void loadPlayer(Player p) {
    if (!isPlayerInDataBase(p)) {
        Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
            @Override
            public void run() {
                try (Connection connection = sqlConnection.c) {
                    PreparedStatement insert = connection.prepareStatement(
                            "INSERT INTO `MurderData` (uuid, wins, deaths, loses, kills, score) VALUES (?, ?, ?, ?, ?, ?)");
                    insert.setString(1, p.getUniqueId().toString());
                    insert.setInt(2, 0);
                    insert.setInt(3, 0);
                    insert.setInt(4, 0);
                    insert.setInt(5, 0);
                    insert.setInt(6, 0);
                    insert.execute();
                    ClosePreparedStatement(insert);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
    }

    if (isPlayerInDataBase(p)) {
        Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
            @Override
            public void run() {
                try (Connection connection = sqlConnection.c;
                     PreparedStatement select = connection.prepareStatement(
                             "SELECT * FROM `MurderData` WHERE uuid='" + p.getUniqueId().toString() + "'")) {
                    ResultSet result = select.executeQuery();
                    if (result.next()) {
                        if (getPlayerData(p) != null) {
                            getPlayerData(p).adddeaths(result.getInt("deaths"));
                            getPlayerData(p).addkill(result.getInt("kills"));
                            getPlayerData(p).addwins(result.getInt("wins"));
                            getPlayerData(p).addlose(result.getInt("loses"));
                            getPlayerData(p).addscore(result.getInt("score"));
                        }
                        CloseResultSet(result);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        });
    }
}

这段代码的错误:

PreparedStatement select = connection.prepareStatement(
                        "SELECT * FROM `MurderData` WHERE uuid='" + p.getUniqueId().toString() + "'")) {

完整代码:

public boolean isPlayerInDataBase(Player p) {
    try (Connection connection = sqlConnection.c;
         PreparedStatement select = connection.prepareStatement(
                 "SELECT * FROM `MurderData` WHERE uuid='" + p.getUniqueId().toString() + "'")) {
        ResultSet result = select.executeQuery();
        if (result.next()) {
            result.close();
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

附带问题:完成该过程后我应该关闭结果集和准备好的语句吗？我应该立即执行还是延迟执行？

Answer 1

看来您已经采取了try-with-resources来自其他代码的语句而不了解它的作用。从该链接:

The try-with-resources statement is a try statement that declares one or more resources. A resource is an object that must be closed after the program is finished with it. The try-with-resources statement ensures that each resource is closed at the end of the statement. Any object that implements java.lang.AutoCloseable, which includes all objects which implement java.io.Closeable, can be used as a resource.

换句话说，当你使用语法时

try (Connection connection = sqlConnection.c) {
     // ...
}

您正在隐式调用 close()在 sqlConnection.c 上，不再分配给它。因此，下次您尝试从此连接查询数据库时，它仍然处于关闭状态，因此您会收到此错误。

一个简单的修复方法是将局部变量 connection 的声明移出 try-with-resources 语句，这样它就不会在方法结束时关闭。您还应该查看程序中其他位置使用 try-with-resources 语法的位置，并确保没有关闭任何您不想关闭的内容。

这是您的代码的固定版本(来自您在评论中链接的 pastebin):

public boolean isPlayerInDataBase(Player p) {
    Connection connection = sql.getConnection();
    try (PreparedStatement select = connection.prepareStatement(
            "SELECT * FROM `MurderData` WHERE uuid='" + p.getUniqueId().toString() + "'")) {

        ResultSet result = select.executeQuery();
        if (result.next()) {
            CloseResultSet(result);
            return true;
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return false;
}

此外，在您的 loadPlayer() 方法中，您可以将两个 if 语句替换为:

if (isPlayerInDataBase(p)) {
    // code if player is in database
} else {
    // code if player is not in database
}