JDBC Statement – Batch Example – Developers Corner

Java

A JDBC statement object is used to send SQL command(s) to your relational database management system (RDBMS). It is associated with an open connection to the database and may not be created without one. In this example, we will insert several records into the employee table using addBatch() and executeBatch() calls. By using executeBatch() we can effectively increase database performance during our inserts as we are effectively reducing the number of database calls and round trip calls to and from the database.

Helpful Tip

When working with executeBatch() always set your AutoCommit to false. Then add all your SQL statements using the addBatch() method on the statement object. Perform the executeBatch() for all the statements and then commit all the SQL statements using the commit() method.

Code Snippet

// set auto-commit to false, we want a single transaction
connection.setAutoCommit(false);

// Add all your SQL statements
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);

// execute all the SQLs statements in one shot
int[] rows = statement.executeBatch();

// commit transaction to apply changes to the database
connection.commit();

Common Methods used with Statements

Method Name	Description
addBatch(String sql)	Adds the given SQL to the current list of SQL commmands for this Statement object
execute(String sql)	Executes the given SQL statement, which may return multiple results
executeBatch()	Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts
executeQuery(String sql)	Executes the given SQL statement, which returns a single ResultSet object
executeUpdate(String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement

Creating a Statement Object

statement = connection.createStatement();
statement.executeUpdate(sql);

Full Program Listing

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementBatchInsertExample {

  public static void main(String[] args) throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    Statement statement   = null;
    ResultSet result      = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    System.out.println("Trying to insert records using executeBatch...");
    try {
      if (connection != null) {
        statement = connection.createStatement();
        String sql1 = "INSERT INTO dbo.employee "
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(20, 'Nathan', 'Rogers', 'S', 123500, '5/24/2010', null);";
        
          String sql2 = "INSERT INTO dbo.employee "
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(21, 'Bill', 'Cunningham', 'S', 144100, '02/12/2006', null);";

          String sql3 = "INSERT INTO dbo.employee "
            + "(employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) "
            + "VALUES(22, 'Stacey', 'Johnson', 'S', 183700, '04/08/2009', null);";
          
          System.out.println(sql1);
          System.out.println(sql2);
          System.out.println(sql3);
        
          connection.setAutoCommit(false);
          statement.addBatch(sql1);
          statement.addBatch(sql2);
          statement.addBatch(sql3);
          
          int[] rows = statement.executeBatch();
          
          connection.commit();
          
          System.out.println("Inserted records into employee table using Batch...");
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
    } catch (SQLException e) {
      if (connection != null) connection.rollback();
      e.printStackTrace();
      return;
    } finally {
      System.out.println("Closing connection...");
        if (connection != null) connection.close();
    }
  }
}

Check Table to verify records where properly inserted

statement_batch_records — Table 1: Employee Table

Output

Trying to insert records using executeBatch...
INSERT INTO dbo.employee (employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(20, 'Nathan', 'Rogers', 'S', 123500, '5/24/2010', null);
INSERT INTO dbo.employee (employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(21, 'Bill', 'Cunningham', 'S', 144100, '02/12/2006', null);
INSERT INTO dbo.employee (employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date) VALUES(22, 'Stacey', 'Johnson', 'S', 183700, '04/08/2009', null);
Inserted records into employee table using Batch...
Closing connection...