JPA + Hibernate count(*) using CriteriaBuilder - w

Question

When trying to create a count(*) type query using CriteriaBuilder I get the below alias problem.

What changes should I make to the code below to get the count?

Constraints:

1. I have to use CriteriaBuilder/Query as the where clause has to be built dynamically based on values.
2. I need only COUNT, not the list of objects in memory.

Code sample snippet:

 Class<ReqStatCumulative> entityClass = ReqStatCumulative.class;
 @Override
    public long getCountForAlertConfig(AlertConfig cfg) {
        long count = 0L;
        if (null != cfg) {
            CriteriaBuilder qb = entityManager.getCriteriaBuilder();

            Metamodel model = entityManager.getMetamodel();
            EntityType<ReqStatCumulative> reqStatEntType_ = model.entity(entityClass);
            CriteriaQuery<ReqStatCumulative> cq = qb.createQuery(entityClass);
            Root<ReqStatCumulative> rootReqStatEnt = cq.from(reqStatEntType_);
            Path<Long> processTimeSeconds = rootReqStatEnt.<Long> get("processTimeSeconds");
            cq.where(qb.and(qb.greaterThan(processTimeSeconds, (long) cfg.getProcessTimeExceedsSec()),//
                    qb.lessThan(processTimeSeconds, (long) cfg.getProcessTimeExceedsSec() + 100))//
            );//
            findCountByCriteria(entityManager, cq, qb);
            log.debug("\n\t#####Alert desc:" + cfg.getDescription());
            log.debug("\n\t#####Alert count= " + count);
        } else {
            // Do nothing
        }
        return count;
    }

    public <T> Long findCountByCriteria(EntityManager em, CriteriaQuery<T> cqEntity, CriteriaBuilder qb) {
        CriteriaBuilder builder = qb;
        CriteriaQuery<Long> cqCount = builder.createQuery(Long.class);
        Root<?> entityRoot = cqCount.from(cqEntity.getResultType());
        cqCount.select(builder.count(entityRoot));
        cqCount.where(cqEntity.getRestriction());
        return em.createQuery(cqCount).getSingleResult();
    }

Log: I want generatedAlias0 to be used in all the where clause attributes instead of generatedAlias1.

select count(*) from abc.domain.ReqStatCumulative as **generatedAlias0** where ( **generatedAlias1**.processTimeSeconds>5L ) and ( **generatedAlias1**.processTimeSeconds<200L )

10:48:57.169 [main] DEBUG o.h.h.i.ast.QueryTranslatorImpl - parse() - HQL: select count(*) from abc.domain.ReqStatCumulative as generatedAlias0 where ( generatedAlias1.processTimeSeconds>5L ) and ( generatedAlias1.processTimeSeconds<200L )
10:48:57.169 [main] DEBUG o.h.h.i.ast.QueryTranslatorImpl - --- HQL AST ---
 \-[QUERY] Node: 'query'
    +-[SELECT_FROM] Node: 'SELECT_FROM'
    |  +-[FROM] Node: 'from'
    |  |  \-[RANGE] Node: 'RANGE'
    |  |     +-[DOT] Node: '.'
    |  |     |  +-[DOT] Node: '.'
    |  |     |  |  +-[IDENT] Node: 'abc'
    |  |     |  |  \-[IDENT] Node: 'domain'
    |  |     |  \-[IDENT] Node: 'ReqStatCumulative'
    |  |     \-[ALIAS] Node: '**generatedAlias0**'
    |  \-[SELECT] Node: 'select'
    |     \-[COUNT] Node: 'count'
    |        \-[ROW_STAR] Node: '*'
    \-[WHERE] Node: 'where'
       \-[AND] Node: 'and'
          +-[GT] Node: '>'
          |  +-[DOT] Node: '.'
          |  |  +-[IDENT] Node: '**generatedAlias1**'
          |  |  \-[IDENT] Node: 'processTimeSeconds'
          |  \-[NUM_LONG] Node: '5L'
          \-[LT] Node: '<'
             +-[DOT] Node: '.'
             |  +-[IDENT] Node: '**generatedAlias1**'
             |  \-[IDENT] Node: 'processTimeSeconds'
             \-[NUM_LONG] Node: '200L'

