I am trying to implement Spring @transactional with jdbc templates from a service level 2 call in DAOImpl and using simplejdbctemplate to insert, and I see in the logs that Spring creates a new transaction according to my maintenance method, and my first insert succeeds and the second insert fails. and although she says that she goes back the same connection, the first insertion never rolls back from my Mysql DB (I use innodb engine).
Here is my service method.
@Service @TransactionConfiguration(transactionManager="txManager") public class NewWizardService{ ApplicationContext ctx = new ClassPathXmlApplicationContext("dataSourcesConfig.xml"); UserDAO userDAO = (UserDAO)ctx.getBean("userDAO"); @Transactional(rollbackFor=Throwable.class, readOnly=false) public void createNewFirm() { userDAO.insert1(); userDAO.insert2(); }
}
Here is my Spring datasource and transaction manager configuration.
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="dbcpDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName"><value>${jdbc.driverClassName}</value></property> <property name="url"><value>${jdbc.url}</value></property> <property name="username"><value>${jdbc.username}</value></property> <property name="password"><value>${jdbc.password}</value></property> </bean> <tx:annotation-driven transaction-manager="txManager" proxy-target-class="true"/>
Here is my log trace.
2012-03-28 16:56:31,460 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Creating new transaction with name [com.CAIS.wizardService.NewWizardServiceImpl.createNewFirm]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; '',-java.lang.RuntimeException 2012-03-28 16:56:31,654 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Acquired Connection [ org.apache.commons.dbcp.PoolableConnection@7d627b8b ] for JDBC transaction 2012-03-28 16:56:31,660 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Switching JDBC Connection [ org.apache.commons.dbcp.PoolableConnection@7d627b8b ] to manual commit 2012-03-28 16:56:31,663 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - Executing prepared SQL update 2012-03-28 16:56:31,663 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - Executing prepared SQL statement [insert into client (fullName, createDate, createUser) values (?, ?, ?)] 2012-03-28 16:56:31,663 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Fetching JDBC Connection from DataSource 2012-03-28 16:56:31,664 DEBUG [org.springframework.jdbc.datasource.DriverManagerDataSource] - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/cais] 2012-03-28 16:56:31,816 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Registering transaction synchronization for JDBC Connection 2012-03-28 16:56:31,833 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - SQL update affected 1 rows 2012-03-28 16:56:31,840 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - SQLWarning ignored: SQL state '01000', error code '1265', message [Data truncated for column 'createDate' at row 1] 2012-03-28 16:56:31,842 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - Executing prepared SQL update 2012-03-28 16:56:31,842 DEBUG [org.springframework.jdbc.core.JdbcTemplate] - Executing prepared SQL statement [insert into client (fullName, createDate, createUser) values (?, ?, ?)] 2012-03-28 16:56:31,918 DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory] - Finished creating instance of bean 'Sybase' 2012-03-28 16:56:31,918 INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] 2012-03-28 16:56:31,918 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - Looking up default SQLErrorCodes for DataSource [ org.springframework.jdbc.datasource.DriverManagerDataSource@6cf8 4b0a] 2012-03-28 16:56:31,920 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - Database product name cached for DataSource [ org.springframework.jdbc.datasource.DriverManagerDataSource@6cf8 4b0a]: name is 'MySQL' 2012-03-28 16:56:31,920 DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory] - SQL error codes for 'MySQL' found 2012-03-28 16:56:31,920 DEBUG [org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator] - Unable to translate SQLException with Error code '1406', will now try the fallback translator 2012-03-28 16:56:31,920 DEBUG [org.springframework.jdbc.support.SQLStateSQLExceptionTranslator] - Extracted SQL state class '22' from value '22001' 2012-03-28 16:56:31,921 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource 2012-03-28 16:56:31,923 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Initiating transaction rollback 2012-03-28 16:56:31,923 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Rolling back JDBC transaction on Connection [ org.apache.commons.dbcp.PoolableConnection@7d627b8b ] 2012-03-28 16:56:31,934 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] - Releasing JDBC Connection [ org.apache.commons.dbcp.PoolableConnection@7d627b8b ] after transaction 2012-03-28 16:56:31,934 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
Thanks in advance.