CODEWITHSUNDEEP
mysqljpawebservereclipselinkentitymanager
Norbware
Norbware

Reputation: 31

Connecting to different mysql schemas selected at runtime in a webserver using JPA EntityManagerFactory (JSE,EclipseLink,Tomcat), is it possible?

Is there a way to recreate an EntityManagerFactory already used in a webapplication at runtime? What I want is tell the entityManagerFactory to forget the last database connection and connect to a new schema at runtime when the webuser selects an other database (mysql schema). Or not forget the already used one but also use a new connection to a different mysql schema which was not used yet. These schemas have the exact same structure (tables, etc.) but for different users for security and other reasons. Is this possible?

I am using the Vaadin framework, Eclipselink 2.4.1, Mysql 5.5 and Tomcat 7. What I found related to my situation and what I tried already are followings. I am using an Eclipselink composite persistence unit, the second member is always the same, I want to change the first schema for example to "42_candidate" when the user selects the 42nd candidate on the webpage after an other schema was already connected like "41_candidate".

private static EntityManager createEntityManagerForCandidateSchema(String candidateSchemaName) throws javax.persistence.PersistenceException {        

    System.out.println("createEntityManagerForCandidateSchema called");
    // set persistence unit properties
    HashMap<String,String> candidatePuProps = new HashMap<String,String>();
    candidatePuProps.put("javax.persistence.jdbc.url", "jdbc:mysql://localhost:3306/"+candidateSchemaName+"?useUnicode=true&amp;characterEncoding=UTF-8");

    HashMap<String,Map> compositePuProps = new HashMap<String,Map>();
    compositePuProps.put("election_model_candidate", candidatePuProps);

    Map puProps = new HashMap();
    puProps.put("eclipselink.logging.level", "FINEST");
    puProps.put("eclipselink.composite-unit.properties", compositePuProps);
    // puProps.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "com.beharbe.ui.ElectionSessionCustomizer");       

    boolean candidateDatabaseSchemaNotFound = false;
    try {
        EntityManagerFactory emf = javax.persistence.Persistence.createEntityManagerFactory("election_composite_pu",puProps);
        emf.close(); // to forget latest things
        emf = javax.persistence.Persistence.createEntityManagerFactory("election_composite_pu",puProps);            
        EntityManager em = emf.createEntityManager(compositePuProps);

...
public void selectionChanged(int newCandidatePersonId) {
    entityManager = Util.createEntityManagerForCandidateSchema(newCandidatePersonId);
    ...

(Eclipselink Composite PU) wiki.eclipse.org/EclipseLink/UserGuide/sandbox/gelernter/Composite_Persistence_Units#Persistence_Unit_Properties

(Dynamic Persistence) dev.eclipse.org/svnroot/rt/org.eclipse.persistence/branches/2.1/trunk/examples/jpa.employee/eclipselink.example.jpa.employee.dynamic/src/example/Main.java

(EclipseLink - How to configure Database Schema name at runtime) www.rqna.net/qna/kxvmwy-jpa-eclipselink-how-to-configure-database-schema-name-at-runtime.html

(Eclipselink with Tomcat tutorial) wiki.eclipse.org/EclipseLink/Examples/JPA/Tomcat_Web_Tutorial#Session_Customizer

(Using JPAContainer with Hibernate (Vaadin)) vaadin.com/book/-/page/jpacontainer.hibernate.html

(How can I make a JPA application access different databases?) stackoverflow.com/questions/9315593/how-can-i-make-a-jpa-application-access-different-databases

(Connect two or more databases dynamically) stackoverflow.com/questions/9732750/connect-two-or-more-databases-dynamically?lq=1

(JPA - Using Multiple data sources to define access control) www.rqna.net/qna/kqqihk-jpa-using-multiple-data-sources-to-define-access-control.html

JPA2 run-time database connection

JPA - EclipseLink - How to configure Database Schema name at runtime (Eclipselink SessionCustomizer)

Maybe I should do it somehow with the EclipseLink SessionCustomizer? (see latest link)

thanks for any help in advance

Meanwhile I found something, this can be what I have to use:

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

I am trying this way but it still connects to the schema which was connected first when the EMF called first time:

....
candidatePuProps.put(PersistenceUnitProperties.JDBC_URL, "jdbc:mysql://localhost:3306/"+candidateSchemaName+"?useUnicode=true&amp;characterEncoding=UTF-8");
    candidatePuProps.put(PersistenceUnitProperties.EXCLUSIVE_CONNECTION_MODE, "Always");
    candidatePuProps.put(PersistenceUnitProperties.EXCLUSIVE_CONNECTION_IS_LAZY, "false");
...

Upvotes: 3

Views: 1834

Answers (1)

James
James

Reputation: 18389

If you want to access two different databases/schemas, you can just call createEntityManagerFactory() passing a properties map with your new connection.

To set the schema with EclipseLink in code you can set the tableQualifier in a Customizer. To get a new EntityManagerFactory you can pass the "eclipselink.session-name" property.

Your code looks correct. What version of EclipseLink are you using?

Remove the composite persistence unit, I think you seem to just want to connect to a different database. Composite persistence units are for when you have relationships across databases.

Upvotes: 2

Related Questions