JPA + Hibernate使用CriteriaBuilder计数（*） - 使用generatedAlias [英] JPA + Hibernate count(*) using CriteriaBuilder - with generatedAlias

查看：1565
本文介绍了JPA + Hibernate使用CriteriaBuilder计数（*） - 使用generatedAlias的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

当使用 CriteriaBuilder 创建一个 count（*）类型查询时，我得到下面的别名问题。



我应该对下面的代码做些什么修改以获得计数？



限制：


  1. 我必须使用CriteriaBuilder / Query作为where子句必须基于值动态构建。

  2. I只需要COUNT，而不是内存中的对象列表。

代码示例片段： 

  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）; 
根< ReqStatCumulative> rootReqStatEnt = cq.from（reqStatEntType_）; 
 Path< Long> processTimeSeconds = rootReqStatEnt。< Long>获得（ processTimeSeconds）; 
 cq.where（qb.and（qb.greaterThan（processTimeSeconds，（long）cfg.getProcessTimeExceedsSec（）），// 
 qb.lessThan（processTimeSeconds，（long）cfg.getProcessTimeExceedsSec（）+ 100 ））// 
）; // 
 findCountByCriteria（entityManager，cq，qb）; 
 log.debug（\\\
\t ##### Alert desc：+ cfg.getDescription（））; 
 log.debug（\\\
\t ##### Alert count =+ count）; 
} else {
 //什么也不做
} 
 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（）; 
 
 $ / code>

日志：我希望generateAlias0用于所有where子句的属性，而不是generatedAlias1。 

  select count（*）from abc.domain.ReqStatCumulative as ** generatedAlias0 ** where（** generatedAlias1 ** （*）.processTimeSeconds> 5L）和（** generatedAlias1 **。processTimeSeconds <200L）
 
 10：48：57.169 [main] DEBUG ohhiast.QueryTranslatorImpl  -  parse（） -  HQL：select count从abc.domain.ReqStatCumulative as generatedAlias0 where（generatedAlias1.processTimeSeconds> 5L）and（generatedAlias1.processTimeSeconds< 200L）
 10：48：57.169 [main] DEBUG ohhiast.QueryTranslatorImpl  -  --- HQL AST --- 
 \- [QUERY]节点：'query'
 +  -  [SELECT_FROM]节点：'SELECT_FROM'
 | +  -  [FROM]节点：'from'
 | | \- [RANGE]节点：'RANGE'
 | | +  -  [DOT]节点：'。'
 | | | +  -  [DOT]节点：'。'
 | | | | +  -  [IDENT]节点：'abc'
 | | | | \- [IDENT]节点：'domain'
 | | | \- [IDENT]节点：'ReqStatCumulative'
 | | \- [ALIAS]节点：'** generatedAlias0 **'
 | \- [SELECT]节点：'select'
 | \- [COUNT]节点：'count'
 | \- [ROW_STAR]节点：'*'
 \- [WHERE]节点：'其中'
 \- [AND]节点：'和'
 +  -  [GT]节点：'>'
 | +  -  [DOT]节点：'。'
 | | +  -  [IDENT]节点：'** generatedAlias1 **'
 | | \- [IDENT]节点：'processTimeSeconds'
 | \- [NUM_LONG]节点：'5L'
 \- [LT]节点：'<'
 +  -  [DOT]节点：'。'
 | +  -  [IDENT]节点：'** generatedAlias1 **'
 | \- [IDENT]节点：'processTimeSeconds'
 \- [NUM_LONG]节点：'200L'
 
 10：48：57.169 [main] DEBUG ohhql.internal.ast。 ErrorCounter  -  throwQueryException（）：没有错误
 10：48：57.169 [main] DEBUG ohhiantlr.HqlSqlBaseWalker  - 选择<< 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]错误ohhql.internal.ast.ErrorCounter  - 无效路径：'generatedAlias1.processTimeSeconds'
 10：48：57.215 [main]错误ohhql.internal.ast .ErrorCounter  - 无效路径：'generatedAlias1.processTimeSeconds'
 org.hibernate.hql.internal.ast.InvalidPathException：路径无效：'generatedAlias1.processTimeSeconds'


解决方案

您的代码失败是因为您使用不同的 Root 和where子句：第一个（按定义顺序）生成 generatedAlias1 别名，另一个生成 generatedAlias0 。您需要重构代码才能在两个地方使用相同的 Root 实例： 

  CriteriaQuery中<长> cqCount = builder.createQuery（Long.class）; 
根< ReqStatCumulative> entityRoot = cqCount.from（cqEntity.getResultType（））; 
 cqCount.select（builder.count（entityRoot））; 
 Path< Long> processTimeSeconds = entityRoot.get（processTimeSeconds）; 
 qq.andThan（processTimeSeconds，（long）cfg.getProcessTimeExceedsSec（））+ 100 ））// 
）; // 
 return em.createQuery（cqCount）.getSingleResult（）;


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

这篇关于JPA + Hibernate使用CriteriaBuilder计数（*） - 使用generatedAlias的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
J2EE最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