HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Spring ORM / Spring 3.2.5 AbstractRoutingDataSource Example

Spring 3.2.5 AbstractRoutingDataSource Example

AbstractRoutingDataSource is very useful feature in spring framework if you do have a design which permits multiple databases based on certain criteria which may change for each user request. An example can be use of database. You can use a particular database when user belongs to certain locale and switch to another locale if user belongs to another locale.

By definition, AbstractRoutingDataSource is an abstract data source implementation that routes getConnection() calls to one of various target DataSources based on a lookup key. The latter is usually (but not necessarily) determined through some thread-bound transaction context.

In this post, I am giving an example of this feature. I am using routing data source for selecting the appropriate data source for users based on their locale. In this example, I have configured only two locales “en” and “es”. You can configure as many you like. Also, locale is not only the only criteria for changing data source. If you have some other requirement, feel free to add your own logic.

Also please note that I am using the source code developed for example of integration between hibernate 4 and Spring 3.2.5.


Download source code

Build the example application

Step 1) Extend AbstractRoutingDataSource class

This is required because here you will decide that which datasource you want to go for.

package com.howtodoinjava.controller;

import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class MyRoutingDataSource extends AbstractRoutingDataSource{
	@Override
	protected Object determineCurrentLookupKey() {
		String language = LocaleContextHolder.getLocale().getLanguage();
		System.out.println("Language obtained: "+ language);
		return language;
	}
}

Step 2) Configure Two data sources in jdbc.properties

This is not necessary and may not be required in your implementation.

jdbc.databaseurlOne=jdbc:mysql://127.0.0.1:3306/test
jdbc.databaseurlTwo=jdbc:mysql://127.0.0.1:3306/testTwo

Step 3) Configure multiple types of data sources in spring config file

Configure various data sources first, without worrying how they will be accessed.

<bean id="abstractDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
	destroy-method="close"
	p:driverClassName="${jdbc.driverClassName}"
	p:username="${jdbc.username}"
	p:password="${jdbc.password}" />

<bean id="concreteDataSourceOne"
	parent="abstractDataSource"
	p:url="${jdbc.databaseurlOne}"/>
 
 <bean id="concreteDataSourceTwo"
	parent="abstractDataSource"
	p:url="${jdbc.databaseurlTwo}"/>

Step 4) Setup the routing for data source

Here you will actually define key-value pairs [“targetDataSources”] for all configured data sources in above step. The value will be data source bean name, and key will be result came from determineCurrentLookupKey() method in MyRoutingDataSource.

You can also mention a default data source if nothing can be found for any user request. If will be default one and prevent from exceptions.

<bean id="dataSource" class="com.howtodoinjava.controller.MyRoutingDataSource">
   <property name="targetDataSources">
	  <map key-type="java.lang.String">
		 <entry key="en" value-ref="concreteDataSourceOne"/>
		 <entry key="es" value-ref="concreteDataSourceTwo"/>
	  </map>
   </property>
   <!-- <property name="defaultTargetDataSource" ref="concreteDataSourceOne"/> -->
</bean>

Step 5) Now use the data source

It’s easy. Right?

<bean id="sessionFactory"
	class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
	<property name="dataSource" ref="dataSource" /> <!-- HERE -->
	<property name="configLocation">
		<value>classpath:hibernate.cfg.xml</value>
	</property>
	<property name="hibernateProperties">
		<props>
			<prop key="hibernate.dialect">${jdbc.dialect}</prop>
			<prop key="hibernate.show_sql">true</prop>
		</props>
	</property>
</bean>

After all changes your spring configuration will look like this:

