Multiple Data Sources + Hibernate + Spring

I am wondering if Spring's hibernate support can handle Multiple Data Sources. From what I saw in the source code, HibernateTemplate only supports one sessionfactory. Dynamically changing the DataSource in that SessionFactory variable may cause thread problem. I am wondering if anybody has good idea on supporting multiple DataSources in Spring/Hibernate environment.

My requirement for multiple data sources is pretty strightforward. We have a Transaction data model, which holds tens of millions of records (hundreds of giga bytes) and is growing. We have 3 DB servers holding those data. Same exact table DDL, same RDBMS. When a user login, based on his input, we need to grab data from the three DBs. One user may have data stored in DB1 and DB2 and DB3.

Idealy, I would think we should have three Sessionfactories for those three DBs, and use diferent SessonFactory for different data request. I am just thinking how to do it elegantly using Spring/Hibernate.

I remember I read some where int he forum Spring/Hibernate supports some kind of switch based on user credential ( I could be totally wrong). But I couldn't find it...

Any ideas?

You might find this thread useful.

Quote:

---

I remember I read some where int he forum Spring/Hibernate supports some kind of switch based on user credential ( I could be totally wrong). But I couldn't find it...

---

You can try searching on UserCredentialsDataSourceAdapter.

Thanks.

I am a little unclear about threading and singleton.

If I create seperate SessionFactory beans for each data source using Spring , that would be fine when multiple users access my web app at the same time - they will just use different SessionFactory instance (this might be an expensive approach).

However, if I only have one Sessionfactory bean (as a singlton created by Spring) and use a TargetDataSource, and swicth TargetDataSource based on different user. I don't think that will work - if different users login at the same time requiring ifferent data source. - Am I right?


TargetDataSource was suggested as a solution to multiple data sources by Rod in the thread you pointed to, I assume if you do that, you can not create SessionFactory as a Singlton. As a result, when you have 100 user logged in simultaneously, your app will use a lot of resource...