What is batch processing in JDBC?

Grouping related SQL statements into a batch and executing/submitting them at once is known as batch processing.

While executing a set of statements one after other the execution switches from the database to program simultaneously.

Using batch processing we can reduce this communication overhead and increase the performance of our Java application.

For Example, if we have a table named Emp with the following description:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Name     | varchar(255) | YES  |     | NULL    |       |
| DOB      | String       | YES  |     | NULL    |       |
| Location | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

And if you want to insert data we will generally do it using the statement as:

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

And using PreparedStatement as:

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();

If you observe carefully in both scenarios, we are executing each insert statement individually. Which implies for each insert statement in the program, when the execute() method invoked, the insert statement is executed in the database and reverts back to the program.

This is ok for less number of inserts. But if this happens while inserting huge number of records this may occur communication overhead and the program takes more time to execute.

Using Batch Processing you can simply add the statements to the batch using the addBatch() method of the Statement Interface and execute them later using the executeBatch() method 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();