Spring RoutingDataSource to Work with Multiple DataSources - II

In previous article, we have discussed about using Spring 'AbstractRoutingDataSource' to dynamically routing the database call to desired database (i.e. data source). This is very handy feature and useful in various scenarios. 

One important use case can be in multi-tenant application. In multi-tenant application, one of the database design consideration can be to use separate database for each tenant. It helps to keep the data of each tenant separate, and would be good for performance also. And, good design would require to use the same application layer code to work with all tenant and their specific database. In this scenario, use of 'AbstractRoutingDataSource' can help to achieve these design considerations. Extend the AbstractRoutingDataSource to create a custom 'MultiTenantRoutingDataSource'. Use a thread local application context state holder to maintain the tenant state for current flow. Use this thread local context in extended 'MultiTenantRoutingDataSource' to return the tenant specific data source. Again keep in consideration please, that Routing Data Source will be invoked by TransactionManager before starting the transaction only. Once transaction is started, data source can not be changed. 

Let us see the example of data source definition in Context for dynamically switching between OLTP and Reporting Database: 

<bean id="dataSourceOltp" name="oltpDataSource" class="..PooledDataSource">
<property name="targetDataSource">
<jee:jndi-lookup jndi-name="jdbc/DB" cache="true" />

<bean id="dataSourceReportDb" name="dataSourceReportDb" class="..PooledDataSource">
<property name="targetDataSource">
<jee:jndi-lookup jndi-name="jdbc/ReportDB" cache="true" />

<bean id="customDataSource" class="com.a.b.common.db.DBCustomRoutingDataSource ">
<property name="targetDataSources">
<map key-type="com.a.b.util.DataSourceConstants">
<entry key="OLTP_DB" value-ref="dataSourceOltp"/>
<entry key="REPORT_DB" value-ref="dataSourceReportDb"/>
<property name="defaultTargetDataSource" ref="dataSourceOltp"/>

<bean id="txAwareCustomDS" name="txAwareCustomDS" class="org.springframework.jdbc.datasource.Transac tionAwareDataSourceProxy">
<property name="targetDataSource" ref="customDataSource">

<bean id="CustomTxManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="txAwareCustomDS" />

In above context definition, we may define data sources for multiple tenants in place of OLTP or Reporting Database. To extend the design further for multiple tenants, definition can be made dynamic.

Important Note:

If you are using TransactionAwareDataSourceProxy with DataSourceTransactionManager, it should always be the outer most wrapper in the hierarchy. Keep data sources hierarchy as following:

TransactionAwareDataSourceProxy > Routing Data Source > Lazy Data Source > Pooled Data Source

Reason is, DataSourceTransactionManager will never work with TransactionAwareDataSourceProxy, rather it picks the wrapped data source to work upon. DataSourceTransactionManager has special handling for TransactionAwareDataSourceProxy to work with actual DataSource wrapped in it. So it is important to keep the TransactionAwareDataSourceProxy as outer most layer. Or otherwise, DataSourceTransactionManager will not be able to handle the transactions properly. Read more at following links:

Java Technology Enthusiasts - How to set Transaction Manager Programatically
Spring Forum - Problem in Managing Managing Transaction with AbstractRoutingDataSource

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