java - MySql 中的多个更新语句

2022-01-17 00:00:00 sql-update mysql jdbc sql-parametrized-query java

所以我有一个软件，它基本上从我的 MySQL 数据库下载 1.5K 游戏服务器地址.然后它会ping所有这些，然后将在线玩家等信息上传回数据库.流程如下:

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:

  1. 下载服务器地址
  2. Ping 服务器并获取信息
  3. 将信息上传回数据库

到目前为止，我已经能够解决下载服务器主机名并 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.

为了更新，我考虑过使用 for 循环来构造一个包含许多更新语句的 BIG 字符串并立即执行它，但这很容易发生 sql 注入.因此，理想情况下，人们会想要使用准备好的语句.

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.

我使用的 SQL 更新语句是:

The SQL update statement i'm using is:

UPDATE serverlist SET `onlineplayers` = '3', maxplayers = '10', 
name = 'A game server' WHERE `ip` = 'xxx.xxx.xxx.xxx' AND `port` = 1234;

所以我的问题是:
如何使用参数化查询执行所有 1.5K 更新语句?

推荐答案

如果你用谷歌搜索jdbc 批量更新"，你会得到很多结果 喜欢这个 或 这个.

If you google for "jdbc bulk update" you'll get lots of results like this one or this one.

后者有一个这样的例子:

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
}

相关文章