The Remarkable Everyday

Java MySQL – Mass Update Using Batch Updating

Posted by leejeok on May 21, 2008

//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.



import java.sql.*;


public class Main {


    public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement stat = null;


        int result = 0;

        int totalRowUpdate = 0;

        boolean updateAll = false;

        boolean updateEmpty = false;

        boolean updateFail = false;


        try {

            // Load the JDBC driver

            String driver = “org.gjt.mm.mysql.Driver”;



            // Create a connection to the database

            String serverName = “localhost”;

            String database = “navigation”;

            String url = “jdbc:mysql://” + serverName +  “/” + database;

            String username = “someroot”;

            String password = “somepassword”;


            conn = DriverManager.getConnection(url, username, password);


            //Disabled the auto commit. By default, it is always true.



            //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);




            //Execute the batch.

            int [] updateCounts = stat.executeBatch();


            //Validate update.

            //If the row value in the updateCounts array is 0 or greater,

            //the update was successfully executed.

            //A value of SUCCESS_NO_INFO means update was successfully executed

            //but MySQL server unable to determind the number of rows affected.

            //A value of EXECUTE_FAILED means MySQL server rejected the query for error.

            for (int i=1; i<updateCounts.length; i++) {

                if (updateCounts[i] >= 1) {

                    totalRowUpdate = totalRowUpdate + 1;

                    updateAll = true;

                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {

                    updateEmpty = true;

                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {

                    updateFail = true;




            //Error found. Rollback.

            //No error, Commit the batch

            if(updateEmpty || updateFail){


            } else if (updateAll){


                System.out.println(“Total Row Update: “+totalRowUpdate);



            //Once the batch is executed, it is a good pratice to clear the batch

            //before adding any new query.



            // Throw BatchUpdateException for any queries fail.

            // Throw ClassNotFoundException for database driver error.

            // Throw SQLException for database error.

        } catch (BatchUpdateException  e) {


        } catch (ClassNotFoundException e) {


        } catch (SQLException e) {






One Response to “Java MySQL – Mass Update Using Batch Updating”

  1. boogie said

    thank you very much, beautiful code

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: