代码之家 › 专栏 › 技术社区 › Yousif Raad

使用MySQL数据更新/存储的完美方式,在更新统计信息时没有延迟和错误

bukkit mysql

Yousif Raad · 技术社区 · 7 年前

public void loadPlayer(Player p) {

if (isPlayerInDataBase(p)) {

    Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
        @Override
        public void run() {
            Connection connection = sql.getConnection();
            try {
                PreparedStatement select = connection
                        .prepareStatement("SELECT * FROM `MurderData` WHERE playername='" + p.getName() + "'");
                ResultSet result = select.executeQuery();

                if (getPlayerData(p) != null) {
                    while (result.next()) {

                        getPlayerData(p).setdeaths(result.getInt("deaths"));
                        getPlayerData(p).setkills(result.getInt("kills"));
                        getPlayerData(p).setwins(result.getInt("wins"));
                        getPlayerData(p).setlose(result.getInt("loses"));
                        getPlayerData(p).setscore(result.getInt("score"));
                        getPlayerData(p).setcoins(result.getInt("coins"));
                    }

                    CloseResultSet(result);

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

        }
    });
} else {
    Connection connection = sql.getConnection();
    Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), new Runnable() {
        @Override
        public void run() {
            try {
                PreparedStatement insert = connection.prepareStatement(
                        "INSERT INTO `MurderData` (playername, wins, deaths, loses, kills, coins, score) VALUES (?, ?, ?, ?, ?, ?, ?)");
                insert.setString(1, p.getName());
                insert.setInt(2, 0);
                insert.setInt(3, 0);
                insert.setInt(4, 0);
                insert.setInt(5, 0);
                insert.setInt(6, 0);
                insert.setInt(7, 0);
                insert.executeUpdate();
                ClosePreparedStatement(insert);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

public boolean isPlayerInDataBase(Player p) {
        Connection connection = sql.getConnection();
        try {
            PreparedStatement select = connection
                    .prepareStatement("SELECT * FROM `MurderData` WHERE playername='" + p.getName() + "'");
            ResultSet result = select.executeQuery();
            if (result.next()) {
                CloseResultSet(result);
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

所以基本上问题是,我应该在更新MySQL数据后关闭准备好的语句吗?我应该让它有一个延迟,比如在5秒后关闭它吗?我可以优化此代码以使其更好吗?

数据库中的播放器是否正常?

public void CloseResultSet(ResultSet s) {

        new BukkitRunnable() {

            @Override
            public void run() {

                if (s != null) {
                    try {
                        s.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }

            }
        }.runTaskLater(this, 20 * getConfig().getInt("close-sql-statements-after"));
    }

    public void ClosePreparedStatement(PreparedStatement s) {

        new BukkitRunnable() {

            @Override
            public void run() {

                if (s != null) {
                    try {
                        s.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }

            }
        }.runTaskLater(this, 20 * getConfig().getInt("close-sql-statements-after"));

    }

public void setSQLData(Player p, int kills, int deaths, int loses, int wins, int coins, int score) {
        Bukkit.getScheduler().runTaskAsynchronously(plugin, new Runnable() {
            @Override
            public void run() {
                Connection connection = plugin.sql.getConnection();
                try {
                    PreparedStatement insert = connection.prepareStatement(
                            "INSERT INTO `MurderData` (playername, wins, deaths, loses, kills, coins, score) VALUES (?, ?, ?, ?, ?, ?, ?)");
                    insert.setString(1, p.getName());
                    insert.setInt(2, wins);
                    insert.setInt(3, deaths);
                    insert.setInt(4, loses);
                    insert.setInt(5, kills);
                    insert.setInt(6, coins);
                    insert.setInt(7, score);
                    insert.executeUpdate();// error line 252
                    plugin.ClosePreparedStatement(insert);
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }

        });
    }

     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at java.lang.reflect.Constructor.newInstance(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2407)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2325)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2310)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at me.joseph.murder.api.MurderAPI$1.run(MurderAPI.java:252)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftTask.run(CraftTask.java:71)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:53)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at java.lang.Thread.run(Unknown Source)
[22:31:18] [Craft Scheduler Thread - 71/WARN]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2529)
[22:31:18] [Craft Scheduler Thread - 71/WARN]:  at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)

2 回复 | 直到 7 年前

bn4t 7 年前

首先:

据我所知,每次执行查询/更新后都会关闭连接。不幸的是,这相当不符合共振。如果在插件启动时打开一个MySQL连接,在插件停止时再次关闭它会更好。

我个人是这样做的:(并不意味着你必须这样做)

连接变量:

private static Connection connection;

    public static void connect(String host, String user, String password, String database) {
    Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {

        //close Connection if still active
        if (connection != null) {
            close();
        }

        //connect to database host
        try {
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://" + host + "/" + database, user, password);

        } catch (SQLException e) {
          System.out.println(e.getMessage());
        }

    });
}

我更新/写入数据库条目的功能:

    public void Update(final String qry) {
    Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
        try {
            Statement stnt = connection.createStatement();
            stnt.executeUpdate(qry);

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

}

我从数据库查询信息的功能:

Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> { Query("your query"); });

    public ResultSet Query(String qry) {
    ResultSet rs = null;

    try {
        Statement stnt = connection.createStatement();
        rs = stnt.executeQuery(qry);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return rs;
}

关闭功能:

    public static void close() {
    Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
        try {
            connection.close();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    });
}

这种方法的缺点是,一次只能连接到一个数据库(在我的情况下,这很好)。

希望对你有帮助。我还经常遇到奇怪的MySQL错误。幸运的是,有了这段代码,一切都很好。

[22:31:18] [Craft Scheduler Thread - 71/WARN]: Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

据我所知,这意味着与MySQL服务器的连接被关闭,即使插件试图使用它。如上所述,在开始时打开一个连接,并将其保持在打开状态,直到插件停止,应该可以解决这个问题。

Yousif Raad 7 年前

感谢所有帮助我的人,我发现问题出在这条线上

 public void setSQLData(Player p, int kills, int deaths, int loses, int wins, int coins, int score) {


  Bukkit.getScheduler().runTaskAsynchronously(plugin, new Runnable() {
        @Override
        public void run() {
            Connection connection = plugin.sql.getConnection();
            try {
                PreparedStatement insert = connection.prepareStatement(
                        "INSERT INTO `MurderData` (playername, wins, deaths, loses, kills, coins, score) VALUES (?, ?, ?, ?, ?, ?, ?)");

应该更新而不是插入