使用MySQL数据更新/存储的完美方法，在更新统计信息时不会出现滞后和错误 [英] Perfect way to use MySQL data updating/storing with no lag and error at updating stats
问题描述
我在加载/存储MySQL数据时遇到问题，这是我的代码
I'm having issues loading/storing MySQL data, this is my code
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秒后关闭吗？我可以优化此代码使其更好吗？
So basically the question is, should i close prepared statements after updating MySQL data? and should i make that have a delay like close it after 5 seconds? can i optimize this code to make it better?
数据库中的播放器还可以吗？
The is player in data base is it alright?
这是我的2种关闭方法：
this is my 2 closing methods:
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"));
}
更新：这是第二个问题，第一个问题是小滞后峰值第二个问题是在第252行存储数据
Update: this is the second problem, first problem is the small lag spikes second problem is storing data in line 252
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)
推荐答案
首先：
据我所知，您每次关闭连接都会执行查询/更新。不幸的是，这是非常糟糕的。最好是在插件启动时打开MySQL连接，然后在插件停止时再次关闭它。
As far as I can see, you are closing the connection every time after you executet a Query/Update. This is unfortunately quite imperformant. It would be better if opened a MySQL connection when the Plugin starts and close it again when the plugin stops.
我个人是这样进行的：（并不意味着您必须这样做）
I personally made it this way:(Doesn't mean you have to do it this way)
连接变量：
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());
}
});
}
我的用于将条目更新/写入数据库的函数：
My function to update/write entries into the database:
public void Update(final String qry) {
Bukkit.getScheduler().runTaskAsynchronously(Main.getInstance(), () -> {
try {
Statement stnt = connection.createStatement();
stnt.executeUpdate(qry);
} catch (SQLException e) {
e.printStackTrace();
}
});
}
我从数据库查询信息的函数：
My function to query information from the database:
如您所见，该函数不是异步的。不幸的是，到目前为止，我尚未对其进行管理以使该功能异步。但是您可以通过使函数调用异步来轻松解决此问题。例如：
Bukkit.getScheduler（）。runTaskAsynchronously（Main.getInstance（），（）-> {Query（ your query）;
}） ;
As you can see is this function not asynchronous. I unfortunately didn't managed it by now to get this function asynchronous. But you can easily workaround this by just making the function call asynchronous. ex:
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());
}
});
}
这种方式的缺点是您只能在一个数据库上连接到一个数据库。时间（在我看来，这还不错）。
A disadvantage of this way is that you can only connect to one Database at a Time(which was in my case just fine).
希望对您有所帮助。我也为怪异的MySQL错误付出了很多努力。幸运的是，使用此代码，一切正常。
Hope it helps you. I also struggled a lot with weird MySQL Errors. Fortunately with this code everything works just fine.
回答您的问题：
[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 Server的连接也已关闭使用它。
如上所述，应在开始时打开一个连接并保持打开状态直到插件停止为止。
As far as I know this means that the connection to the MySQL Server is closed even though the plugin tries to use it. As mentioned above opening a connection at the start and leaving it open until the plugin stops should fix this.
这篇关于使用MySQL数据更新/存储的完美方法，在更新统计信息时不会出现滞后和错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