<?xml  version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:lang="http://www.springframework.org/schema/lang"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
        http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
 
    <context:annotation-config />
    <context:component-scan base-package="com.howtodoinjava.controller" />
 
    <bean id="jspViewResolver"
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="viewClass"
            value="org.springframework.web.servlet.view.JstlView" />
        <property name="prefix" value="/WEB-INF/view/" />
        <property name="suffix" value=".jsp" />
    </bean>
 
    <bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
        p:location="/WEB-INF/jdbc.properties" />
    
    <!-- Step 3 -->
    
	 <bean id="abstractDataSource" class="org.apache.commons.dbcp.BasicDataSource" 
        destroy-method="close"
        p:driverClassName="${jdbc.driverClassName}"
        p:username="${jdbc.username}"
        p:password="${jdbc.password}" />
 
    <bean id="concreteDataSourceOne"
       	parent="abstractDataSource"
        p:url="${jdbc.databaseurlOne}"/>
     
     <bean id="concreteDataSourceTwo"
       	parent="abstractDataSource"
        p:url="${jdbc.databaseurlTwo}"/>
     
      <!-- Step 4 -->
     
     <bean id="dataSource" class="com.howtodoinjava.controller.MyRoutingDataSource">
	   <property name="targetDataSources">
	      <map key-type="java.lang.String">
	         <entry key="en" value-ref="concreteDataSourceOne"/>
	         <entry key="es" value-ref="concreteDataSourceTwo"/>
	      </map>
	   </property>
	   <!-- <property name="defaultTargetDataSource" ref="concreteDataSourceOne"/> -->
	</bean>
	
     <bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource"
	      p:basenames="messages" />
	
	<!-- Declare the Interceptor -->
	<mvc:interceptors>    
	    <bean class="org.springframework.web.servlet.i18n.LocaleChangeInterceptor"
	          p:paramName="locale" />
	</mvc:interceptors>
	
	<!-- Declare the Resolver -->
	<bean id="localeResolver" class="org.springframework.web.servlet.i18n.SessionLocaleResolver" />
    
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation">
            <value>classpath:hibernate.cfg.xml</value>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">${jdbc.dialect}</prop>
                <prop key="hibernate.show_sql">true</prop>
            </props>
        </property>
    </bean>
    
    <bean id="employeeDAO" class="com.howtodoinjava.dao.EmployeeDaoImpl"></bean>
    <bean id="employeeManager" class="com.howtodoinjava.service.EmployeeManagerImpl"></bean>
 
    <tx:annotation-driven transaction-manager="transactionManager"/>
    <bean id="transactionManager"
        class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    
</beans>

Test the application

Before testing the application, I have created two database schemas and two similar tables in them. Both tables are exactly same except the data inside them. I have kept it different intentionally to demonstrate that two different requests are actually hitting different databases.

delimiter $$

CREATE DATABASE 'test' /*!40100 DEFAULT CHARACTER SET latin1 */$$

USE test$$

CREATE TABLE 'employee' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'FIRSTNAME' varchar(30) DEFAULT NULL,
  'LASTNAME' varchar(30) DEFAULT NULL,
  'TELEPHONE' varchar(15) DEFAULT NULL,
  'EMAIL' varchar(30) DEFAULT NULL,
  'CREATED' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('ID')
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1$$

INSERT INTO 'test'.'employee' ('ID','FIRSTNAME','LASTNAME','TELEPHONE','EMAIL','CREATED') 
VALUES (4,'Lokesh','Gupta','9811111111','howtodoinjava@gmail.com',CURRENT_TIMESTAMP);

CREATE DATABASE 'testtwo' /*!40100 DEFAULT CHARACTER SET latin1 */$$

USE testtwo$$

CREATE TABLE 'employee' (
  'ID' int(11) NOT NULL AUTO_INCREMENT,
  'FIRSTNAME' varchar(30) DEFAULT NULL,
  'LASTNAME' varchar(30) DEFAULT NULL,
  'TELEPHONE' varchar(15) DEFAULT NULL,
  'EMAIL' varchar(30) DEFAULT NULL,
  'CREATED' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ('ID')
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1$$

INSERT INTO 'testtwo'.'employee' ('ID','FIRSTNAME','LASTNAME','TELEPHONE','EMAIL','CREATED') 
VALUES (1,'Rakesh','Shukla','1234','email',CURRENT_TIMESTAMP);

1) Hit the URL: http://localhost:8080/Spring3.2.5Hibernate4.0.1Integration/?locale=en

AbstractRoutingDataSource_example_locale_en

2) Hit the URL: http://localhost:8080/Spring3.2.5Hibernate4.0.1Integration/?locale=es

AbstractRoutingDataSource_example_locale_es

3) Hit the URL: http://localhost:8080/Spring3.2.5Hibernate4.0.1Integration/?locale=fr

This will cause an exception because we have neither setup any datasource for this locale nor any default datasource [commented lines].

HTTP Status 500 - Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is java.lang.IllegalStateException: Cannot determine target DataSource for lookup key [fr]

type Exception report

message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is java.lang.IllegalStateException: Cannot determine target DataSource for lookup key [fr]

Download source code

Drop me a comment if something needs more explanation.

