JPA - EclipseLink - How to change default schema

Question

I'm programming a web application using weblogic and oracle. the datasource is configured through JNDI, with a restricted database user who can DML into tables, but can't DDL. As you may guess, that user isn't the owner of those tables, but he's granted access.

Let's say he is GUEST_USER

The application is using JPA + EclipseLink, and have lots of entities already defined. I don't want to write in each an every entity class the attribute to change schema. I've tried a SessionCustomizer, with this code.

public class MyCustomizer implements SessionCustomizer{

    @Override
    public void customize(Session session) throws Exception {

    session.executeNonSelectingSQL("ALTER SESSION SET CURRENT_SCHEMA = OWNERS_SCHEMA");
    }
}

It seems that there's something uninitiallized, I'm getting a null pointer exception, I'm not even sure if this is the way to change the schema for the connections before they are used. Any samples or ideas?

Thanks in advance for your help!

score 16 · Accepted Answer · answered Jul 09 '10 at 17:36

If all of the entities use the same schema you can use an xml mapping file to define a default schema.

Something like this should work (example is for JPA 2.0, change the schemaLocation for 1.0)

orm.xml:

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd"
    version="2.0">
    <persistence-unit-metadata>
        <persistence-unit-defaults>
            <schema>OWNERS_SCHEMA</schema>
        </persistence-unit-defaults>
    </persistence-unit-metadata>   
    . . .
</entity-mappings>

persistence.xml:

<persistence
    xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0" >
    <persistence-unit name="foo">
        . . .
        <mapping-file>orm.xml</mapping-file>
        . . .
    </persistence-unit>
</persistence>

this is good but still not dynamic, what if I need the schema changed for two versions of same app pointing to two diff db instances? — Kalpesh Soni, May 28 '14 at 19:39
Moreover if I set the schema for a particular EntityManager with a `SET search_path TO ...`, passing the entity manager to other methods has no effects. I suppose Wildfly (in my case) takes one of the pool's connections previously created with the public default schema — Chris, Feb 04 '16 at 09:10

Ati · Answer 2 · 2016-03-13T13:53:25.293

4

You can do it programatically. You can configure the default schema value for each session.

public class MySessionCustomizer implements SessionCustomizer {

  private static String schemaName;

  public static void setSchemaName(String schemaName) {
      MySessionCustomizer.schemaName = schemaName;
  }

  @Override
  public void customize(Session session) throws Exception {
      if (StringUtils.hasText(this.schemaName)) {
          session.getLogin().setTableQualifier(this.schemaName);
      }
  }
}

Then set the session customizer to entity manager factory properties:

PersistenceUnitProperties.SESSION_CUSTOMIZER

e.g.

propertiesMap.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, MySessionCustomizer.class.getName());

edited Mar 13 '16 at 13:53

answered Apr 03 '15 at 21:34

Ati

181
2
9

1

Is there a JPA standard way of doing this? – Archimedes Trajano Aug 25 '15 at 13:22
A JPA standard way is using parameter schema in annotation [`@Table`](http://docs.oracle.com/javaee/7/api/javax/persistence/Table.html) – Ati Mar 13 '16 at 13:56
According to [Eclipse User Guide](https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Advanced_JPA_Development/Customizers) there is another way to register a customizer - add it to the `properties` tag of `persistence.xml`, e.g.: `` – vocasle Sep 05 '18 at 09:52

score 0 · Answer 3 · edited Dec 23 '16 at 21:36

I use EJB right before querying the database, so by using Interceptors I'm able to set the schema in the EJB context by looking the current authenticated user.

Then when I build the entity manager I can set the right schema. In this way, by not specifying the schema before the table name, PostgreSQL will look at the search_path for determining which schema to query.

<!-- language: lang-java -->

public class Interceptor {

    Logger logger = Logger.getLogger(Interceptor.class);

    /**
     * 
     * @param ctx is always null before being passed to EJB implementation. We always query database
     * for populating context data, making user's session accessible to all EJB implementations
     * @return
     * @throws Exception
     */
    @SuppressWarnings({ "unchecked", "unused" })
    @AroundInvoke
    public Object intercept(InvocationContext ctx) throws Exception {
        Authentication auth = SecurityContextHolder.getContext().getAuthentication();

        String ejbName = ctx.getMethod().getDeclaringClass().getSimpleName();
        String methodName = ctx.getMethod().getName();
        Boolean override_schema = false;
        String overridden_schema = "";

        logger.info("Intercepting " + ejbName + "." + methodName);

        if(auth != null) {

            UserDetails userDetails = (UserDetails)auth.getPrincipal();
            String username = userDetails.getUsername();

            Collection<SimpleGrantedAuthority> permList = (Collection<SimpleGrantedAuthority>) auth.getAuthorities();   
            List<String> permissions = new ArrayList<String>();

            for (SimpleGrantedAuthority authority : permList) {
                permissions.add(authority.getAuthority());
            }


            Query query = getMasterEntityManager()
                            .createNativeQuery(
                "SQL for retrieving the schema by the current logged in user");

            query.setParameter("username", username);
            List<Object[]> result = null; //query.getResultList();

            if(result != null) {
                logger.info("Interceptor: context set for " + username);
                Object[] userObj = result.get(0);

                getContext().getContextData().put("username", username);
                getContext().getContextData().put("schema_name",(String)userObj[1]);
            }
        }

        return ctx.proceed();
      }
    }

Then when you build the entity manager, you can set the schema you want.

JPA - EclipseLink - How to change default schema

3 Answers3

Linked