How to get rid of multiple query for join table in criteria builder (aka multiple db calls)

Question

I have a criteriaBuilder code which runs query on three table;

My root table is checkintable which includes also guestId in it and guest table also associated with event table

• checkin table: checknid / guestId
• guest table: guestId / eventId
• eventTable: guestid/ eventuuid

So whenever I query the checkin table via only eventUuId which should find the eventId and find all the guests form the guest table by join and then again join by checkin table and retrieve checkins of all the guests form the checkin table,

I have implemented the code below, as super class and subclass,

but regards of what I do it always run query at guest table for every guest,

This is my super class:

public abstract class ApiCriteriaRepository<E, R, P> {

    protected EntityManager entityManager;
    protected CriteriaBuilder criteriaBuilder;

    protected ApiCriteriaRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
        this.criteriaBuilder = entityManager.getCriteriaBuilder();
    }

    protected abstract Predicate getPredicate(P requestParameters, Long accountId, Root<E> root);

    public List<R> findAllWithFilters(P requestParameters, Long accountId, Class<E> entityClass) {

        CriteriaQuery<E> criteriaQuery = criteriaBuilder.createQuery(entityClass);
        Root<E> root = criteriaQuery.from(entityClass);
        Predicate predicate = getPredicate(requestParameters, accountId, root);
        criteriaQuery.where(predicate);

        TypedQuery<E> typedQuery = entityManager.createQuery(criteriaQuery);

        return prepareResult(typedQuery, requestParameters);

    }

    protected List<R> prepareResult(TypedQuery<E> typedQuery, P requestParameters) {
        return typedQuery.getResultStream()
                .map(this::transformEntity)
                .toList();
    }

    protected abstract R transformEntity(E eventEntity);

    public Page<R> findAllWithFilters(P requestParameters, Long accountId, int page, int pageSize,
                                      String sortBy, Direction sortOrder, Class<E> entityClass) {

        CriteriaQuery<E> criteriaQuery = criteriaBuilder.createQuery(entityClass);
        Root<E> root = criteriaQuery.from(entityClass);
        Predicate predicate = getPredicate(requestParameters, accountId, root);
        criteriaQuery.where(predicate);
        setOrder(criteriaQuery, root, sortBy, sortOrder);

        TypedQuery<E> typedQuery = entityManager.createQuery(criteriaQuery);
        typedQuery.setFirstResult(page * pageSize);
        typedQuery.setMaxResults(pageSize);

        Pageable pageable = getPageable(page, pageSize, sortBy, sortOrder);

        long entityCount = getEntityCount(entityClass, predicate);

        return new PageImpl<>(prepareResult(typedQuery, requestParameters), pageable, entityCount);

    }

    protected void setOrder(CriteriaQuery<?> criteriaQuery, Root<?> root, String sortBy, Direction sortOrder) {
        if (sortOrder.equals(Direction.ASC)) {
            criteriaQuery.orderBy(criteriaBuilder.asc(root.get(sortBy)));
        }
        else {
            criteriaQuery.orderBy(criteriaBuilder.desc(root.get(sortBy)));
        }
    }

    protected Pageable getPageable(int page, int pageSize, String sortBy, Direction sortOrder) {
        Sort sort = Sort.by(sortOrder, sortBy);
        return PageRequest.of(page, pageSize, sort);
    }

    protected long getEntityCount(Class<E> entityClass, Predicate predicate) {
        CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
        countQuery.select(criteriaBuilder.count(countQuery.from(entityClass)));
        countQuery.where(predicate);
        return entityManager.createQuery(countQuery).getSingleResult();
    }
}

And I have override the class as below

@Repository
public class CheckinCriteriaRepository extends ApiCriteriaRepository<CheckinEntity, CheckinResource, CheckinRequestParameters> {

    List<Predicate> predicates = new ArrayList<>();

    protected CheckinCriteriaRepository(EntityManager entityManager) {
        super(entityManager);
    }

