JPA: multi-level fetch with limitation

Question

I am trying to fetch entities over multiple levels with JPA CriteriaQuery using one call to the database.

The NamedEntityGraph:

@Entity
@Table(name = "matchh")
@NamedEntityGraph(name = "Match.MatchPlayerAssociation.Player",
    attributeNodes = @NamedAttributeNode(value = "matchPlayerAssociation", subgraph = "player"),
    subgraphs = @NamedSubgraph(name = "player", type = Player.class, attributeNodes = @NamedAttributeNode("player")))
public class Match implements Serializable {
    ...
}

The Load method for fetching entities:

public List<T> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters, String graph) {
    EntityGraph entityGraph = entityManager.getEntityGraph(graph);
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<T> query = builder.createQuery(tClass);
    Root<T> root = query.from(tClass);
    query.select(root);

    applyFilters(builder, query, root, filters);
    applySorting(builder, query, root, sortField, sortOrder);

    return entityManager.createQuery(query)
            .setFirstResult(first)
            .setMaxResults(pageSize)
            .setHint(QueryHints.HINT_LOADGRAPH, entityGraph)
            .getResultList();
}

It works fine except that Hibernate selects all rows at once and performs the limitation of the result in memory.

org.hibernate.hql.internal.ast.QueryTranslatorImpl.list HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

So my question is, is it somehow possible to perform the limitation at the database?

I have tried to select from a subselect but i can't figure out how to do the following SQL Statement in JPA

select m.*, mp.*, p.* from (select id from matchh limit 5,10) m
        left outer join match_player mp on m.id=mp.matchid 
        left outer join Player p on mp.playerid=p.id;

Answer 1

I ended up using an additional call to the database where i fetch a list of match.id that is limited by the sort and filter options and using this list to limit the actual query.

public List<T> load(int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters, String graph) {
    EntityGraph entityGraph = entityManager.getEntityGraph(graph);

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<T> query = builder.createQuery(tClass);
    Root<T> root = query.from(tClass);

    query.select(root)
            .where(root.get("id").in(fetchLimitedListOfIds(builder, first, pageSize, sortField, sortOrder, filters)));

    applySorting(builder, query, root, sortField, sortOrder);

    return entityManager.createQuery(query)
            .setMaxResults(pageSize)
            .setHint(QueryHints.HINT_LOADGRAPH, entityGraph)
            .getResultList();
}

private List<ID> fetchLimitedListOfIds(CriteriaBuilder builder, int first, int pageSize, String sortField, SortOrder sortOrder, Map<String, Object> filters) {
    CriteriaQuery<T> query = builder.createQuery(tClass);
    Root<T> root = query.from(tClass);
    query.select(root.get("id"));

    applyFilters(builder, query, root, filters);
    applySorting(builder, query, root, sortField, sortOrder);

    return (List<ID>) entityManager.createQuery(query)
            .setFirstResult(first)
            .setMaxResults(pageSize)
            .getResultList();
}