In JPA 2 using a CriteriaQuery how to count results

Question

I am rather new to JPA 2 and it s CriteriaBuilder   CriteriaQuery API   CriteriaQuery javadoc  CriteriaQuery in the Java EE 6 tutorial  I would like to count the results of a CriteriaQuery without actually retrieving them  Is that possible  I did not find any such method  the only way would be to do this   CriteriaBuilder cb   entityManager getCriteriaBuilder     CriteriaQuery lt MyEntity gt  cq   cb          createQuery MyEntityclass       initialize predicates here  return entityManager createQuery cq  getResultList   size      And that can t be the proper way to do it     Is there a solution

User · Answer

It is a bit tricky  depending on the JPA 2 implementation you use  this one works for EclipseLink 2 4 1  but doesn t for Hibernate  here a generic CriteriaQuery count for EclipseLink   public static Long count final EntityManager em  final CriteriaQuery lt   gt  criteria          final CriteriaBuilder builder em getCriteriaBuilder        final CriteriaQuery lt Long gt  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          The other day I migrated from EclipseLink to Hibernate and had to change my count function to the following  so feel free to use either as this is a hard problem to solve  it might not work for your case  it has been in use since Hibernate 4 x  notice that I don t try to guess which is the root  instead I pass it from the query so problem solved  too many ambiguous corner cases to try to guess     public static  lt T gt  long count EntityManager em Root lt T gt  root CriteriaQuery lt T gt  criteria          final CriteriaBuilder builder em getCriteriaBuilder        final CriteriaQuery lt Long gt  countCriteria builder createQuery Long class        countCriteria select builder count root         for Root lt   gt  fromRoot   criteria getRoots           countCriteria getRoots   add fromRoot              final Predicate whereRestriction criteria getRestriction        if whereRestriction  null         countCriteria where whereRestriction              final Predicate groupRestriction criteria getGroupRestriction        if groupRestriction  null         countCriteria having groupRestriction              countCriteria groupBy criteria getGroupList         countCriteria distinct criteria isDistinct         return em createQuery countCriteria  getSingleResult

User · Answer

I ve sorted this out using the cb createQuery    without the result type parameter    public class Blah          CriteriaBuilder criteriaBuilder   entityManager getCriteriaBuilder        CriteriaQuery query   criteriaBuilder createQuery        Root lt Entity gt  root      Predicate whereClause      EntityManager entityManager      Class lt Entity gt  domainClass           Methods to create where clause          public Blah EntityManager entityManager  Class lt Entity gt  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 lt Entity gt  getQuery             query select root           query where whereClause           return query             public CriteriaQuery lt Long gt  getQueryForCount             query select criteriaBuilder count root            query where whereClause           return query             public List lt Entity gt  list             TypedQuery lt Entity gt  q   this entityManager createQuery this getQuery             return q getResultList               public Long count             TypedQuery lt Long gt  q   this entityManager createQuery this getQueryForCount             return q getSingleResult              Hope it helps

User · Answer

A query of type MyEntity is going to return MyEntity   You want a query for a Long   CriteriaBuilder qb   entityManager getCriteriaBuilder    CriteriaQuery lt Long gt  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 build up your expression with whatever restrictions and groupings etc you skipped in the example

User · Answer

As others answers are correct  but too simple  so for completeness I m presenting below code snippet to perform SELECT COUNT on a sophisticated JPA Criteria query  with multiple joins  fetches  conditions    It is slightly modified this answer   public  lt T gt  long count final CriteriaBuilder cb  final CriteriaQuery lt T gt  selectQuery          Root lt T gt  root        CriteriaQuery lt Long gt  query   createCountQuery cb  selectQuery  root       return this entityManager createQuery query  getSingleResult       private  lt T gt  CriteriaQuery lt Long gt  createCountQuery final CriteriaBuilder cb          final CriteriaQuery lt T gt  criteria  final Root lt T gt  root         final CriteriaQuery lt Long gt  countQuery   cb createQuery Long class       final Root lt T gt  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 lt   extends Fetch lt      gt  gt  joins  Root lt   gt  root        doJoins  Set lt   extends Join lt      gt  gt   joins  root      private void doJoins Set lt   extends Join lt      gt  gt  joins  Root lt   gt  root        for  Join lt      gt  join   joins            Join lt      gt  joined   root join join getAttribute   getName    join getJoinType             joined alias join getAlias             doJoins join getJoins    joined            private void doJoins Set lt   extends Join lt      gt  gt  joins  Join lt      gt  root        for  Join lt      gt  join   joins            Join lt      gt  joined   root join join getAttribute   getName    join getJoinType             joined alias join getAlias             doJoins join getJoins    joined             Hope it saves somebody s time    Because IMHO JPA Criteria API is not intuitive nor quite readable

User · Answer

You can also use Projections   ProjectionList projection   Projections projectionList    projection add Projections rowCount     criteria setProjection projection    Long totalRows    Long  criteria list   get 0

User · Answer

With Spring Data Jpa  we can use this method                  non-Javadoc          see org springframework data jpa repository JpaSpecificationExecutor count org springframework data jpa domain Specification               Override     public long count  Nullable Specification lt T gt  spec            return executeCountQuery getCountQuery spec  getDomainClass

User · Answer

CriteriaBuilder cb   em getCriteriaBuilder    CriteriaQuery lt Long gt  cq   cb createQuery Long class   cq select cb count cq from MyEntity class      return em createQuery cq  getSingleResult

[java] In JPA 2, using a CriteriaQuery, how to count results

Examples related to java

Examples related to jpa-2.0

Examples related to criteriaquery