如何在Hibernate中使用HAVING COUNT（*） [英] HOW to use HAVING COUNT(*) with hibernate

问题描述

我需要创建一个查询，并且需要 COUNT（*）和 HAVING COUNT（*）= x 。

我使用的解决方法是使用 CustomProjection 类，我在某处下载。

这是我尝试实现的SQL：

$ p $ select count（* ）as y0_，this_.ensayo_id as y1_ from Repeticiones this_
inner join Lineas linea1_ on this_.linea_id = linea1_.id
where this.pesoKGHA> 0.0 and this_.nroRepeticion = 1 and linea1_.id in 18,24）
group by this_.ensayo_id
count（*）= 2


这是代码，我使用 Projection Hibernate类：

  critRepeticion.setProjection（Projections.projectionList（）
 .add（Projections.groupProperty（ensayo））
 .add（CustomProjections.groupByHaving（ensayo_id，Hibernate.LONG，COUNT ensayo_id）=+ String.valueOf（lineas.size（）））
 .ad d（Projections.rowCount（））
）; 
  

错误是：

 ！STACK 0 
 java.lang.NullPointerException $ b $ org.hibernate.criterion.ProjectionList.toSqlString（ProjectionList.java:50）
 org.hibernate.loader .criteria.CriteriaQueryTranslator.getSelect（CriteriaQueryTranslator.java:310）
位于org.hibernate.loader.criteria.CriteriaJoinWalker。< init>（CriteriaJoinWalker.java:71）
位于org.hibernate.loader。 criteria.CriteriaLoader。< init>（CriteriaLoader.java:67）
 at org.hibernate.impl.SessionImpl.list（SessionImpl.java:1550）
 at org.hibernate.impl.CriteriaImpl.list （CriteriaImpl.java:283）
 at ar.com.cse.cseagro.controller.RepeticionController.buscarEnsayo（RepeticionController.java:101）
  

如果我用 CustomProjections 类注释行，查询就可以工作，但我没有得到在SQL中具有COUNT（*）过滤器...

基本上，查询尝试在master - detail中检索chema，所有主记录中的细节清单同时出现，比如你想知道哪些发票有两种产品，A和B。

这就是为什么如果我在 IN 子句中有3个项目，我需要使用 HAVING COUNT = 3 子句。

任何想法或建议？
此致，

解决方案

我找到了问题所在。我将CusotmProjections类替换为：

  .add（Projections.sqlGroupProjection（ensayo_id，groupBy，alias，types））; 
  

其中groupBy，别名和类型是：

  String groupBy =ensayo_id+having+count（*）=+ String.valueOf（lineas.size（））; 
 String [] alias = new String [1]; 
 Alias [0] =ensayo_id; 
键入[] types = new Type [1]; 
类型[0] = Hibernate.INTEGER; 
  

魔法在groupby String上。 -

I need to create a query and I need COUNT(*) and HAVING COUNT(*) = x.

I'm using a work around that uses the CustomProjection class, that I downloaded somewhere.

This is the SQL that I try to achieve:

select count(*) as y0_, this_.ensayo_id as y1_ from Repeticiones this_
inner join Lineas linea1_ on this_.linea_id=linea1_.id
where this_.pesoKGHA>0.0 and this_.nroRepeticion=1 and linea1_.id in (18,24)
group by this_.ensayo_id
having count(*) = 2

This is the code, where I use the Projection Hibernate class:

critRepeticion.setProjection(Projections.projectionList()
                .add( Projections.groupProperty("ensayo") )
                .add( CustomProjections.groupByHaving("ensayo_id",Hibernate.LONG,"COUNT(ensayo_id) = "+String.valueOf(lineas.size()))
                .add( Projections.rowCount() )
                );

The error is:

!STACK 0
java.lang.NullPointerException
at org.hibernate.criterion.ProjectionList.toSqlString(ProjectionList.java:50)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getSelect(CriteriaQueryTranslator.java:310)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:71)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1550)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at ar.com.cse.cseagro.controller.RepeticionController.buscarEnsayo(RepeticionController.java:101)

If I comment the line with CustomProjections class, the query work, but I don't get the HAVING COUNT(*) filter in the SQL ...

Basically the query try to retrieve, in a master - detail schema, all the master records where a list of details are simultaneously present, like if you want tho know "which invoices have both products, A and B".

That why if I got 3 items in the IN clause, I need to use HAVING COUNT = 3 clause.

Any idea or suggestion? Best regards,

解决方案

I figured out the problem. I replace CusotmProjections class, with:

.add( Projections.sqlGroupProjection("ensayo_id", groupBy , alias, types));

where groupBy, alias and types are:

 String groupBy = "ensayo_id" + " having " + "count(*) = " + String.valueOf(lineas.size());
 String[] alias = new String[1]; 
 Alias[0] = "ensayo_id"; 
 Type[] types = new Type[1]; 
 types[0] = Hibernate.INTEGER;

and the magic is on groupby String. –

这篇关于如何在Hibernate中使用HAVING COUNT（*）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！