MyBatis + AbstractRoutingDataSource

[_mass_nerder]

Mark Fischer's example [URL="http://blog.springsource.com/2007/01/23/
dynamic-datasource-routing/"]http://blog.springsource.com/2007/01/23/
dynamic-datasource-routing/[/URL] works great for the most part, but I
have a quirky problem.

I have 3 data sources. When routing to the 1st and 3rd data source,
everything works fine, but fails with the 2nd data source every time.
Any help would be greatly appreciated!

I also posted this on the spring forum:
http://forum.springsource.org/showthread.php?p=349331#post349331

Implementation Code:
[CODE]
/**
* Load an existing Po entity
*
*/
@Transactional
public Po getPoByPoNumber(String poNumber) {

// Get Po by Po number from the default CMC Reporting data source
Po po = poDAO.getPoByPoNumber(poNumber);

// if the Po is null, then get Po by Po number from CLS Reporting
data source
// THIS NEVER RETURNS A RESULT SET?!??!?!
if(po == null){
DataSourceContextHolder.setTargetDataSource(DataSourceEnum.CLS);
po = poDAO.getPoByPoNumber(poNumber);
}

// if the Po is null, then get Po by Po number from MTL Reporting
data source
// This returns a result set
if(po == null){
DataSourceContextHolder.setTargetDataSource(DataSourceEnum.MTL);
po = poDAO.getPoByPoNumber(poNumber);
}

// if the Po is STILL null, then instantiate Po
//and set the CreatedUsername to "No Matching PO"
if(po == null){
po = new Po();
po.setPoCreatedUsername("No Matching P0");
}

return po;
}
[/CODE]

ContextHolder:
[CODE]
public class DataSourceContextHolder {
private static final ThreadLocal<DataSourceEnum> contextHolder =
new ThreadLocal<DataSourceEnum>();

public static void setTargetDataSource(DataSourceEnum
targetDataSource){
Assert.notNull(targetDataSource, "Target data source cannot be
null");
contextHolder.set(targetDataSource);
}

public static DataSourceEnum getTargetDataSource(){
return (DataSourceEnum) contextHolder.get();
}

public static void resetDefaultDataSource(){
contextHolder.remove();
}
}
[/CODE]

Enumerator:
[CODE]
public enum DataSourceEnum {
CMC,
CLS,
MTL
}
[/CODE]

RoutingDataSource:
[CODE]
public class RoutingDataSource extends AbstractRoutingDataSource{

@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getTargetDataSource();
}
}
[/CODE]

Application Context:
[HTML]
<!-- enable component scanning (beware that this does not enable
mapper scanning!) -->
<context:component-scan base-package="com.test" />

<!-- enable autowire -->
<context:annotation-config />

<!-- enable transaction demarcation with annotations -->
<tx:annotation-driven />

<bean id="parentDataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
abstract="true">
<property name="driverClassName"
value="net.sourceforge.jtds.jdbc.Driver"/>
<property name="username" value="***"/>
<property name="password" value="***"/>
</bean>

<bean id="cmcDataSource" parent="parentDataSource">
<property name="url" value="jdbc:jtds:sqlserver://localhost:3306/
Database1"/>
</bean>

<bean id="clsDataSource" parent="parentDataSource">
<property name="url" value="jdbc:jtds:sqlserver://localhost:3306/
Database2"/>
</bean>

<bean id="mtlDataSource" parent="parentDataSource">
<property name="url" value="jdbc:jtds:sqlserver://localhost:3306/
Database3"/>
</bean>

<bean id="dataSource"
class="com.test.util.datasource.RoutingDataSource">
<property name="defaultTargetDataSource" ref="cmcDataSource"/>
<property name="targetDataSources">
<map key-type="com.test.util.datasource.DataSourceEnum">
<entry key="CLS" value-ref="clsDataSource"/>
<entry key="MTL" value-ref="mtlDataSource"/>
</map>
</property>
</bean>

<!-- transaction manager, use JtaTransactionManager for global tx -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>

<!-- define the SqlSessionFactory, notice that configLocation is not
needed when you use MapperFactoryBean -->
<bean id="sqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:mybatis-config.xml" /
>
</bean>

<!-- scan for mappers and let them be autowired -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.test.finance.dao" />
</bean>

<!-- Logging Aspect Bean -->
<bean id="loggingInterceptor"
class="com.test.util.logging.LoggingMethodInterceptor"/>

<aop:config>
<aop:pointcut expression="execution(* com.test..*.* (..))"
id="loggingPointcut"/>
<aop:advisor advice-ref="loggingInterceptor" pointcut-
ref="loggingPointcut"/>
</aop:config>
[/HTML]

MyBatis Config:
[HTML]
<configuration>
<settings>
<!-- changes from the defaults -->
<setting name="lazyLoadingEnabled" value="false" />
</settings>
<typeAliases>
<typeAlias alias="Po" type="com.test.finance.domain.Po" />
</typeAliases>
</configuration>
[/HTML]

[Eduardo]

Maybe you missed on line here?

<map key-type="com.test.util.datasource.DataSourceEnum">
<entry key="CLS" value-ref="clsDataSource"/>
<entry key="MTL" value-ref="mtlDataSource"/>
</map>

[kyle adams]

The clsDataSource is actually the one that's giving me the problems. I might just have to rip mybatis out and see if I can replicate the issue with straight JDBC. 

-- 
Thanks, 
Kyle Adams 
ECM Architect | ECM Developer 
Crowley Maritime Corporation 
9487 REGENCY SQUARE BLVD. • JACKSONVILLE • FLORIDA • 32225 
www.crowley.com 
Cell: 757.235.141

0 

[Eduardo]

Ok Kyle. 2nd try. This time I have read your config more carefully.

MyBatis uses a SqlSession cache. So If you try to execute the same
sentence + params on the same SqlSession it will give you the first
result. And that seems to be what is happening.

Behind the scenes mybatis-spring is creating a SqlSession and
attaching it to Spring transaction. Try removing the @Transactional
attribute and you will see that your code works because there will be
no transaction and a new SqlSession will be created for each
statement.

[kyle adams]

Great suggestion! I was actually thinking that I should remove those annotations.