In JPA 2 using CriteriaQuery how to count results

Question

I am new to JPA 2 and the CriteriaBuilder / CriteriaQuery API:

CriteriaQuery javadoc

CriteriaQuery in Java EE Tutorial 6

I would like to count the results of CriteriaQuery without loading them. Is it possible I did not find such a method, the only way to do this is:

 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<MyEntity> cq = cb .createQuery(MyEntityclass); // initialize predicates here return entityManager.createQuery(cq).getResultList().size(); 

And this may not be the right way to do it ...

Is there a solution?

Answer 1

A query of type MyEntity will return MyEntity . You need an inquiry for Long .

 CriteriaBuilder qb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long> cq = qb.createQuery(Long.class); cq.select(qb.count(cq.from(MyEntity.class))); cq.where(/*your stuff*/); return entityManager.createQuery(cq).getSingleResult(); 

Obviously, you will want to create your expression with any restrictions and groupings, etc. that you skipped in this example.

Answer 2

I sorted this using cb.createQuery () (without a result type parameter):

 public class Blah() { CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery query = criteriaBuilder.createQuery(); Root<Entity> root; Predicate whereClause; EntityManager entityManager; Class<Entity> domainClass; ... Methods to create where clause ... public Blah(EntityManager entityManager, Class<Entity> domainClass) { this.entityManager = entityManager; this.domainClass = domainClass; criteriaBuilder = entityManager.getCriteriaBuilder(); query = criteriaBuilder.createQuery(); whereClause = criteriaBuilder.equal(criteriaBuilder.literal(1), 1); root = query.from(domainClass); } public CriteriaQuery<Entity> getQuery() { query.select(root); query.where(whereClause); return query; } public CriteriaQuery<Long> getQueryForCount() { query.select(criteriaBuilder.count(root)); query.where(whereClause); return query; } public List<Entity> list() { TypedQuery<Entity> q = this.entityManager.createQuery(this.getQuery()); return q.getResultList(); } public Long count() { TypedQuery<Long> q = this.entityManager.createQuery(this.getQueryForCount()); return q.getSingleResult(); } } 

Hope this helps :)

Answer 3

 CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Long> cq = cb.createQuery(Long.class); cq.select(cb.count(cq.from(MyEntity.class))); return em.createQuery(cq).getSingleResult(); 

Answer 4

Like other answers, they are correct, but too simple, so for completeness I present below a code fragment for executing SELECT COUNT in a complex JPA query criteria (with several unions, selections, conditions).

This answer is slightly modified.

 public <T> long count(final CriteriaBuilder cb, final CriteriaQuery<T> selectQuery, Root<T> root) { CriteriaQuery<Long> query = createCountQuery(cb, selectQuery, root); return this.entityManager.createQuery(query).getSingleResult(); } private <T> CriteriaQuery<Long> createCountQuery(final CriteriaBuilder cb, final CriteriaQuery<T> criteria, final Root<T> root) { final CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); final Root<T> countRoot = countQuery.from(criteria.getResultType()); doJoins(root.getJoins(), countRoot); doJoinsOnFetches(root.getFetches(), countRoot); countQuery.select(cb.count(countRoot)); countQuery.where(criteria.getRestriction()); countRoot.alias(root.getAlias()); return countQuery.distinct(criteria.isDistinct()); } @SuppressWarnings("unchecked") private void doJoinsOnFetches(Set<? extends Fetch<?, ?>> joins, Root<?> root) { doJoins((Set<? extends Join<?, ?>>) joins, root); } private void doJoins(Set<? extends Join<?, ?>> joins, Root<?> root) { for (Join<?, ?> join : joins) { Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType()); joined.alias(join.getAlias()); doJoins(join.getJoins(), joined); } } private void doJoins(Set<? extends Join<?, ?>> joins, Join<?, ?> root) { for (Join<?, ?> join : joins) { Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType()); joined.alias(join.getAlias()); doJoins(join.getJoins(), joined); } } 

Hope this saves you some time.

Because the IMHO JPA API is not intuitive or readable.

Answer 5

This is a bit complicated, depending on the JPA 2 implementation you are using, it works for EclipseLink 2.4.1, but not for Hibernate, here is the general CriteriaQuery score for EclipseLink:

 public static Long count(final EntityManager em, final CriteriaQuery<?> criteria) { final CriteriaBuilder builder=em.getCriteriaBuilder(); final CriteriaQuery<Long> countCriteria=builder.createQuery(Long.class); countCriteria.select(builder.count(criteria.getRoots().iterator().next())); final Predicate groupRestriction=criteria.getGroupRestriction(), fromRestriction=criteria.getRestriction(); if(groupRestriction != null){ countCriteria.having(groupRestriction); } if(fromRestriction != null){ countCriteria.where(fromRestriction); } countCriteria.groupBy(criteria.getGroupList()); countCriteria.distinct(criteria.isDistinct()); return em.createQuery(countCriteria).getSingleResult(); } 

Answer 6

You can also use projections:

 ProjectionList projection = Projections.projectionList(); projection.add(Projections.rowCount()); criteria.setProjection(projection); Long totalRows = (Long) criteria.list().get(0); 

Answer 7

Use countDistinct instead of count .