Spring DAO transaction rollback issue

[bluefin]

Current i am facing problem in rollback the database changes.
I am using a Bean Managed Session Bean to call the DAO to execute the database change. In DAO impl, i am calling the JDBCTemplate to select, update, delete or query the result. I am using the JTA Transaction Manager to manage the transaction rollback at the session bean business method. When i try to roll back the transaction, it doesn't change at all.

Below is the partial of application context.xml file.

<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerD ataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@db:1521:db_name</value>
</property>
<property name="username">
<value>username</value>
</property>
<property name="password">
<value>password</value>
</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref bean="datasource" />
</constructor-arg>
</bean>

<bean id="txManager" class="org.springframework.transaction.jta.JtaTransaction Manager"/>

<bean id="AccessControlServiceBean" class="com.accesscontrol.ejb.AccessControlServiceBean" singleton="false">
<property name="txManager">
<ref bean="txManager" />
</property>
</bean>


Below is the partial code for the session bean in business method:

try{
txManager = new JtaTransactionManager();
UserTransaction utx = txManager.getUserTransaction();
if (adminUserServiceHome == null) {
getReferences();
}
AdminUserService adminUserService = adminUserServiceHome .create();
adminUserDto = adminUserService.getAdminUserByPk(loginId);
adminUserDto.setLoginStatus("YES");
adminUserDto.setSuccessLoginDate(new Date());
updateAdminUser(adminUserDto);
utx.rollback(); //for testing purpose i rollback the transaction
//utx.commit();
}catch(Exception ex){
ex.printStackTrace();
}

The default login status in database is "NO". Even if i update to "YES", it will rollback to "NO" when i rollback the user transaction right? Or is there anything wrong in my code?

[epitoman]

utx.begin();

[bluefin]

utx.begin(); is there.. i just forget to copy out.

[epitoman]

But your main problem is that you're using a datasource that's not managed by the tx manager. Try the DataSourceTransactionManager instead and make sure it knows about your datasource, then your test cased should work. Regarding your configuration I don't know if you're executing within a Jta env. or not, but consider declarative transactions rather than injecting your txmgr into the bean and manage them by yourself.

[bluefin]

It means i need to replace JTA transaction manager with this?

<!-- DataSourceTransactionManager-->
<bean id="transMgr" class="org.springframework.jdbc.datasource.DataSourceTran sactionManager">
<property name="dataSource">
<ref local="datasource" />
</property>
</bean>


Regarding your configuration I don't know if you're executing within a Jta env. or not, but consider declarative transactions rather than injecting your txmgr into the bean and manage them by yourself.
But i don understand this, what do you mean by declarative transaction? It means that need to use PlatformTransactionManager ?

[bluefin]

This is the latest changes of code for application context

<beans>
<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerD ataSource">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@DB:1521:DB</value>
</property>
<property name="username">
<value>DB</value>
</property>
<property name="password">
<value>DB</value>
</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref bean="datasource" />
</constructor-arg>
</bean>

<!-- DataSourceTransactionManager-->
<bean id="transMgr" class="org.springframework.jdbc.datasource.DataSourceTran sactionManager">
<property name="dataSource">
<ref bean="datasource" />
</property>
</bean>

<!-- TransactionInterceptor -->
<bean id="transactionInterceptor" class="org.springframework.transaction.interceptor.Transa ctionInterceptor">
<property name="transactionManager">
<ref bean="transMgr" />
</property>
<property name="transactionAttributeSource">
<value>org.springframework.prospring.ticket.service.BoxOf fice.get*=PROPAGATION_SUPPORTS ,readOnly org.springframework.prospring.ticket.service.BoxOf fice.allocate*= PROPAGATION_REQUIRED</value>
</property>
</bean>
<!-- BeanNameAutoProxyCreator -->
<bean id="autoProxyCreator" class="org.springframework.aop.framework.autoproxy.BeanNa meAutoProxyCreator">
<property name="interceptorNames">
<value>transactionInterceptor</value>
</property>
<property name="beanNames">
<list>
<idref local="PayeeSetupServiceBean" />
</list>
</property>
</bean>

