Multiple Database Access with Spring & Hibernate

[phayte]

We have a situation on the project I'm working on in which we need to use multiple schemas to contain the same domain model information. I've come up with the following idea for a solution and wanted to run it past the forums just in case there is something I've missed.

We'll be using a single Hibernate SessionFactory. I'll create a DataSource implementation that takes a value from a ThreadLocal (indicating which database the user wishes to use) set and cleaned up afterwards within a Servlet Filter from a HTTP Session attribute. I will then delegate to the data source for that database. Basically the SessionFactory will think it is dealing with a single data source but would be getting connections to different databases from different DataSources.

It would seem to me that everything "above" the SessionFactory (Spring helper classes, my DAOs, etc. etc.) wouldn't be aware of or affected by the changing connections. Anyone see a big, huge, gaping hole in this idea?

Thanks,
C

[irbouho]

The Spring classes as well as the DAOs should work great. I do, however, see two issues here:
1. The management of the Hibernate second level cache. If it happen that you have some classes that are mapped to tables in both schemas, you need to clear the second level cache on each request. This is bad for scalability.
2. Distributed transactions: I do not know if you need this in your case.

If you will be using Oracle databases, you may consider using DB-Links.
HTH

[phayte]

The Spring classes as well as the DAOs should work great. I do, however, see two issues here:
1. The management of the Hibernate second level cache. If it happen that you have some classes that are mapped to tables in both schemas, you need to clear the second level cache on each request. This is bad for scalability.
2. Distributed transactions: I do not know if you need this in your case.

If you will be using Oracle databases, you may consider using DB-Links.
HTH

Thanks for the feedback! I agree that Hibernate's second-level cache won't be able to be used at all in this case. But, since we have a mostly read-write domain model I'm not too sure we'd gain too much from it anyway.