Can I set the schema before creating the EntityManager?

Question

I need to use multiply persistence units with different properties (in my case MySQL and Oracle database). In persistence.xml I define two different "persistence-unit"s and list only the entity classes there.

Properties could be set in persitence.xml with

<properties> <property name="..." value="..." /> ...

Im doing it in a java class before creating the EntityManager, because I must use different properties (which I read before):

        EntityManagerFactory factory;
        ...
        HashMap<String, String> dbProperties = new HashMap<String, String>();
        dbProperties.put("javax.persistence.jdbc.driver", driver);
        dbProperties.put("javax.persistence.jdbc.url", url);
        dbProperties.put("javax.persistence.jdbc.user", user);
        dbProperties.put("javax.persistence.jdbc.password", password);
        dbProperties.put("eclipselink.ddl-generation", "none");
        dbProperties.put("eclipselink.ddl-generation.output-mode", "database");

        factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME, dbProperties);
        EntityManager em = factory.createEntityManager();
        ...

For Oracle I need to set schema dynamicly (if possible) and not hardcoded in @Table annotation in each Entity class.

For now I am using a constant (means it is not dynamicly)

@Table(name="MYTABLE", schema = Constants.ORACLE_SCHEMA)

I want to use

@Table(name="MYTABLE")

And set the schema as property

dbProperties.put(...)

Is there such a property?

On my search I found a syntax which could help

ALTER SESSION SET CURRENT_SCHEMA=MYSCHEMA

But I don't know how to combine it with EntityManager.

I have allready asked about all available properties here, but could not find anything yet.

Thank you in advance.

score 1 · Accepted Answer · answered Dec 09 '15 at 13:58

1

I have found a solution for this with the help of this post: https://community.oracle.com/thread/2270534?tstart=0

It can be done with a query after EntityManager is created:

        ...
        EntityManager em = factory.createEntityManager();
        Query query;

        em.getTransaction().begin();
        query = em.createNativeQuery("ALTER SESSION SET CURRENT_SCHEMA="+schemaName);
        query.executeUpdate();
        em.getTransaction().commit();

Check current schema with:

        query = em.createNativeQuery("SELECT SYS_CONTEXT('USERENV','SESSION_SCHEMA') FROM DUAL");
        System.out.println("Current schema: " + query.getResultList());

answered Dec 09 '15 at 13:58

Andreas L.

2,805
23
23

Couldn't you tie the schema to the user as well, and then pick the user based on the schema you wanted to use? – Chris Dec 09 '15 at 16:20
Maybe this does solve the issue, but there is something called 'InitSql' in Tomcat and Weblogic that allows you to run some SQL after the database connection is checked out. The solution is then to bind the schema to the jdbc pool. Hope it helps – rjdkolb Dec 10 '15 at 07:52
@Chris You can run the query ALTER SESSION SET CURRENT_SCHEMA each time you need to change schema (because it works after creation of EntityManager) or create different EntityManagers for each user(?). – Andreas L. Dec 10 '15 at 08:47
@rjdkolb Thank you for your response. I am using a standalone java programm with EclipseLink. If you have an example/solution for a server (Tomcat/Weblogic or other) I would appreciate it too. – Andreas L. Dec 10 '15 at 08:50
@Andreas L. - You can do the same thing with a session event http://stackoverflow.com/questions/15100698/setting-a-db-session-level-parameter-in-eclipselink-for-every-connection and hide the query from your application, but it should not be dynamically changed, as JPA maintains caches, and executing a native query over the entityManager really depends on how the internals of how the provider uses and pools those connections. If you tie the scheme to the user in the database, you don't need to alter the schema on the connection, and instead can specify the user as a property. – Chris Dec 10 '15 at 18:38

rjdkolb · Answer 2 · 2015-12-10T10:07:52.673

I am not sure if this will solve your problem, but several application server vendors may something called InitSql.

It is a SQL statement that is run on every successful database connection made.

In Weblogic 12c this is called InitSql
In Tomcat 7 this is also called InitSql

I can't find something like this for EclipseLink in Java SE, but perhaps there is a post checkout SQL that can be run like InitSql

Like Andreas L.'s answer this can allow you to run commands like .

ALTER SESSION SET CURRENT_SCHEMA=xxx

Can I set the schema before creating the EntityManager?

2 Answers2