Total row count for pagination using JPA Criteria API

Question

I am implementing "Advanced Search" kind of functionality for an Entity in my system such that user can search that entity using multiple conditions(eq,ne,gt,lt,like etc) on attributes of this entity. I am using JPA's Criteria API to dynamically generate the Criteria query and then using setFirstResult() & setMaxResults() to support pagination. All was fine till this point but now I want to show total number of results on results grid but I did not see a straight forward way to get total count of Criteria query.
This is how my code looks like:

CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Brand> cQuery = builder.createQuery(Brand.class); Root<Brand> from = cQuery.from(Brand.class); CriteriaQuery<Brand> select = cQuery.select(from); . . //Created many predicates and added to **Predicate[] pArray** . . select.where(pArray); // Added orderBy clause TypedQuery typedQuery = em.createQuery(select); typedQuery.setFirstResult(startIndex); typedQuery.setMaxResults(pageSize); List resultList = typedQuery.getResultList(); 

My result set could be big so I don't want to load my entities for count query, so tell me efficient way to get total count like rowCount() method on Criteria (I think its there in Hibernate's Criteria).

Answer 1

Thanks Vladimir! I took your idea and used separate count query to use my existing array of predicates in it. Final implementation looks like this:

CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaQuery<Brand> cQuery = builder.createQuery(Brand.class); Root<Brand> from = cQuery.from(Brand.class); CriteriaQuery<Brand> select = cQuery.select(from); . . //Created many predicates and added to **Predicate[] pArray** . . CriteriaQuery<Long> cq = builder.createQuery(Long.class); cq.select(builder.count(cq.from(Brand.class))); // Following line if commented causes [org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.enabled' [select count(generatedAlias0) from xxx.yyy.zzz.Brand as generatedAlias0 where ( generatedAlias1.enabled=:param0 ) and ( lower(generatedAlias1.description) like :param1 )]] em.createQuery(cq); cq.where(pArray); Long count = em.createQuery(cq).getSingleResult(); . . select.where(pArray); . . // Added orderBy clause TypedQuery typedQuery = em.createQuery(select); typedQuery.setFirstResult(startIndex); typedQuery.setMaxResults(pageSize); List resultList = typedQuery.getResultList() 

Though this is working fine but still I am not sure why I have to write

em.createQuery(cq); 

to get it working. Any Idea?