Java Batch Processing using JDBC Drivers: A Comprehensive Guide 101

Java Database Connectivity (JDBC) is an API that enables Java programs to interact with databases. JDBC API is implemented through the JDBC drivers that help you access your database through Java code. Java can interact with the database using SQL or MySQL queries.

• Executing each SQL query after the other can cause a lot of communication overhead between the Java program and the database.
• Therefore, batch processing is developed to reduce such overhead and increase the speed of the Java programs. With batch processing, you can execute a group of queries in the program at once.
• In this tutorial, you will learn about the steps required to set up Java Batch Processing using JDBC drivers.

Prerequisites

• Fundamental understanding of APIs.

What is Java Language?

Developed in 1995 by Sun Microsystems, Java is a simple, object-oriented, high-level programming language. Object-oriented programming language refers to programming languages that use classes and objects.

Key Features of Java

1) Platform Independent

Java’s execution process consists of Java Virtual Machine (JVM), including writing, compiling, and running program execution stages. The compilation stage is done by the Java compiler called javac, included in the Java Development Kit (JDK). It takes the simple Java code as input and gives the bytecode as output. In the running stage, JVM executes this bytecode. Every operating system consists of a different JVM, but the output produced after the byte code execution is the same across all the operating systems. Therefore, Java is known as a platform-independent language.

2) Simple to Understand

Since Java is influenced by C and C++ programming, it is simple to understand. However, Java removes the complexities involved in C and C++ programming languages like explicit pointers, storage classes, operator overloading, preprocessors, header files, and more.

3) Distributed Programming

With the Java programming language, users can create distributed applications. They can split a program into many parts and store them on different computers. Programmers sitting on one machine can access all these programs. This feature in Java is called distributed programming, mainly used to develop large applications. Features like Enterprise JavaBeans (EJB) and Remote Method Innovation (RMI) help users achieve distributed programming.

4) High Performance

Java provides high performance because of its intermediate bytecode. JVM consists of a Just In Time Compiler (JIT), which compiles the bytecode to machine code at runtime. For example, if there is an XYZ instruction in a program that has to be executed more than 1000 times. In such a scenario, traditional JVM will read XYZ instruction, convert it into machine code, and execute it 1000 times. But this will be time-consuming. As a result, the JIT compiler in JVM is developed to check at a run time for the instructions executing multiple times. JIT will compile that (XYZ) instruction and keep it in the memory. Therefore, it eliminates the need for compiling the instructions again to provide high performance.

What is Java Batch Processing?

• Batch Processing refers to the processing or executing SQL statements grouped into a batch at once. Generally, when you execute SQL statements one after the other, the execution simultaneously switches from the database to the program.
• As a result, the communication overhead increases between the database and the program. Therefore, to reduce the communication overhead, batch processing is used.

For example, consider the table Emp with the below description.

You can insert data to the EMP table using SQL statements as follows.

Stmt.execute("INSERT INTO Emp VALUES ('Amit', '30-9-1989', 'Hyderabad')");
Stmt.execute("INSERT INTO Emp VALUES ('Amit', '1-9-1989', 'Vishakhapatnam')");

You can insert the same record using the PreparedStatement as follows.

String query = "INSERT INTO Emp(Name, String, Location) VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, "Amit");
pstmt.setDate(2, "30-9-1989"));
pstmt.setString(3, "Hyderabad");
pstmt.execute();
pstmt.setString(1, "Sumith");
pstmt.setDate(2, "1-9-1989"); //Friday, Sept 1, 1989 12:00:00 AM
pstmt.setString(3, "Vishakhapatnam");
pstmt.execute();

In both the above cases, each insert statement is executed individually. When the execute() method gets invoked, the insert statement is executed in the database and reverts to the program.

This method of inserting values into tables is best for inserting fewer number values. In case you have to insert many values into the tables, you will need batch processing to add the statements to the batch using the addBatch() method and executeBatch() method of the Statement interface, as shown below.

Stmt.addBatch("INSERT INTO Emp VALUES ('Amit', '30-9-1989', 'Hyderabad')");
Stmt.addBatch("INSERT INTO Emp VALUES ('Amit', '1-9-1989', 'Vishakhapatnam')");
Stmt.executeBatch();
pstmt.setString(1, "Amit");
pstmt.setDate(2, "30-9-1989"));
pstmt.setString(3, "Hyderabad");
pstmt.addBatch();
pstmt.setString(1, "Sumith");
pstmt.setDate(2, "1-9-1989"); //Friday, Sept 1, 1989 12:00:00 AM
pstmt.setString(3, "Vishakhapatnam");
pstmt.addBatch();
pstmt.executeBatch();

How to Set up Java Batch Processing?

Java Batch Processing consists of the below steps:

1. Load the driver class.
2. Create the connection.
3. Create the Statement.
4. Add a query in the batch.
5. Execute the batch.
6. Close the connection.

The below example consists of Java Batch Processing using JDBC.

import java.sql.*;  
class FetchRecords{  
public static void main(String args[])throws Exception{  
Class.forName("oracle.jdbc.driver.OracleDriver");  
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
con.setAutoCommit(false); 
Statement stmt=con.createStatement();  
stmt.addBatch("insert into user420 values(190,'abhi',40000)");  
stmt.addBatch("insert into user420 values(191,'umesh',50000)");   
stmt.executeBatch();//executing the batch 
con.commit();  
con.close();  }} 

