JPA Criteria Join Fetch several tables

Hibernate ORM
1

I’m making some modifications to a query do so some pre-fetching for performance reasons in very specific scenarios. This is a query that requires joining several tables with 1-N relationships, which are needed to be referenced in the where clause. I know that hibernate limits Join Fetch to 2 tables for 1-N relationships. After I’ve exceeded 2 Join Fetches, is it possible to join the rest of the entities with just a regular join to one of the previous entities that has been join fetched? When I attempt this, I receive an “invalid path” error, as I am trying to reference the attributes in the where clause.

Ex:
Tables: A, B, C, D, E

Fetch<A, B> abFetch = root.fetch("b", JoinType.LEFT);
Join<A, B> abJoin = (Cast) abFetch;
Fetch<B, C> bcFetch = abJoin.fetch("c", JoinType.LEFT);
Join<B, C> bcJoin = (Cast) bcFetch;

Join<C, D> cdJoin = bcJoin.join("d", JoinType.LEFT);
Join<C, E> ceJoin = bcJoin.join("e", JoinType.LEFT);

where ...
cdJoin.get("attribute"),
ceJoin.get("attribute"),
...

How am I able to join the rest of my tables?

2

Your query should IMO use just joins and what you want to fetch should be applied as load graph via a query hint. I’d suggest you to use a DTO model instead though if possible, with a tool like Blaze-Persistence Entity-Views for example. With that library, there are no fetch joins and you don’t run into the problem. The increasing cardinality is a problem either way though. Maybe try to think about using an exists predicate instead e.g.

var subquery = cq.subquery(Integer.class);
subquery.select(cb.literal(1));
var subRoot = subquery.correlate(root);

var abJoin = root.join("b");
var bcJoin = abJoin.join("c";

var cdJoin = bcJoin.join("d", JoinType.LEFT);
var ceJoin = bcJoin.join("e", JoinType.LEFT);

subquery.where(
    cb.equal(
        cdJoin.get("attribute"),
        ceJoin.get("attribute")
    )
);

cq.where( cb.exists(subquery) );