JPA 2.0: count for arbitrary CriteriaQuery?

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 equi开发者_JAVA技巧valent 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?

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

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

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();
  }

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

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

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.

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