Batch Processing in JDBC – JDBC中的批量处理

最后修改: 2017年 12月 19日

中文/混合/英文(键盘快捷键：t)

1. Introduction

1.介绍

Java Database Connectivity (JDBC) is a Java API used for interacting with databases. Batch processing groups multiple queries into one unit and passes it in a single network trip to a database.

Java数据库连接（JDBC）是一个用于与数据库互动的Java API。批量处理将多个查询分组为一个单元，并以单一的网络行程将其传递给数据库。

In this article, we’ll discover how JDBC can be used for batch processing of SQL queries.

在这篇文章中，我们将发现JDBC是如何被用于批量处理SQL查询的。

For more on JDBC, you can check out our introduction article here.

关于JDBC的更多信息，你可以查看我们的介绍文章这里。

2. Why Batch Processing?

2.为什么要批量处理？

Performance and data consistency are the primary motives to do batch processing.

性能和数据一致性是进行批处理的主要动机。

2.1. Improved Performance

2.1.改善性能

Some use cases require a large amount of data to be inserted into a database table. While using JDBC, one of the ways to achieve this without batch processing, is to execute multiple queries sequentially.

有些用例需要将大量的数据插入到数据库表中。在使用JDBC时，在不进行批处理的情况下，实现这一目的的方法之一是按顺序执行多个查询。

Let’s see an example of sequential queries sent to database:

让我们看看一个向数据库发送顺序查询的例子。

statement.execute("INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
 + "VALUES ('1','EmployeeName1','Designation1')"); 
statement.execute("INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
 + "VALUES ('2','EmployeeName2','Designation2')");

These sequential calls will increase the number of network trips to database resulting in poor performance.

这些连续的调用将增加到数据库的网络旅行次数，导致性能不佳。。

By using batch processing, these queries can be sent to the database in one call, thus improving performance.

通过使用批处理，这些查询可以在一次调用中被发送到数据库，从而提高性能。

2.2. Data Consistency

2.2.数据的一致性

In certain circumstances, data needs to be pushed into multiple tables. This leads to an interrelated transaction where the sequence of queries being pushed is important.

在某些情况下，数据需要被推送到多个表中。这导致了一个相互关联的事务，其中被推送的查询顺序很重要。

Any errors occurring during execution should result in a rollback of the data pushed by previous queries if any.

在执行过程中发生的任何错误都会导致以前的查询所推送的数据回滚，如果有的话。

Let’s see an example of adding data to multiple tables:

让我们看一个向多个表添加数据的例子。

statement.execute("INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
 + "VALUES ('1','EmployeeName1','Designation1')"); 
statement.execute("INSERT INTO EMP_ADDRESS(ID, EMP_ID, ADDRESS) "
 + "VALUES ('10','1','Address')");

A typical problem in the above approach arises when the first statement succeeds and the second statement fails. In this situation there is no rollback of the data inserted by the first statement, leading to data inconsistency.

当第一条语句成功而第二条语句失败时，上述方法的一个典型问题就出现了。在这种情况下，第一条语句插入的数据没有回滚，导致了数据的不一致性。

We can achieve data consistency by spanning a transaction across multiple insert/updates and then committing the transaction at the end or performing a rollback in case of exceptions, but in this case, we’re still hitting the database repeatedly for each statement.

我们可以通过将一个事务跨越多个插入/更新来实现数据的一致性，然后在结束时提交事务，或者在出现异常时执行回滚，但在这种情况下，我们仍然在为每个语句重复地冲击数据库。

3. How To Do Batch Processing

3.如何进行批量处理？

JDBC provides two classes, Statement and PreparedStatement to execute queries on the database. Both classes have their own implementation of the addBatch() and executeBatch() methods which provide us with the batch processing functionality.

JDBC提供了两个类，Statement和PreparedStatement来执行对数据库的查询。这两个类都有自己的addBatch()和executeBatch()方法的实现，为我们提供批处理功能。

3.1. Batch Processing Using Statement

3.1.使用Statement进行批量处理

With JDBC, the simplest way to execute queries on a database is via the Statement object.

通过JDBC，在数据库上执行查询的最简单方法是通过Statement对象。语句对象。

First, using addBatch() we can add all SQL queries to a batch and then execute those SQL queries using executeBatch().

首先，使用addBatch()，我们可以将所有SQL查询添加到一个批处理中，然后使用executeBatch()执行这些SQL查询。

The return type of executeBatch() is an int array indicating how many records were affected by the execution of each SQL statement.

executeBatch()的返回类型是一个int数组，表明每个SQL语句的执行影响了多少条记录。

Let’s see an example of creating and executing a batch using Statement:

让我们看一个使用Statement创建和执行批处理的例子。

Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
 + "VALUES ('1','EmployeeName','Designation')");
statement.addBatch("INSERT INTO EMP_ADDRESS(ID, EMP_ID, ADDRESS) "
 + "VALUES ('10','1','Address')");
statement.executeBatch();

In the above example, we are trying to insert records into the EMPLOYEE and EMP_ADDRESS tables using Statement. We can see how SQL queries are being added in the batch to be executed.

在上面的例子中，我们试图用Statement在EMPLOYEE和EMP_ADDRESS表中插入记录。我们可以看到SQL查询是如何被添加到要执行的批次中的。

3.2. Batch Processing Using PreparedStatement

3.2.使用PreparedStatement进行批量处理

PreparedStatement is another class used to execute SQL queries. It enables reuse of SQL statements and requires us to set new parameters for each update/insert.

PreparedStatement 是另一个用于执行SQL查询的类。它能够重复使用SQL语句，并要求我们为每次更新/插入设置新的参数。

Let’s see an example using PreparedStatement. First, we set up the statement using an SQL query encoded as a String:

让我们看一个使用PreparedStatement的例子。首先，我们使用编码为字符串的SQL查询来设置语句：字符串：。

String[] EMPLOYEES = new String[]{"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[]{"CFO","CSO","CTO","CEO","CMO"};

String insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) "
 + "VALUES (?,?,?)";
PreparedStatement employeeStmt = connection.prepareStatement(insertEmployeeSQL);

Next, we loop through an array of String values and add a newly configured query to the batch.

接下来，我们循环浏览一个String值的数组，并将一个新配置的查询添加到批次中。

Once the loop is finished, we execute the batch:

一旦循环结束，我们就执行批处理。

for(int i = 0; i < EMPLOYEES.length; i++){
    String employeeId = UUID.randomUUID().toString();
    employeeStmt.setString(1,employeeId);
    employeeStmt.setString(2,EMPLOYEES[i]);
    employeeStmt.setString(3,DESIGNATIONS[i]);
    employeeStmt.addBatch();
}
employeeStmt.executeBatch();

In the example shown above, we are inserting records into the EMPLOYEE table using PreparedStatement. We can see how values to be inserted are set in the query and then added to the batch to be executed.

在上面显示的示例中，我们正在使用PreparedStatement向EMPLOYEE表插入记录。我们可以看到要插入的值是如何在查询中设置，然后添加到要执行的批次中的。

4. Conclusion

4.结论

In this article, we saw how batch processing of SQL queries are important while interacting with databases using JDBC.

在这篇文章中，我们看到了在使用JDBC与数据库进行交互时，SQL查询的批处理是多么重要。

As always, the code related to this article can be found over on Github.

一如既往，与本文相关的代码可以在Github上找到over。