Rollback not working with @Transactional

[Manish Sridharan]

Hi,

I am testing spring based Annotation transaction mechanism for. Annontation based transaction is not getting rollback when i use my db connection file. And it works fine when I extend my DB connection class from org.springframework.jdbc.core.support.JdbcDaoSupport. I am sure I am missing something in my db connection class. Can any one point me in right direction.
I am using spring-framework-3.1.1.RELEASE jars, sqlitejdbc-v056.jar, jdk 1.6.0_31
The list of files i am using are mentioned below.

1. App-Config.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd" > <bean id="springExecutor" class="com.spring.bean.SpringExecutor"/> <bean id="dbConnection" class="com.spring.dao.DBConnection"> <property name="dataSource" ref="dataSource"/> </bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.sqlite.JDBC" /> <property name="url" value="jdbc:sqlite:D:\\SqlLite\\sqlitejdbc-v056" /> <property name="username" value="" /> <property name="password" value="" /> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <tx:annotation-driven transaction-manager="transactionManager"/> </beans>
2. SpringMain

public class SpringMain {

	/**
	 * @param args
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		ApplicationContext ac = new ClassPathXmlApplicationContext("App-Config.xml");
		SpringExecutor se = (SpringExecutor) ac.getBean("springExecutor");
		//DataSource dataSource = (DataSource)ac.getBean("dataSource");
		DBConnection dbcon = (DBConnection)ac.getBean("dbConnection");
		se.executeTransaction(dbcon);
	}

3. SpringExecutor.java

package com.spring.bean;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import javax.sql.DataSource;

import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.spring.dao.DBConnection;

public class SpringExecutor {

	private final static class Grinch {

		private static final Random RND = new Random();
		private static final String MESSAGE = "Its broken now";
		
		private Grinch(){}
		
		static void ruin(){
			if(RND.nextInt()%3==0){
				System.out.println(MESSAGE);
				throw new RuntimeException(MESSAGE);
			}
		}
	}
	
	
	@Transactional(
		    propagation = Propagation.REQUIRED,
		    isolation = Isolation.DEFAULT,
		    readOnly = false)
	public void executeTransaction(DBConnection dbcon) throws ClassNotFoundException, SQLException{
		dbcon.removeEmployeeData();
		dbcon.removeSalaryData();
		Grinch.ruin();
		System.out.println("INSERTING EMPLOYEE DATA");
		dbcon.insertEmployeeData(populateEmployeeBean());
		Grinch.ruin();
		System.out.println("INSERTING SALARY DATA");
		dbcon.insertSalaryData(populateSalaryBean());
		
		System.out.println("RETRIEVING SALARY DATA");
		List<SalaryBean> beans = dbcon.getSalaryData();
		System.out.println("Salary Data : "+beans.toString());
		
		System.out.println("RETRIEVING EMPLOYEE DATA");
		List<EmployeeBean> employeeBean = dbcon.getEmployeeData();
		System.out.println("Employee Data : "+employeeBean.toString());
		
	}
	
	private List<SalaryBean> populateSalaryBean(){
		String[] names = {"A","B","C", "D","E", "F", "G", "H"};
		Integer[] salaries = {100,200,300,400,500,600,700,800};
		List<SalaryBean> beans = new ArrayList<SalaryBean>();
		for(int cntr = 0 ; cntr < names.length ; cntr++){
			SalaryBean bean = new SalaryBean(names[cntr],salaries[cntr]);
			beans.add(bean);
		}
		return beans;
	}
	
	private List<EmployeeBean> populateEmployeeBean(){
		String[] names = {"A","B","C", "D","E", "F", "G", "H"};
		Integer[] ages = {10,20,30,40,50,60,70,80};
		List<EmployeeBean> beans = new ArrayList<EmployeeBean>();
		for(int cntr = 0 ; cntr < names.length ; cntr++){
			EmployeeBean bean = new EmployeeBean(names[cntr],ages[cntr]);
			beans.add(bean);
		}
		return beans;
	}



}

4. DBConnection.java

package com.spring.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.spring.bean.EmployeeBean;
import com.spring.bean.SalaryBean;

	
public class DBConnection /*extends JdbcDaoSupport*/{
		
		private Connection conn = null;
		private DataSource dataSource = null;
		
		public DataSource getDataSource() {
			return dataSource;
		}

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

		private Connection getConnection() throws ClassNotFoundException, SQLException{
			
			if(conn == null){
				Class.forName("org.sqlite.JDBC");
				conn = DriverManager.getConnection("jdbc:sqlite:D:\\SqlLite\\sqlitejdbc-v056");
				System.out.println("getting new db connection");
				conn = dataSource.getConnection();
			}
			return conn;
		}

		public void removeEmployeeData() throws ClassNotFoundException, SQLException{
	        String sql = "delete from employee;";
	        Connection conn = getConnection();
	        PreparedStatement ps = conn.prepareStatement(sql);
	        int result  = ps.executeUpdate();
	        System.out.println("result of delete Query :  "+result);
	        ps.close();
	        
		}

		public void insertEmployeeData(List<EmployeeBean> beans) throws ClassNotFoundException, SQLException{
			Connection conn = getConnection();
			String sql = "insert into employee values (?, ?);";
			PreparedStatement ps = conn.prepareStatement(sql);
			for(EmployeeBean bean : beans){
				ps.setString(1, bean.getName());
				ps.setInt(2, bean.getAge());
				ps.addBatch();
			}
			int[] results = ps.executeBatch();
			/*int cntr=0;
	        for(int result : results)
	        	System.out.println("result["+(cntr++) +"] = "+result);*/
			ps.close();
			
		}
		
		
		public List<EmployeeBean> getEmployeeData() throws ClassNotFoundException, SQLException{
			List<EmployeeBean> beans = new ArrayList<EmployeeBean>();
	        String sql = "select * from employee;";
	        Connection conn = getConnection();
	        PreparedStatement ps = conn.prepareStatement(sql);
	        ResultSet rs = ps.executeQuery();
	        while (rs.next()) {
	            EmployeeBean bean = new EmployeeBean();
	            bean.setName(rs.getString("name"));
	            bean.setAge(rs.getInt("age"));
	            beans.add(bean);
	        }
	        rs.close();
	        ps.close();
	        
	        System.out.println(beans.toString());
			return beans;
		}

