performing-batch-operations.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic
Performing batch operations	Learn how to use batch operations to improve performance using the Microsoft JDBC Driver for SQL Server.	David-Engel	davidengel	08/12/2019	sql	connectivity	conceptual

Performing batch operations

[!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.

Note

If you do not have to use update counts, you can first issue a SET NOCOUNT ON statement to [!INCLUDEssNoVersion]. This will reduce network traffic and additionally enhance the performance of your application.

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();
   }
}

See also

Using statements with the JDBC driver