Using Spring, JPA with Hibernate to Access Multiple Databases / Data Sources Configured in Jboss

Question

I have a requirement when I need to configure a Spring-based application to work with two databases. We have two databases that we use to store real-time data, and the other database is used as a data warehouse and contains archive data (which have an exact structure like Live db).

To keep it simple, suppose there is a search query for a product. What the application should do is search for product details in the Live database, and if it is not found, it will check the archive database.

If I need to configure this setting, I still need to tune to data sources and will the search code use the first data source to check the current database, and if it is not found, another query will be launched using the archive database?

The above is probably doable, but I'm wondering if there is a better way to do this. For example, is it possible to run an application on a single data source, although behind the scenes it actually works with two databases?

The application is based on Spring, JPA / Hibernate, SOAP and Mysql and Jboss 7 database as an application server.

Any examples showing how this is configured using Spring and Jboss will be very helpful.

thanks

Answer 1

Spring has exactly what you want - AbstractRoutingDataSource . See this blog post for how to use it. In your case, you need to switch the data source during one request, so you will need to have 2 transactions, switching the data source between them, changing the data source indicator to ThreadLocal :

• For these DAOs, demarcate the Service-layer wrapper either using separate packages, class names, or method names.
• Tell Spring that service level method calls should be made in their transactional contexts, annotating with @Transactional(propogation=Propogation.REQUIRES_NEW)
• Create an aspect (using the AspectJ @Aspect annotation) to trigger service level method calls (using @Around ) to set the ThreadLocal value before calling the method and subsequently disable it
• In @Controller just call the service level methods. Aspect will take care of setting the values ​​to indicate which data source to use, and AbstractRoutingDataSource will use this data source in the context of each transaction.