JPA using multiple database schemas at runtime dynamically

Question

how can I set schema in run time ? I want run a name query in more than one database and aggregate their answers.

for example run this query in db1 and db2 : SELECT CUSTOMER FROM CUSTOMER CUS WHERE CUS.CODE=?1

note : I can set schema on @Table in entities ,but it isn't dynamic and i can't change it in run time.

please help! regards

Answer 1

One solution is to setup N datasources each pointing to different database/schema:

DataSource ds1 = // setup ds 1
DataSource ds2 = // setup ds 2

and N entity manager factories (EMF) with 1-to-1 mapping with each ds :

EntityManagerFactory emf1 = // setup emf1 mapped to ds1
EntityManagerFactory emf2 = // setup emf2 mapped to ds2

Then you need N copies of each DAO classes injected with each EMF :

public class CustomerDAO {

  private EntityManagerFactory emf;

  public CustomerDAO(EntityManagerFactory emf) {
    this.emf = emf;
  }

  // dao methods here..
}

CustomerDAO emf1CustomerDAO = // setup customer DAO injected with emf1
CustomerDAO emf2CustomerDAO = // setup customer DAO injected with emf2

Your application can now make a decision at runtime which database/schema to use by selecting the DAO

Answer 2

You might want to look into EclipseLink's data partitioning support,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/Partitioning