Problems with Join Fetch using Criteria API

[Arsenii01]

Hello. I'm having trouble with making my query to DB. I'm have updated dependencies in my project, and spring-boot-starter-jpa was updated too (3.4.0 version), so org.hibernate.orm:hibernate-core dependency went from something like 6.1.7 to 6.6.2.Final.

I have 3 entities, lets say A, B, C
A has One-To-Many relation to B (FetchType.LAZY in mapping at A class) , B has One-To-Many relation to C (FetchType.EAGER in mapping at B class).

I have repository for A entity that extends interface JpaSpecificationExecutor<A>, here is a method used:
fun findAll(spec: Specification<A>, pageable: Pageable): Slice<A>

Before update I had such code:

entityARepo.findAll(
                Specification.where(
                    %some criteria%)
                .and(%some another criteria%)
            , page)

It worked properly before updating, making 1 query to A table in DB (with no JOINs), and N queries to B table in DB (with LEFT JOIN on C), N is a number of A entities that I got in first query (for every A entity in resultList, 1 query to B (+ join C) table)

After updating dependencies to spring-boot-starter-jpa:3.4.0 (hibernate-orm:6.6.2.Final), I startet getting this error:org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: EntityA.bEntities : could not initialize proxy - no Session
I read this article and decided to use fetching. I wrote such code:

val fetch: (SetAttribute<A, B>, SetAttribute<B, C>, JoinType) -> Specification<A> =
    { entityAattribute, entityBattribute, joinType ->
        Specification<A> { root, _, _ ->
            root.fetch(entityAattribute, joinType)
                   .fetch(entityBattribute, joinType)
            null
        }
    }

And so method invocation looks like this:

entityARepo.findAll(
                criteria
                    .and(fetch(A_.bEntities, B_.cEntities, JoinType.LEFT))
                    .and(%some another criteria%)
            , page)

Error LazyInitializationException disappeared, but now I'm getting org.hibernate.query.SemanticException: Query specified join fetching, but the owner of the fetched association was not present in the select list [SqmSetJoin(EntityA(83).bEntities(84))]

Hibernate makes 1 big query to A table, with LEFT JOINs on B and C

How can I fix this?

[Arsenii01]

I solved this with adding @EntityGraph(attributePaths = ["bEntities", "bEntities.cEntities"]) to findAll method, and deleting .and(fetch(A_.bEntities, B_.cEntities, JoinType.LEFT)) from method invocation
Is this the best way to fix the problem? Will the performance of other methods of A entity repository suffer in this way?

[Arsenii01]

With this solution, endpoint works much slower (up to 4-5x response time) if there are a lot of A entitites in resultList, my k8s pod even got restarted once idk why. I think this is because current query leads to making quite big table because of two left join operations. Is there is another way to fix the problem without affecting the request execution time?

[mp911de]

This is purely a JPA-specific topic on selection. Performance is a result of heavy joins and indicates that your underlying table model isn't a great match for the query you're trying to run. Either go with SQL directly or create something like an indexed view for your query.