03
Oct

Batch Statement in SQL

Batch Statement in SQL

The following examples show you how to perform a batch operation to insert bulk records into a relational database using the JDBC API.

The “PreparedStatement#addBatch()” method add a statement into a batch and “PreparedStatement#
executeBatch() “method submits a batch of statements to the database for execution.

The following example inserts 200 records in a single batch using the JDBC batch operation.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* @author SR

*/

public class JDBCBatchExample {

private static final String INSERT_SQL = “INSERT INTO BOOKS “

+ “(NAME, AUTHOR) VALUES (?,?)”;

public static void main(String[] args) {

String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”;

String username = “username”;

String password = “pwd”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

conn.setAutoCommit(false);

try (PreparedStatement stmt = conn.prepareStatement(INSERT_SQL);) {

// Insert sample records

for (int i = 0; i < 200; i++) {

stmt.setString(1, “Java”);

stmt.setString(2, “Sunil Singh”);

//Add statement to batch

stmt.addBatch();

}

//execute batch

stmt.executeBatch();

conn.commit();

System.out.println(“Transaction is commited successfully.”);

} catch (SQLException e) {

e.printStackTrace();

if (conn != null) {

try {

System.out.println(“Transaction is being rolled back.”);

conn.rollback();

} catch (Exception ex) {

ex.printStackTrace();

}

}

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

This program may generate an “OutOfMemoryError”, if we insert large number of records in a single batch.

To avoid this problem, “we can divide large number of records” into n batch and execute every batch one-by-one.

The following example creates a batch of 10 records and send to database for execution until all records are processed.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* @author SR

*/

public class JDBCBatchExample {

private static final String INSERT_SQL = “INSERT INTO BOOKS “

+ “(NAME, AUTHOR) VALUES (?,?)”;

public static void main(String[] args) {

String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”;

String username = “username”;

String password = “pwd”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) {

conn.setAutoCommit(false);

try (PreparedStatement stmt = conn.prepareStatement(INSERT_SQL);) {

// Insert sample records

int counter=1;

for (int i = 0; i < 10; i++) {

stmt.setString(1, “Java”);

stmt.setString(2, “Sunil Singh”);

//Add statement to batch

stmt.addBatch();

//Execute batch of 10 records

if(i%10==0){

stmt.executeBatch();

conn.commit();

System.out.println(“Batch “+(counter++)+” executed successfully”);

}

}

//execute final batch

stmt.executeBatch();

conn.commit();

System.out.println(“Final batch executed successfully”);

} catch (SQLException e) {

e.printStackTrace();

if (conn != null) {

try {

System.out.println(“Transaction is being rolled back.”);

conn.rollback();

} catch (Exception ex) {

ex.printStackTrace();

}

}

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

output:

Batch 1 executed successfully

Batch 2 executed successfully

Batch 3 executed successfully

Batch 4 executed successfully

Batch 5 executed successfully

Batch 6 executed successfully

Batch 7 executed successfully

Batch 8 executed successfully

Batch 9 executed successfully

Batch 10 executed successfully

Final batch executed successfully