JDBC – Batch Processing

Batch processing:

  • It is the concept of submitting group of related SQL statements with one call to database.
  • It improves the performance by reduce the amount of communication.
  • Statement interface providing methods for batch processing.
void addBatch(String query)It adds query into batch.
int[] executeBatch()It executes the batch of queries.
package online;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class BatchInsert
{
            static Connection con;
            public static void main(String args[]) throws Exception
            {
                        try
                        {
                                    Class.forName(“oracle.jdbc.driver.OracleDriver”);
                                    System.out.println(“Driver is ready”);
 
                                    con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”);
                                    System.out.println(“Connection is ready”);
                                   
                                    String q1 = “insert into account values(101, ‘chinnu’, 7000)”;
                                    String q2 = “insert into account values(102, ‘pandu’, 6000)”;
                                    String q3 = “update account set balance=balance+500 where num>=1001”;
                                   
                                    Statement stmt = con.createStatement();
                                    stmt.addBatch(q1);
                                    stmt.addBatch(q2);
                                    stmt.addBatch(q3);
                                   
                                    int results[] = stmt.executeBatch();
                                    for(int i=0 ; i<results.length ; i++)
                                    {
                                                System.out.println(“Query-” + (i+1) + ” updates : ” + results[i] + ” records”);
                                    }
                        }
                        finally
                        {
                                    if(con != null)
                                    {
                                                con.close();
                                                System.out.println(“Connection closed”);
                                    }
                        }
            }
}

Output:

Driver is ready
Connection is ready
Query-1 updates : 1 records
Query-2 updates : 1 records
Query-3 updates : 4 records
Connection closed

Batch processing using PreparedStatement example:

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class BatchPrepared
{
            static Connection con;
            static BufferedReader br;
            public static void main(String args[]) throws Exception{
                        try
                        {
                                    Class.forName(“oracle.jdbc.driver.OracleDriver”);
                                    System.out.println(“Driver is ready”);
 
                                    con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”, “system”, “admin”);
                                    System.out.println(“Connection is ready”);
                                    br = new BufferedReader(new InputStreamReader(System.in));
                                    String query = “insert into account values(?,?,?)”;
                                    PreparedStatement stmt=con.prepareStatement(query);
                                    while(true)
                                    {
                                                System.out.println(“Enter account details(num, name, balance) : “);
                                                int nu = Integer.parseInt(br.readLine());
                                                String na = br.readLine();
                                                int ba = Integer.parseInt(br.readLine());
                                   
                                                stmt.setInt(1, nu);
                                                stmt.setString(2, na);
                                                stmt.setInt(3, ba);
                                                stmt.addBatch();
                                                System.out.println(“Query added to batch”);
                                                System.out.println(“Do you want to add one more record(y/n) : “);
                                                if(br.readLine().charAt(0)==’n’)
                                                {
                                                            break;
                                                }
                                    }
                                    stmt.executeBatch();
                                    System.out.println(“Batch executed”);                         
                        }
                        finally
                        {
                                    if(con != null)
                                    {
                                                con.close();
                                                System.out.println(“Connection closed”);
                                    }
                                    if(br != null)
                                    {
                                                br.close();
                                                System.out.println(“Buffer closed”);
                                    }
                        }
            }
}

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top