Batch Processing in JDBC

Instead of executing a single query, we can execute a batch (group) of queries. It makes the performance fast.

The java.sql.Statement and java.sql.PreparedStatement interfaces provide methods for batch processing.

Advantage of Batch Processing

Fast Performance

---

Methods of Statement interface

The required methods for batch processing are given below:

Method	Description
void addBatch(String query)	It adds query into batch.
int[] executeBatch()	It executes the batch of queries.

---

Example of batch processing in jdbc

Let's see the simple example of batch processing in jdbc. It follows following steps:

• Load the driver class
• Create Connection
• Create Statement
• Add query in the batch
• Execute Batch
• Close Connection

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

If you see the table user420, two records has been added.

Example of batch processing using PreparedStatement

1. import java.sql.*;  
2. import java.io.*;  
3. class BP{  
4. public static void main(String args[]){  
5. try{  
6.   
7. Class.forName("oracle.jdbc.driver.OracleDriver");  
8. Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.   
10. PreparedStatement ps=con.prepareStatement("insert into user420 values(?,?,?)");  
11.   
12. BufferedReader br=new BufferedReader(new InputStreamReader(System.in));  
13. while(true){  
14.   
15. System.out.println("enter id");  
16. String s1=br.readLine();  
17. int id=Integer.parseInt(s1);  
18.   
19. System.out.println("enter name");  
20. String name=br.readLine();  
21.   
22. System.out.println("enter salary");  
23. String s3=br.readLine();  
24. int salary=Integer.parseInt(s3);  
25.   
26. ps.setInt(1,id);  
27. ps.setString(2,name);  
28. ps.setInt(3,salary);  
29.   
30. ps.addBatch();  
31. System.out.println("Want to add more records y/n");  
32. String ans=br.readLine();  
33. if(ans.equals("n")){  
34. break;  
35. }  
36.   
37. }  
38. ps.executeBatch();  
39.   
40. System.out.println("record successfully saved");  
41.   
42. con.close();  
43. }catch(Exception e){System.out.println(e);}  
44.   
45. }}  

It will add the queries into the batch until user press n. Finally it executes the batch. Thus all the added queries will be fired