Spring RoutingDataSource to Work with Multiple DataSources

Finally back to blogging. It was a long journey to get ready, pick the pen (i.e. computer) and start writing again. Writing is a matter of habit.

Today we are going to discuss about using multiple data sources with Spring Transaction Management. Scenario is,

• Application is having lot of legacy code
• Application needs to work with more than one databases
• There are some legacy methods in application which are supposed to work with any one of the database depending upon use case. Same method could be invoked with different data source context for other transaction.
• Application is using Spring Transaction Management
• Requirement of Spring Transaction Management is to declare data sources and associated transaction manager with application context (1 to 1 relation)

What would be the initial design considerations. 

• Define all required data sources in application context
• Define corresponding transaction managers too
• Use any of the Persistent Template to implement the DAOs.
• Define desired transaction behavior on service methods

We are done with basic infrastructure code. In above steps, missing part is how to tell application code about which data source to be used from multiple data sources defined in context. It can be done by specifying the specific transaction manager (related to desired data source) with Service transactional attributes. Transaction manager can be defined either at class level or at method level. Or a good approach could be to use Transactional advice and define the transaction manager in context configuration with a pattern to identify the methods. 

A general workflow would be; whenever any service method is called using Spring Framework, Spring will see if there is any transaction proxy available for that method. If yes, transaction proxy will get invoked. It will work with defined transaction manager and will start a new or use existing transaction (based on transaction attributes). It will also try to attach the connection with the current transaction (thread local based implementation). Connection will be retrieved from the data source which is associated with the transaction manager (defined in context). This connection will be linked with current transaction as synchronized resource. Please note, that connection may be attached lazily in case of 'LazyConnectionDataSourceProxy'. In this way, Spring enables the scenario to use multiple data sources in application based on specified transaction manager.  All the methods invoked during the service call, or from nested methods will go to the database which is associated with current transaction manager (and the data source).

However, a tricky scenario could be, if we have legacy code and have some methods which needs to be worked with one database in one call and with other database later depending upon the use case. How to manage such scenario where database will be decided on the fly based on the 'use case' or application context. One solution could be to break the methods or services and create two (or more) different services specific to a database, hence design separate services based on their concerns. All the requests targeted for a specified database will go to the related service. This service is aware about database and hence can use the right transaction manager and data source. This solution can solve the issue, and would result in better code also. But, it could be time consuming (even if desired and good for quality) to create wrapper services for whole legacy code at once. It will need a lot of development and testing efforts to test whole service layer again. At time, Project schedule may not allow you to do such huge changes.  

Solution for above problem could be in 'Dynamic Data Source' routing. In above scenario, requirement is to work with multiple data sources, and need a transaction manager which can choose any one of these data sources, based on application state and can manage the current transaction in that context. This concept is supported by Spring framework by providing a class named as 'AbstractRoutingDataSource', where one wrapper data source is defined having map of many other data sources against some user defined keys. Actual data source, used to pick the connection, can be decided at runtime based on the user requirement. User can override the 'determineCurrentLookupKey' method to return the user context specific key identifying the desired data source for current scenario. Any custom logic can be implemented to decide the right data source for current use context. To implement it: 

• Define different data sources in context configuration
• Extend the 'AbstractRoutingDataSource' and define your custom implementation to return the lookup key depending upon custom logic. Override 'determineCurrentLookupKey' method.
• Define your custom Data Source having Map of different required data sources against various lookup keys
• Define a transaction manager using above routing data source
• Use this transaction manager with legacy methods which need to work on different data sources

Now, whenever Spring proxy needs to start the transaction, transaction manager will interact with custom routing data source to pick the connection. Routing data source will route the call to specific data source returned for that scenario based on look up key. Hence right data source, i.e. connection will be picked for current transaction. It will enable the scenario to use different data sources with same transaction manager. Same methods will be invoked with different data sources based on user context.

Here one important point to consider is, 'determineCurrentLookupKey' method will be invoked only when Transaction Manager will start the transaction. Hence, data source can be decided only at the beginning of transaction and can not be changed in between (which is also not desirable). So if you are changing the context value to impact the current lookup key in-between the transaction, it won't take effect. 

Refer to following link for example code: https://spring.io/blog/2007/01/23/dynamic-datasource-routing 

Hope it will help. Please share comments for addition. You may contribute by:

• Posting your comments which will add value to the article contents
• Posting the article link on Social Media using 'Social Media Bookmark' bar
• Consider joining us at 'Java Technology Enthusiasts' linkedin group to participate in discussions
• Connecting with 'VedantaTree' on Facebook

People who read this post also read :