JPA create count query from existing CriteriaQuery

Question

I have a query with some predicates, I need to count total records for paging.

Currently, what I'm doing is declare 2 roots for the query to get result list (1) and the count query (2), then with each predicate, duplicate it with different root like this

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<A> cq = cb.createQuery(A.class);
        Root<A> root = cq.from(A.class);
        CriteriaQuery<Long> cq = cb.createQuery(A.class);
        Root<A> rootCount = countQuery.from(A.class);

        List<Predicate> predicates = new ArrayList<>();
        List<Predicate> predicatesCount = new ArrayList<>();
        
        Predicate p = cb.equal(root.get(A.ID), 1);
        predicates.add(p);
        Predicate p1 = cb.equal(rootCount.get(A.ID), 1);
        predicatesCount.add(p1);
        ...
        // execute both query to get result

So the question is:

Is it possible to create count query from query (1)? Or something to reuse the predicates with count query?

Thanks for reading!

score 0 · Answer 1 · answered Dec 26 '20 at 11:26

The below example showcases setting up a criteria builder/predicate restrictions, then reusing that to do a count query as well.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<EntityStub> criteriaQuery = builder.createQuery(EntityStub.class);
Root<EntityStub> entity_ = criteriaQuery.from(EntityStub.class);
entity_.alias("entitySub"); //assign alias to entity root

criteriaQuery.where(builder.equal(entity_.get("message"), "second"));

// Generic retrieve count
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<T> entity_ = countQuery.from(criteriaQuery.getResultType());
entity_.alias("entitySub"); //use the same alias in order to match the restrictions part and the selection part
countQuery.select(builder.count(entity_));
Predicate restriction = criteriaQuery.getRestriction();
if (restriction != null) {
  countQuery.where(restriction); // Copy restrictions
}
Long count = entityManager.createQuery(countQuery).getSingleResult();

See if that helps you, take note of the root alias, and when doing a Count Query, make sure the Entity class type is Long.class

https://forum.hibernate.org/viewtopic.php?p=2471522#p2471522

So the reason I can't use same predicates is because of the alias? Your code will assign alias for both root to be the same so jpa understands? I will try it later, thanks! — ccr, Dec 26 '20 at 21:08

score 0 · Answer 2 · answered Dec 28 '20 at 12:12

You could use Blaze-Persistence to generate the count query for you as it's not that easy to implement such a count query efficiently.

Blaze-Persistence is a library that works on top of JPA/Hibernate and adds support for advanced SQL constructs, rich pagination support and much more. It also has a JPA Criteria implementation which you can use as a drop-in replacement. You can then convert this query to a Blaze-Persistence Core query builder which allows to generate a count query: https://github.com/Blazebit/blaze-persistence#jpa-criteria-api-quick-start

score 0 · Answer 3 · answered Apr 08 '21 at 15:51

0

I think this guy answered your question with its utility class like so :

Long count = JpaUtils.count(entityManager, criteriaQuery);

https://stackoverflow.com/a/9246377/5611906

answered Apr 08 '21 at 15:51

TCH

421
1
6
25

JPA create count query from existing CriteriaQuery

3 Answers3