Java Batch Update Example With SQL Statement & PreparedStatement

In this example, we are showing how to use Java Batch Update Example With SQL Statement & PreparedStatement

In batch update, update happens on the database as batches, which will increase the processing speed and performance. We can use batch updates for both JDBC Statement & PreparedStatement. You can see the JDBC examples for both Statement & PreparedStatement below.

In this tutorial, we are using H2 database and inserting some data using Java Batch Update

H2 is an open source software implementation of Java SQL database. The main features of H2 are.

  • Very fast, open source, JDBC API
  • Embedded and server modes; in-memory databases
  • Browser based Console application
  • Small footprint: around 1.5 MB jar file size

Reference -> http://www.h2database.com/html/main.html

Required Libraries

For using H2 database, You need to download

  1. h2-1.4.182.jar

Project Structure

Batch Update Example

Batch Update Example Using H2 Database

package com.h2.examples;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

/*
Batch Update Example

In this example, we are showing how to use batch update with SQL Statement & PreparedStatement
*/

public class H2DatabaseBatchExample {

   
private static final String DB_DRIVER = "org.h2.Driver";
   
private static final String DB_CONNECTION = "jdbc:h2:~/test";
   
private static final String DB_USER = "";
   
private static final String DB_PASSWORD = "";

   
public static void main(String[] args) throws Exception {
       
try {
           
// delete the database named 'test' in the user home directory for initialization
           
DeleteDbFiles.execute("~", "test", true);
            batchInsertWithStatement
();
            batchInsertWithPreparedStatement
();

       
} catch (SQLException e) {
           
e.printStackTrace();
       
}
    }

   
//Batch Update With SQL PreparedStatement Example
   
private static void batchInsertWithPreparedStatement() throws SQLException {
       
Connection connection = getDBConnection();
        PreparedStatement preparedStatement =
null;

        String Query =
"INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
       
try {
           
connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement
(Query);

            preparedStatement.setInt
(1, 4);
            preparedStatement.setString
(2, "Rockey");
            preparedStatement.addBatch
();

            preparedStatement.setInt
(1, 5);
            preparedStatement.setString
(2, "Jacky");
            preparedStatement.addBatch
();

           
int[] countWithoutException = preparedStatement.executeBatch();
            System.out.println
("Inserted = " + countWithoutException.length);
            connection.commit
();
       
} catch (BatchUpdateException e) {
           
System.out.println("Exception Message " + e.getLocalizedMessage());
       
} catch (Exception e) {
           
e.printStackTrace();
       
} finally {
           
preparedStatement.close();
            connection.close
();

       
}
    }

   
//Batch Update With SQL Statement Example
   
private static void batchInsertWithStatement() throws SQLException {
       
Connection connection = getDBConnection();
        Statement stmt =
null;
       
try {
           
connection.setAutoCommit(false);
            stmt = connection.createStatement
();
            stmt.execute
("CREATE TABLE PERSON(id int primary key, name varchar(255))");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(1, 'A')");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(2, 'B')");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(3, 'C')");

           
int[] countWithoutException = stmt.executeBatch();
            System.out.println
("Inserted = " + countWithoutException.length);
            connection.commit
();
       
} catch (BatchUpdateException e) {
           
System.out.println("Exception Message " + e.getLocalizedMessage());
       
} catch (Exception e) {
           
e.printStackTrace();
       
} finally {
           
stmt.close();
            connection.close
();
       
}
    }

   
private static Connection getDBConnection() {
       
Connection dbConnection = null;
       
try {
           
Class.forName(DB_DRIVER);
       
} catch (ClassNotFoundException e) {
           
System.out.println(e.getMessage());
       
}
       
try {
           
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
           
return dbConnection;
       
} catch (SQLException e) {
           
System.out.println(e.getMessage());
       
}
       
return dbConnection;
   
}
}
Output
Inserted Count Using SQL Statement  = 3
Inserted Count Using SQL PreparedStatement = 2

JDBC Batch Update Exception Handling

In Batch update when an exception occurs, we will get a BatchUpdateException.

BatchUpdateException.getUpdateCounts();

Above method will provide details like how many records have updated, failed etc.

package com.h2.examples;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.h2.tools.DeleteDbFiles;

/*
Batch Update Example

In this example, we are showing how to handle exception when using JDBC batch update
*/

public class H2DatabaseBatchUpdateExceptionExample {

   
private static final String DB_DRIVER = "org.h2.Driver";
   
private static final String DB_CONNECTION = "jdbc:h2:~/test";
   
private static final String DB_USER = "";
   
private static final String DB_PASSWORD = "";

   
public static void main(String[] args) throws Exception {
       
try {
           
// delete the database named 'test' in the user home directory for initialization
           
DeleteDbFiles.execute("~", "test", true);
            batchInsertWithStatement
();
       
} catch (SQLException e) {
           
e.printStackTrace();
       
}
    }

   
private static void batchInsertWithStatement() throws SQLException {
       
Connection connection = getDBConnection();
        Statement stmt =
null;
       
try {
           
connection.setAutoCommit(false);
            stmt = connection.createStatement
();
            stmt.execute
("CREATE TABLE PERSON(id int primary key, name varchar(255))");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(1, 'A')");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(2, 'B')");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(3, 'C')");
            stmt.addBatch
("INSERT INTO PERSON(id, name) VALUES(3, 'C')");

           
int[] countWithoutException = stmt.executeBatch();
            System.out.println
("OK: countWithoutException = " + countWithoutException.length);
            connection.commit
();
       
} catch (BatchUpdateException e) {
           
int[] updateCounts = e.getUpdateCounts();
           
for (int i = 0; i < updateCounts.length; i++) {
               
if (updateCounts[i] >= 0) {
                   
// Successfully executed; the number represents number of affected rows
                   
System.out.println("OK: updateCount=" + updateCounts[i]);
               
} else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                   
// Successfully executed; number of affected rows not available
                   
System.out.println("OK: updateCount=Statement.SUCCESS_NO_INFO");
               
} else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                   
// Failed
                   
System.out.println("updateCount=Statement.EXECUTE_FAILED");
               
}
            }
           
System.out.println("Exception Message " + e.getLocalizedMessage());
       
} catch (Exception e) {
           
e.printStackTrace();
       
} finally {
           
stmt.close();
            connection.close
();
       
}
    }

   
private static Connection getDBConnection() {
       
Connection dbConnection = null;
       
try {
           
Class.forName(DB_DRIVER);
       
} catch (ClassNotFoundException e) {
           
System.out.println(e.getMessage());
       
}
       
try {
           
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
           
return dbConnection;
       
} catch (SQLException e) {
           
System.out.println(e.getMessage());
       
}
       
return dbConnection;
   
}
}
Output
OK: updateCount=1
OK: updateCount=1
OK: updateCount=1
updateCount=Statement.EXECUTE_FAILED
Exception Message Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.PERSON(ID)"; SQL statement:
INSERT INTO PERSON(id, name) VALUES(3, 'C') [23505-182]

From output you can see that first 3 records of stmt.addBatch are different. But last one is same as third one, So here BatchUpdateException happens.

Here first 3 records are printing as "OK: updateCount=1" and "updateCount=Statement.EXECUTE_FAILED" for last record.

It is upto the Database driver whether complete record is discard or commit the correct data when BatchUpdateException occurs





Your email address will not be published. Required fields are marked *