Multiple DB Instances

Multiple DB Instances

Introduction

This blog deals with creating Multiple DB Instance configurations in Spring. It covers the configuration of Multiple SessionFactory, HibernateTemplate, DataSource by doing the necessary annotations. It also explains various approaches to achieve Multiple DB Instance.

Use Case

This use case describes the different ways to configure multiple data sources, in a single Spring applicationcontext.xml. It also states how to annotate the required values. In this use case we have configured Multiple Session Factory to connect different data sources.

Solution

Approach 1: Abstract Routing DataSource for multiple databases

Abstract Routing DataSource is an abstract data source implementation that routes get Connection () calls to one of various target DataSources based on a lookup key.

The AbstractRoutingDatasource is used as a model to create another datasource that can “switch” between multiple DataSources by setting a key value. So, we need to use vanilla AbstractRoutingDatasource to create, and select the right datasource based on database name but this requires defining 100 DataSources in this context, which would be unproductive.

So we have customized the AbstractRoutingDatasource to create the “selected” DataSource, if it did not find any in HashMaps. This works great if we do not use annotated transactions.

Challenge:

The annotated transaction loads DataSource before DAO could set it correct. The result of this process would be either it loads “default” datasource ignoring datasource that is selected later or blows up if there is no “default” datasource. Removing @Transactional will make everything work smoothly. We were not able to delay the retrieving of @Transactional until we call jdbcTemplate.query().

Approach 2: Setting Connection String from Meta Data Table

Read the datasource connection credentials from properties file and get the Connection String URL, port number, user name and password from MetaData table. Iterate the Connection String ResultSet and set connection properties to the datasource for the subsequent schema. Map this datasource to DAO context xml file.

Challenge:

We attempted to load the DataSource before the DAO could get a chance to set the correct DataSource so we did not get the details.

Approach 3: Configure Multiple DB Instance in the context xml file

To configure Multiple SessionFactory in the applicationcontext xml file. Read the various database connection URLs from the properties file and use subsequent datasource mapped hibernate templates to get connected to many Database Instances.

Challenge:

When we use more than one SessionFactory, we will get an error saying “expected single matching bean but found 2”. This is due to the conflict issue in the SessionFactory.

Approach 4 – Configure Multiple DB Instance using @Qualifier to avoid @Autowire conflict

The approach configures multiple session factories and connects it to different DB instances.
It was successful, after annotating @Autowired SessionFactory to specifically use @Qualifier (“sessionfactory_name”) to overcome the sessionfactory conflict issue.

Properties File:
Properties file contains the connection details.
Add new database connection credentials.

 DAO context xml: 

  • Add new datasource details referring the key values from the properties file.
  • Add new Session Factory and map the new DataSource object to it
  • List DAOs related to the DataSource connection
     
  • Map HibernateTemplate for SessionFactory
    Now we are set to use new Database connection details

    Note: Check if any SessionFactory is annotated as @Autowired.
    If so, we have to give @Qualifier (“sessionFactory”) to specifically separate it from collaborating, with the newly created session factory (testSessionFactor). Transaction Manager should not be configured twice.

Challenges Overcome:

We resolved the SessionFactory Conflict issue, after using @Qualifier for @Autowired SessionFactory.

  • To maintain scope as Singleton in session factor
  • Configure scope as singleton in session factory using the code given below.
  •  To control the DB Connection retry option
    The above command restricts the retry of DB connection option. Even when one of the DB connections is not available, or DB URL is changed, this command will help to run the application without any problem.

Conclusion

If we add the new database connection credentials in the properties file and configure the DAO Context xml file by adding new DataSource, SessionFactory, HibernateTemplate and map those as explained in the document, we can obtain the connection and retrieve the data from Multiple DB Instances. We can configure more than 1 DB Instances by adding multiple SessionFactory, DataSource, HibernateTemplate, however there should be only one transaction through ApplicationContext xml file.

 Reference

5087 Views 2 Views Today