Very simple Spring transactions of JDBC not roll back (even log said yes)

Question

I'm new to Spring transactions. I use Spring 3.2.2 and MySQL 5.5.20(InnoDB). I can see in the log file that it did roll back, but in the database, the record still being updated to 9. What did I miss? Thanks.

The config.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/javatest?useUnicode=true&amp;characterEncoding=UTF-8" />
        <property name="username" value="root" />
        <property name="password" value="xxx" />
</bean>

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

<bean id="hello" class="com.xol.oss.HelloService">
    <property name="dataSource" ref="dataSource"/>
</bean>

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

The Java code:

public void setDataSource(BasicDataSource dataSource) {
    this.dataSource = dataSource;
}

@Transactional
public void getData()  {
    Connection con=null;
    try {
        con = dataSource.getConnection();
        Statement stat = con.createStatement();
        stat.executeUpdate("update testdata set foo=9 where id=1");
        throw new RuntimeException("an Exception for test");
    } catch (SQLException e) {
        e.printStackTrace();  
    } finally{
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();  
        }
    }
}

The log said it did roll back:

15:15:36,936 DEBUG DataSourceTransactionManager:366 - Creating new transaction with name [com.xol.oss.HelloService.getData]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; ''
15:15:37,525 DEBUG DataSourceTransactionManager:205 - Acquired Connection [jdbc:mysql://127.0.0.1:3306/javatest?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver] for JDBC transaction
15:15:37,535 DEBUG DataSourceTransactionManager:222 - Switching JDBC Connection [jdbc:mysql://127.0.0.1:3306/javatest?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver] to manual commit
15:15:37,581 DEBUG DataSourceTransactionManager:844 - Initiating transaction rollback
15:15:37,582 DEBUG DataSourceTransactionManager:280 - Rolling back JDBC transaction on Connection [jdbc:mysql://127.0.0.1:3306/javatest?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver]
15:15:37,583 DEBUG DataSourceTransactionManager:323 - Releasing JDBC Connection [jdbc:mysql://127.0.0.1:3306/javatest?useUnicode=true&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver] after transaction
15:15:37,583 DEBUG DataSourceUtils:327 - Returning JDBC Connection to DataSource
Exception in thread "main" java.lang.RuntimeException: an RuntimeException for test
    at com.xol.oss.HelloService.getData(HelloService.java:31)
    at com.xol.oss.HelloService$$FastClassByCGLIB$$3d7d84e8.invoke(<generated>)

Answer 1

The problem is that you are not using the connection managed by spring, instead you are opening a new connection. Change the code to the following and try.

import org.springframework.jdbc.datasource.DataSourceUtils;    

@Transactional
public void getData()  {
    Connection con=null;
    try {
       // Get the connection associated with the transaction
       con = DataSourceUtils.getConnection(dataSource);
       Statement stat = con.createStatement();
       stat.executeUpdate("update testdata set foo=9 where id=1");
       throw new RuntimeException("an Exception for test");
     } catch (SQLException e) {
       e.printStackTrace();  
     } finally{
        DataSourceUtils.releaseConnection(dataSource, con);
     }
  }

If you are writing new code you should be using the JdbcTemplate instead of raw jdbc.

class HelloService {
    JdbcTemplate jdbcTemplate;
    public setDataSource(DataSource dataSource) {
         jdbcTemplate = new JDBCTemplate(dataSource);
    }

    @Transactional
    public void getData()  {
       jdbcTemplate.update(update testdata set foo=9 where id=1);
       throw new RuntimeException("an Exception for test");
    }