JDBC batch example

"A batch is a group of one or more SQL (Structured Query Language) statements sent in one go to the SQL Server from an application for execution. The SQL Server parse the SQL statements in a single executable unit, called an SQL statement statement execution plan. The statements in the execution plan are then executed by the server in one go."

In Java, JDBC support batch processing using java.sql.Statement and java.sql.PreparesStatement inrerfaces. We can submit multiple DML(Data Manupulation Language) statements to the database server in one call from java application using java.sql package. Only insert, update and delete statements are allowed in batch processing in java. It minimize the number of hits (calls) to database server from java applications.

We can create batch by calling addBatch("Insert or Update or Delete sql statement") method from Statement or PreparesStatement interface. After adding all the statements, call executeBatch() to execute batch. The return type is arry type of int. The values in the itn array indicates how many are affected by the SQL statement in database server.
Syntax : SQL statements to batch -

  • stmt.addBatch("insert into emp values('a001','ram','noida',1000)");
  • stmt.addBatch("insert into emp values('a002','shayam','pune',2000)");
  • stmt.addBatch("insert into emp values('a003','raman','kolkata',3000)");
  • stmt.addBatch("insert into emp values('a004','suman','mumbai',4000)");

Execute batch in database server -
int i[] = st.executeBatch();


Follwoing is the source code to demostrates the batch processing using JDBC -
Here I am inserting multiple records in Student table using batch -

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BatchDemo1 {
Connection con;
Statement stmt;

public BatchDemo1() {
  try {
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection("jdbc:mysql://localhost/
        test?user=root&password=root");
  catch (Exception e) {
  System.out.println("Error in connection" + e);
  }
}

// Create table in database server
public void createTable() {
  try {
  String sql1 = "drop table if exists Student";
  String sql = "create table Student(rollNo int primary key,
  Name varchar(30), Dob varchar(20), Course varchar(20))";

    // drop the table if exists
    stmt = con.createStatement();
    stmt.executeUpdate(sql1);

    // execute SQL statement to create table statement
    stmt = con.createStatement();
    int i = stmt.executeUpdate(sql);
    System.out.println("Table created");
  catch (Exception e) {
    e.printStackTrace();
  }
}

public void batchProcess() {
  try {
  stmt.addBatch("insert into Student values
  (1,'Ram Kumar','10-02-1990','BTech')");
  
  stmt.addBatch("insert into Student 
  values(2,'Shayam Kumar','15-05-1992','BTech')");
  
  stmt.addBatch("insert into Student 
  values(3,'Ritu Kumari','4-07-1989','MCA')");
  
  stmt.addBatch("insert into Student 
  values(4,'Suman Saurabh','12-12-1995','MCA')");
  
  stmt.addBatch("insert into Student 
  values(5,'Suresh Sharma','10-11-1991','BTech')");
  int i[] = stmt.executeBatch();
  for (int j : i) {
  if (j != 0)
  System.out.println("Executed successfully");
  else
  System.out.println("Not Executed successfully");
  }
catch (Exception e) {
  e.printStackTrace();
}
}
public void showRecords() {
  try {
  stmt = con.createStatement();
  ResultSet res = stmt.executeQuery("select * from Student");
  while (res.next()) {
    System.out.print(res.getString(1));
    System.out.print("\t" + res.getString(2));
    System.out.print("\t" + res.getString(3));
    System.out.println("\t" + res.getString(4));
  }
catch (Exception e) {
  System.out.println("Error in fetching data" + e);
}
}
public static void main(String[] args) {
  BatchDemo1 obj = new BatchDemo1();
  obj.createTable();
  obj.batchProcess();
  obj.showRecords();
}
}
Download

Previous Next


Write your Comment

Name :
Email Id : [ We will not publish your email id ]
Your Comment : [ Maximum 200 chars ]
Enter Security code