Using Spring, JPA with Hibernate to access multipl

Question

I have a requirement where i need to configure a Spring based application to work with two databases. We have two databases, one that we use to keep the live data and the other database is used as a datawarehouse and contains archived data (which has the exact structure as the Live db).

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

If i need to configure such a setup, do i still need to configure to datasources and would the search code have to use the first datasource to check the live database and if not found it will run another query using the archive database?

The above is probably doable but i am wondering whether there is a better way of doing this. For example, is it possible for the application to work on a single datasource even though behind the scenes it actually works with two databases?

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

Any examples showing how this is configured using Spring and Jboss would be very useful.

Thanks

Answer 1

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

1. For these DAOs, demarcate the wrapping Service-layer either with distinct packages, class names, or method names
2. Indicate to Spring that the Service-layer method calls should run in their own transactional contexts by annotating with @Transactional(propogation=Propogation.REQUIRES_NEW)
3. Create an Aspect (using AspectJ annotation @Aspect) to fire around the service-layer method calls (using @Around) to set the ThreadLocal value before the method call, and to unset it afterwards
4. In the @Controller, simply call the Service-layer methods. The Aspect will take care of setting the values to indicate which datasource to use, and the AbstractRoutingDataSource will use that datasource in the context of each transaction.