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

if you want the result and the count of all elements like Spring Data's Page-Element you can do two queries. What you can do is to separate the criteria from the query-execution.

Example to find Users by City

 public List<User> getUsers(int userid, String city, other values ...) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> q = cb.createQuery(User.class);
    Root<User> c = q.from(User.class);

    List<Predicate> conditions = createConditions(c, cb, userid, city, ...other values);
    List<User> users = em.createQuery(q.select(c).where(conditions.toArray(new Predicate[] {})).distinct(true))
            .setMaxResults(PAGE_ELEMENTS).setFirstResult(page * PAGE_ELEMENTS).getResultList();
    return users;
}

addiional to the getUser Method you can build a second that will count your elements

public Long getElemCount(int userid,  String city, ...other values) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Long> q = cb.createQuery(Long.class);
    Root<Location> root = q.from(Location.class);

    List<Predicate> conditions = createConditions(root, cb, userid, page, city, filter, module, isActive);
    Long userCount = em.createQuery(q.select(cb.count(root)).where(conditions.toArray(new Predicate[] {})).distinct(true))
            .getSingleResult();

    return userCount;
}

and the createConditions Method will handle both, so you do not have to duplicate your logic for the criteria.

<T> List<Predicate> createConditions(Root<T> root, CriteriaBuilder cb, int userid, String city, ... other values) {

    Join<User, SecondEntity> usr = root.join("someField");
    // add joins as you wish

    /*
     * Build Conditions
     */
    List<Predicate> conditions = new ArrayList<>();

    conditions.add(cb.equal(root.get("id"), userid));

    if (!city.equals("")) {
       conditions.add(cb.like(...));
    }

   // some more conditions...

    return conditions;
}

in your contoller you can do something like

long elementCount = yourCriteriaClassInstance.getElementCount(...); List users = yourCriteriaClassInstance.getUsers(...)