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).