Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set autocommit to false in spring jdbc template

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

   <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.

  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

Thanks

like image 571
bad programmer Avatar asked Aug 08 '14 07:08

bad programmer


People also ask

How do you set Autocommit false in spring?

Explicit Transaction Management When Auto-Commit Is False We need to disable auto-commit mode when we want to handle transactions ourselves and group multiple SQL statements into one transaction. We do this by passing false to the connection's setAutoCommit method: connection. setAutoCommit(false);

How do you set Autocommit false?

setAutoCommit(true); //Setting the auto commit off con. setAutoCommit(false); Following JDBC program establishes a connection with the database and turns off the auto-commit.

What does set Autocommit false true?

setAutoCommit(false) will allow you to group multiple subsequent Statement s under the same transaction. This transaction will be committed when connection. commit() is invoked, as opposed to after each execute() call on individual Statement s (which happens if autocommit is enabled).

Does JDBC automatically commit a transaction?

By default, JDBC uses an operation mode called auto-commit. This means that every update to the database is immediately made permanent. Any situation where a logical unit of work requires more than one update to the database cannot be done safely in auto-commit mode.


4 Answers

The problem is that you are setting autocommit on a Connection, but JdbcTemplate doesn't remember that Connection; instead, it gets a new Connection for each operation, and that might or might not be the same Connection instance, depending on your DataSource implementation. Since defaultAutoCommit is not a property on DataSource, you have two options:

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

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

  1. Set the DataSource to 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;
      }
    });
    
like image 136
Yosef Weiner Avatar answered Oct 25 '22 09:10

Yosef Weiner


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();
    }
like image 39
Sujan Avatar answered Oct 25 '22 11:10

Sujan


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.datasource.hikari.auto-commit: false

Spring Boot 2.4.x Doc for Hikari

like image 40
moffeltje Avatar answered Oct 25 '22 11:10

moffeltje


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

  1. set a connection with connection.setAutoCommit(false);
  2. create a jbc template with that connection;
  3. do your work and commit.
    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();
like image 24
solocoding Avatar answered Oct 25 '22 09:10

solocoding