JPA Criteria: Obtain total count just before full result with all columns; reuse Where clause

Question

In JPA Criteria I have a complex query which works. It involves many Joins and a complex Where clause. But right before I run it for the full selection, I need to get a quick COUNT of the full resultset.

I tried to reuse my where clause and all my Joins and select from my top element, nvRoot, using cb.count. But I got the error Caused by: java.lang.IllegalStateException: No criteria query roots were specified.

     CriteriaBuilder cb = entityManager.getCriteriaBuilder();
     CriteriaQuery<Result> criteriaQuery = cb.createQuery(Result.class);
     Root<NvisionTrainee> nvRoot = criteriaQuery.from(Nv.class);
     Join<Object,Object> plans = nvRoot.join("plans", JoinType.LEFT);
     // etc., other Joins

     Predicate where = cb.conjunction();
     // Complex Where clause built...
     criteriaQuery.where(where);

     // --- HERE I NEED TO RUN A QUICK COUNT QUERY, with all Joins/Where as built
     // --- BUT THE BELOW DOESN'T WORK: 
     // --- Caused by: java.lang.IllegalStateException: No criteria query roots were specified
     CriteriaQuery<Long> cqCount = cb.createQuery(Long.class);
     cqCount.select(cb.count(nvRoot));     
     cqCount.distinct(true);
     cqCount.where(where);
     Long totalCount = entityManager.createQuery(cqCount).getSingleResult();

     // --- THIS FULL QUERY WORKS (THE REMAINDER), IT GETS ME MY FULL SELECTION
     CompoundSelection<Result> selectionFull = cb.construct(
                                   Result.class,
                                   nvRoot.get("firstName"),
                                   // etc. - many columns
                                   );
     criteriaQuery.select(selectionFull);
     criteriaQuery.distinct(true);
     TypedQuery<Result> query = entityManager.createQuery(criteriaQuery);
     List<Result> results = query.getResultList();

Per the comment below, I tried adding cqCount.from(Nv.class) in the code, but that gave me:

Invalid path: 'generatedAlias2.id'

Answer 1

The simplest workaround would be to extract the predicate-building part into a method and reuse it like so:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

//count query
CriteriaQuery<Long> cqCount = cb.createQuery(Long.class);
Root<NvisionTrainee> nvCountRoot = buildQuery(cqCount, ...);
cqCount.select(cb.count(nvCountRoot));     
cqCount.distinct(true);
Long totalCount = entityManager.createQuery(cqCount).getSingleResult();

//actual query
CriteriaQuery<Result> criteriaQuery = cb.createQuery(Result.class);
Root<NvisionTrainee> nvRoot = buildQuery(criteriaQuery, ...); //you might need to return other paths created inside buildQuery if you need to use them in the SELECT clause
CompoundSelection<Result> selectionFull = cb.construct(
    Result.class,
    nvRoot.get("firstName"),
    ...
    );
criteriaQuery.select(selectionFull);
criteriaQuery.distinct(true);
TypedQuery<Result> query = entityManager.createQuery(criteriaQuery);
List<Result> results = query.getResultList();

where buildQuery is defined like so:

private Root<NvisionTrainee> buildQuery(CriteriaQuery<?> query, ... /* possibly many other arguments*/) {
    Root<NvisionTrainee> nvRoot = query.from(Nv.class);
    Join<Object,Object> plans = nvRoot.join("plans", JoinType.LEFT);
    // etc., other Joins - build your WHERE clause here
    return nvRoot;
}