java - MySql中的Multipile更新语句 [英] java - Multipile update statements in MySql

问题描述

所以我有一个软件，基本上从我的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
} 

这篇关于java - MySql中的Multipile更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！