JDBC Tutorial - JDBC Batch Processing

• « Previous
• Next »

Batch Processing can group SQL statements into one block and pass them with one call to the database.

Batch process reduces the amount of communication overhead and improves performance.

We can use DatabaseMetaData.supportsBatchUpdates() method to check if the database supports batch update processing.

The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch.

The executeBatch() is used to execute of the batch and returns an array of integers. Each element of the array represents the update count for the corresponding update statement.

We can remove statements added with the addBatch() method with the clearBatch() method.

The following code shows how to do a batch update using Statement object.

Statement stmt = conn.createStatement();
conn.setAutoCommit(false);

String SQL = "INSERT INTO Employees VALUES(2,'name')";
stmt.addBatch(SQL);
SQL = "INSERT INTO Employees VALUES(2,'new name')";
stmt.addBatch(SQL);
SQL = "UPDATE Employees SET age = 5 WHERE id = 1";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();

Batching with PrepareStatement Object

The following code shows how to do a batch update using PrepareStatement object

String SQL = "INSERT INTO Employees (id, firstName)VALUES(?, ?)";
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 101 );
pstmt.setString( 2, "name" );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 102 );
pstmt.setString( 2, "new name" );
// Add it to the batch
pstmt.addBatch();

//add more batches
//...
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

Example

The following code is a full runnable example showing how to do batch in JDBC.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/*w  w  w . java 2  s  .c  o m*/
public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Class.forName(DB_DRIVER);
    dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    String insertTableSQL = "INSERT INTO Person"
        + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
        + "(?,?,?,?)";
    preparedStatement = dbConnection.prepareStatement(insertTableSQL);

    dbConnection.setAutoCommit(false);

    java.util.Date today = new java.util.Date();

    preparedStatement.setInt(1, 101);
    preparedStatement.setString(2, "101");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 102);
    preparedStatement.setString(2, "102");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.setInt(1, 103);
    preparedStatement.setString(2, "103");
    preparedStatement.setString(3, "system");
    preparedStatement.setTimestamp(4,new java.sql.Timestamp(today.getTime()));
    preparedStatement.addBatch();

    preparedStatement.executeBatch();

    dbConnection.commit();

    preparedStatement.close();
    dbConnection.close();

  }
}

• Next »
• « Previous