dynamically change Spring data source

Question

I have a Spring application, i want to change the data source dynamically,ie. when input a DS URL, the Spring beans and all dependency will get updated automatically.I know this is somewhat strange, but anyway i want to achieve that. My Spring configuration as following:

<bean id="majorDataSource" class="org.postgresql.ds.PGSimpleDataSource">
    <property name="serverName" value="${jdbc.serverName}" />
    <property name="portNumber" value="${jdbc.portNumber}" />
    <property name="user" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="databaseName" value="${jdbc.databaseName}" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="majorDataSource"/>
</bean>

<tx:annotation-driven transaction-manager="transactionManager"/>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="majorDataSource"/>
    <property name="configLocation" value="classpath:sqlmap-config.xml"/>
</bean>

The questions are:

1. The JDBC URL is stored in properties, which could be changed runtime.

2. Once the URL is changed, i need to re-create the data source and maybe the dependent objects. I could not figure out how to do it elegantly in Spring?

I have known that Spring did could dynamically route data source based on one key, but the data source URL is predefined in Spring and will not change runtime. It is not my case.

Vita

Answer 1

You can use spring's AbstractRoutingDataSource by extending it and overriding the method determineCurrentLookupKey() that should return the key referencing the datasource's spring bean to be used.

Take a look at this blog article on spring source's blog which will show you an example of how to use that feature.

Basically to answer your questions, what you will need to do is to define the two datasources as different spring bean in your XML config. There is no need to create one dynamically, spring will load both, and use one or the other dynamically depending on your criteria in the determineCurrentLookupKey() method.

This would lead to something like:

XML config

<!-- first data source -->
<bean id="majorDataSource" class="org.postgresql.ds.PGSimpleDataSource">
    <property name="serverName" value="${jdbc.major.serverName}" />
    <property name="portNumber" value="${jdbc.major.portNumber}" />
    <property name="user" value="${jdbc.major.username}" />
    <property name="password" value="${jdbc.major.password}" />
    <property name="databaseName" value="${jdbc.major.databaseName}" />
</bean>
<!-- second data source -->
<bean id="minorDataSource" class="org.postgresql.ds.PGSimpleDataSource">
    <property name="serverName" value="${jdbc.minor.serverName}" />
    <property name="portNumber" value="${jdbc.minor.portNumber}" />
    <property name="user" value="${jdbc.minor.username}" />
    <property name="password" value="${jdbc.minor.password}" />
    <property name="databaseName" value="${jdbc.minor.databaseName}" />
</bean>
<!-- facade data source -->
<bean id="dataSource" class="blog.datasource.CustomerRoutingDataSource">
   <property name="targetDataSources">
      <map>
         <entry key="MINOR" value-ref="minorDataSource"/>
         <entry key="MAJOR" value-ref="majorDataSource"/>
      </map>
   </property>
   <property name="defaultTargetDataSource" ref="majorDataSource"/>
</bean>
<!-- wiring up -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:sqlmap-config.xml"/>
</bean>

Java

public class MyRoutingDataSource extends AbstractRoutingDataSource {
   @Override
   protected Object determineCurrentLookupKey() {
      // get the current url
      HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getRequest();
      if (request.getRequestURL().toString().endsWith("/minor"))
          return "MINOR";
      else
          return "MAJOR";
   }
}

Brunobruns

i use major&minor DB for failover. When major DB failed, i will promote the minor DB as major.Then i need to setup a third DB for standby,its URL will be dynamic which could not be predefined here. Anyway maybe i could force to use static IP for the major&minor DB,thus i need not to change the URL dynamically. – Vita

You may use multi tenancy strategy instead. for example docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html – Auguste

Answer 2

I am not sure if this is the correct Way of doing this but what you can do is something like this.

<bean id="majorDataSource" class="org.postgresql.ds.PGSimpleDataSource">
    <property name="serverName" value="dummydata" />
    <property name="portNumber" value="dummydata" />
    <property name="user" value="dummydata" />
    <property name="password" value="dummydata" />
    <property name="databaseName" value="dummydata" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="majorDataSource"/>
</bean>

<tx:annotation-driven transaction-manager="transactionManager"/>

and in Java Class

public class TestTransaction {

   @Autowired
   private DataSourceTransactionManager manager;

   private PlatformTransactionManager transactionManager;

   public testExecution(DataSource ds) {
       manager.setDataSource(ds);
       transactionManager = manager;
       TransactionDefinition def = new DefaultTransactionDefinition();
       TransactionStatus status = transactionManager.getTransaction(def);
       try {
           jdbcTemplate.update();
           transactionManager.commit(status);
       } catch (Exception ex) {
           transactionManager.rollback(status);
       }
   }
}

Please suggest if this Approach could Work as i am Still new to Spring

Aldrin