JDBC - setAutoCommit 用于只读操作

2022-01-24 00:00:00 connection jdbc java

假设我有一个创建数据库连接的通用方法:

Let's say I have a common method which creates a DB connection:

Connection getConnection() throws SQLException {
    Connection con = ... // create the connection
    con.setAutoCommit(false);
    return con;
}

我把 setAutoCommit(false) 调用放在这里，这样这个方法的调用者就不必担心设置它了.但是，如果调用者执行的操作只是读取数据，这是一种不好的做法吗?有额外的开销吗?

I put the setAutoCommit(false) call here so that callers of this method never have to worry about setting it. However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

我个人的看法是，最好将逻辑集中在一个地方，这样调用者就不必设置自动提交，这样可以避免代码冗余.我只是想确保它不会对只读操作产生任何不必要的开销.

My personal opinion is that it's better to centralize the logic in one place, that way callers never have to set the auto commit and this avoids code redundancy. I just wanted to make sure it didn't incur any unnecessary overhead for a read only operation.

推荐答案

我将 setAutoCommit(false) 调用放在这里，这样该方法的调用者就不必担心设置它.

I put the setAutoCommit(false) call here so that callers of this method never have to worry about setting it.

这很好 IMO，我个人认为永远不应该在应用程序中启用自动提交模式.所以我的建议是关闭自动提交.

This is fine IMO and I personally believe that one should never ever enable auto-commit mode inside an application. So my recommendation would be to turn off auto-commit.

但是，如果调用者执行的操作只是读取数据，这是一种不好的做法吗?有额外的开销吗?

However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

从严格的性能角度来看，它为每个具有开销并可能降低应用程序性能的 SQL 语句启动和结束数据库事务.

From a strict performance point of view, it's starting and ending a database transaction for every SQL statement that has an overhead and may decrease the performance of your application.

顺便说一下，SELECT 语句受 setAutoCommit(boolean) 根据 javadoc:

By the way, SELECT statements are affected by setAutoCommit(boolean) according to the javadoc:

设置此连接的自动提交模式到给定的状态.如果一个连接处于自动提交模式，那么它的所有 SQL 语句都将是作为个人执行和承诺交易.否则，它的 SQL语句被分组为被终止的交易调用方法 commit 或方法回滚.默认情况下，新连接处于自动提交模式.

Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.

提交发生在语句的时候完成. 语句的时间完成取决于 SQL 的类型声明:

The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement:

  • 对于 DML 语句，例如 Insert、Update 或 Delete，以及 DDL 语句，声明完成后它已完成执行.
  • 对于 Select 语句，语句完成时关联结果集已关闭.
  • 对于 CallableStatement 对象或返回多个的语句结果，语句完成当所有关联的结果集已关闭，所有更新计数和输出参数已已检索.

相关文章