Accessing Multiple Databases with Spring and Hibernate

[elemur]

Hi,

I like Spring and Hibernate, and think they do a wonderful job. I have a task that I haven't attempted to do with these frameworks, where I need to access database-partitioned data. Essentially I have independent databases configured with the same schema, but different user data. I can programatically figure out which database I need to access, but I need to figure out how to work with Spring and Hiberante to work well in this model.

Essentially, a user logging in would get a session active on their destination database and the program logic would work totally consistently between different users. Presumably the hibernate caches would be unique..

So, does anybody have any pointers to this sort of configuration?

Thanks for your assistance!

[assaf]

I had a similar problem in a webapp (see topic), where I needed to determine which customer database to use based on the URL.

I ended up configuring the webapp to use a different DispatcherServlet for each customer, and creating, for each servlet, a separate servletname-servlet.xml configuration file which sets a PropertyPlaceholderConfigurer to an appropriate jdbc properties file for that customer, and imports all of the other beans from a single shared config file for all servlets.

This meant practically doing away with applicationContext.xml, since almost all beans depend on the sessionFactory which depends on the dataSource which itself depends on this servlet-specific propertyConfigurer.

There might be a more elegant solution though!
Best regards,
Assaf

[Ben Alex]

We just use a single database with partioning via a "subscriberId" column in central tables (ie tables that are referred to by foreign keys of other tables). We have an ActiveSubscriberHolder ThreadLocal which is managed by a filter. That filter looks at the HttpSession and/or URL to set the effective subscriberId. Service layer beans (ie read*, get*, find*, create*) methods refer to the ActiveSubscriberHolder to obtain the active subscriberId and they set the appropriate properties on the bean/query before sending it to the DAO layer. We could AOP that if we wanted, but it's pretty straight forward as-is.