title: How to handle several DB schemas with Hibernate?
body: <p>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.</p>
  
  <p>How would you manage the connections, as well as the persistence layer, for this kind of project?</p>
  
  <p>I want to use Hibernate for that. What are the points on which I must be really carefull when dealing with several database / schemas ?</p>
  
  <p>Can Spring be of any help in this case?</p>
  
  <hr/>
  
  <p>If I am not clear enough, let me explain the situation with an example.
  Imagine that my application can handle two clients: <code>clientONE</code> and <code>clientTWO</code>.
  I already implemented a class that can provide me the database schema, user, password and connection String for a given client.</p>
  
  <p>Each client have a list of debtors, but unfortunately, the DEBTOR table structure is not the same for <code>clientONE</code> and <code>clientTWO</code>.
  Even the names of tables / columns are not the same...</p>
  
  <p>So I can create one debtor class per client (I use Hibernate annotations):</p>
  
  <pre><code>@Entity
  @Table(name = "T_DEBTOR_ONE")
  ...
  public class ClientOneDebtor {
  
      @Id
      @Column(name = "ID_DEBTOR")
      private String idDebtor;
  
      ...
  
  }
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>@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;
  
      ...
  
  }
  </code></pre>
  
  <p>Ideally, I will try to have a common <code>Debtor</code> class (here is an Abstract class, but I may use an Interface):</p>
  
  <pre><code>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;
  
      ...
  
  }
  </code></pre>
  
  <p>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 <code>public List&lt;AbstractDebtor&gt; getAllDebtors() { ... }</code>.</p>
  
  <p>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 (<code>ClientOneDebtor</code> or <code>ClientTwoDebtor</code>) regarding the client that is currently managed by my application?</p>
  
  <p>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...</p>
  
  <hr/>
  
  <p><em>Edit regarding the first answers:</em></p>
  
  <p>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.</p>
  
  <p>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 ;) ).</p>