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? <br/>
Am i missing anything ? <br/>
or any alternative to set autocommit in a specific java method by spring

Thanks

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][1]), 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);`

2. 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;
}
});

[1]:

[To see links please register here]

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();
}

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.

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:

/**
* 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 `true` which is the default behavior.

Hope it helps someone!

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

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

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.
```Java
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();
```

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](

[To see links please register here]

)

I needed it to do some unit testing
In fact Spring already provides the _SingleConnectionDataSource_ implementation with the _setAutoCommit_ method

// import org.springframework.jdbc.datasource.SingleConnectionDataSource;
SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
dataSourceRX71.setAutoCommit(false);
dataSourceRX71.setDriverClassName("xxx");
dataSourceRX71.setUrl("xxx");
dataSourceRX71.setUsername("xxx");
dataSourceRX71.setPassword("xxx");