Spring + Hibernate + JPA + multiple databases

Question

I have a Spring + Hibernate + JPA app. The user, when logging in, can choose from a list of DB's to connect to (these are the requirements). All the DB's have the same schema, so the same entities and DAO's will be used.

Right now I have one EntityManager (been working with one database for the moment) that is injected in the DAO like this:

@PersistenceContext
private EntityManager entityManager;

Is there any way to have the DAO receive the entityManager automatically (managed by Spring) based on a parameter/property received from the service layer ? (The web layer sends a kind of context, and the name/code/id of the chosen database will be in there).

Or do I have to manage this myself (creating all the entityManagers, putting them in a map, telling the DAO which one of them to use for each call) ?

I did some research before asking this, but the results were inconclusive - most of the questions dealt with a model spread over 2 or more DB's and transactions spanning multiple DB's, but this is not the case for me.

In my case, once the user is connected, it's just as if he connected to an application that only has one entity manager, the one for the database he selected. There's no switching between DB's mid-session or any other such stuff.

Thank you.

Answer 1

In spring you can build the EntityManagerFactory dynamically with an Annotation configuration (AnnotationWebConfiguration) using something like this:

@Configuration
public class MyAppConfig{
   public LocalContainerEntityManagerFactoryBean getEmf(){
       LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean ();
       Datasource ds = new .... ; // HERE!! you can create and configure your datasource to point to whatever you need
       emf.setName("system_pu");
       emf.setDatasource(ds);
       emf.setPackagesToScan(""); //optional if no persistence.xml is defined
       return emf;
   }
}

Answer 2

This feature is called multi-tenancy.

Hibernate 4 should support it out of the box, though I'm not sure whether it can be integrated with Spring-managed EntityManager.

Alternatively, the easiest way to do it is to intercept creation of database connections, either at ConnectionProvider level or at DataSource level, and choose the appropriate database based on tenant identifier stored in a ThreadLocal variable.

See also:

• Multi-tenancy Design
• Multi-tenancy in Hibernate