What is the standard way to avoid both N+1 and the Cartesian Product issues while fetching collections with JPA

Question

When one has an entity with fields part of which are collections, one wishes to fetch the data with the smallest number of queries possible and using as less memory as possible.

The first problem is addressed by "join fetch" in JPQL queries (solving the N+1 problem).

However "join fetch" (as easily seen by inspecting the respective SQL query) causes the Cartesian product problem: each "row" which corresponds to the entity fields without multiplicities, is present in the returned result set with multiplicity N_1 x N_2 x ... x N_m, where N_1 is the multiplicity of the first collection, N_2 is the multiplicity of the second, and N_m is the multiplicity of the m-th collection, assuming that the entity has m fields which are collections.

Hibernate solves this problem with FetchMode.SUBSELECT (which, If I am not mistaken, makes m+1 queries, each of which returns no redundant data). What is the standard way to resolve this issue in JPA (it seems to me I cannot mix, at least in this case, JPA annotations with those of Hibernate)?

Answer 1

The best way is to replace collections with queries, especially when the expected size is large enough to lead to a performance issue:

1. You remove the bidirectional @OneToMany side, leaving only the owning @ManyToOne side

2. You select the parent entity (e.g. Country) run queries like:

    select c from City c where c.country = :country
    select c from County c where c.country = :country
    select count(p), c.name from People p join p.country group by c.name