Oracle Hint Ineffectiveness in JPQL with Hibernate Generated Aliasing (H3-H5 Migration)

Hibernate ORM
1

Hi everybody,

I am encountering an issue regarding the ineffectiveness of specific Oracle hints embedded in JPQL queries within a project that is migrating from Hibernate 3 to Hibernate 5 .

Problem Context: Our project extensively uses JPQL queries (not native SQL) that include Oracle hints (e.g., /*+ full(alias) */ , /*+ first_rows(N) */ ). For these hints to be effective, they must refer to the table alias generated by Hibernate in the final SQL.

During the migration from Hibernate 3 to Hibernate 5, we have observed that the table aliases generated by Hibernate for entities in JPQL queries have changed . Consequently, Oracle hints that referred to the old aliases have become ineffective, as the database cannot map the alias specified in the hint to the alias actually generated and used in the underlying SQL.

Concrete Example: Consider the following JPQL query:

@Query("SELECT /*+ first_rows full(target) */ t FROM MyEntity t WHERE t.id = :id")
List<MyEntity> getEntityFromId(@Param("id") Long id);

In this example, target is the alias we would like to use in the hint. However, if Hibernate generates a different alias (e.g., myentity0_. or generated_alias_0_ ) for MyEntity in the final SQL, the full(target) hint becomes null.

Request for Support / Proposed Solution: I know that it’s not the correct way to use hint in not-native JPQL, but I would like to know if there is a mechanism or an extension point in Hibernate 5 (or related libraries) that allows intercepting the JPQL to SQL translation process. The objective would be to:

  1. Identify the presence of Oracle hints within the JPQL query.
  2. Detect the alias generated by Hibernate for the entities involved in the query.
  3. Dynamically replace the “placeholder” alias in the hint (e.g., target ) with the correct alias generated by Hibernate, before the query is sent to the database.

I am available to actively contribute to the development of this functionality, should it be deemed feasible and align with Hibernate’s roadmap or extensible architecture.

2

Theoretically, you could in your code inspect the generated AST to understand the SQL aliases that are generated for a certain HQL alias, but it’s very cumbersome.
Since JPA Criteria query FROM nodes don’t have aliases that one could refer to, it’s hard to justify adding something like that. We do have alias specific locks though, so I guess you could argue this is similar.
I don’t mind rethinking how we handle database hints and potentially offer richer database hint support, but whatever we do, such new features would only go into the latest version i.e. ORM 7.1 at the time of writing.

If you want to drive this effort, I would suggest you join our Zulip chat platform and bring up this topic for discussion.