Java MySQL - Mass Update Using Batch Updating

Some sample of the code to perform the mass update of database.

The ideas behind of this code is to improve the database performance by reduce the cost of query executions. This is refer to the INSERT, UPDATE and DELETE of any rows within the database.

Some developer may execute the query statement within the WHILE or FOR loop condition.

With batch updating, a set of SQL query can be writen and execute together to improve the performance.

Example of the code:

//Create the prepared statement
String updateSQL = “UPDATE customer SET country = ? WHERE id = ?”;
stat = conn.prepareStatement(updateSQL);

//Insert the country and hardcoded id.
for(int i = 0; i <= 5; i++){
stat.setString(1, “Malaysia”);
stat.setInt(2, i);
stat.addBatch();
}

//Execute the batch.
int [] updateCounts = stat.executeBatch();

Full code available here: http://leejeok.wordpress.com/2008/05/21/java-mysql-mass-update-using-batch-updating/