Java Batch Update Example With SQL Statement & PreparedStatement

By admin | August 24, 2014 | Updated : April 30, 2016 database, h2, java, sql, No Comments

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

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

You might also like following tutorials :

Required Libraries

Project Structure

Batch Update Example Using H2 Database

Output

JDBC Batch Update Exception Handling

Output

Related Posts