[!INCLUDEDriver_JDBC_Download]

To improve performance when multiple updates to a [!INCLUDEssNoVersion] database are occurring, the [!INCLUDEjdbcNoVersion] provides the ability to submit multiple updates as a single unit of work, also referred to as a batch.

The SQLServerStatement, SQLServerPreparedStatement, and SQLServerCallableStatement classes can all be used to submit batch updates. The addBatch method is used to add a command. The clearBatch method is used to clear the list of commands. The executeBatch method is used to submit all commands for processing. Only Data Definition Language (DDL) and Data Manipulation Language (DML) statements that return a simple update count can be run as part of a batch.

The executeBatch method returns an array of int values that correspond to the update count of each command. If one of the commands fails, a BatchUpdateException is thrown, and you should use the getUpdateCounts method of the BatchUpdateException class to retrieve the update count array. If a command fails, the driver continues processing the remaining commands. However, if a command has a syntax error, the statements in the batch fail.

As an example, create the following table in the [!INCLUDEssSampleDBnormal] sample database:

CREATE TABLE TestTable   
   (Col1 int IDENTITY,   
    Col2 varchar(50),   
    Col3 int);  

In the following example, an open connection to the [!INCLUDEssSampleDBnormal] sample database is passed in to the function, the addBatch method is used to create the statements to be executed, and the executeBatch method is called to submit the batch to the database.

public static void executeBatchUpdate(Connection con) {  
   try {  
      Statement stmt = con.createStatement();  
      stmt.addBatch("INSERT INTO TestTable (Col2, Col3) VALUES ('X', 100)");  
      stmt.addBatch("INSERT INTO TestTable (Col2, Col3) VALUES ('Y', 200)");  
      stmt.addBatch("INSERT INTO TestTable (Col2, Col3) VALUES ('Z', 300)");  
      int[] updateCounts = stmt.executeBatch();  
      stmt.close();  
   }  
   catch (Exception e) {  
      e.printStackTrace();  
   }  
}  

Using Statements with the JDBC Driver