Dynamic sqlSession with Spring

[Steve Hill]

Good morning!

I have a question which I am sure others have run into before. In our
environment we are using Spring, myBatis, and the myBatis generator. It
is all working very well!

We have multiple databases, one for each client; the schemas are the same.
At runtime I need a way to switch which sqlSession it is using however
have not yet found a way to do so. Has anyone run into this and have a
solution?

I currently have a mapper that looks like the following. This is
annotated as autowired into the code and then the user simply executes
laneCustomMapper.retrieveLanes( criteria );

public interface LaneCustomMapper {
public List<Lane> retrieveLane( LaneCO criteria );
}

The Spring configuration file has the following elements. As you can see
the sqlSessionFactory is hardwired; it is this that needs to be dynamic at
run time.

<bean id="T24.MyBatis.CFASqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean"
p:dataSource-ref="T24.JDBC.CFADataSource"
/>

<bean id="T24.MyBatis.ARCSqlSessionFactory"
class="org.mybatis.spring.SqlSessionFactoryBean"
p:dataSource-ref="T24.JDBC.ARCDataSource"
/>

<bean id="T24.MyBatis.MapperConfig.Product"
class="org.mybatis.spring.mapper.MapperScannerConfigurer"
p:basePackage="com.arm.t24.dao.product"
p:sqlSessionFactory-ref="T24.MyBatis.ARCSqlSessionFactory"
/>


Thanks!
Steve.

[Steve Hill]

Good afternoon!

After some trial and error we managed to find a solution. The answer was
in using Spring's AbstractRoutingDatasource. We point the
SqlSessionFactory's datasource to a class we created which returns a
different datasource based on parameters on the code. Follows are the
relevant snippets in case anyone needs to perform the same action.

(CustomerType is a simple enum with values C3 and C7)

public class SharedDataSource extends AbstractRoutingDataSource {

protected Object determineCurrentLookupKey() {

BigDecimal customerId = UserContext.getUserContext().getCustomerId();
switch( customerId.intValue() ) {
case 3:
return CustomerType.C3;
case 7:
return CustomerType.C7;
default:
throw new RuntimeException( "Datasource not defined for customer " +
customerId );
}
}

}

spring configuration for the datasource

<bean id="T24.JDBC.SharedDataSource"
class="com.armada.t24.core.db.SharedDataSource">
<property name="targetDataSources">
<map key-type="com.armada.t24.core.db.CustomerType">
<entry key="C3" value-ref="T24.JDBC.ARCDataSource"/>
<entry key="C7" value-ref="T24.JDBC.CFADataSource"/>
</map>
</property>
</bean>


Thanks!
Steve.

[Kurth, Felix]

Maybe:
org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy

may help you out?

[Kurth, Felix]

Accutally Im using BoneCP for that purpose:

/** Like a normal datasource except it supports an extra method: switchDataSource to slowly migrate to a new datasource.
*
* Simply call switchDataSource with your new BoneCP configuration: the existing pool will be shutdown gracefully (checkout out
* connections are unaffected) and a new one will take it's place. A typical use case would be to transparently instruct your application
* to use a new database without restarting the application.
*
* @author Wallace
*
*/
public class DynamicDataSourceProxy extends DelegatingDataSource{

-----Original Message-----
From: mybati...@googlegroups.com [mailto:mybati...@googlegroups.com] On Behalf Of Steve Hill
Sent: Donnerstag, 18. Oktober 2012 19:28
To: mybati...@googlegroups.com
Subject: Re: Dynamic sqlSession with Spring