How to set autocommit to false in spring jdbc template
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:
- Assuming your concrete datasource has a setter for
defaultAutoCommit
(for example, org.apache.commons.dbcp.BasicDataSource), cast theDataSource
to your concrete implementation. Of course this means that you can no longer change yourDataSource
in your Spring configuration, which defeats the purpose of dependency injection.
((BasicDataSource)getJdbcTemplate().getDataSource()).setDefaultAutoCommit(false);
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; }});
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'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