Databases
StackOverflow2013
Postsdbo
POHOW to use HAVING COUNT(*) with hibernate
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHOW to use HAVING COUNT(*) with hibernate
    text
    copied!<p>I need to create a query and I need <code>COUNT(*)</code> and <code>HAVING COUNT(*) = x</code>.</p> <p>I'm using a work around that uses the <code>CustomProjection</code> class, that I downloaded somewhere.</p> <p>This is the SQL that I try to achieve:</p> <pre><code>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&gt;0.0 and this_.nroRepeticion=1 and linea1_.id in (18,24) group by this_.ensayo_id having count(*) = 2 </code></pre> <p>This is the code, where I use the <code>Projection</code> Hibernate class:</p> <pre><code>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() ) ); </code></pre> <p>The error is:</p> <pre><code>!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.&lt;init&gt;(CriteriaJoinWalker.java:71) at org.hibernate.loader.criteria.CriteriaLoader.&lt;init&gt;(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) </code></pre> <p>If I comment the line with <code>CustomProjections</code> class, the query work, but I don't get the <code>HAVING COUNT(*)</code> filter in the SQL ...</p> <p>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".</p> <p>That why if I got 3 items in the <code>IN</code> clause, I need to use <code>HAVING COUNT = 3</code> clause.</p> <p>Any idea or suggestion? Best regards,</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload