Switching the datasource

[klogger]

I have two datasources defined in my spring-config file:



<bean id="dataSourceProsoc" class="org.springframework.jdbc.datasource.DriverManagerD ataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/prosoc</value>
</property>
<property name="username">
<value>prosoc</value>
</property>
<property name="password">
<value>prosoc-</value>
</property>
</bean>

<!--
Datasource for prosoc_forum database
-->
<bean id="dataSourceProsocForum" class="org.springframework.jdbc.datasource.DriverManagerD ataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/prosoc_forum</value>
</property>
<property name="username">
<value>prosoc</value>
</property>
<property name="password">
<value>prosoc-</value>
</property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFac toryBean">
<property name="dataSource" ref="dataSourceProsoc"/>
<property name="mappingResources">
<list>
<value>uk/co/prodia/prosoc/user/User.hbm.xml</value>
<value>uk/co/prodia/prosoc/forum/mvnforum/MVNForumMember.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">true</prop>

<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>

<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransa ctionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>

<bean id="user" class="uk.co.prodia.prosoc.persistence.hibernate.DAOUserI mpl">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>


and in my class I would like to swap from the first dataSource to the second one. Currently I am trying:



DriverManagerDataSource driverManagerDataSource = (DriverManagerDataSource) Config.getSpringBeans().getBean("sessionFactory");



but that seems to return a class of type $Proxy4 and so throws a ClassCastException.

How can I get my sessionFactory so that I can call the setDataSource(.) method on it?

[dejanp]

Take a look at AbstractRoutingDataSource. The trick is to delegate the switch to a virtual datasource and bind the value that decides which physical datasource to use to a thread.

[klogger]

Is there any more detailed documentation on how to implement this?

For example, the IsolationLevelDataSourceRouter has some XML for configuring the datasource but how do I tell spring which data source to route too at run time?

I would like my Java code to ba as follows:

1) use the default datasource to write to database A;
2) Switch datasource within the same method and write some data to database B;
3) reset the datasource to use database A.

I am not bothered about transactional support (2 phase commit) between database A and database B at the moment as I am just trying to understand the basics.

[dejanp]

You can define a static ThreadLocal variable somewhere:


class Someclass {
public static ThreadLocal<String> currentDataSourceKey = new ThreadLocal<String>();
}

in your code you can then set it:


Someclass.currentDataSourceKey.set("ds1");
...
Someclass.currentDataSourceKey.set("ds2");

and your AbstractRoutingDataSource implementation can read it:


protected Object determineCurrentLookupKey() {
return Someclass.currentDataSourceKey.get();
}

[dejanp]

Anyway, why don't you use 2 sessionFactories?

[klogger]

I tried to use 2 session factories but end up with an error:



HibernateTransactionManager hibernateTransactionManager = (HibernateTransactionManager) Config.getSpringBeans().getBean("transactionManager");

hibernateTransactionManager.setSessionFactory((Ses sionFactory) Config.getSpringBeans().getBean("sessionFactoryProsocForum"));

DAOMVNForumMemberImpl daoMVNForumMemberImpl = (DAOMVNForumMemberImpl) Config.getSpringBeans().getBean("mvnForumMember");

MVNForumMember mvnForumMember = daoMVNForumMemberImpl.getUserByUsername(prosocUser .getUsername());


The error I get is:



org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here


I assume that my 2nd sessionFactory isn't being set correctly but am also not sure how to fix this.

[dejanp]

The error means you have no transaction defined for that sessionFactory.

[klogger]

Thank you very much indeed for your patience. I had indeed missed of the



@Transactional()


annotation on my getUserByUsername(String memberName).