JdbcTemplate rollback not working

[amitkhanna]

Hi All

I have written a simple (standalone) application using spring 3 to test how transaction management works with JdbcTemplate and mysql database. All source files are listed below. (All files are also attached as src.zip)

I have intentionally put a RuntimeException in addEmployee(Employee e1, Employee e2) method of EmployeeService.java so that the transaction could not complete successfully. But after execution of SpringMain.main() method employee e1 is added in the database. This means the first EmployeeDAO operation is not rolled back.

(I understand that there are other ways of transaction management too but I would like to do this particular application with @Transactional annotation)

Please help me understand why rollback is not working.

Thanks
Amit Khanna

Employee.java

package pkg.spring3;

public class Employee {
	private int employeeId;
	private String name;
	private Integer managerId;
	public int getEmployeeId() {
		return employeeId;
	}
	public void setEmployeeId(int employeeId) {
		this.employeeId = employeeId;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public void setManagerId(Integer managerId) {
		this.managerId = managerId;
	}
	public Integer getManagerId() {
		return managerId;
	}
	public Employee(int employeeId, String name) {
		this.employeeId = employeeId;
		this.name = name;
	}
	public Employee(int employeeId, String name, Integer managerId) {
		this.employeeId = employeeId;
		this.name = name;
		this.managerId=managerId;
	}
}

EmployeeDAO.java

package pkg.spring3;


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

public class EmployeeDAO extends JdbcDaoSupport{
	public int getEmployeeCount(){
		int count=getJdbcTemplate().queryForInt("select count(*) from employee");
		System.out.println(count);
		return count;
	}
	public void createEmployee(Employee e){
		getJdbcTemplate().update("insert into employee(emp_id, name, manager_id) values(?,?,?)", e.getEmployeeId(), e.getName(), e.getManagerId());
	}
}

EmployeeService.java

package pkg.spring3;

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

@Transactional(propagation=Propagation.SUPPORTS, readOnly=true)
public class EmployeeService {
	private EmployeeDAO employeeDAO;
	boolean failTransaction=true;
	public void setEmployeeDAO(EmployeeDAO employeeDAO) {
		this.employeeDAO = employeeDAO;
	}
	public EmployeeDAO getEmployeeDAO() {
		return employeeDAO;
	}
	@Transactional(propagation=Propagation.REQUIRED, readOnly=false, rollbackFor=RuntimeException.class)
	public void addEmployee(Employee e1, Employee e2){
		employeeDAO.createEmployee(e1);
		if(failTransaction==true) 
			throw new RuntimeException();
		else{
		employeeDAO.createEmployee(e2);
		}
	}
}

SpringMain.java

package pkg.spring3;

import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;

public class SpringMain {
	public static void main(String[] args) {
		XmlBeanFactory factory=new XmlBeanFactory(new ClassPathResource("spring-helloworld.xml"));
		EmployeeService serv=factory.getBean("employeeService", EmployeeService.class);
		Employee e1=new Employee(101, "amit");
		Employee e2=new Employee(102, "khanna");
		serv.addEmployee(e1, e2);
	}
}

spring-helloworld.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/beans	
	http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
	http://www.springframework.org/schema/tx 
	http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

	<bean id="employeeDAO" class="pkg.spring3.EmployeeDAO">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<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://localhost/mysql" />
		<property name="username" value="user" />
		<property name="password" value="pass" />
		<property name="defaultAutoCommit" value="false"/>
	</bean>
	<bean id="employeeService" class="pkg.spring3.EmployeeService">
		<property name="employeeDAO" ref="employeeDAO"/>
	</bean>
	<bean id="transactionManager" class="org.springframework.jdbc.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource" />
	</bean>
	<tx:annotation-driven/>
</beans>

[amitkhanna]

I tried it the same example with with postgre SQL. When I set defaultAutoCommit to false JdbcTemplate does not commit anything but also after the transaction is complete (without any exception) no changes are saved in database.

[ptalakanti]

try this, this will roll back on any exception

@Transactional(rollbackFor = java.lang.Exception.class)

[Marten Deinum]

Don't use a BeanFactory use an ApplicationContext (I suggest a read of chapter 3 of the reference guide it explains the differences).

[amitkhanna]

Thanks for your reply, It worked with ApplicationContext.