How to configure the database schema name dynamically based on user input.

[Raghu Sha]

configure the database schema name dynamically based on user input.

For ex:
We have two schemas:
Schema1 - base schema having 15 tables.
Schema2 - tables which is specific to modules. Having only 10 tables which is also available in Schema1

Login to application using Schema 1

Access a particlular module and select the country. Here country selection is identified.

Based on the country selection, we need to connect the schema respectively.
If the user selects France --> It should connect Schema1
If the user selects Germeny --> It should connect schema2.

Used: Eclipselink

[E Armitage]

The schema is set on an Entity declaration before the entity should be completely described at application startup. Do you have different entities for the different schemas? If you do then all you need is a DAOFactory that returns the returns DAOs that interact with the right schemas. Another approach (if you can set the schema at persistence unit level) is to have two persistence units and have your DAOs use the right persistence context for the right user.

[Raghu Sha]

Thanks Armitage.

Entites are same irrespective of the data base.
Code base should be same irrespective the users.
I need to configure the db schema name for entities based on user input.

Please advice.

[E Armitage]

Persistence definitions are not dynamic. So you are going to have to create two persistence units pointing to the different schemas in the <persistence-unit-defaults> tag of your persistence.xml. You can then use a DAOFactory that takes in an EntityManager and pass it one based on the right persistence context.

[Raghu Sha]

Thanks Amir..

I am using JTA.
Could you please suggest how to get schema name using JTA?

In non-jta environment using Persistence.createEntityManagerFactory("schemaPU"); and then manange the datasource.

Please suggest how to get schema name using JTA (EJB3.1).

[E Armitage]

@PersistenceContext(unitName="yourUnitForSchema1") 

[Raghu Sha]

I am not able to pass the persistence unit based on user selection in
@PersistenceContext(unitName="yourUnitForSchema1")

In a method based on user input
if(selectedValue=="FR"){
@PersistenceContext(unitName="yourUnitForSchema1")
EntityManager em;
}else {
@PersistenceContext(unitName="yourUnitForSchema2")
EntityManager em;

}

The above way is not working.
Please give suggestion to pass the schema name in above scenerio.
Used JTA in persistence.xml

[E Armitage]

Of course that won't work like that. You have to inject both units as properties and have your DAOFactory take the em that the DAO is working on.

@PersistenceContext(unitName="yourUnitForSchema1")   
EntityManager emSchema1;

@PersistenceContext(unitName="yourUnitForSchema2")   
EntityManager emSchema2;