Total row count for pagination using JPA Criteria

问题:

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).

回答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?

回答2:

Why don't you just use count?

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> cQuery = builder.createQuery(Long.class);
Root<Brand> from = cQuery.from(Brand.class);
CriteriaQuery<Long> select = cQuery.select(builder.count(from));
.
.
//Created many predicates and added to **Predicate[] pArray**
.
.
select.where(pArray);
// Added orderBy clause
TypedQuery<Long> typedQuery = em.createQuery(select);
typedQuery.setFirstResult(startIndex);
//typedQuery.setMaxResults(pageSize);
// here is the size of your query 
Long result = typedQuery.getSingleResult();

回答3:

If you're using Hibernate as your JPA-Provider have a look at projections, especially Projections.rowCount().

You might have to execute the query twice though, first get the count then get the results.

Note that for plain JPA you might need some other approach.