<bean id="payeeSetupDao" class="my.com.infopro.ibadmin.app.dao.payeesetup.PayeeSet upDaoImpl" singleton="false">
<property name="datasource">
<ref bean="datasource" />
</property>
<property name="jdbcTemplate">
<ref bean="jdbcTemplate" />
</property>
</bean>

<bean id="PayeeSetupServiceBean" class="my.com.infopro.ibadmin.app.payeesetup.ejb.PayeeSet upServiceBean" singleton="false">
<property name="pyDao">
<ref bean="payeeSetupDao" />
</property>
<property name="transMgr">
<ref bean="transMgr" />
</property>
</bean>

this is partical of business method is the SLSB using BMP


public Long createPayee(PayeeSetupDto dto) throws SystemException {
Long value = null;
TransactionDefinition td = new DefaultTransactionDefinition();
TransactionStatus tranStatus = transMgr.getTransaction(td);
try {
value = pyDao.updatePayee(dto, PayeeSetupDaoImpl.INSERT_PAYEE);
transMgr.rollback(tranStatus);//testing roll back from here

//may call more different dao and business logic to manually roll back database transaction here
//here also may call another session bean method, and roll back of method there also hope to be roll back here as well.

//transMgr.commit(tranStatus);
} catch (SystemException se) {
transMgr.rollback(tranStatus);
throw se;
}

return value;
}

And this is the partial code for dao class

public Long updatePayee(PayeeSetupDto dto, String sql)
throws SystemException {
int results = 0;
try {
Object args[] = new Object[] { dto.getCompCode(),
dto.getOrgBrnCode(), dto.getCodeType(),
dto.getCurrencyCode(), (dto.isActive() ? "N" : "Y"),
dto.getPayeeCode() };
int[] types = new int[] { Types.VARCHAR, Types.VARCHAR,
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR };
results = jdbcTemplate.update(sql, args, types);
} catch (DataAccessException dae) {
throw dae;
}

return new Long(results);
}

Now I face the problem that transMgr return null pointer exception when calling. TransactionStatus tranStatus = transMgr.getTransaction(td);

Do i need to initialize transMgr in the code? Another question is this design able to meet my objective that i able to roll back the transaction that done in the DAO layer using JDBCTemplate or call to another session bean that call another DAO layer?

[bluefin]

The main concern for me now is the null pointer exception. Anyone know why it happen? For your information i using JBoss 4.0.4 GA release now.

[epitoman]

I'm a little bit confused you say SLSB and BMP? Do you use EJBs?

[bluefin]

Yup. i use stateless session bean coz this is the existing design.

[epitoman]

So your Spring pojo is a SLSB(implements or annotated), is that what you mean?
Secondly, in your business method createPayee(PayeeSetupDto) you always rollback the transaction but this couldn't be the real thing or is it?

Sorry if I missing something but I can't see that this is your actual environment as you don't work with a deployed SLSB and that you always rollback the transaction within the business logic.

[bluefin]

That is a implemented and deployed SLSB. In fact, in normal situation it should commit the transaction. I purposely putting a rollback as i try to test for rollback raise by the business logic error instead of system exception.

Below is the implementation of the my session bean and is deployed to the jboss.

