MySql Batch Insert/Update in Java

March 2nd, 2009 Hitesh Agrawal Leave a comment Go to comments

Sometimes we want to update multiple rows in an single table, opening and closing MySql connection for every update would be costly affair. To overcome this MySql Connector provides us updateBatch option where we can update multiple rows with single MySqlConnection. This article describes on how we can update multiple rows using single MySql Connection.

Here we will be using Prepared Statements for MySql Operation. We can also achieve the same using normal Statement Class also. The example that i will be explaining uses the following table structure.

CREATE TABLE test('id' int(11) NOT NULL AUTO_INCREMENT,
  'name' varchar(100) NOT NULL,
  'desg' varchar(50) NOT NULL,
  PRIMARY KEY  ('id')
)

Batch Update using Java and MySql:

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 
public class MySqlUpdate {
 
  private static Connection getConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/test";
    String usrname = "root";
    String passwd = "";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, usrname, passwd);
    return conn;
  }
 
  public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      String query = "insert into test(name, desg) values(?, ?)";
      ps = conn.prepareStatement(query);
      ps.setString(1, "ABC");
      ps.setString(2,"Software Engineer");
      ps.addBatch();
 
      ps.setString(1, "XYZ");
      ps.setString(2,"Sr. Software Engineer");
      ps.addBatch();
 
      ps.setString(1, "PQR");
      ps.setString(2,"Team Leader");
      ps.addBatch();
 
      int[] updateCounts = ps.executeBatch();
      System.out.println("Length:" + updateCounts.length);
      conn.commit();
    } catch (BatchUpdateException e) {
      try {
        conn.rollback();
      } catch (Exception e2) {
        e.printStackTrace();
      }
    } catch (Exception e) {
    	e.printStackTrace();
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}

Code Explanation:

getConnection() method will be responsible for creating an MySql Connection with “test” database.
pstmt.addBatch() is called after adding data for every rows. Here if you see we are calling pstmt.executeBatch() after we ae done adding data to the batch.
Also we want to find the status of the MySql statements, the executeBatch() will return an array; we can the insert was successful or not.
Finally we commit the changes by calling conn.commit.

Custom Search

Techie Zone

MySql Batch Insert/Update in Java

Popular Articles:

Java Tutorial

Translator

Follow me on Twitter