Happy Learning !!

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.
TwitterFacebookLinkedInRedditPocket

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Veerabadrachari R

    May 4, 2018

    I have also used this method. However in a single method, If I try to switch between datasources, the old (previous) datasource is considered and the results are from previous datasource connection.

    How to switch between datasources in a single method? Setting the tenant id in ThreadLocal is not switching the datasource.

  2. Deepthi

    March 16, 2018

    Hi Lokesh Gupta,

    I would like to pass some instance names dynamically in place of locale. Can you please help me to achieve this. I am new to spring.

  3. Sourav Basu

    January 5, 2018

    can we switch target data source from one schema to another in runtime? For example we have a schema called A . Inside schema A there is one table called user with column targetDbName with record as xyz. and in the same MySQL DB we have one another schema called xyz. So at runtime first it will check from the A schema and will pick the corresponding targetDbName from the user table and the application connection has to switch from schema A to xyz schema. Appreciate your help…..

  4. Ganesh Ampavalli

    June 6, 2016

    As per your code run multiple databases but i want to implement if one record save into one database at the same time that record replicated to another database how it possible please help me

  5. Hemant

    May 17, 2016

    In my case, it ran perfectly.
    But hbm2ddlschemaupdate was performed only on a single DB (DefaultTargetDatabase), whereas schema wasn’t updated for any other targetDB in AbstractRoutingDataSource

    https://stackoverflow.com/questions/37273880/spring-abstractroutingdatasource-hibernate-hbm2ddlschemaupdate-is-executed-on

  6. MaNik

    April 6, 2016

    HI Lokesh
    My Service class is autowiring dao(@Autowired ContentDAO) and this service has @Transactional method. The DAO has @Autowired sessionFactory. On start up, i get exception while creating bean of SessionFactory.
    Below is the application-context.xml. Please advice how to get around this error. Is it possible to use AbstractRoutingDataSource + annotation transaction and inject dao that in turn has injected sessionFactory

    classpath:batch/batch_multiDS.properties




    <!– –>

    classpath:batch/hibernate-cfg.xml

  7. Vaish

    October 5, 2015

    Hi Lokesh,
    Thanks for the post. My application has this functionality implemented, in which we have 2 specific and 1 default datasource configured in applicationContext. When the API is accessed using UI, depending on the user credentials, one out of specific databases is chosen.
    However, recently we have got a usecase in which we had to package this spring app in JAR which will be accessed by another application using service layer directly. This works well with default datasource. However we would like to change the database name of this “default” datasource. Here we are failing.
    In the Main class method which gets invoked from this third application, we are retrieving this defaultDataSource bean out of applicationContext, and changing its URL property, and refreshing the applicationContext; however these changes are not getting retained when actual service calls are made.
    We are using JPA repositories with Spring Roo .
    Any pointers please?

  8. James

    August 7, 2015

    Do you have the Java Config for this?

    • Lokesh Gupta

      August 8, 2015

      As of now.. NO.

  9. Arun

    January 3, 2015

    how this achieve in spring JPA ?

  10. Sudhakar

    November 26, 2014

    Hi, I have a rest easy interceptor for authenticating the request,I used similar setup for my datasources.
    The interceptor request not going through this routing strategy.
    Plz suggest

    • Sudhakar

      November 26, 2014

      Authenticating logic is to fetch the user credentials from DB and compare with the user entered details.
      I used Resteasy-spring.

    • Lokesh Gupta

      November 27, 2014

      Mostly people forget to correctly implement step 4. Verify your code steps.

  11. Aman Gupta

    November 26, 2014

    Nice Tutorial Lokesh ! However I have a little doubt that, is it possible to handle username and password dynamically too ? A simple use case is, a web app, where several admin login through UI with their username and password with particular database.

    • Lokesh Gupta

      November 27, 2014

      I am confused with your usecase. But based on what I understood, this is very much doable. You authenticate admins from a common DB and then use specific bean after checking what he is eligible for. It will require some filter/interceptor post login event.

  12. Ajay

    August 1, 2014

    Lokesh, It is good to attach the code but can you please also attached the lib?

    • Lokesh Gupta

      August 1, 2014

      All the jar files are included in sourcecode for this project. https://howtodoinjava.com/spring-orm/spring3-hibernate4-integration-example/

  13. Chris

    June 27, 2014

    Nice work. Thank you. Was a great help.

  14. salish

    May 30, 2014

    good work
    i have another issue,if datasource selection depends on login.
    for example
    an employee of one branch bank has a login id,with that loginid he loged in then that datasource need to connect ,depends on his loginid

    how to do the same

  15. ravi

    May 27, 2014

    Excellent topic coverage Lokesh……
    Tnx for valuable info…

  16. Azeem

    March 10, 2014

    how about if i need to decide about the lookup key at service layer instead of locale. like i want customers data to be diverted to different DBs based on their registered date. I dont want to use threadLocal.

  17. Prashant Rohilla

    December 31, 2013

    Got what i was looking for , thanks Lokesh.

Comments are closed on this article!

Search Tutorials

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Sealed Classes and Interfaces