AbstractRoutingDataSource is a very useful feature if we have a design which permits multiple databases based on certain criteria which may change for each user request. An example can be when we can use a particular database when the user belongs to a certain locale and switch to another locale if the user belongs to another locale.
AbstractRoutingDataSource
is an abstract data source implementation that routesgetConnection()
calls to one of the various target DataSources based on a lookup key. The latter is usually (but not necessarily) determined through some thread-bound transaction context.
Lets see an example to use AbstractRoutingDataSource
for selecting the appropriate data source for users based on their locale.
In the given example, we have configured only two locales “en” and “es”. We can configure as many data sources as we may like. Also, the locale is not only the only criteria for changing data sources. If you have some other requirements, feel free to add your own logic.
AbstractRoutingDataSource Example
1.1. Extend AbstractRoutingDataSource
This is required because here we will decide which datasource we want to go for in which condition.
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; } }
1.2. Configure the datasources
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}"/>
1.3. Setup the routing
Here we 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
.
We 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>
1.4. Spring Configuration
<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>
2. Demo
Before testing the application, we 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 data source 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 !!
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.
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.
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…..
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
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
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
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?
Do you have the Java Config for this?
As of now.. NO.
how this achieve in spring JPA ?
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
Authenticating logic is to fetch the user credentials from DB and compare with the user entered details.
I used Resteasy-spring.
Mostly people forget to correctly implement step 4. Verify your code steps.
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.
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.
Lokesh, It is good to attach the code but can you please also attached the lib?
All the jar files are included in sourcecode for this project. https://howtodoinjava.com/spring5/webmvc/spring5-mvc-hibernate5-example/
Nice work. Thank you. Was a great help.
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
Excellent topic coverage Lokesh……
Tnx for valuable info…
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.
Got what i was looking for , thanks Lokesh.