How can I config to turn off autocommit in Spring + JDBC?

Question

I am using Spring with JDBC and found that it is autocommit.

How can I config to turn it off in spring-servlet.xml?

This is my current configuration:

<bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
    p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.databaseurl}" p:username="${jdbc.username}"
    p:password="${jdbc.password}" />

<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
</bean>

Solution 1

It seems that my configuration missed this line:

<tx:annotation-driven transaction-manager="txManager"/>

Then, in my service classes, I use @Transactional annotation. For example

@Service
class CompanyServiceImpl implements CompanyService{
    @Autowired
    private CompanyDAO companyDAO;

    @Transactional
    public void addCompany(Company company) {
            companyDAO.addCompany(company); // in here, there is JDBC sql insert
            companyDAO.addCompany_fail(company); // just for test
    }
}

If there is a exception happening in the addCompany_fail(), the first addCompany() one will also be rollbacked.

I followed this document to understand idea how transaction controlled in Spring. http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/transaction.html

I followed this document to understand how to code with JDBC in Spring. http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html

I also read this (Free) http://www.infoq.com/news/2009/04/java-transaction-models-strategy. It is really good one. And I feel the same with the writer that most people do not understand (or care) about transaction.

PS: Seem that many people misunderstand that using such Hibernate/Spring framework is only for avoid complexity of JDBC and Transaction Control. Many people think like "JDBC and Transaction are so complex, just use Hibernate and forget about those two". Many examples on the internet about Spring+Hibernate or Spring+JDBC seemingly not care about transaction at all. I feel that this is a bad joke. Transaction is too serious for just letting something handle it without truly understanding.

Hibernate and Spring is so powerful and so complex. Then, as someone said, "Great power comes with responsibilities".

UPDATE: 2013-08-17: There are good example about transaction here http://www.byteslounge.com/tutorials/spring-transaction-propagation-tutorial. However, this is not explain that if you want to use REQUIRES_NEW, why you need to create another class (otherwise you will get this problem Spring Transaction propagation REQUIRED, REQUIRES_NEW , which it seems REQUIRES_NEW does not really create a new transaction)

Update: 2018-01-01: I have created a full example with Spring Boot 1.5.8.RELEASE here https://www.surasint.com/spring-boot-database-transaction-jdbi/ and some basic experiment examples here https://www.surasint.com/spring-boot-connection-transaction/

Solution 2

Try defaultAutoCommit property. Code would look like this:

<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
p:driverClassName="${jdbc.driverClassName}"
p:url="${jdbc.databaseurl}" p:username="${jdbc.username}"
p:password="${jdbc.password}"
p:defaultAutoCommit="false" />

Look at javadoc: http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html#defaultAutoCommit

Solution 3

You can't, simply run your code within a transaction, Spring will automatically disable auto-commit for you. The easiest (at least to set-up) way to run a piece of code in a transaction in Spring is to use TransactionTemplate:

TransactionTemplate template = new TransactionTemplate(txManager);

template.execute(new TransactionCallback<Object>() {
  public Object doInTransaction(TransactionStatus transactionStatus) {
    //ALL YOUR CODE ARE BELONG TO... SINGLE TRANSACTION
  }
}

Comments

[Surasin Tancharoen]

Thanks for the answer. To support bigger picture, seem Spring creates many more complexity for simple jdbc. :)

[Tomasz Nurkiewicz]

@SurasinTancharoen: well, not really. I said easiest to set-up, but not to use. With @Transactional or AOP you can run several lines of code within a single transaction with minimal changes to code.

[Surasin Tancharoen]

I have just read this ibm.com/developerworks/java/library/j-ts2/index.html Can "Programmatic transactions with Spring" be an alternative?

[Surasin Tancharoen]

Also, I found about org.springframework.transaction.interceptor.TransactionProxy‌​FactoryBean as be explained in here nerdnotes.wordpress.com/2007/03/30/… . Can it be another alternative?

[Raedwald]

And this worked because the Spring transaction manager switches off auto-commit and does its own commits?