Very simple Spring JDBC transaction no rollback (even the log said yes)

Question

I am new to Spring transactions. I am using Spring 3.2.2 and MySQL 5.5.20 (InnoDB). I can see in the log file that it is rolled back, but in the database the record is still updated to 9. What did I skip? Thanks.

Config.xml file:

<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"/> 

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 says it rollback:

 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 a spring-driven connection, you are opening a new connection instead. Change the code to the next 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 use 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"); }