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.
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
2) Hit the URL: http://localhost:8080/Spring3.2.5Hibernate4.0.1Integration/?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]
Drop me a comment if something needs more explanation.
Happy Learning !!
Veerabadrachari R
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.
Deepthi
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.
Sourav Basu
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…..
Ganesh Ampavalli
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
Hemant
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
MaNik
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
Vaish
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?
James
Do you have the Java Config for this?
Lokesh Gupta
As of now.. NO.
Arun
how this achieve in spring JPA ?
Sudhakar
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
Authenticating logic is to fetch the user credentials from DB and compare with the user entered details.
I used Resteasy-spring.
Lokesh Gupta
Mostly people forget to correctly implement step 4. Verify your code steps.
Aman Gupta
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
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.
Ajay
Lokesh, It is good to attach the code but can you please also attached the lib?
Lokesh Gupta
All the jar files are included in sourcecode for this project. https://howtodoinjava.com/spring-orm/spring3-hibernate4-integration-example/
Chris
Nice work. Thank you. Was a great help.
salish
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
ravi
Excellent topic coverage Lokesh……
Tnx for valuable info…
Azeem
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.
Prashant Rohilla
Got what i was looking for , thanks Lokesh.