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!

Answer 1

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>