1. Introduction
1.介绍
Database connections created with the JDBC API have a feature called auto-commit mode.
使用JDBC API创建的数据库连接有一个名为自动提交模式的功能。
Turning this mode on can help eliminate boilerplate code needed for managing transactions. In spite of this, however, its purpose and how it influences transaction handling when executing SQL statements can sometimes be unclear.
打开该模式可以帮助消除管理事务所需的模板代码。然而，尽管如此，它的目的以及它在执行SQL 语句时如何影响事务处理，有时可能并不清楚。
In this article, we’ll discuss what auto-commit mode is and how to use it correctly for both automatic and explicit transaction management. We’ll also cover various problems to avoid when auto-commit is either on or off.
在这篇文章中，我们将讨论什么是自动提交模式，以及如何在自动和显式事务管理中正确使用它。我们还将介绍自动提交开启或关闭时应避免的各种问题。
2. What Is JDBC Auto-Commit Mode?
2、什么是JDBC自动提交模式？
Developers will not necessarily understand how to manage database transactions effectively when using JDBC. Thus, if handling transactions manually, developers may not start them where appropriate or not at all. The same problem applies to issuing commits or rollbacks where necessary.
开发人员在使用JDBC时不一定了解如何有效地管理数据库事务。因此，如果手动处理事务，开发人员可能不会在适当的地方启动事务，或者根本就不启动。同样的问题也适用于在必要时发出commits或rollbacks。
To get around this problem, the auto-commit mode in JDBC provides a way to execute SQL statements with transaction management handled automatically by the JDBC driver.
为了解决这个问题，JDBC中的自动提交模式提供了一种执行SQL语句的方法，其事务管理由JDBC驱动程序自动处理。
Thus, the intention of auto-commit mode is to lift the burden from developers of having to manage transactions themselves. In this way, turning it on can make it easier to develop applications with the JDBC API. Of course, this only helps where it’s acceptable for data updates to be persisted immediately after each SQL statement completes.
因此，自动提交模式的意图是解除开发人员必须自己管理事务的负担。通过这种方式，打开它可以使使用JDBC API开发应用程序变得更容易。当然，这只是在每个SQL语句完成后，数据更新被立即持久化的情况下有帮助。
3. Automatic Transaction Management When Auto-Commit Is True
3.当自动提交为真时的自动事务管理
JDBC drivers turn on auto-commit mode for new database connections by default. When it’s on, they automatically run each individual SQL statement inside its own transaction.
JDBC驱动程序默认开启了新数据库连接的自动提交模式。当它开启时，它们会自动在自己的事务中运行每个单独的SQL语句。
Besides using this default setting, we can also turn on auto-commit manually by passing true to the connection’s setAutoCommit method:
除了使用这个默认设置，我们还可以通过向连接的setAutoCommit方法传递true，手动打开自动提交。
connection.setAutoCommit(true);This way of switching it on ourselves is useful for when we’ve previously switched it off, but then later we need automatic transaction management restored.
这种自己开启的方式对于我们之前关闭了它，但后来我们需要恢复自动交易管理时很有用。
Now that we’ve covered how to ensure that auto-commit is on, we’ll demonstrate that when set as such, JDBC drivers run SQL statements in their own transactions. That is, they automatically commit any data updates from each statement to the database right away.
现在我们已经介绍了如何确保自动提交处于开启状态，我们将证明，当设置为自动提交时，JDBC驱动程序会在自己的事务中运行SQL语句。也就是说，它们会自动将每个语句中的任何数据更新提交到数据库中。
3.1. Setting Up the Example Code
3.1.设置示例代码
In this example, we’ll use the H2 in-memory database to store our data. To use it, we first need to define the Maven dependency:
在本例中，我们将使用H2内存数据库来存储我们的数据。要使用它，我们首先需要定义Maven的依赖性。
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.200</version>
</dependency>To start with, let’s create a database table to hold details about people:
首先，让我们创建一个数据库表来保存关于人的详细信息。
CREATE TABLE Person (
id INTEGER not null,
name VARCHAR(50),
lastName VARCHAR(50),
age INTEGER,PRIMARY KEY (id)
)Next, we’ll create two connections to the database. We’ll use the first one to run our SQL queries and updates on the table. And we’ll use the second connection to test if updates have been made to that table:
接下来，我们将创建两个连接到数据库。我们将使用第一个连接来运行我们的SQL查询和对表的更新。我们将使用第二个连接来测试是否对该表进行了更新。
Connection connection1 = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");
Connection connection2 = DriverManager.getConnection("jdbc:h2:mem:testdb", "sa", "");Note, we need to use a separate connection to test for committed data. This is because if we run any select queries on the first connection, then they will see updates that haven’t been committed yet.
注意，我们需要使用一个单独的连接来测试已提交的数据。这是因为如果我们在第一个连接上运行任何选择查询，那么它们将看到尚未提交的更新。
Now we’ll create a POJO to represent a database record that holds information about a person:
现在我们将创建一个POJO来表示一个数据库记录，该记录持有关于一个人的信息。
public class Person {
private Integer id;
private String name;
private String lastName;
private Integer age;
// standard constructor, getters, and setters
}To insert a record into our table, let’s create a method named insertPerson:
为了在我们的表中插入一条记录，让我们创建一个名为insertPerson的方法。
private static int insertPerson(Connection connection, Person person) throws SQLException {
try (PreparedStatement preparedStatement = connection.prepareStatement(
"INSERT INTO Person VALUES (?,?,?,?)")) {
preparedStatement.setInt(1, person.getId());
preparedStatement.setString(2, person.getName());
preparedStatement.setString(3, person.getLastName());
preparedStatement.setInt(4, person.getAge());
return preparedStatement.executeUpdate();
}
}
We’ll then add a updatePersonAgeById method to update a specific record in the table:
然后我们将添加一个updatePersonAgeById方法来更新表中的一个特定记录。
private static void updatePersonAgeById(Connection connection, int id, int newAge) throws SQLException {
try (PreparedStatement preparedStatement = connection.prepareStatement(
"UPDATE Person SET age = ? WHERE id = ?")) {
preparedStatement.setInt(1, newAge);
preparedStatement.setInt(2, id);
preparedStatement.executeUpdate();
}
}
Lastly, let’s add a selectAllPeople method to select all records from the table. We’ll use this to check the results of our SQL insert and update statements:
最后，让我们添加一个selectAllPeople方法，从表中选择所有记录。我们将用它来检查我们的SQLinsert和update语句的结果。
private static List selectAllPeople(Connection connection) throws SQLException {
List people = null;
try (Statement statement = connection.createStatement()) {
people = new ArrayList();
ResultSet resultSet = statement.executeQuery("SELECT * FROM Person");
while (resultSet.next()) {
Person person = new Person();
person.setId(resultSet.getInt("id"));
person.setName(resultSet.getString("name"));
person.setLastName(resultSet.getString("lastName"));
person.setAge(resultSet.getInt("age"));
people.add(person);
}
}
return people;
}
With these utility methods now in place, we’ll test the effects of turning on auto-commit.
现在有了这些实用的方法，我们将测试打开自动提交的效果。
3.2. Running the Tests
3.2.运行测试
To test our example code, let’s first insert a person into the table. Then after that, from a different connection, we’ll check that the database has been updated without us issuing a commit:
为了测试我们的示例代码，让我们首先向表中插入一个人。然后，从另一个连接中，我们将检查数据库是否已经被更新，而我们没有发出commit。
Person person = new Person(1, "John", "Doe", 45);
insertPerson(connection1, person);
List people = selectAllPeople(connection2);
assertThat("person record inserted OK into empty table", people.size(), is(equalTo(1)));
Person personInserted = people.iterator().next();
assertThat("id correct", personInserted.getId(), is(equalTo(1)));
Then, with this new record inserted into the table, let’s update the person’s age. Thereafter, we’ll check from the second connection that the change has been saved to the database without us needing to call commit:
然后，随着这个新记录被插入到表中，我们来更新这个人的年龄。此后，我们将从第二个连接中检查该变化是否已经保存到数据库中，而不需要调用commit。
updatePersonAgeById(connection1, 1, 65);
people = selectAllPeople(connection2);
Person personUpdated = people.iterator().next();
assertThat("updated age correct", personUpdated.getAge(), is(equalTo(65)));
Thus, we have verified with our tests that when the auto-commit mode is on, the JDBC driver implicitly runs every SQL statement in its own transaction. As such, we don’t need to call commit to persist updates to the database ourselves.
因此，我们已经通过测试验证了，当自动提交模式开启时，JDBC驱动会隐含地在自己的事务中运行每一条SQL语句。因此，我们不需要调用commit来坚持自己对数据库的更新。
4. Explicit Transaction Management When Auto-Commit Is False
4.自动提交为假时的显式事务管理
We need to disable auto-commit mode when we want to handle transactions ourselves and group multiple SQL statements into one transaction.
当我们想自己处理事务，并将多个SQL语句归入一个事务时，我们需要禁用自动提交模式。
We do this by passing false to the connection’s setAutoCommit method:
我们通过向连接的setAutoCommit方法传递false来做到这一点。
connection.setAutoCommit(false);When the auto-commit mode is off, we need to manually mark the end of each transaction by calling either commit or rollback on the connection.
当自动提交模式关闭时，我们需要通过调用连接上的commit或rollback来手动标记每个交易的结束。
We need to note, however, that even with auto-commit turned off, the JDBC driver will still automatically start a transaction for us when needed. For example, this happens before we run our first SQL statement, and also after each commit or rollback.
然而，我们需要注意的是，即使关闭了自动提交功能，JDBC驱动程序仍然会在需要时自动为我们启动一个事务。例如，这发生在我们运行第一个SQL语句之前，以及每次提交或回滚之后。
Let’s demonstrate that when we execute multiple SQL statements with auto-commit off, the resulting updates will only be saved to the database when we call commit.
让我们来演示一下，当我们在关闭自动提交的情况下执行多条SQL语句时，所产生的更新只有在我们调用commit时才会被保存到数据库。
4.1. Running the Tests
4.1.运行测试
To start with, let’s insert the person record using the first connection. Then without calling commit, we’ll assert that we cannot see the inserted record in the database from the other connection:
首先，让我们使用第一个连接插入人的记录。然后在不调用commit的情况下，我们将断言我们无法从另一个连接中看到数据库中插入的记录。
Person person = new Person(1, "John", "Doe", 45);
insertPerson(connection1, person);
List<Person> people = selectAllPeople(connection2);
assertThat("No people have been inserted into database yet", people.size(), is(equalTo(0)));
Next, we’ll update the person’s age on that record. And as before, we’ll then assert without calling commit, that we still can’t select the record from the database using the second connection:
接下来，我们将更新该记录中的人的年龄。和以前一样，我们将在不调用提交的情况下断言，我们仍然不能使用第二个连接从数据库中选择记录。
updatePersonAgeById(connection1, 1, 65);
people = selectAllPeople(connection2);
assertThat("No people have been inserted into database yet", people.size(), is(equalTo(0)));
To complete our testing, we’ll call commit and then assert that we can now see all the updates in the database using the second connection:
为了完成我们的测试，我们将调用commit，然后断言我们现在可以使用第二个连接看到数据库中的所有更新。
connection1.commit();
people = selectAllPeople(connection2);
Person personUpdated = people.iterator().next();
assertThat("person's age updated to 65", personUpdated.getAge(), is(equalTo(65)));
As we have verified in our tests above, when the auto-commit mode is off we need to manually call commit to persist our changes to the database. Doing so will save any updates from all the SQL statements we’ve executed since the start of our current transaction. That is either since we opened the connection if this is our first transaction, or otherwise after our last commit or rollback.
正如我们在上面的测试中所验证的那样，当自动提交模式关闭时，我们需要手动调用commit来将我们的更改持久化到数据库中。这样做将保存我们在当前事务开始后执行的所有SQL语句的任何更新。如果这是我们的第一个事务，那就是自我们打开连接以来，或者在我们最后一次commit或rollback之后。
5. Considerations and Potential Problems
5.考虑因素和潜在的问题
It can be convenient for us to run SQL statements with auto-commit turned on for relatively trivial applications. That is, where manual transaction control is not necessary. However, in more complex situations we should consider that when the JDBC driver handles transactions automatically, this may sometimes lead to unwanted side effects or problems.
对于相对琐碎的应用来说，我们在运行SQL语句时打开自动提交是很方便的。也就是说，在这种情况下，手动事务控制是不必要的。然而，在更复杂的情况下，我们应该考虑到，当JDBC驱动自动处理事务时，有时可能会导致不必要的副作用或问题。
One thing we need to consider is that when auto-commit is on it can potentially waste significant processing time and resources. This is because it causes the driver to run every SQL statement in its own transaction whether necessary or not.
我们需要考虑的一点是，当自动提交开启时，有可能会浪费大量的处理时间和资源。这是因为它导致驱动程序在自己的事务中运行每个SQL语句，无论是否有必要。
For example, if we execute the same statement many times with different values, each call is wrapped in its own transaction. Therefore, this can lead to unnecessary execution and resource management overhead.
例如，如果我们用不同的值多次执行同一个语句，那么每次调用都被包裹在自己的事务中。因此，这可能导致不必要的执行和资源管理开销。
Thus in such cases, it’s usually better for us to turn auto-commit off and explicitly batch multiple calls to the same SQL statement into one transaction. In doing so, we’ll likely see a substantial improvement in application performance.
因此，在这种情况下，我们通常最好关闭自动提交，并明确地将对同一SQL语句的多次调用批量化为一个事务。这样做，我们可能会看到应用程序的性能有很大的改善。
Something else to note is that it’s not advisable for us to switch auto-commit back on during an open transaction. This is because turning the auto-commit mode on mid-transaction will commit all pending updates to the database, whether the current transaction is complete or not. Thus, we should probably avoid doing this as it may lead to data inconsistencies.
还有一点需要注意的是，我们不建议在一个开放的事务中重新开启自动提交。这是因为在交易中途打开自动提交模式，将向数据库提交所有未决的更新，无论当前交易是否完成。因此，我们也许应该避免这样做，因为这可能会导致数据不一致。
6. Conclusion
6.结论
In this article, we discussed the purpose of auto-commit mode in the JDBC API. We also covered how to enable both implicit and explicit transaction management by turning it on or off respectively. Lastly, we touched on various issues or problems to consider whilst using it.
在这篇文章中，我们讨论了JDBC API中自动提交模式的目的。我们还介绍了如何通过打开或关闭它来启用隐式和显式事务管理。最后，我们谈到了在使用它时需要考虑的各种问题。
As always, the full source code of the examples can be found over on GitHub.
一如既往，可以在GitHub上找到这些例子的完整源代码。