Multiple DataSources and SessionFactory configuration in Spring

Today, we will see ” how to connect 2 DBs from a Spring Application using Hibernate”?

Generally we use one DB, one SessionFactory and one TransactionManager with Spring within single Application Context, but sometimes we need to access 2 (or more) DBs within same application context. In such cases we need to add two Session Factories and Two TransactionManager, one for each DB.

Some of you might already be familiar with the solution but few of you may get benefited from my blog and example attached. I have used basic annotations like @Autowired and @Transactional.

In attached example I have given 2 links at home page, each will fetch data from separate databases.

Below are given steps and coding snippets for implementing multiple session factories and transaction managers.

– First of all define beans in context configuration file. Normally file is named as ApplicationContext.xml but in my case I have named it as “servlet-context.xml” under /WEB-INF/spring/appServlet folder. I added following bean configurations.

<bean id=”db1DataSource” destroy-method=”close”>………</bean>
<bean id=”db1SessionFactory”>………</bean>
<bean id=”db1TransactionManager”>………</bean>
<bean id=”db2DataSource” destroy-method=”close”>………</bean>
<bean id=”db2SessionFactory”>………</bean>
<bean id=”db2TransactionManager”>………</bean>

– In service level add name of the transaction manager which you want to use with current service method. E.g. below are 2 methods from MyServiceImpl class. One method is using “db1TransactionManager” and another is using “db2TransactionManager”.


@Override
@Transactional("db1TransactionManager")
public List getUserNameListFromSessionFactory1() {
return userNameDAO.getUserNameListFromSessionFactory1();
}

@Override
@Transactional("db2TransactionManager")
public List getOrderDetailsListFromSessionFactory2() {
return orderDetailsDAO.getOrderDetailsListFromSessionFactory2();
}

– Our TransactionManager knows on which SessionFactory a transaction will be executing but we need to fetch respective SessionFactory in DAO as well for getting the session (Connection).
We defined two DAO classes i.e. UserNameDAO and OrderDetailsDAO. In each DAO we are using the respective SessionFactory through annotation @Autowired and @Qualifier(“db1SessionFactory”). In BaseDAO we just give “db1SessionFactory” as default SessionFactory. You can give any SessionFactoy as default but don’t forget to override it by appropriate SessionFactory in the respective DAO classes.

Now you will ask that do we need to set SessionFactory in each DAO class, then the answer is no.  We can define another superclass of each DAO class but subclass of BaseDAO. E.g. DB1BaseDAO extends BaseDAO and DB2BaseDAO extends BaseDAO and each DAO class will extends either DB1BaseDAO or DB2BaseDAO as per the requirements.

Please go through the attached example project. It is a Maven project and I am using Spring MVC.
Before running attached project you need to do the following

  1. Create 2 databases named test1 and test2.
  2. Execute script named “Queries.sql” given in “Source” folder. It will create required tables and put some dummy data into them.
  3. I am using MS SQL server. Please change credentials in “\src\main\resources\spring-env\dev.properties” file and driver (if required) in case you want to use MySQL or any other DB.

Do let me know in case of any issues in running this project or any other concern or comments.

Thanks for reading.

Krishan

Example Project: HelloSpringMVC

Leave a Reply

Your email address will not be published. Required fields are marked *


eight + = 14

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>