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'

Well, you never call `from` on `cqCount`, so the query is incomplete. Why not simply extract the common part of both queries to a method and then use it twice? — crizzis, Jul 26 '20 at 17:15
Please provide a more detailed answer, with specific code or pseudo-code, so it's easier to follow, thanks, much appreciated. — gene b., Jul 26 '20 at 17:16
I'm a little confused, was your comment directed at me? Because my unwillingness to expand my suggestion into a full-fledged answer is precisely why I made it into a comment ;) — crizzis, Jul 26 '20 at 17:25
Yes, the comment was directed at you. A full answer would be easier to follow, particularly with "extracting/refactoring the method" and showing how it's done along with the count. Thanks. — gene b., Jul 26 '20 at 17:27
BTW adding `cqCount.from(Nv.class)` didn't work either, `Invalid path: 'generatedAlias2.id'` — gene b., Jul 26 '20 at 17:38
Yeah, it won't work, predicates cannot be reused across queries. See my answer — crizzis, Jul 26 '20 at 17:41

crizzis · Accepted Answer · 2020-07-26T20:34:14.407

2

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;
}

edited Jul 26 '20 at 20:34

answered Jul 26 '20 at 17:40

crizzis

9,978
2
28
47

1

Thanks, but this doesn't include the `where` building/setting. The `where`-building takes place **outside** of `buildQuery()` so it can't find all those Join objects that I created, like `plans` etc. – gene b. Jul 26 '20 at 19:37
There's also one other problem. When doing the CompoundSelection, I select some results from other tables, such as: `plans.get("someField")`. But the variable `plans` is no longer visible outside `buildQuery()`. – gene b. Jul 26 '20 at 19:45
1

I'm afraid you misunderstood my previous comments. It is **not possible** to reuse the search criteria in multiple queries. You **have to** repeat the whole process of assembling the predicates etc. for each query. I merely suggested ways to organize the code around that limitation – crizzis Jul 26 '20 at 19:46
And yes, if you're planning on selecting fields from joined entities, you need to return them all from `buildQuery`, in some form or another, to be able to reference them later on – crizzis Jul 26 '20 at 19:48
So you're saying that the `where` building has to be put into the method `buildQuery`? – gene b. Jul 26 '20 at 19:49
I'm just elaborating on the 'extract the common part of both queries to a method and then use it twice' idea from my original comment. Yes, that's exactly what I'm suggesting – crizzis Jul 26 '20 at 19:52
Thanks, this worked. Before I accept the answer, for the benefit of others reading this, could you edit your code with these corrections: (1) Typo: in `buildQuery` the 1st line should be the param, `query` not `criteriaQuery`; (2) Add a comment saying "Build Where-clause here" at the end of `buildQuery`; (3) I return a HashMap for all later selections. That's what `buildQuery` should return. You can also add a brief comment there about this as well. Thanks again for your help!!! I really appreciate it! – gene b. Jul 26 '20 at 20:29

score 0 · Answer 2 · answered May 18 '22 at 16:43

Aliases for roots are generated in some random manner between queries so let's hardcode them.

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

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


 CriteriaQuery<Long> cqCount = cb.createQuery(Long.class);
 // -- Added additional roots with the same alias names --
 Root<NvisionTrainee> nvRootCqCount  = cqCount.from(Nv.class);
 nvRootCqCount.alias("nvRoot");
 Join<Object,Object> plansCqCount = nvRootCqCount.join("plans", JoinType.LEFT);
 plansCqCount.alias("plansRoot");
 // etc., other Joins

 cqCount.select(cb.count(nvRootCqCount));     
 cqCount.distinct(true);
 // -- and here 'where' substituted with 'criteriaQuery.getRestriction()' --
 cqCount.where(criteriaQuery.getRestriction());
 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();

Written by hand so I'm not sure if this works. I have had similar problem with error: Invalid path: 'generatedAlias2.id'.

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

2 Answers2