You can use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. This method is returned true if your JDBC driver supports batch processing.
Whenever you want to connect your database with Java, you need interfaces to interact with your databases. Java consists of three such interfaces Statement, PreparedStatement, and CallableStatement, that define the methods and properties, enabling you to send SQL or PL/SQL commands to your database and receive data from your database.

The Statement, PreparedStatement, and CallableStatement interfaces consist of the addBatch(), executeBatch(), and clearBatch() methods. While the addBatch() method is used to add individual statements to the batch, the executeBatch() method is used for starting the execution of all the grouped statements. The executeBatch()method returns an array of integers, where the elements in the array represent the update count for the respective update statement, the clearBatch() method is used to remove statements that you have added with the addBatch() method.

A) Java Batch Processing with the Statement object

Follow the below steps to perform batching with the Statement object.

1. Create a Statement object using createStatement() method.
2. Set the setAutoCommit() method to false.
3. Use addBatch() method to add as many SQL statements as you want into the batch.
4. Use executeBatch() method to execute all the SQL statements.
5. Use the commit() method to commit all the changes.

The below code consists of a batch processing example using the Statement object.

Statement stmt = conn.createStatement();

conn.setAutoCommit(false);

String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(200,'Zia', 'Ali', 30)";
.
stmt.addBatch(SQL);


String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(201,'Raj', 'Kumar', 35)";


stmt.addBatch(SQL);

String SQL = "UPDATE Employees SET age = 35 " +
             "WHERE id = 100";

stmt.addBatch(SQL);

int[] count = stmt.executeBatch();

conn.commit();

B) Java Batch Processing with the PreparedStatement object

Follow the below steps to perform batch processing with the PreparedStatement object.

1. Create SQL statements using placeholders. Placeholders are used as text characters in SQL statements.
2. Create a PreparedStatement object using PreparedStatement() method.
3. Set the setAutoCommit() method to false.
4. Use addBatch() method to add as many SQL statements as you want into the batch.
5. Use executeBatch() method to execute all the SQL statements.

The below code consists of a batch processing example using the PreparedStatement object.

String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
//Set auto-commit to false
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
.
.
.
.
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

C) Java Batch Processing with the CallableStatement object

Follow the below steps to perform batch processing with the CallableStatement object.

1. Use the registerDriver() method of the DriverManager class to register the driver class. Pass the driver class name to the registerDriver() method as a parameter.
2. Use the getConnection() method of the DriverManager class to connect with the database. Pass URL, username, and password to it as parameters.
3. Use the setAutoCommit() method of the Connection interface to set the auto-commit to false. 
4. Use the prepareCall() method of the Connection interface to create a CallableStatement object. Pass the query to it as a parameter with placeholders.
5. Use the setter methods of the CallableStatement interface to set the values to the placeholders.
6. Use the addBatch() method of the Statement interface to add the required statements to the batch.
7. Use the executeBatch() method of the Statement interface to execute the batch. 
8. Use the commit() method of the Statement interface to commit the changes.

For example, you have created the table named ‘Dispatches’ with the below descriptions.

Create a procedure called myProcedure, which stores values in the above-created table using the below command.

Create procedure myProcedure (
   IN Product_Name VARCHAR(255),
   IN Name_Of_Customer VARCHAR(255),
   IN Month_Of_Dispatch VARCHAR(255),
   IN Price INT, IN Location VARCHAR(255))
BEGIN
   insert into Dispatches values ();
END

The below program calls a procedure called myProcedure, which inserts data into the Dispatch table. It sets values to the callable statement using batch updates.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class BatchProcessing_CallableStatement {
   public static void main(String args[])throws Exception {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //CREATE TABLE Dispatches( Product_Name VARCHAR(255), Name_Of_Customer
      VARCHAR(255), Month_Of_Dispatch VARCHAR(255), Price INT, Location VARCHAR(255));
      //Setting auto-commit false
      con.setAutoCommit(false);
      //Creating a PreparedStatement object
      CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?, ?, ?)}");
      cstmt.setString(1, "Keyboard");
      cstmt.setString(2, "Amith");
      cstmt.setString(3, "January");
      cstmt.setInt(4, 1000);
      cstmt.setString(5, "Hyderabad");
      cstmt.addBatch();
      cstmt.setString(1, "Earphones");
      cstmt.setString(2, "Sumith");
      cstmt.setString(3, "March");
      cstmt.setInt(4, 500);
      cstmt.setString(5,"Vishakhapatnam");
      cstmt.addBatch();
      cstmt.setString(1, "Mouse");
      cstmt.setString(2, "Sudha");
      cstmt.setString(3, "September");
      cstmt.setInt(4, 200);
      cstmt.setString(5, "Vijayawada");
      cstmt.addBatch();
      //Executing the batch
      cstmt.executeBatch();
      //Saving the changes
      con.commit();
      System.out.println("Records inserted......");
   }
}

Output:

Connection established......
Records inserted......

Verify the contents of the Dispatch table.

This is how you can perform Java Batch Processing in a seamless manner!

Conclusion

• In this tutorial, you learned to set up Java Batch Processing using JDBC drivers.
• This tutorial focuses on performing Java Batch Processing using Statement, PreparedStatement, and CallableStatement interfaces.
• Batch processing is essential in financial institutions that generate large amounts of data and need to transfer it in less time.
• It is used in ATM transactions, credit card transactions, customer services, and more. In case you want to export data from a source of your choice into your desired Database/destination then Hevo Data is the right choice for you! 
• Discover how to use the MariaDB JDBC driver to connect your applications to MariaDB databases with our comprehensive guide.

Share your experience of learning about Java Batch Processing. Let us know in the comments section below!