java - MySql 中的多个更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14651276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java - Multipile update statements in MySql
提问by Krimson
so I have a software which basically downloads 1.5K game server address from my MySQL db. It then pings all of them and then upload the information such as online players back to the database. The process looks like this:
所以我有一个软件，它基本上从我的 MySQL 数据库下载 1.5K 游戏服务器地址。然后它对所有这些人进行 ping 操作，然后将在线玩家等信息上传回数据库。该过程如下所示：
- Download server address
- Ping the servers and get information
- Upload information back to the database
- 下载服务器地址
- Ping 服务器并获取信息
- 将信息上传回数据库
So far I have been able to solve the part where it download the server host name and pings them but the problem arises when updating the servers.
到目前为止，我已经能够解决它下载服务器主机名并ping它们的部分，但是在更新服务器时出现了问题。
To update I thought about using a for loop to construct one BIG string of many update statements and execute it at once but this is prone to sql injections. So idealy one would want to use prepared statements.
为了更新，我想过使用 for 循环来构造一个包含许多更新语句的 BIG 字符串并立即执行它，但这很容易发生 sql 注入。所以理想情况下，人们会想要使用准备好的语句。
The SQL update statement i'm using is:
我正在使用的 SQL 更新语句是：
UPDATE serverlist SET `onlineplayers` = '3', maxplayers = '10',
name = 'A game server' WHERE `ip` = 'xxx.xxx.xxx.xxx' AND `port` = 1234;
So my question is:
How can i execute all the 1.5K updates statements using parameterized queries?
所以我的问题是：
如何使用参数化查询执行所有 1.5K 更新语句？
回答by Daniel Kaplan
If you google for "jdbc bulk update" you'll get lots of results like this oneor this one.
如果谷歌为“JDBC批量更新”，你会得到很多结果像这样的还是这一个。
The latter has an example like this:
后者有一个这样的例子：
try {
...
connection con.setAutoCommit(false);
PreparedStatement prepStmt = con.prepareStatement(
"UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
for (int i=0; i < numUpdates.length; i++) {
if (numUpdates[i] == -2)
System.out.println("Execution " + i +
": unknown number of rows updated");
else
System.out.println("Execution " + i +
"successful: " numUpdates[i] + " rows updated");
}
con.commit();
} catch(BatchUpdateException b) {
// process BatchUpdateException
}
回答by Cliff
Sounds like you want to do a batch SQL update. Prepared statements are your friend. Here's an example of using prepared statements in batch:
听起来您想要进行批量 SQL 更新。准备好的语句是你的朋友。这是批量使用准备好的语句的示例：
http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
http://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/
Using prepared statements makes setting parameters easier and it allows the DB to efficiently perform multiple updates. Executing multiple SQL strings would work but would be inefficient since each SQL string would be sent to the DBMS, parsed, compiled, then executed. With prepared statements the SQL is parsed and compiled once then reused for future updates with different parameters.
使用准备好的语句可以更轻松地设置参数，并允许数据库有效地执行多次更新。执行多个 SQL 字符串是可行的，但效率低下，因为每个 SQL 字符串都会被发送到 DBMS、解析、编译，然后执行。使用准备好的语句，SQL 被解析和编译一次，然后重用用于具有不同参数的未来更新。
回答by Yuriy Perepelytsia
Another important step that you should be aware about during MySQL batch update / insert is JDBC Connection propertie rewriteBatchedStatements=true( false by default ). Without it batch mode is useless. It cost me 1 day to "fix bug" till I found out this. When you have small number of lines and close client-to-DB location ( 1ms ping ) , you even can't realize that you in "fake batch mode" , but when I switch environment to remote client ( ping=100ms ) and 100k lines to update , it would take 4hours of "batch mode update" with default rewriteBatchedStatements=falseand just 2minutes with rewriteBatchedStatements=true
在 MySQL 批量更新/插入期间，您应该注意的另一个重要步骤是 JDBC 连接属性rewriteBatchedStatements=true（默认为 false）。没有它批处理模式是没有用的。“修复错误”花了我 1 天的时间，直到我发现了这一点。当您的行数较少并关闭客户端到数据库的位置（1ms ping）时，您甚至无法意识到您处于“假批处理模式”，但是当我将环境切换到远程客户端（ ping=100ms ）和 100k 时要更新的行，默认rewriteBatchedStatements=false需要 4 小时的“批处理模式更新”，rewriteBatchedStatements=true需要 2分钟
回答by JB Nizet
Create a prepared statement:
创建一个准备好的语句：
String sql = "update serverlist SET onlineplayers = ?, maxplayers = ?, name = ? where ip = ? and port = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
Then loop through your list, and at each iteration, do
然后遍历您的列表，并在每次迭代时，执行
stmt.setInt(1, onlinePlayers);
stmt.setInt(2, maxPlayers);
stmt.setString(3, name);
stmt.setString(4, ip);
stmt.setInt(5, port);
stmt.executeUpdate();
For better performance, you could also use batch updates.
为了获得更好的性能，您还可以使用批量更新。
Read the JDBC tutorial.
阅读JDBC 教程。
