count for JPA criteria query

sstelca · **Joined:** Tue Nov 16, 2010 5:21 am **Posts:** 2

Hello,

I am trying to write a method that takes a JPA CritieriaQuery and returns the number of rows of it. I need this for generic paging. Here's what I do:

Code:

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

And here's a test for this method:

Code:

// 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);

calling findCountByCriteria() on this query throws the exception:

Code:

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 this a Hibernate bug? Can anyone suggest how to write a method that takes a JPA query and returns its count?

Thanks in advance,

sstelca

vdim · **Joined:** Sun Oct 17, 2010 7:54 am **Posts:** 5

Hello,
Sorry, but I don't understand: where do you call method findCountByCriteria?

sstelca · **Joined:** Tue Nov 16, 2010 5:21 am **Posts:** 2

from within a spring-test-enabled JUnit test class. I don't think it should matter though, as all other tests for the same DAO I am writing succeed.

andrewshilliday · **Joined:** Thu Dec 16, 2010 3:03 pm **Posts:** 2

The problem is that your table aliases don't match up between the restrictions part and the selection part of the query (see in your query where the selection part uses "generatedAlias0" but the WHERE clause uses "generatedAlias1".

This happened because you called criteria.from() twice, once when you generated the criteria that you sent as input to the method, and once again inside the method. Each time you call it, you're returning a new root object with its own alias.

Rather than calling

Code:

Root<?> entityRoot = countCriteria.from(criteria.getResultType());

Try

Code:

Root<?> entityRoot = criteria.getRoots().iterator().next();

This will set entityRoot to the actual root of the criteria object, rather than a new root of the same class. You might need to add some extra smarts if your original query is a join of multiple tables, as you'll need to make sure that you select the correct root from the collection returned by getRoots(), but you could probably search it for a root of the correct type.

hallmit · **Posted:** Tue Aug 27, 2013 4:33 am

andrewshilliday wrote:

Try

Code:

Root<?> entityRoot = criteria.getRoots().iterator().next();

This will set entityRoot to the actual root of the criteria object, rather than a new root of the same class.

the call countCriteria.from(criteria.getResultType()) always generated a new root (even if you use criteria.getRoots().iterator().next()).

As workaround you should to set an alias on entityRoot in order to share the same "name" between restrictions part and selection part of the query. see this post for an example: https://forum.hibernate.org/viewtopic.php?f=1&t=1004089