**
* @ejb.bean name="PayeeSetupService" display-name="Name for PayeeSetupService"
* description="Description for PayeeSetupService"
* jndi-name="ejb/PayeeSetupService" type="Stateless"
* view-type="local" transaction-type="Bean"
*/
public class PayeeSetupServiceBean implements SessionBean {
private DataSourceTransactionManager transMgr;
private SessionContext context;

/**
* @ejb.interface-method view-type = "local"
*
* @throws SystemException
*/
public Long createPayee(PayeeSetupDto dto) throws SystemException {
Long value = null;
TransactionDefinition td = new DefaultTransactionDefinition();
TransactionStatus tranStatus = transMgr.getTransaction(td);
try {
value = pyDao.updatePayee(dto, PayeeSetupDaoImpl.INSERT_PAYEE);
transMgr.rollback(tranStatus);//testing roll back from here

//may call more different dao and business logic to manually roll back database transaction here
//here also may call another session bean method, and roll back of method there also hope to be roll back here as well.

//transMgr.commit(tranStatus);
} catch (SystemException se) {
transMgr.rollback(tranStatus);
throw se;
}

return value;
}
/

[epitoman]

I see, you would like to perform the test with a Spring container and your deployment environment is a EJB2 application.
You do understand that you're not testing against an EJB2 but only it's implementation as you create a simple POJO, it's not deployed you're just using it's content?
Secondly I would NEVER change my business logic in order to test a scenario.
Third, if you're using EJB2 you should really use declarative transaction, don't deal with transaction inside your business logic, trust the container.

Now, how should you setup this one, assuming you're using declarative tx. Well I guess many have opinion about this but I would do this.
1. Extract your business logic into a POJO
2. Use this POJO in your Spring configuration and write a test that look like this pseudo example


public void testRollback() {
begin transaction;
perform operation;
rollback transaction;
refresh from persistence;
fail if data has been altered;
}


3. Use your EJB2 as delegator to your POJOs (think you call them BusinessObjects), i.e. the EJB is just one entry point to your business logic.

If you must just EJB2 I think this is a good solution, because it allows you to run tests easily outside the EJB container, and also your application becomes easier to move between different platforms as long as you don't involve propriatary classes in your BOs (like TransactionManagers from Spring, EJB interfaces etc...)

[bluefin]

I still not able to rollback the transaction even delegate the business logic to POJO. I suspect is the JDBCTemplate set to commit the transaction once it is done regardless the rollback or any exception. Who can confirm with me about this?

[jbalint]

The problem here is that your data source in your JDBC DAO and the data source in the EJB are two different database connections. In order to do this you should use the container-managed datasource (which can be retrieved via JNDI and injected into your DAO).

Jess

[bluefin]

The problem here is that your data source in your JDBC DAO and the data source in the EJB are two different database connections. In order to do this you should use the container-managed datasource (which can be retrieved via JNDI and injected into your DAO).

Jess

Can give me some idea on implement container-managed datasource?

[jbalint]

Use the JndiObjectFactoryBean to retrieve the data source. Use the data source name that you are using for your BMP EJBs.

[bluefin]

Use the JndiObjectFactoryBean to retrieve the data source. Use the data source name that you are using for your BMP EJBs.

Yup. after i change the data source, then it works already.

[bluefin]

now i facing another problem on that. Coz currently the datasource refer to different db connection when calling. Dao class 1 own conn1 while Dao class 2 own conn2. When i run conn2 that time, i need value from conn1. So is it possible to let 2 dao share the same connection? it call by the same EJB and the dao using jdbcTemplate. The transaction control is done by ejb cmt using JTA transaction manager.

Below is the abstract business method sample call in ejb.


public void ejbMethod() throw EjbException{
adminUserDao.method1();
logDao.method2();//dao2 need the value from dao1
}


Below is the application context.xml:


<bean id="datasource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName">
<value>java:/JNDINAME</value>
</property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref bean="datasource" />
</constructor-arg>
</bean>

<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransaction Manager">
<property name="userTransactionName">
<value>UserTransaction</value>
</property>
</bean>

<!-- Service -->
<bean id="adminUserDao" class="com.AdminUserDaoImpl" singleton="false">
<property name="jdbcTemplate">
<ref bean="jdbcTemplate" />
</property>
</bean>
<bean id="logDao" class="com.LogDaoImpl" singleton="false">
<property name="jdbcTemplate">
<ref bean="jdbcTemplate" />
</property>
</bean>