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

gerrytan · Accepted Answer · 2013-07-13 09:50:18Z

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

answered Jul 13, 2013 at 9:50

gerrytan

41.2k12 gold badges88 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James · Accepted Answer · 2013-07-15 14:34:58Z

1

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

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

answered Jul 15, 2013 at 14:34

James

18.5k13 gold badges100 silver badges154 bronze badges

Collectives™ on Stack Overflow

JPA using multiple database schemas at runtime dynamically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related