JDBC Batch Processing (Batch)

Batch processing allows you to group related SQL statements into a batch and submit them by making a single call to the database.

When you send multiple SQL statements to the database at once, you can reduce communication overhead, thereby improving performance.

• The JDBC driver does not need to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. If the JDBC driver supports this feature, the method returns true.

• The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add a single statement to the batch. executeBatch() is used to start executing all the statements combined together.

• executeBatch() returns an array of integers, with each element representing the update count of the corresponding update statement.

• Just as you can add statements to a batch for processing, you can also use the clearBatch() method to delete them. This method deletes all statements that have been added with the addBatch() method. However, you cannot select which statements to delete.

Use statement objects for batch processing

This is a typical sequence of steps to use batch processing with Statement objects-

• Use these twocreateStatement()method to create Statement object.

• use setAutoCommit() to set auto-commit is set to false.

• Use the addBatch() method on the created statement object to add any number of SQL statements to the batch.

• 对所创建的语句对象使用 executeBatch ()方法执行所有 SQL 语句。

• 最后，使用commit()方法提交所有更改。

Example

The following code snippet provides an example of using the Statement object for batch updates-

//　Create a statement object
Statement stmt = conn.createStatement();
//　将自动提交设置为false
conn.setAutoCommit(false);
//　创建SQL语句
String　SQL　=　"INSERT　INTO　Employees　(id,　first,　last,　age)　"　+
　　　　　　　　　　　　　"VALUES(200, 'Zia', 'Ali',　30)";
//　Add the above SQL statements in the batch.
stmt.addBatch(SQL);
//　Create another SQL statement
String　SQL　=　"INSERT　INTO　Employees　(id,　first,　last,　age)　"　+
　　　　　　　　　　　　　"VALUES(201Raj, Kumar,　35)";
//　Add the above SQL statements in the batch.
stmt.addBatch(SQL);
//　Create another SQL statement
String SQL = "UPDATE Employees SET age =　35　"　+
　　　　　　　　　　　　　"WHERE id =　100";
//　Add the above SQL statements in the batch.
stmt.addBatch(SQL);
//　创建一个int　[]来保存返回的值
int[]　count　=　stmt.executeBatch();
//明确提交语句以应用更改
conn.commit();

为了更好地理解，让我们研究批处理示例代码。

Use PrepareStatement objects for batch processing

This is a typical sequence of steps to use batch processing with PrepareStatement objects-

1. Create SQL statements using placeholders.

2. Use any PrepareStatement() method to create a PrepareStatement object.

3. use setAutoCommit() to set auto-commit is set to false.

4. 在创建的语句对象上使用 addBatch ()方法添加尽可能多的 SQL 语句到批处理中。

5. 对所创建的语句对象使用 executeBatch ()方法执行所有 SQL 语句。

6. 最后，使用commit()方法提交所有更改。

以下代码段提供了使用PrepareStatement对象进行批量更新的示例-

//　创建SQL语句
String　SQL　=　"INSERT　INTO　Employees　(id,　first,　last,　age)　"　+
　　　　　　　　　　　　　"VALUES(?,　?,　?,　?)";
//　创建PrepareStatement对象
PreparedStatemen　pstmt　=　conn.prepareStatement(SQL);
//将自动提交设置为false
conn.setAutoCommit(false);
//　设置变量
pstmt.setInt(　1,　400　);
pstmt.setString(　2,　"Pappu"　);
pstmt.setString(　3,　"Singh"　);
pstmt.setInt(　4,　33　);
//　将其添加到批
pstmt.addBatch();
//　设置变量
pstmt.setInt(　1,　401　);
pstmt.setString(　2,　"Pawan"　);
pstmt.setString(　3,　"Singh"　);
pstmt.setInt(　4,　31　);
//　将其添加到批
pstmt.addBatch();
//添加更多批
.
.
.
.
//创建一个int　[]来保存返回的值
int[]　count　=　stmt.executeBatch();
//明确提交语句以应用更改
conn.commit();

为了更好地理解，让我们研究批处理示例代码。