    public List<CheckinResource> findAllWithFilters(CheckinRequestParameters requestParameters, Long organizerId, Class<CheckinEntity> entityClass) {
        CriteriaQuery<CheckinEntity> criteriaQuery = criteriaBuilder.createQuery(entityClass);
        Root<CheckinEntity> root = criteriaQuery.from(entityClass);

        // Define the predicates
        List<Predicate> predicates = new ArrayList<>();

        // If eventId is given, create a subquery to select the IDs of CheckinEntity records
        if (Objects.nonNull(requestParameters.getEventId())) {
            Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
            Root<CheckinEntity> subqueryRoot = subquery.from(entityClass);
            Join<CheckinEntity, GuestEntity> subqueryGuestJoin = subqueryRoot.join("guest", JoinType.INNER);
            Join<GuestEntity, EventEntity> subqueryEventJoin = subqueryGuestJoin.join("event", JoinType.INNER);

            subquery.select(subqueryRoot.get("id"));
            subquery.where(criteriaBuilder.and(
                    criteriaBuilder.equal(subqueryEventJoin.get("id"), requestParameters.getEventId()),
                    criteriaBuilder.isNull(subqueryGuestJoin.get("deletedAt")),
                    criteriaBuilder.equal(subqueryRoot.get("guest").get("id"), subqueryGuestJoin.get("id"))
            ));

            predicates.add(root.get("id").in(subquery));
        }

        criteriaQuery.where(predicates.toArray(new Predicate[0]));

        TypedQuery<CheckinEntity> typedQuery = entityManager.createQuery(criteriaQuery);

        return prepareResult(typedQuery, requestParameters);
    }

    @Override
    protected Predicate getPredicate(CheckinRequestParameters checkinRequestParameters, Long accountId, Root<CheckinEntity> checkinEntityRoot) {

        // I am adding some additional criteias in here too
        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));

    }

    @Override
    protected CheckinResource transformEntity(CheckinEntity entity) {
        return CheckinMapper.transformEntity(entity);
    }
}

when I give the query parameter as below

{
  "eventId": "dcf5a9b4-e769-4278-8d79-c0e6907c842b"
}

This creates the following queries

2023-08-24 10:34:28.046 DEBUG 25915 --- [nio-8080-exec-1] org.hibernate.SQL                        : select * from event evententit0_ where ( evententit0_.deleted_at IS null) and evententit0_.uuid=? and evententit0_.organizer_id=?
2023-08-24 10:34:28.130  INFO 25915 --- [nio-8080-exec-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQL10Dialect


2023-08-24 10:34:28.271 DEBUG 25915 --- [nio-8080-exec-1] org.hibernate.SQL                        : select * from checkin checkinent0_ inner join guest guestentit1_ on checkinent0_.guest_id=guestentit1_.id inner join event evententit2_ on guestentit1_.event_id=evententit2_.id where ( checkinent0_.deleted_at IS null) and evententit2_.id=16930 and (guestentit1_.deleted_at is null) and checkinent0_.guest_id=guestentit1_.id


// Creates multiple of this query
**2023-08-24 10:34:28.345 DEBUG 25915 --- [nio-8080-exec-1] org.hibernate.SQL 

select * from guest guestentit0_ left outer join guest_list categoryen1_ on guestentit0_.guest_list_id=categoryen1_.id left outer join guest guestentit2_ on guestentit0_.parent_guest_id=guestentit2_.id where guestentit0_.id=? and ( guestentit0_.deleted_at IS null)

2023-08-24 10:34:28.406  INFO 25915 --- [nio-8080-exec-1] org.hibernate.dialect.Dialect  **

So it calls guest query as the number of guests but I simple a IN or join structure;

How could I get rid of this multiple calls?

is that works if I replace it as fetch, which I am not sure of the benefit as well.

Answer 1

The problem seems to be with your mappings and what is extensively written about as the N+1 problem. Non-eager fetches in Hibernate and other providers are done after, when building the objets, and so done to fetch the data to build each object. If you want to reduce the number of queries when building CheckinEntity instances, you'll want to look at the mappings and strategically decide which should be eagerly fetched and which you don't always need. Hibernate will automatically join the eagerly fetched relations by default.

Alternatively, you can look at batch fetches (see https://stackoverflow.com/a/26843184/496099 and others). Batch fetching will use another set of queries to bring in relationships, but can fetch say all 'GuestEntity' instances for all EventEntity instances just read in with fewer queries. This reduces the N+1 problem to a manageable amount, and with OneToMany and ManyToMany relationships, doesn't require the database to always return N*M results as would happen with fetch joins.