lexicore lexicore - 24 days ago 8
Java Question

How to configure default database schema for native queries in pure JPA?

Our situation is as follows:


  • We use (mustly pure) JPA for persistence in our enterprise application.

  • Due to performance reasons, we use a number of fairly complicated native queries here an there.

  • When accessing the database (Oracle 11g), we use a database user
    APP_ACCESS
    which is different from the "owner" of the tables (
    APP_OWNER
    ). This is a hard requirement from the database administation.

  • At the moment the schema name (
    APP_OWNER
    ) is hardcoded into the native queries, a-la:



"SELECT * FROM " + DatabaseSchemaConstants.SCHEMA_NAME + ".LOCATION"


Needless to say, we are not particularly happy about this hardcoding as changes of the "owner" user name would mean changes in the code - which is definitely not good for maintenance.

So here is my question:

How could we configure the default database schema for native queries in pure JPA?

This is what I've found so far:


How to set up default schema name in JPA configuration?


Specificaly this answer which proposes a combination of a persistence unit and an
orm.xml
file:

<?xml version="1.0" encoding="UTF-8"?>
<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 http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
version="1.0">
<persistence-unit-metadata>

<persistence-unit-defaults>
<schema>myschema</schema>
</persistence-unit-defaults>
</persistence-unit-metadata>
</entity-mappings>


But I think this does not apply to the native queries (we're testing it at the moment). Another issue here is that
orm.xml
isn't really for configuration. (But its is still better than hardcoding in the Java code).

Another post with a similar answer:


JPA - EclipseLink - How to change default schema


A further question:


getting hibernate default schema name programmatically from session factory?


See this answer which suggests using
{h-schema}
. Looks suitable, but it is Hibernate-specific. We'd like to stay with "pure JPA". Another problem is that I could not find any mentions of {h-schema} in Hibernate documentation, so it is not safe for us to rely on this feature.

Are there standard JPA way to set the default database schema for everything - JPA entites and queries as well as native queries?

Alternatively this could be solved if we'd be able to use some "parameters" or "settings" set somewhere in configuration in our native queries.

Answer Source

Finally, after a few internal discussions we've decided to solve this on the database level. Specificaly, using an after logon trigger:

CREATE OR REPLACE TRIGGER db_logon AFTER logon ON DATABASE
WHEN
(
  USER = 'APP_ACCESS'
)
BEGIN
  EXECUTE immediate 'ALTER SESSION SET CURRENT_SCHEMA = APP_OWNER';
END;