Issue about (count,group by) to get record count in HQL.

sfykemble · **Joined:** Tue Jun 15, 2004 1:10 am **Posts:** 5

I want get the recordset by the following SQL(or HQL):

Code:

select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f

and I can get the record count by the following RAW SQL :

Code:

select count(*) as recordCnt from (select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f) as Tmpt

but how can I translate the second RAW SQL into HQL,it seems that Hibernate does not support such syntax,How can get the record count in such scenario??

Your reply will be greatlly appreciated!
Thanks a lot!

emmanuel · **Posted:** Mon Jun 21, 2004 2:49 am

Code:

select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f

sfykemble · **Joined:** Tue Jun 15, 2004 1:10 am **Posts:** 5

Hi,emmanuel:
well,maybe I did not explain the question clearly. :)

The situation is :
I have a following HQL:

Code:

select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f

I want get the record count of this hql.
How can I get it?

1.One approach is that:

Code:

Query query = session.createQuery(hql);
List list = query.list();
int recordCount = list.size();

but this appraoch is impossible if the record count exceeds more than 100000 or even large.

2.another appraoch is that:

Code:

String hql2 = "select count(*) as recordCnt from (select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f) as Tmpt";
//wrap the "select count(*) from (...) as TempT" around the original hql
Query query = session.createQuery(hql2);
//Run error because Hibernate does not support hql in such syntax.
List list = query.list();
int recordCount = list.size();

I prefer to this appraoch ,but failed because of the syntax of hql.

How can I? I was deeply puzzled.

Thanks a lot!

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

I have same problem.
PostgreSql, Oracle (and probably other database) support subqueries in from clause.

Is there support for subqueries in from clause in HQL.

I think that Query interface have to have count function.
When make query for database count is only:

select count(*) from ( <query> ) as cnt

Then we can do :

Query q = ...
int count = q.count();
same like
List l = q.list();

Tips from reference 11.13
( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
is only for complete class

regards

emmanuel · **Posted:** Mon Jun 28, 2004 12:07 pm

Sorry wrong copy pastle, what I tryed to say is that you don't really need a so complex query for returning the size

Code:

select count(distinct t.f) from Tbl as t where t.name like 'J%'

SIau_Tie · **Posted:** Thu Jul 10, 2008 5:02 am

emmanuel wrote:

Sorry wrong copy pastle, what I tryed to say is that you don't really need a so complex query for returning the size

Code:

select count(distinct t.f) from Tbl as t where t.name like 'J%'

hi emmanuel, How about if I have multple field to group by?

Code:

select count(*) as recordCnt from (select t.f as tf, count(t.f) as tfCnt from Tbl as t where t.name like 'J%' group by t.f, t.g) as Tmpt