In this tutorial, we are going to learn about Batch processing in JDBC with example program.
Batch Processing in JDBC :
Here are the most important points about Batch Processing in JDBC.
- If there are multiple sql operations in a jdbc program then one by one operation will be executed on database sequentially.
- If the commands are executed sequentially (one by one) then the number of trips (rounds) between an application to database will be increased.
- If number of trips are increased then the performance of an application will be decreased.
- In order to improve the performance by reducing the number of trips, we will use Batch Processing in JDBC.
- In Batch Processing, the SQL operations will be constructed as a batch and then the batch will be send to database in a single trip.
How to Prepare a Batch :
To perform the batch processing in JDBC, the Statement interface provided two methods.
- addBatch()
- executeBatch()
addBatch() :
addBatch() method is used to construct a batch. Constructing a batch means, storing the SQL commands in a buffer, maintained by Statement object.
executeBatch() :
To execute the batch, we will use the executeBacth() method. When executeBatch() called, then the commands will be transferred at a time as a batch from buffer to database.
While executing the batch in database, in the middle of command fails then all remaining all commands will be cancelled and finally BatchUpdateException will be thrown. So that it is recommended to execute the batch with Transaction Management.
Batch Processing in JDBC Example :
Here is the complete example for Batch Processing in JDBC.
package com.onlinetutorialspoint.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Jdbc_BatchProcess_Example {
public static void main(String[] args) throws Exception {
Connection connection = null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
"systemuser23!");
Statement stmt = connection.createStatement();
stmt.addBatch("update student set sname='Rajesh' where sid=101 ");
stmt.addBatch("insert into student values(30,'Chandra Shekhar','Banglore','30')");
stmt.addBatch("delete from student where sid=3001");
int[] result = stmt.executeBatch();
int sum = 0;
for (int i = 0; i > result.length; i++) {
sum = sum + result[i];
}
System.out.println(sum + " records are effected ");
stmt.close();
connection.close();
}
}
Output :
2 records are effectedHappy Learning 🙂
