PreparedStatement in Batch Mode

This section describes how to use PreparedStatement objects in batch mode.

If you want to execute a PreparedStatement object multiple times in a single transaction, you can use the batch feature of the PreparedStatement object. Each time the addBatch() method is called, a copy of the embedded SQL statement will be created, but not executed until the execution method call. Here is sample sequence of method calls to execute a PreparedStatement object in batch mode:

ps.setXXX(...); // Set parameters for the first copy
...
ps.addBatch();  // Create the first copy of the SQL statement
ps.setXXX(...); // Set parameters for the second copy
...
ps.addBatch();  // Create the second copy of the SQL statement
ps.setXXX(...); // Set parameters for the third copy
...
ps.addBatch();  // Create the third copy of the SQL statement

ps.executeBatch(); // Execute all copies together as a batch

Here is a sample program that creates a PrepareStatement object and executes it in batch mode to run an INSERT statement 4 times:

/* DerbyPreparedStatementBatch.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class DerbyPreparedStatementBatch {
  public static void main(String [] args) {
    Connection con = null;
    try {
      Hashtable env = new Hashtable();
      env.put(Context.INITIAL_CONTEXT_FACTORY,
        "com.sun.jndi.fscontext.RefFSContextFactory");
      env.put(Context.PROVIDER_URL, "file:/local/fscontext");
      Context ctx = new InitialContext(env);
      DataSource ds = (DataSource) ctx.lookup("DerbyTestDB");
      con = ds.getConnection();

// PreparedStatement
      PreparedStatement ps = con.prepareStatement(
  "INSERT INTO Profile (FirstName, LastName) VALUES (?, ?)");

// Provide values to parameters for copy 1
      ps.setString(1,"John");
      ps.setString(2,"First");

// Create copy 1
      ps.addBatch();

// Provide values to parameters for copy 2
      ps.setString(1,"Bill");
      ps.setString(2,"Second");

// Create copy 2
      ps.addBatch();

// Provide values to parameters for copy 3
      ps.setString(1,"Mark");
      ps.setString(2,"Third");

// Create copy 3
      ps.addBatch();

// Provide values to parameters for copy 4
      ps.setString(1,"Jack");
      ps.setString(2,"Last");

// Create copy 4
      ps.addBatch();

// Execute all 4 copies
      int[] counts = ps.executeBatch();

      int count = 0;
      for (int i=0; i<counts.length; i++) {
        count += counts[i];
      }
      System.out.println("Total effected rows: "+count);

// Close the PreparedStatement object
      ps.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

Here is the output:

Total effected rows: 4

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Derby (Java DB)

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Derby (Java DB) - DML Statements

 Derby (Java DB) - ResultSet Objects of Queries

Derby (Java DB) - PreparedStatement

 PreparedStatement Overview

 PreparedStatement with Parameters

PreparedStatement in Batch Mode

 Performance of Inserting Rows with a PreparedStatement

 Performance of Inserting Rows with a Regular Statement

 Performance of Inserting Rows with a ResultSet

 Summary of JDBC Drivers and Database Servers

 Using Connection Pool with JDBC

 Archived Tutorials

 References

 Full Version in PDF/EPUB