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

Question

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.

Solution 1

This question may be old, but answering just in case you still run into this. You could use {h-schema} for native SQL queries (verified for Hibernate v4). This is defined by hibernate.default_schema configuration property.

Example:

"SELECT * FROM {h-schema}LOCATION"

Reference: https://vladmihalcea.com/how-to-resolve-the-hibernate-global-database-schema-and-catalog-for-native-sql-queries/

Solution 2

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;

Comments

[Matthias]

This is gold and pretty useful when circumventing @Where