«Back to Home

Core Java
Topics

Batch Processing In JDBC

Batch Processing
 
In JDBC, we can execute a batch (group) of queries instead of executing a single query.
 
The java.sql.Statement interface and java.sql.PreparedStatement interface provides methods for batch processing.
 
Advantage of Batch Processing

Fast Performance

it is mainly used to make the performance fast.
 
Methods of Statement interface

The necessary methods for batch processing are,
 
void addBatch(String query)

This method is used to add a query into a batch.
 
int[] executeBatch()

This method is used to execute the batch of queries.
 
Let's see an example of batch processing in JDBC.
 
Code
  1. import java.sql.*;  
  2. public class StudentDatabase {  
  3.     public static void main(String args[]) throws Exception {  
  4.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  5.         Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Student", "Student", "student");  
  6.         conn.setAutoCommit(false);  
  7.         Statement st = conn.createStatement();  
  8.         st.addBatch("insert into STUDENT.STUDENTDB values(190,'Jia',23,'RTMS College')");  
  9.         st.addBatch("insert into STUDENT.STUDENTDB values(191,'Joncy',29,'TMTS College')");  
  10.   
  11.         st.executeBatch();  
  12.         conn.commit();  
  13.         conn.close();  
  14.     }  
  15. }  
13

If we see the table student, two records has been added.
 
Let’s see an example of batch processing using PreparedStatement.
 
Code
  1. import java.sql.*;  
  2. import java.io.*;  
  3. public class StudentDatabase {  
  4.     public static void main(String args[]) throws Exception {  
  5.         Class.forName("org.apache.derby.jdbc.ClientDriver");  
  6.         Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Student", "Student", "student");  
  7.   
  8.   
  9.         PreparedStatement ps = conn.prepareStatement("insert into STUDENT.STUDENTDB values(?,?,?,?)");  
  10.         BufferedReader br = new BufferedReader(new InputStreamReader(System.in));  
  11.         while (true) {  
  12.             System.out.println("Enter id");  
  13.             String s1 = br.readLine();  
  14.             int id = Integer.parseInt(s1);  
  15.             System.out.println("Enter name");  
  16.             String name = br.readLine();  
  17.             System.out.println("Enter age");  
  18.             String s3 = br.readLine();  
  19.             int age = Integer.parseInt(s3);  
  20.             System.out.println("Enter collage");  
  21.             String college = br.readLine();  
  22.             ps.setInt(1, id);  
  23.             ps.setString(2, name);  
  24.             ps.setInt(3, age);  
  25.             ps.setString(4, college);  
  26.             ps.executeUpdate();  
  27.             System.out.println("Want to add more records yes/no");  
  28.             String ans = br.readLine();  
  29.             if (ans.equals("no")) {  
  30.                 break;  
  31.             }  
  32.         }  
  33.         ps.executeBatch();  
  34.         System.out.println("Record successfully saved");  
  35.         conn.close();  
  36.     }  
  37. }  
14
15

Output

16

In the example stated above, it will add the queries into the batch until the user presses no. Finally, it executes the batch. Thus, all the added queries will be fired.
 
Summary

Thus, we learnt, in JDBC, we can execute a batch (group) of queries instead of executing a single query and also learn how we can use it in Java.