10:48:57.169 [main] DEBUG o.h.hql.internal.ast.ErrorCounter - throwQueryException() : no errors
10:48:57.169 [main] DEBUG o.h.h.i.antlr.HqlSqlBaseWalker - select << begin [level=1, statement=select]
10:48:57.169 [main] DEBUG o.h.h.internal.ast.tree.FromElement - FromClause{level=1} : erf.domain.ReqStatCumulative (generatedAlias0) -> reqstatcum0_
10:48:57.169 [main] ERROR o.h.hql.internal.ast.ErrorCounter -  Invalid path: 'generatedAlias1.processTimeSeconds'
10:48:57.215 [main] ERROR o.h.hql.internal.ast.ErrorCounter -  Invalid path: 'generatedAlias1.processTimeSeconds'
org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'generatedAlias1.processTimeSeconds'

Answer 1

I had the same problem, and I solved with:

CriteriaQuery<Long> countCriteria = cb.createQuery(Long.class);
Root<EntityA> countRoot = countCriteria.from(cq.getResultType());
Set<Join<EntityA, ?>> joins = originalEntityRoot.getJoins();
for (Join<EntityA, ?> join :  joins) {
    countRoot.join(join.getAttribute().getName());
}
countCriteria.select(cb.count(countRoot));
if(finalPredicate != null)
    countCriteria.where(finalPredicate);

TypedQuery<Long> queryCount = entityManager.createQuery(countCriteria);
Long count = queryCount.getSingleResult();

Where

originalEntityRoot is the main root where I did the query with the where clauses.

Answer 2

I was looking a more generic solution based on the OP's question. And just leaving a more generic solution based on @Hector's example:

public class CountQueryHelper<T> {

    final Class<T> typeParameterClass;

    public CountQueryHelper(Class<T> typeParameterClass) {
        this.typeParameterClass = typeParameterClass;
    }

    public CriteriaQuery<Long> getCountQuery(CriteriaQuery<T> originalQuery, EntityManager em) {
        CriteriaBuilder cb = em.getCriteriaBuilder();

        // create count query
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);


        // start copying root/joins/restrictions from  the original query

        // copy roots
        for (Root r : originalQuery.getRoots()) {
            Root root = countQuery.from(r.getModel());
            root.alias(r.getAlias());
        }

        // copy joins
        for (Root r : originalQuery.getRoots()) {
            Set<Join<T, ?>> joins = r.getJoins();
            for (Join<T, ?> join : joins) {
                for (Root countRoot : countQuery.getRoots()) {
                    try {
                        Join joinOnCount = countRoot.join(join.getAttribute().getName());
                        joinRecursive(joinOnCount, join);
                    } catch (IllegalArgumentException e) {
                        // attribute does not exist on this root
                    }
                }
            }
        }

        countQuery.select(cb.count(countQuery.from(this.typeParameterClass)));

        //  copy restrictions
        if (originalQuery.getRestriction() != null) {
            countQuery.where(originalQuery.getRestriction());
        }

        return countQuery;
    }

    private void joinRecursive(Join countJoins, Join<T, ?> originalJoin) {
        for(Join original : originalJoin.getJoins()) {
            Join<Object, Object> childJoin = countJoins.join(original.getAttribute().getName());
            joinRecursive(childJoin, original);
        }
    }
}

Answer 3

Your code fails because you are using different Root instances for the count and the where clauses: the first one (in order of definition) generates generatedAlias1 alias, and the other generates generatedAlias0. You need to refactor the code in order to use the same Root instance in both places:

CriteriaQuery<Long> cqCount = builder.createQuery(Long.class);
Root<ReqStatCumulative> entityRoot = cqCount.from(cqEntity.getResultType());
cqCount.select(builder.count(entityRoot));
Path<Long> processTimeSeconds = entityRoot.get("processTimeSeconds");
cqCount.where(qb.and(qb.greaterThan(processTimeSeconds, (long) cfg.getProcessTimeExceedsSec()),//
                qb.lessThan(processTimeSeconds, (long) cfg.getProcessTimeExceedsSec() + 100))//
                );//    
return em.createQuery(cqCount).getSingleResult();