我可以多次更改JDBC连接的AutoCommit属性吗? [英] Can I change AutoCommit property of a JDBC connection multiple times
问题描述
我正在处理一个连接池，该连接池仅使用"autoCommit = true"创建连接.
I am dealing with a Connection Pool that only creates connections with "autoCommit=true".
但是，对于我的特定用例，我需要"autoCommit = false"，以便可以在JDBC语句上设置"fetch size"属性.
However, for my specific use case, I need "autoCommit=false" so that I can set the "fetch size" property on the JDBC statements.
我的初始测试测试表明，我可以在JDBC Connection实例上设置AutoCommit属性，然后在将连接返回到池之前再次对其进行重置.
My initial tests tests indicate that I can set the AutoCommit property on the JDBC Connection instance, and then reset it again before returning the connection to the pool.
Connection conn = pool.getConnection();
try {
conn.setAutoCommit(false);
// execute queries for my use case using above connection.
} finally {
conn.setAutoCommit(true);
// do other cleanup like statement and result set close
}
pool.returnConnection(conn);
有人知道这是否是正确的用例吗?
Does anybody know if this is a correct use case?
我正在使用Postgres，但稍后可能会迁移到Oracle.
I am using Postgres, but may migrate to Oracle later on.
推荐答案
最终更新:是的，您可以多次更改autoCommit，也可以在语句中使用commit/rollback命令解决该问题，如下所示:你发现了.我的建议是坚持将autoCommit设置为false，并始终在需要它们的地方使用事务.
Final update: yes you can change autoCommit multiple times, you can also workaround it using the commit/rollback command in a statement as you discovered. My advise is to stick with autoCommit set to false and always use transactions where you need them.
我也在使用Postgres和Oracle，并且始终使用autocommit = false，因为我无法使用autocommit = true管理交易
I'm also using Postgres and Oracle and I'm always using autocommit = false, since I cannot manage trasactions with autocommit = true
您可以在测试时更改自动提交，但我鼓励您显式管理事务，即使它是一条语句也是如此.
You can change autocommit as you tested but I encourage you to manage transactions explicitly even if it's a single statement.
如果可以使用诸如Spring(或Guice)之类的框架，则可以通过AOP进行事务管理，而无需担心提交和回滚指令.
If you can use a framework like Spring (or Guice) there is transaction management made via AOP and you don't need to bother with commit and rollback instructions.
在Oracle中，提交时间不取决于所提交的数据量，而且较高的提交频率(就功能要求而言)也可能损害性能.
In Oracle commit time does not depend to the amount of data committed and committing with an higher frequency (with respect to functional requirements) can also hurt performance.
更新:从您的评论中可以看出，Postgres尊重自动提交中的事务边界；我无法重现此行为，这是一个简单的测试用例:
Update: From your comment you state that Postgres respects transaction boundaries in autocommit; I cannot reproduce the behaviour here is a simple test case:
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestPostgresAutocommit {
public static void main(String[] args) throws Exception {
Connection connection= DriverManager.getConnection("jdbc:postgresql://pgdev/dbxxx","xxx","xxx");
connection.setAutoCommit(true);
Connection connection2= DriverManager.getConnection("jdbc:postgresql://pgdev/dbxxx","xxx","xxx");
connection2.setAutoCommit(true);
Statement statement=connection.createStatement();
for (int i=0; i<10; i++) {
statement.execute("insert into test_gc(col1,col2) values ("+ i + "," + "'" + i + "')");
}
statement.close();
countElements(connection2);
statement=connection.createStatement();
statement.execute("delete from test_gc");
statement.close();
statement=connection.createStatement();
statement.execute("begin");
statement.close();
statement=connection.createStatement();
for (int i=0; i<10; i++) {
statement.execute("insert into test_gc(col1,col2) values ("+ i + "," + "'" + i + "')");
}
connection.rollback();
countElements(connection2);
}
private static void countElements(Connection connection2) throws Exception {
Statement statement2=connection2.createStatement();
ResultSet rs=statement2.executeQuery("select count(*) from test_gc");
rs.next();
System.out.println("row num in table=" + rs.getInt(1));
rs.close();
statement2.close();
}
}
程序无法回滚并出现异常:
the program fails to rollback with an Exception:
org.postgresql.util.PSQLException:当autoCommit为时无法回滚 启用.
org.postgresql.util.PSQLException: Cannot rollback when autoCommit is enabled.
因此，当autoCommit为true时，就不可能管理事务.你找到不同的东西了吗?
So it's not possible to manage transactions when autoCommit is true; did you find something different?
更新II :即使我认为这段代码可以反映您评论中的数据，但我还是遇到了例外:
Update II: Even with this code that I think it reflects the data in your comment I got the Exception:
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestPostgresAutocommit {
public static void main(String[] args) throws Exception {
//System.out.println("start");
Connection connection= DriverManager.getConnection("jdbc:postgresql://pgdev/dbxxx","xxx","xxx");
connection.setAutoCommit(true);
Connection connection2= DriverManager.getConnection("jdbc:postgresql://pgdev/dbdxxx","xxx","xxx");
connection2.setAutoCommit(true);
Statement statement=connection.createStatement();
for (int i=0; i<10; i++) {
statement.execute("insert into test_gc(col1,col2) values ("+ i + "," + "'" + i + "')");
}
statement.close();
countElements(connection2);
statement=connection.createStatement();
statement.execute("delete from test_gc");
statement.close();
statement=connection.createStatement();
statement.execute("begin");
for (int i=0; i<10; i++) {
statement.execute("insert into test_gc(col1,col2) values ("+ i + "," + "'" + i + "')");
}
connection.rollback();
countElements(connection2);
}
private static void countElements(Connection connection2) throws Exception {
Statement statement2=connection2.createStatement();
ResultSet rs=statement2.executeQuery("select count(*) from test_gc");
rs.next();
System.out.println("row num in table=" + rs.getInt(1));
rs.close();
statement2.close();
}
}
这篇关于我可以多次更改JDBC连接的AutoCommit属性吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
