JPA 2.0: count for arbitrary CriteriaQuery?

Question

I am trying to implement the following convenience method:

/**
 * Counts the number of results of a search.
 * @param criteria The criteria for the query.
 * @return The number of results of the query.
 */
public int findCountByCriteria(CriteriaQuery<?> criteria);

In Hibernate, this is done by

criteria.setProjection(Projections.rowCount());

What is the equivalent to the above in JPA? I found numerous simple count examples, but none of them made use of a CriteriaQuery whose row count should be determined.

EDIT:

I unfortunately found out that @Pascal's answer is not the correct one. The problem is very subtle and only shows up when you use joins:

// Same query, but readable:
// SELECT *
// FROM Brain b
// WHERE b.iq = 170

CriteriaQuery<Person> query = cb.createQuery(Person.class);
Root<Person> root = query.from(Person.class);
Join<Object, Object> brainJoin = root.join("brain");
Predicate iqPredicate = cb.equal(brainJoin.<Integer>get("iq"), 170);
query.select(root).where(iqPredicate);

When calling findCountByCriteria(query), it dies with the following exception:

org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.iq' [select count(generatedAlias0) from xxx.tests.person.dom.Person as generatedAlias0 where generatedAlias1.iq=170]

Is there any other way to provide such a CountByCriteria method?

Answer 1

I wrote a utility class, JDAL JpaUtils to do it:

• count results: Long count = JpaUtils.count(em, criteriaQuery);
• copy CriteriaQueries: JpaUtils.copyCriteria(em, criteriaQueryFrom, criteriaQueryTo);
• get count criteria: CriteriaQuery<Long> countCriteria = JpaUtils.countCriteria(em, criteria)

and so on...

If you are interested in the source code, see JpaUtils.java

Answer 2

i do somethig like thath with hibernate and criteria api

public Long getRowsCount(List<Criterion> restrictions ) {
       Criteria criteria = getSession().createCriteria(ThePersistenclass.class);
       for (Criterion x : restrictions)
             criteria.add(x);
   return criteria.setProjection(Projections.rowCount()).uniqueResult();        

}

hope help

Answer 3

Are you looking for something like this?

/**
 * Counts the number of results of a search.
 * 
 * @param criteria The criteria for the query.
 * @return The number of results of the query.
 */
public <T> Long findCountByCriteria(CriteriaQuery<?> criteria) {
    CriteriaBuilder builder = em.getCriteriaBuilder();

    CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
    Root<?> entityRoot = countCriteria.from(criteria.getResultType());
    countCriteria.select(builder.count(entityRoot));
    countCriteria.where(criteria.getRestriction());

    return em.createQuery(countCriteria).getSingleResult();
}

That you could use like this:

// a search based on the Criteria API
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Person> criteria = builder.createQuery(Person.class);
Root<Person> personRoot = criteria.from(Person.class);
criteria.select(personRoot);
Predicate personRestriction = builder.and(
    builder.equal(personRoot.get(Person_.gender), Gender.MALE),
    builder.equal(personRoot.get(Person_.relationshipStatus), RelationshipStatus.SINGLE)
);
criteria.where(personRestriction);
//...

// and to get the result count of the above query
Long count = findCountByCriteria(criteria);

PS: I don't know if this is the right/best way to implement this, still learning the Criteria API...

Answer 4

None of the above solutions work for EclipseLink 2.4.1, they all ended with a count on a Cartesian product (N^2), here is a small hack for EclipseLink, the only drawback is that I don't know what will happen if you are selecting FROM more than one Entity, it will try to count from the 1st found Root of your CriteriaQuery, this solution DOESN'T work for Hibernate though (JDAL does, but JDAL doesn't work 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 5

The whole idea of criteria queries is that they are strongly typed. So each solution, where you're using raw types (without generics in CriteriaQuery or Root or Root) - these solutions are against that main idea. I just run into the same issue and I'm struggling to solve it in a "proper" (along with JPA2) way.

Answer 6

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<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 it helps :)

What I did is something like a builder of a CriteriaBuilder where you can build a query and call list() or count() with the same criteria restrictions