Using Transactions with UserCredentialsDataSourceAdapter

[suikodian]

The transactional method shown below makes three calls to a mapper
class. For some reason each call on the mapper results in the
getConnection method of the target datasource to be called. Isn't the
transaction manager supposed to cache the connection and reuse it?
MyBatis-Spring-1.1.1 and Spring-3.1 are used.

@Transactional
public String checkUser(String username){
User user = userMapper.getUser(username);
String address = userMapper.getAddress(username);
String telephone = userMapper.getTelephone(username);
return "done";
}

<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
</bean>

<bean id="userDataSource"
class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
<property name="targetDataSource" ref="dataSource" />
</bean>

<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="userDataSource" />
</bean>

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="userDataSource" />
</bean>

<tx:annotation-driven/>

[Dridi Boukelmoune]

Hi,

If your transaction manager is properly configured (-> connection
pool), getConnection should actually return 3 times the same
connection for a given thread (instead of opening 3 physical
connections).

Dridi
http://www.zenika.com/

--


Dridi Boukelmoune

Développeur/Formateur

GSM : +33 (0)6 17 91 14 23
dridi.bo...@zenika.com


51, rue le Peletier - 75009 Paris
Tel : +33 (0)1 45 26 19 15

[Eduardo Macarron]

Dridi is right but Spring should keep the connection in its tx context
so it looks like your tx behaviour is not working well.

Set the log to debug to see what happens.

[suikodian]

Thanks Dridi and Eduardo,

The @Transactional annotation was on a handler method of an MVC controller which prevented the transaction from being triggered. This could be related to the dynamic proxy mechanism used by Spring to add the transaction as an advice to the bean. The debug output confirmed that a new connection was returned and no traces on the transaction manager were mentioned

DEBUG: org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'sqlSessionFactory'
DEBUG: - ooo Using Connection [oracle.jdbc.driver.T2CConnection@15a3dac]
DEBUG: - ooo Using Connection [oracle.jdbc.driver.T2CConnection@1d3659b]
DEBUG: - ooo Using Connection [oracle.jdbc.driver.T2CConnection@7c6703]

So, I moved the method to a service class as follows

// service method

@Transactional
public String checkUser(String username){
        User user = userMapper.getUser(username);
        String address = userMapper.getAddress(username);

        String telephone = userMapper.getTelephone(username);
        return "done";
}

// MVC Controller hander method
@RequestMapping("checkuser")
public String checkUser(@RequestParam("user") String username){
    return userService.checkUser(username);
}

Now the transaction got triggered and the same database connection was returned

DEBUG: org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'transactionManager'
DEBUG:  - ooo Using Connection [oracle.jdbc.driver.T2CConnection@1bfa3e4]
DEBUG:  - ooo Using Connection [oracle.jdbc.driver.T2CConnection@1bfa3e4]
DEBUG:  - ooo Using Connection [oracle.jdbc.driver.T2CConnection@1bfa3e4]

No configuration changes were needed.

Thanks

[Rajesh Karnam]

I want to implement UserCredentialsDataSourceAdapter with myBatis. Can you give a sample how you did that?