Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
To improve performance when multiple updates to a SQL Server database are occurring, the Microsoft JDBC Driver for SQL Server 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 SQL Server. This will reduce network traffic and additionally enhance the performance of your application.
As an example, create the following table in the AdventureWorks2022 sample database:
CREATE TABLE TestTable
(Col1 int IDENTITY,
Col2 varchar(50),
Col3 int);
In the following example, an open connection to the AdventureWorks2022 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();
}
}
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Module
Set up batch jobs in finance and operations apps - Training
By using batch jobs, you can avoid slowing down your computer or the server during typical working hours. You can run many tasks in finance and operations apps as part of batch jobs. For example, batch jobs can include tasks for printing reports, performing maintenance, or sending electronic documents.