spring 如何在spring jdbc模板中将自动提交设置为false

How to set autocommit to false in spring jdbc template

提问by bad programmer

Currently I'm setting autocommit to false in spring through adding a property to a datasource bean id like below :

目前，我通过向数据源 bean id 添加一个属性，在 spring 中将 autocommit 设置为 false，如下所示：

   <property name="defaultAutoCommit" value="false" /> 

But i need to add it specifically in a single java method before executing my procedure. I used the below code snippet.

但是我需要在执行我的过程之前将它专门添加到单个 java 方法中。我使用了下面的代码片段。

  getJdbcTemplate().getDataSource().getConnection().setAutoCommit(false);

But the above line was not setting autocommit to false?
Am i missing anything ?
or any alternative to set autocommit in a specific java method by spring

但是上面的行没有将 autocommit 设置为 false？
我错过了什么吗？
或通过 spring 在特定 java 方法中设置自动提交的任何替代方法

Thanks

谢谢

回答by Yosef Weiner

The problem is that you are setting autocommit on a Connection, but JdbcTemplatedoesn't remember that Connection; instead, it gets a new Connectionfor each operation, and that might or might not be the same Connectioninstance, depending on your DataSourceimplementation. Since defaultAutoCommitis not a property on DataSource, you have two options:

问题是您在 a 上设置了自动提交Connection，但JdbcTemplate不记得了Connection；相反，它Connection为每个操作获取一个新的，并且可能是也可能不是同一个Connection实例，这取决于您的DataSource实现。由于defaultAutoCommit不是 上的属性DataSource，您有两个选择：

1. Assuming your concrete datasource has a setter for defaultAutoCommit(for example, org.apache.commons.dbcp.BasicDataSource), cast the DataSourceto your concrete implementation. Of course this means that you can no longer change your DataSourcein your Spring configuration, which defeats the purpose of dependency injection.

1. 假设您的具体数据源有一个设置器defaultAutoCommit（例如，org.apache.commons.dbcp.BasicDataSource），则将其转换DataSource为您的具体实现。当然，这意味着您不能再更改您DataSource的 Spring 配置，这违背了依赖注入的目的。

((BasicDataSource)getJdbcTemplate().getDataSource()).setDefaultAutoCommit(false);

((BasicDataSource)getJdbcTemplate().getDataSource()).setDefaultAutoCommit(false);

1. Set the DataSourceto a wrapper implementation that sets AutoCommit to false each time you fetch a connection.

   final DataSource ds = getJdbcTemplate().getDataSource();
   getJdbcTemplate().setDataSource(new DataSource(){
     // You'll need to implement all the methods, simply delegating to ds
   
     @Override
     public Connection getConnection() throws SQLException {
       Connection c = ds.getConnection();
       c.setAutoCommit(false);
       return c;
     }
   });
   

2. 将 设置为DataSource每次获取连接时将 AutoCommit 设置为 false 的包装器实现。

   final DataSource ds = getJdbcTemplate().getDataSource();
   getJdbcTemplate().setDataSource(new DataSource(){
     // You'll need to implement all the methods, simply delegating to ds
   
     @Override
     public Connection getConnection() throws SQLException {
       Connection c = ds.getConnection();
       c.setAutoCommit(false);
       return c;
     }
   });
   

回答by Sujan

You need to get the current connection. e.g.

您需要获取当前连接。例如

Connection conn = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());
    try {
        conn.setAutoCommit(false);

        /**
         * Your Code
         */
        conn.commit();
    } catch (SQLException e) {
        conn.rollback();
        e.printStackTrace();
    }

回答by Prabhu R

You will have to do for each statement that the jdbcTemplate executes. Because for each jdbcTemplate.execute() etc it gets a new connection from the Datasource's connection pool. So you will have to set it for the connection that the connection the jdbcTemplate uses for that query. So you will have to do something like

