Multiple Session Factories under Spring/Hibernate

Question

I have been given a requirement where I need to support multiple databases in the same instance, to support multi-tenancy. Each DB has an identical schema. The user logs into a specific database by choosing from a list, and all subsequent calls will go to that DB until they log out.

I want to hot swap the session factory inside a single HibernateDaoTemplate based on a parameter supplied by the client.

I can find lots of stuff on hot-swapping data sources (and all the transaction issues associated with that) but I want to hot swap session factories - retaining all the caching for each.

What's the easiest way to do this? Configure a HotSwappableTarget for the DaoTemplate? Can anyone point me to samples on how to do this?

Answer 1

If all the databases are identical, then I can suggest using a single SessionFactory and providing your own implementations for the DataSource and Cache that are actually "tenant-aware". (Implementing these is fairly trivial: just maintain a map of tenant id -> real cache/real datasource and then delegate all calls to the appropriate one). Configure the single SessionFactory to use your tenant-aware Cache and DataSource. A ThreadLocal can be used to make the tenant ID of the current request available to any code that needs to know about it.

I have used this approach before successfully to support multi-tenancy.

Answer 2

Where I used to work we did this via ThreadLocal following this guide. We just used one SessionFactory and swapped it's datasource based on a session variable the user could change while logged in. I don't remember the exact details, but if you're interested I can dig up some more information on our implementation.

That being said though, the guys at my former workplace are now moving away from this approach and towards a sharded database. Definitely a more elegant solution that I'd recommend you take a look at.