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.

 

 

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”;

            Class.forName(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.

            conn.setAutoCommit(false);

            

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

           

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

                conn.rollback();

            } else if (updateAll){

                conn.commit();

                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.

            stat.clearBatch();

           

            // Throw BatchUpdateException for any queries fail.

            // Throw ClassNotFoundException for database driver error.

            // Throw SQLException for database error.

        } catch (BatchUpdateException  e) {

            System.err.println(e);

        } catch (ClassNotFoundException e) {

            System.err.println(e);

        } catch (SQLException e) {

            System.err.println(e);

        }

    }

}