您必须为 jdbcTemplate 执行的每个语句执行一次。因为对于每个 jdbcTemplate.execute() 等，它都会从数据源的连接池中获得一个新连接。因此，您必须为 jdbcTemplate 用于该查询的连接设置它。所以你将不得不做类似的事情

 jdbcTemplate.execute("<your sql query", new PreparedStatementCallback<Integer>(){

        @Override
        public  Integer doInPreparedStatement(PreparedStatement stmt) throws SQLException, DataAccessException 
        {
            Connection cxn = stmt.getConnection();
            // set autocommit for that cxn object to false
            cxn.setAutoCommit(false);
            // set parameters etc in the stmt
            ....
            ....
            cxn.commit();
            // restore autocommit to true for that cxn object. because if the same object is obtained from the CxnPool later, autocommit will be false
            cxn.setAutoCommit(true);
            return 0;

        }
    });

Hope this helps

希望这可以帮助

回答by Nagendra Varma

I just came across this and thought the solution would help someone even if it's too late.

我刚刚遇到这个问题，并认为即使为时已晚，该解决方案也会对某人有所帮助。

As Yosef said, the connection that you get by calling getJdbcTemplate().getDataSource().getConnection()method may or may not be the one used for the communication with database for your operation.

正如 Yosef 所说，您通过调用getJdbcTemplate().getDataSource().getConnection()方法获得的连接可能是也可能不是用于与数据库通信以进行操作的连接。

Instead, if your requirement is to just test your script, not to commit the data, you can have a Apache Commons DBCP datasource with auto commit set to fault. The bean definition is given below:

相反，如果您的要求只是测试您的脚本，而不是提交数据，您可以将自动提交设置为错误的 Apache Commons DBCP 数据源。bean定义如下：

/**
 * A datasource with auto commit set to false.
 */
@Bean
public DataSource dbcpDataSource() throws Exception {
    BasicDataSource ds = new BasicDataSource();
    ds.setUrl(url);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setDefaultAutoCommit(false);
    ds.setEnableAutoCommitOnReturn(false);
    return ds;
}

// Create either JdbcTemplate or NamedParameterJdbcTemplate as per your needs
@Bean
public NamedParameterJdbcTemplate dbcpNamedParameterJdbcTemplate() throws Exception {
    return new NamedParameterJdbcTemplate(dbcpDataSource());
}

And use this datasource for any such operations.

并将此数据源用于任何此类操作。

If you wish to commit your transactions, I suggest you to have one more bean of the datasource with auto commit set to truewhich is the default behavior.

如果您希望提交事务，我建议您再拥有一个数据源 bean，并将自动提交设置为true默认行为。

Hope it helps someone!

希望它可以帮助某人！

回答by moffeltje

I'm posting this because I was looking for it everywhere: I used configuration property in Spring boot to achieve setting the default autocommit mode with:

我发布这个是因为我到处都在寻找它：我在 Spring boot 中使用配置属性来实现设置默认自动提交模式：

spring.datasource.hikari.autocommit: false

回答by solocoding

after 5 years still a valid question, i resolved my issue in this way :

5 年后仍然是一个有效的问题，我以这种方式解决了我的问题：

1. set a connection with connection.setAutoCommit(false);
2. create a jbc template with that connection;
3. do your work and commit.

1. 使用 connection.setAutoCommit(false) 设置连接；
2. 使用该连接创建一个 jbc 模板；
3. 做你的工作并承诺。

    Connection connection = dataSource.getConnection();
    connection.setAutoCommit(false);
    JdbcTemplate jdbcTemplate = 
    new  JdbcTemplate(newSingleConnectionDataSource(connection, true));
    // ignore case in mapping result
    jdbcTemplate.setResultsMapCaseInsensitive(true);
    // do your stuff
    connection.commit();

回答by zhuguowei

In some case you could just add @Transactionalin the method, e.g. After some batch insert, execute commit at last.

在某些情况下，您可以只添加@Transactional方法，例如，在一些批量插入之后，最后执行提交。