How to count the number of rows of a JPA 2 CriteriaQuery in a generic JPA DAO?

Question

I'm new in JPA and want to implement a generic JPA DAO and need to find the number of rows of a query result set to implement pagination. After searching the web, I can't find a practical way to do that. Here is the code suggested in many articles:

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

However, that code doesn't work when using join. Is there any way to count the rows of a query result set using the JPA Criteria API?

UPDATE : here is the code that create CriteriaQuery :

    CriteriaQuery<T> queryDefinition = criteriaBuilder.createQuery(this.entityClass);
    Root<T> root = queryDefinition.from(this.entityClass);

and some joins may be added to the root until the query have been executed:

public Predicate addPredicate(Root<T> root) {
                Predicate predicate = getEntityManager().getCriteriaBuilder().ge(root.join(Entity_.someList).get("id"), 13);
                return predicate;
}

and the generated exception is like :

org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select count(generatedAlias0) from entity.Entity as generatedAlias0 where ( generatedAlias0.id>=13L ) and ( (generatedAlias1.id<=34L ) )]

which generatedAlias1 should be on Entity and generatedAlias0 should be on the association that I joined on that. Note that I implement Join properly because when I execute query without count query it executes without error and the Join works properly but when I try to execute count query it throws exception.

Solution 1

I've done this:

public Long getRowCount(CriteriaQuery criteriaQuery,CriteriaBuilder criteriaBuilder,Root<?> root){
    CriteriaQuery<Long> countCriteria = criteriaBuilder.createQuery(Long.class);
    Root<?> entityRoot = countCriteria.from(root.getJavaType());
    entityRoot.alias(root.getAlias());
    doJoins(root.getJoins(),entityRoot);
    countCriteria.select(criteriaBuilder.count(entityRoot));
    countCriteria.where(criteriaQuery.getRestriction());
    return this.entityManager.createQuery(countCriteria).getSingleResult();
}

private void doJoins(Set<? extends Join<?, ?>> joins,Root<?> root_){
    for(Join<?,?> join: joins){
        Join<?,?> joined = root_.join(join.getAttribute().getName(),join.getJoinType());
        doJoins(join.getJoins(), joined);
    }
}

private void doJoins(Set<? extends Join<?, ?>> joins,Join<?,?> root_){
    for(Join<?,?> join: joins){
        Join<?,?> joined = root_.join(join.getAttribute().getName(),join.getJoinType());
        doJoins(join.getJoins(),joined);
    }
}

of course you do not need Root as input parameter you could get it from criteria query,

Solution 2

@lubo08 gave correct answer - kudos for him. But for two corner cases his/her code won't work:

• When criteria query's restrictions use aliases for joins - then COUNT also require these aliases to be set.
• When criteria query use fetch join [root.fetch(..) instead of root.join(..)]

So for completeness I dared to improve his/her solution and present below:

public <T> long count(final CriteriaBuilder cb, final CriteriaQuery<T> criteria,
        Root<T> root) {
    CriteriaQuery<Long> query = createCountQuery(cb, criteria, root);
    return this.entityManager.createQuery(query).getSingleResult();
}

private <T> CriteriaQuery<Long> createCountQuery(final CriteriaBuilder cb,
        final CriteriaQuery<T> criteria, final Root<T> root) {

    final CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    final Root<T> 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<? extends Fetch<?, ?>> joins, Root<?> root) {
    doJoins((Set<? extends Join<?, ?>>) joins, root);
}

private void doJoins(Set<? extends Join<?, ?>> joins, Root<?> root) {
    for (Join<?, ?> join : joins) {
        Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType());
        joined.alias(join.getAlias());
        doJoins(join.getJoins(), joined);
    }
}

private void doJoins(Set<? extends Join<?, ?>> joins, Join<?, ?> root) {
    for (Join<?, ?> join : joins) {
        Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType());
        joined.alias(join.getAlias());
        doJoins(join.getJoins(), joined);
    }
}

Although it is still not perfect, because only one root is honored.
But I hope it helps somebody.

Solution 3

I can't tell you where your problem is, but I can tell you that count queries with joins work well, at least in eclipselink jpa. My guess is that this is standard stuff, so it should work also in hibernate. I would start by simplifying your code in order to catch where the problem is. I see that you have copied some pieces of your cont query from your main query. Maybe you can try to change a little bit this approach, just for debugging purpose.

What I do usually is:

CriteriaQuery cqCount = builder.createQuery();
Root<T> root = cq.from(T.class);
cqCount.select(builder.count(root)); 
ListJoin<T, Entity> join = root.join(T_.someList);
Predicate predicate = builder.ge(join.get(Entity_.id), "myId"); 
cqCount.where(predicate);
TypedQuery<Long> q = em.createQuery(cqCount);

Looking at your pseudo-code, it seems that you are using the wrong class in the join method: it must be the starting class, not the target class.

Comments

[Raindal]

Reading your simple answer after a good night just got me out of 10 hours of struggling with this join query and criteria stuff... : )

[DaniEll]

I had to add a Predicate cb.equal(countRoot, root), otherwise there is a "cartesian join"