本文介绍了我可以多次更改JDBC连接的AutoCommit属性吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在处理一个连接池,该连接池仅使用"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:

因此,当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属性吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-10 23:54