		public void insertSalaryData(List<SalaryBean> beans) throws ClassNotFoundException, SQLException{
			Connection conn = getConnection();
	        String sql = "insert into salary values (?, ?);";
	        PreparedStatement ps = conn.prepareStatement(sql);
	        for(SalaryBean salary : beans){
	        	ps.setString(1, salary.getName());
	        	ps.setInt(2, salary.getSalary());
	        	ps.addBatch();
	        }
	        int[] results = ps.executeBatch();
	        /*int cntr=0;
	        for(int result : results)
	        	System.out.println("result["+(cntr++) +"] = "+result);*/
	        ps.close();
	        
		}


		public void removeSalaryData() throws ClassNotFoundException, SQLException{
	        String sql = "delete from salary;";
	        Connection conn = getConnection();
	        PreparedStatement ps = conn.prepareStatement(sql);
	        int result  = ps.executeUpdate();
	        System.out.println("result of delete Query :  "+result);
	        ps.close();
	        
		}
		
		public List<SalaryBean> getSalaryData() throws ClassNotFoundException, SQLException{
			List<SalaryBean> beans = new ArrayList<SalaryBean>();
 
	        String sql = "select * from salary;";
	        Connection conn = getConnection();
	        PreparedStatement ps = conn.prepareStatement(sql);
	        ResultSet rs = ps.executeQuery();
	        while (rs.next()) {
	            SalaryBean bean = new SalaryBean();
	            bean.setName(rs.getString("name"));
	            bean.setSalary(rs.getInt("amount"));
	            beans.add(bean);
	        }
	        rs.close();
	        ps.close();
	        
	        System.out.println(beans.toString());
			return beans;
		}

}

[Bill Gorder]

Ill be honest I did not look that closely at all the code you had posted. But I did notice that you are throwing SqlException. If you look at the API for @Trasactional the default behavior is to only roll back on runtime exception which SqlException is not. Typically when using spring you wrap your sql exceptions with the spring DataAccessException( this is a Runtime Exception) this is done automatically for you when you annotate your repositories with @Repository. I would consider using JdbcTemplate you can get rid of a lot of boiler plate code for managing the connection and closing statements, connectons etc. Otherwise if you want to continue on with what you have you will need to configure the rollback strategy on the @Transactional annotation to rollback on SqlException.

Thanks,

[Manish Sridharan]

Thanks Bill for reply.
I am throwing SQLExceptions only just to avoid using try catch for now. I do acknowledge your point that using jdbctemplate would give a cleaner representation. Infact when I used the jdbcdaosupport class (which is internally using jdbctemplate) , same code works fine i.e. transactions are getting rolled back in case of runtime exceptions.
I have gone through couple of books on spring, they mentioned I can use a simple java class for my db operation and transaction management will be done by spring by defining the transaction manager bean.
With the kind of error that I am getting, it seems spring transaction management works only if database classes are extended from spring APIs. This is hard to believe fact that spring txn works with spring database APIs only.

Also the reason I put it lot of code here so that if some one wants to test it locally, he can use all this code.

Still looking for answer i.e. why my code is not doing the rollback when not extended from jdbcdaosupport classs.

Thanks

[Bill Gorder]

You would not need the try catch if you used JDBC template and springs DataAccessException since they are Runtime exceptions they do not need to be caught and your @transactional would roll back correctly.

But back to your last statement:

Still looking for answer i.e. why my code is not doing the rollback when not extended from jdbcdaosupport classs

I had said in my previous post :

Otherwise if you want to continue on with what you have you will need to configure the rollback strategy on the @Transactional annotation to rollback on SqlException.

In other words make your @Transactional look like this

@Transactional(rollbackFor = SQLException.class)

[Manish Sridharan]

Hello Bill,

I am throwing the Runtime Exception randomly from Grinch.Ruin method and not the SQLEXception. I tried changes you have mentioned i.e

@Transactional(rollbackFor = RuntimeException.class) 

but again rollback is still not working. I have infact debugged spring classes like TransactionInterceptor , TransactionAspectSupport, DefaultTransactionStatus. it seems to me rollback behaviour is exactly same in both the cases i.e. with and without JdbcDaoSupport class.
I am enabling the logging for Spring Classes to compare both the scenarios's log. I'll let you know if something more come out of it.

Thanks

[Manish Sridharan]

Finally found the answer why rollback is not working. I missed setting autocommit to false when creating the connection. Rollback is working fine all along. its just that there was nothing to rollback since autocommit is set to true. I know right from the start there is something wrong in DBConnection.java but didn't know what it is. Now, I knew it.

To set the record straight, I agree we should use preexisting Spring APIs for making DB queries to avoid this kind of scenarios.

Method getConnection in DBConnection.java should now look like this :

private Connection getConnection() throws ClassNotFoundException, SQLException{  
              
            if(conn == null){  
                conn = dataSource.getConnection();  
		conn.setAutoCommit(false);
            }  
            return conn;  
        }  

Thanks

[Bill Gorder]

Ahh yes, I should have seen that I just kind of glanced at the code. Glad you got it figured out.