Jdbc Batch Processing
Batch Processing in Jdbc
Instead of executing a single query, we can execute a batch (group) of queries using batch processing.
Why need of Batch Processing
In a Jdbc program if multiple sql operations are there, then each operation is individually transferred to the database. This approach will increase the number of round trips between java program (application) and database. If the number of round trips is increased between an application and database, then it will reduce the performance of an application. To overcome these problems we use Batch Processing.
Advantage of Batch Processing
Increase the performance of an application.
Method of Batch Processing
The following two methods are used for performing Batch Processing. These methods are given by Statement Interface.
| Method | Description | |
|---|---|---|
| 1 | void addBatch(String query) | It adds query into batch. |
| 2 | int[] executeBatch() | It executes the batch of queries. |
Note: In Batch Processing only non-select operations are allowed select operation is not allowed
If any operation failed in batch processing then java.sql.BatchUpdate Exception will be thrown
When we want to cancel all the operation of the batch when one operation failed then apply batch processing with transaction management.
Example of Batch Processing
import java.sql.*;
class BatchTest
{
public static void main(String[] args) throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@rama-pc:1521:xe","system","system");
Statement stmt=con.createStatement();
//create batch
stmt.addBatch("insert into student values(901,'PQR',788)");
stmt.addBatch("update emp_info set esal=8888 where eno=1012");
stmt.addBatch("delete from customer where custid=111");
//disabl auto-commit mode
con.setAutoCommit(false);
try
{
int i[]=stmt.executeBatch();
con.commit();
System.out.println("batch is successfully executed");
}
catch (Exception e)
{
try
{
con.rollback();
System.out.println("batch is failed");
System.out.println("Exception is"+e);
}
catch (Exception e1)
{
}
}//end of outer catch
//cleanup
stmt.close();
con.close();
}
}