Using Batch Updates

Another aspect of enhancing performance of applications is the reduction of network traffic between J2EE tiers, or applications that partitioned to run on different servers. One way to reduce network traffic back and forth between components and enhance the performance of the running application is to use bulk updates, which are coarse-grain updates. This experience is reflected by the JDBC architects in the JDBC API, and is implemented by using the addBatch() method of the Statement object to prepare the batch before using the executeBatch() method. Batch updates use any of the INSERT, UPDATE, and DELETE operations. To use batch updates, you must disable the auto-commit mode.

try{
  // Disable auto-commit
  conn.setAutoCommit(false);
  // Create a Statement from Connection
  Statement stmt = conn.createStatement()
  stmt.addBatch("INSERT INTO DAY09_STUDENT " +
  "values('7', 'ERIC', 'CHRISTIAN', '1 MEMORIAL DRIVE, CAMBRIDGE, MA')";
  stmt.addBatch("UPDATE DAY09_STUDENT set first_name='Laura' where id='5'");
  int [] rc = stmt.executeBatch();
  conn.commit();
  conn.setAutoCommit(true);
} catch(BatchUpdateException sqlb) {
    System.err.println("SQL State: " + sqlb.getSQLState());
    System.err.println("Message: " + sqlb.getMessage());
    System.err.println("Vendor Error: " + sqlb.getErrorCode());
    System.err.print("Update counts:  ");
    int [] updateCounts = sqlb.getUpdateCounts();
    for (int i = 0; i < updateCounts.length; i++) {
      System.err.print(updateCounts[i] + "   ");
    }
    System.err.println("");
  }

The executeBatch() method returns an array of integers that specifies the row count of each operation in the batch.

Batch updates throw BatchUpdateException, which inherits from SQLException, and information can extracted about each update. Performance can be enhanced by tuning the number of rows to be fetched at a runtime from database. By default, you can get the default number of rows provided by the JDBC driver by using getFetchSize() on the Statement object. You can tune the size by using the setFetchSize(int rows) method of the Statement object.