Use different db/hibernate sessionfactory dynamically depending on user

[dan_feist]

I have an application that is designed in such a way that 1 applicaion server uses multiple databases (each with the same schema). The database used is dependent on the user logged into the application. I need to implement hibernate and spring in this application.

As i see it different multiple DB's means multiple hibernate session factories. Thats fine, the problem is that the HibernateTempate, HibernateTransactionManager and my DAO's need to use different hibernate session factories depending on the user and so the session factory used needs to be determined in runtime rather than configures statically in spring config.

Possible solutions i have come up with are:
1) Inject a session factory proxy into classes that use the hibernate session factory instead of a session factory itself.
2) Extend classes that use hibernate session factory overriding getSessionFactory() methods.

Has anybody encountered this "special case" or got any suggestions about the bets way to resolve this?

[Colin Yates]

If you do a search for dynamic data source you will probably get lots of hits because this question keeps coming up.

Try searching, and go from there

[rgeorgiev]

You can look at :
http://forum.springframework.org/showthread.php?t=11538
or in
http://forum.springframework.org/showthread.php?t=12665

[dan_feist]

Thanks, I have look at the links and other posts in the forum....

- One solution seems to be to use "prototype" DAO's and extend the LocalSessionFactory to switch between a cached set of session factories depending on user attributes. The problem with this is that i want to use singletons!

- Another is delgating datasource which seems a good idea but not sure it will work. I will have multiple users using different datasources(and therefore session factories) at one time. I therefore need to have multiple cached session factories and decide which one to use in spring DAO's/TxManager at runtime.

- Or i can use a factory bean for SessionFactory which returns a SessionFactoryProxy which returns the correct real SessionFactory depening on user attributes. The chosen instance for a user/request would be stored in a threadlocal varibale. This seems it would work, and is simailair to one of my ideas as in first post. I am wary about injecting proxies into the TxManager as this is such a critical part of an application.

Any further ideas/comments?

[rgeorgiev]

I try something similar and it work fine for me.
I use org.springframework.aop.target.HotSwappableTargetS ource for SessionFactory and org.aopalliance.intercept.MethodInterceptor for swapping
SessionFactory base on method parameters. For this to work I also extend
org.springframework.orm.hibernate.HibernateTemplat e and override the
public SessionFactory getSessionFactory() ; method to return SessionFactory from the swapper.

[dan_feist]

Yeah thats one option ive been toying with.. that is the alternative to using a SessionFactory proxy. Of course with your approach you would need to also extend HibernateTransactionManager overriding the getSessionFactory() method.

Has anyone had any experience injecting a SessionFactory proxy into HibernateTemplate/HibernateTransactionManager which determines actual session factory in runtime. I think this solution is more elegant than extending HibernateTemplate and HibernateTransactionManager but i want to know if anybody has done this before.

thanks

[Costin Leau]

I think your scenario is seldom use - however search the forums. The class you need is not very complex - inject the session factories and based on the user return the appropriate one. I'm not patronizing here - I had to deal with some 'strange' requirements myself which I thought were complex and in the end they turned out to be quite straight forward

[dan_feist]

I agree that it shouldnt be to hard to solve i'm just trying to decide which is the best approach before getting into coding.

Which approach are you suggesting Costin? A sessionfactory proxy or overriding HibernateTemplate/HIbernateTransactionManager getSessionFactory() methods?

[Costin Leau]

Definitely a sessionFactory proxy (better named factory). It's the least intrusive solution IMO.

[djcampbell]

Dan, (if this thread is not dead)
Did you find a satisfactory solution? If so, would you mind sharing a few details? This is does seem to be a common issue. Thx.