Here are the details -
Using JDBCTemplate for query execution. Configuration of required classes in spring application context file is as follows -
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="mydataSource" />
</bean>
<bean id="jdbcTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="mydataSource" />
<qualifier value="jdbcTransactionManager"/>
</bean>
<tx:annotation-driven transaction-manager="jdbcTransactionManager"/>
<bean class="org.springframework.transaction.aspectj.AnnotationTransactionAspect" factory-method="aspectOf">
<property name="transactionManager" ref="datamigrationTransactionManager" />
</bean>
Please note in the same config file, HibernateTemplate is also configured with appropriate transaction manager declaration as follows -
<bean id="hibernateTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="dataSource" ref="mydataSource" />
<property name="sessionFactory" ref="refSessionFactory" />
<qualifier value="hibernateTransactionManager"/>
</bean>
<tx:annotation-driven transaction-manager="hibernateTransactionManager"/>
<bean class="org.springframework.transaction.aspectj.AnnotationTransactionAspect" factory-method="aspectOf">
<property name="transactionManager" ref="hibernateTransactionManager" />
</bean>
I am using @Transactional annotation for method in service class and have properly specified name of the jdbc transaction manager declared in spring config file where i am executing 3 statements in method annotated as follows -
@Transactional(value = "jdbcTransactionManager", propagation = Propagation.REQUIRED, rollbackFor = java.lang.Exception.class)
and statements executed in following sequence - (1) execute insert query for table 1 (2) execute insert query for table 2 (3) execute update query for table 3 method using the transactional annotation as follows -
@Transactional(value = "jdbcTransactionManager", propagation = Propagation.REQUIRED, rollbackFor = java.lang.Exception.class)
private boolean executeDBOperations(List<Integer> listIdData, List<String> listStringIds, int index) throws Exception
{
List<Integer> idsInBatch = jdbcDAO.getIdsInBatch(index);
jdbcDAO.fetchAndInsertMainTableRecords(listIdData);
jdbcDAO.fetchAndInsertAssociatedTableRecords(listStringIds);
jdbcDAO.updateAssociatedTable2Values(idsInBatch);
jdbcDAO.deleteSourceRecords(index);
return true;
}
When update query -> jdbcDAO.updateAssociatedTable2Values(idsInBatch); from above method fails, ideally records inserted during first 2 calls should be rolled back. But its not happening. Of course delete operation does not execute since update operation fails. Also please note exception related with data thrown for update query. It's just that cause of data issue that update does not happens and I have introduced that error to verify the commit and rollback behavior of the method.
Now as per implementation and the specifications, if any of the db operations/calls fails, rest of the operations performed in the same method should rolled back. But its not working. I am using Oracle DB.
I checked few questions on SO associated with transactions with JDBCTemplate and use of 2 transaction managers in the same spring application context, but i did not get concrete answers for the same.
