java - Multipile update statements in MySql

Question

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. Download server address
2. Ping the servers and get information
3. Upload information back to the database

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.

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.

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;

So my question is:
How can i execute all the 1.5K updates statements using parameterized queries?

Solution 1

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
} 

Solution 2

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:

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.

Solution 3

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=false and just 2minutes with rewriteBatchedStatements=true

Solution 4

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.

View more solutions

Comments

[Krimson]

Awesome. Just what i was looking for!

[Yuriy Perepelytsia]

Similar details you could found here : stackoverflow.com/questions/2993251/…