1

When a DetachedCrieria has projections, how to get the row count of the DetachedCriteria result set? For HQL:

select count(*) from (select name, sum(grade) from score group by name).

For hibernate, how to implement Subqueries.rowCount(DetachedCriteria dc)? Thanks.

Dave
  • 487
  • 1
  • 6
  • 19

2 Answers2

0

If all you want is the count number why not just executing 

select count(name) from score group by name
overmeulen
  • 1,158
  • 6
  • 15
  • no. It is a general requirement. We have a DetachedCriteria. How to get the size of the query result? – Dave Feb 22 '13 at 04:05
  • Execute it and see the number or results returned ? detachedCriteria.getExecutableCriteria(session).list().size() – overmeulen Feb 22 '13 at 08:53
  • If the result set has millions of entities, will it retrieve all the entities, then calculate its size? If it depends on database, How about mysql? thanks. – Dave Feb 22 '13 at 21:07
  • If you don't want to retrieve the entities you could try this : detachedCriteria.getExecutableCriteria(session).setProjection(Projections.rowCount()).uniqueResult(). It takes your current criteria and overrides the select clause with a row count. – overmeulen Feb 25 '13 at 10:03
  • this works if the detachedCriteria does not have projections. But if it already has projections, it will not work. Thanks – Dave Feb 26 '13 at 03:44
  • Why? If the criteria already has projections they will be overriden and the projections won't change the number of rows retrieved. – overmeulen Feb 26 '13 at 08:17
  • For example, select count(*) from (select name, sum(grade) from score group by name). I like to know the number of rows of the subquery result. The detachedCriteria of the subquery has the projections. – Dave Feb 26 '13 at 19:17
  • The solution I gave you will transform your detachedCriteria "select name, sum(grade) from score group by name" into an executable criteria "select count(*) from score group by name". This will give you exactly what you want, the number of results for the detachedCriteria whithout loading anything! Changing the projections doesn't matter here since we work on a different instance. – overmeulen Feb 27 '13 at 09:37
  • No. take a simple examle: name/sum(grade): A/100,B/200,C/300. I like to get the number of results that is 3. If replacing the projections with count(*), it will give me 5,5,5, where 5 is the number of grades each student has, for exmaple, each student has the grades for math/english/piano/sports/programming. – Dave Mar 08 '13 at 19:24
0

I had the same issue...I came up with the below solution and it worked : 

myCriteria.setProjection(Projections.sqlProjection("count(*) as count from ( select distinct id , {alias}.enteredDate  ", new String[] { "count" }, new Type[] { StandardBasicTypes.LONG }));

// this is needed to close the parentheses opened in the Projection
myCriteria.add(Restrictions.sqlRestriction(" 1=1)"));
Taby
  • 1
  • 1
  • It is better to extends a class in Hibernate and implements it. That is, add select count(*) to the generated SQL of DetachedCriteria. But have not figured out how. Thanks for sharing. – Dave Mar 10 '13 at 05:29