Explore using JPQL queries instead of Criteria Queries

[mp911de]

We should explore how we could switch from Criteria Queries to JPQL queries as Hibernate has a much better efficiency running String-based queries:

CriteriaBuilder query
Benchmark                                   Mode  Cnt       Score      Error  Units
RepositoryFinderTests.derivedFinderMethod  thrpt    5  110633,482 ± 3980,501  ops/s

String-based query
Benchmark                                   Mode  Cnt       Score       Error  Units
RepositoryFinderTests.derivedFinderMethod  thrpt    5  344518,379 ± 24561,002  ops/s

We would need to build our own JpqlQueryBuilder that mimics CriteriaBuilder in terms of expressions, predicates and automated inclusion of joins.

Collection of related tickets:

• Support for sorting by subclass properties inside repository methods #3593
• Consider NULLS precedence using Sort for Criteria Queries #3587
• Derived Method Unneccessarily Joins for filter #3423
• Use implicit join to create Predicate if possible #3374
• Why default implementation JpaRepository doesn't support JpaSort.unsafe(...)? #3172

[mp911de]

It is possible to use String-based queries. We should consider how to inject Keyset-pagination predicates and introduce joins for sort by fields that work for part tree queries and JPQL queries using ideally the same mechanism.

[derekm]

Is this why FilterDef broke somewhere between Spring Boot 2.1 and 2.5? See #2565

If queries are rendered to JPQL/HQL again, then can they be created through EntityManager, so that those of us who unwrap Session and enable filters will be able to use @FilterDefs again?

[mp911de]

If queries are rendered to JPQL/HQL again,

We've been using CriteriaQuery for derived queries and SimpleJpaRepository implementations since the inception of Spring Data JPA.

[quaff]

Why does JPQL queries perform better than Criteria Queries? Parsing JPQL into SQM seems more heavy than mapping CriteriaQuery to SQM, maybe it should be improved at Hibernate side, or something going wrong at Spring Data JPA side.

[mp911de]

Why does JPQL queries perform better than Criteria Queries?

Because Hibernate seems to cache the parsed HQL query.