How to handle several DB schemas with Hibernate?

Question

In one of my projects, I have an application that manages several clients (or customer if you prefer). For each of them, I have a dedicated schema on a database. However, the application handles only one client at a time, i.e. the user must switch from one client to another in the application (at runtime, no restart of the application) in order to access the data from this new client.

How would you manage the connections, as well as the persistence layer, for this kind of project?

I want to use Hibernate for that. What are the points on which I must be really carefull when dealing with several database / schemas ?

Can Spring be of any help in this case?

---

If I am not clear enough, let me explain the situation with an example. Imagine that my application can handle two clients: clientONE and clientTWO. I already implemented a class that can provide me the database schema, user, password and connection String for a given client.

Each client have a list of debtors, but unfortunately, the DEBTOR table structure is not the same for clientONE and clientTWO. Even the names of tables / columns are not the same...

So I can create one debtor class per client (I use Hibernate annotations):

@Entity
@Table(name = "T_DEBTOR_ONE")
...
public class ClientOneDebtor {

    @Id
    @Column(name = "ID_DEBTOR")
    private String idDebtor;

    ...

}

and:

@Entity
@Table(name = "T_DEBTOR_TWO") // Table names are not the same among the different schemas...
...
public class ClientTwoDebtor {

    @Id
    @Column(name = "DEBTOR_ID") // It's just to show that the same information is stored in a column that has not the same name.
    private String idDebtor;

    ...

}

Ideally, I will try to have a common Debtor class (here is an Abstract class, but I may use an Interface):

public abstract class AbstractDebtor {

    public abstract String getIdDebtor();

    ...

}


@Entity
@Table(name = "T_DEBTOR_ONE")
...
public class ClientOneDebtor extends AbstractDebtor {

    @Id
    @Column(name = "ID_DEBTOR")
    private String idDebtor;

    ...

}


@Entity
@Table(name = "T_DEBTOR_TWO")
...
public class ClientTwoDebtor extends AbstractDebtor {

    @Id
    @Column(name = "DEBTOR_ID") // It's just to show that the same information is stored in a column that has not the same name.
    private String idDebtor;

    ...

}

This way, it will be easier for me to manipulate the Debtor objects in my DAO / Service layer, as I will not need to duplicate my DAO and Services for every client. For example, the method from DAO to get the list of all Debtors will be public List<AbstractDebtor> getAllDebtors() { ... }.

So, how would I change the context when I change the client managed by my application? In others words, how would I indicate to Hibernate (or Spring?) that I want to use the correct persistence objects (ClientOneDebtor or ClientTwoDebtor) regarding the client that is currently managed by my application?

If you think that I am going in the wrong direction, do not hesitate to share your ideas on how to solve this kind of problem...

---

Edit regarding the first answers:

The number of different schemas I will need to handle is around 15 - 20. In addition to that, I will only need to map only a little subset of their tables.

I also know that having one schema per client/customer is not the best solution for storing data. However, this architecture exist since 5 years, and we may move to only one schema during the next year (in the best case ;) ).

Answer 1

From my experience, it's never a particularly good idea to have different schemas for different clients/users/companies/whatever. How many such schemas do you have? 2? 5? 100?

In applications where we have two or more schemas (hosting them on different servers or accessing DB's hosted by third-parties as typical usecases), we simply configure independent session factories for each of them. Choosing the right one is than "implicitly done" by the session factory that was injected into the DAO. So for your situation, you might have a clientOneDetborDAO and a clientTwoDebtorDAO - this will get hard for 100 clients though ;)

Answer 2

If only one at a time will every be required, it makes it much simpler. Simply create a SessionFactory per database. Avoid the HibernateUtils static SessionFactory instance approach and you won't have any problems.

A neat way to do this with Spring if you don't have too many databases (hundreds) is to instantiate a separate Spring ApplicationContext for each one that contains the SessionFactoryBean and DataSource configurations specially for that database.

You can use Spring mechanisms like PropertyOverrideConfigurer and a common parent ApplicationContext to factor out all the common stuff so that your many child ApplicationContexts are small and maintainable.

Then when a request comes in, just select the ApplicationContext you want to work with and start pulling beans out of it.

If you want to do it without Spring, you could also create multiple SessionFactory instances and store the "current" one in a static ThreadLocal.

Answer 3

Unfortunately, the Real World often does require multiple databases/schemas, especially when you have a vendor product whose database must be distinct from your corporate databases.

Making an arbitrary number of databases would be a mess, and for that, you really SHOULD consider a better form of data organization. But for a fixed (hopefully small) set of databases, just define them in the persistence configuration with a separate PersistenceUnit for each (which implies a separate EntityManager).

Using your illustrated inheritance scheme, you would assign the appropriate EntityManager to each derived class, assuming that the framework lets you.