JDBC DAO bean in other bundle

[Diego Bravo]

Hello,

I'm using a trivial DAO with SimpleJdbcTemplate that throws an exception to force a rollback:

package com.xxx.dao;

import org.springframework.transaction.annotation.Transactional;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

@Transactional
public class MyDAOImpl extends SimpleJdbcDaoSupport implements MyDAO {
	public void putData() {
		String sql = "INSERT INTO user (login,pass) "
	        + "VALUES (?, ?)";
		getJdbcTemplate().update(sql, "some" , "data" );
                throw new RuntimeException("Test rollback");
	}
}

This works ok inside a bundle that defines the data source and tx manager:

    <bean id="dataSource" destroy-method="close"
          class="org.apache.commons.dbcp.BasicDataSource">
...mysql parameters...    </bean>
    
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  		<property name="dataSource" ref="dataSource"/></bean>
	
	<tx:annotation-driven transaction-manager="txManager"/>
	
	<bean id="dao" class="com.xxxx.dao.MyDAOImpl">
		<property name="dataSource" ref="dataSource"/>
	</bean>

The problem occurs when I put the bean inside another bundle, from which I try to reference the datasource and the transaction manager in this way:

	<osgi:service ref="dataSource" interface="javax.sql.DataSource"/>
	<osgi:service ref="txManager" interface="org.springframework.transaction.PlatformTransactionManager"/>
        <tx:annotation-driven transaction-manager="txManager"/>
...

The code inserts the row, but the rollback doesn't undo it. Checking the MySQL log, the rollback indeed runs, but is applied to another connection.

My guess is that the spring-osgi-proxy nature of the datasource/txmanager beans is confilcting the way the aop proxy associates the transaction to the current thread with the connection used by the JdbcTemplate.

The log shows a couple of messages that (I think) point in that direction.

0:26:13.049] Thread-108                   <> Acquired Connection [jdbc:mysql://localhost/xx, UserName=xx@localhost, MySQL-AB JDBC Driver] for JDBC transaction 
[2010-04-16 20:26:13.050] Thread-108                   <> Switching JDBC Connection [jdbc:mysql://localhost/xx, UserName=xx@localhost, MySQL-AB JDBC Driver] to manual commit 
[2010-04-16 20:26:13.051] Thread-108                   <> Fetching JDBC Connection from DataSource

Can you give me some ideas to follow? I'm not sure if I can publish the datasource/txmanager at all; in that case I'd like to know alternatives for externalizing the DAOs.

Regards,

BTW, using springsource-dm-server-2.0.1.RELEASE.

[Diego Bravo]

It works now by sharing a JdbcTemplate bean inside the same BasicDataSource's bundle (actually a JdbcOperations interface.) I think this code works because the JdbcTemplate is dealing with the actual Datasource bean and not the proxy in the (remote) dao. It could be related to a datasource used as a key in a map inside TransactionSynchronizationManager.java.

Anyway, I don't know if this is the expected behavior or the best JDBC pattern.

[Glyn Normington]

Glad you made progress. I'm afraid the questions you were asking are more appropriate in a Spring framework forum where the Spring specialists hang out.

[Diego Bravo]

Glad you made progress. I'm afraid the questions you were asking are more appropriate in a Spring framework forum where the Spring specialists hang out.

Thank you Glyn; I'll post to the Data Access forum. Hope their people don't consider this a DM specific issue...

[Glyn Normington]

I hope so too! The issue seemed to be related to data access combined with Spring DM and nothing too specific to dm Server.