JPA + Hibernate count(*) using CriteriaBuilder - with generatedAlias

jpa count query example
criteriabuilder count group by
jpa count results
how to get count of records in jpa
hibernate criteriabuilder join example
hibernate count query
no criteria query roots were specified
jpa pagination count query

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'

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

In JPA 2, using a CriteriaQuery, how to count results, A query of type MyEntity is going to return MyEntity . You want a query for a Long . CriteriaBuilder qb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long>  hibernate-core 5.3.5.Final: Hibernate's core ORM functionality. Implements javax.persistence:javax.persistence-api version 2.2 hibernate-jpamodelgen 5.3.5.Final: Annotation Processor to generate JPA 2 static metamodel classes.


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.

javax.persistence.criteria.CriteriaBuilder.count java code examples , In JPA 2, using a CriteriaQuery, how to count results. CriteriaBuilder qb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long> cq = qb. JPA needs the sql-statement-aliases for itself to use when generating sql-statements. For the nested query hints to work, the relationship needs to be specified in the entities. For example, if your Person entity have a OneToMany mapping to a House entity - and the property name in the Person class is livedInHouses.


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

How to count the results from a Custom Entity using JPA - iOS, return em.createQuery(cqCount).getSingleResult(); } As also described in this post: JPA + Hibernate count(*) using CriteriaBuilder - with generatedAlias  I try to count number of rows using JPA.I want to use where clause however I can't. CriteriaBuilder qb = entityManager.getCriteriaBuilder(); CriteriaQuery<Long>; cq = qb.createQuery(Long.cl


How to pass list of arguments in criteria query , jpa? - jpa - iOS, I am new in JPA but have to implement my project in that technology what I'm in this post: JPA + Hibernate count(*) using CriteriaBuilder - with generatedAlias  Select distinct GLOBAL_LABEL ,count (distinct FILE) from MY_TABLE group by GLOBAL_LABEL Secondly it's always good to not name your columns with primary names to avoid problems. Finaly you can use this as your HQL query (with no magic) : Select distinct ge.globalLabel,count (distinct ge.file) from GlobalEntity ge group by ge.globalLabel


org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path on , _codigo' [select count(generatedAlias0) from com.its.entidades.db. The weird thing is that if I comment the quoted line, and in consequence the two lines ProductoService.java setListadoModelo(parametros); try { CriteriaBuilder cb = em. The JPA Criteria API can be used to easily add multiple AND/OR conditions when querying records in a database. In this tutorial, we'll explore a quick example of JPA criteria queries that combine multiple AND/OR predicates. If you're not familiar with predicates, we suggest reading about the basic JPA criteria queries first.


count JPA + Hibernate (*) folosind CriteriaBuilder, Codul eșuează pentru că sunt utilizați diferite Root instanțe pentru numărarea și clauzele în cazul în care: prima (în ordinea definiției) generează  hibernate-core 5.4.1.Final: Hibernate's core ORM functionality. Implements javax.persistence:javax.persistence-api version 2.2 hibernate-jpamodelgen 5.4.1.Final: Annotation Processor to generate JPA 2 static metamodel classes.