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
1 2 Next





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