Translate this SQL query to HQL

antopaul · **Posted:** Wed Apr 23, 2008 1:32 am

Hi,
How do I convert this query to HQL ?
select count(*) from (select * from emp e order by empno)

I need to find out how many records were returned by the inner query.

Telling how to do it in Criteria is also helpful.

Anto

sanne.grinovero · **Posted:** Wed Apr 23, 2008 3:03 am

you better remove subselects and reordering:

Code:

select count(*) from empno

antopaul · **Posted:** Wed Apr 23, 2008 4:53 am

That is not useful. I cannot change the original query as I will get the query only at runtime. I need to find out how many records are returned by the original query. What I have to do is wrapping of any query inside the count(*) query. So that I get the no of records returned by the original query.

SIau_Tie · **Posted:** Wed Apr 23, 2008 6:25 am

antopaul wrote:

That is not useful. I cannot change the original query as I will get the query only at runtime. I need to find out how many records are returned by the original query. What I have to do is wrapping of any query inside the count(*) query. So that I get the no of records returned by the original query.

I think Hibernate will return select count(*) from empno

antopaul · **Posted:** Thu Apr 24, 2008 3:11 am

Quote:

I think Hibernate will return select count(*) from empno

suppose the query is
select count(*) from (select * from emp e where sal > 1000 order by empno)

How do I translate it. The query in bold is obtained at run time and it will be in HQL. I have to add the HQL to find out how many records are returned by the actual HQL query.

sanne.grinovero · **Posted:** Thu Apr 24, 2008 4:11 am

can't you just concatenate the "select count(*) (" string in front of your HQL?

antopaul · **Posted:** Thu Apr 24, 2008 5:34 am

Thanks for all the replies.
I read the reference documentation section(14.13) on subqueries and it says that

Quote:

Note that HQL subqueries may occur only in the select or where clauses.

So I think I cannot do it dynamically. I will try it with Criteria queries.

sanne.grinovero · **Posted:** Thu Apr 24, 2008 5:49 am

just concatenate the strings in some way it works (it depends a bit on the "dynamic" query your are getting).
My previous post was incomplete but could give the idea:
make it

Code:

"select count(*) from ( "+hql+" ) "

If it still doesn't work and need a more generic way you can
open a Criteria(HQL) , jump to last row and see which row number you ended up:

Code:

ScrollableResults results = session.createCriteria( hql ).scroll( ScrollMode.FORWARD_ONLY );
results.last();
long rowNumber = results.getRowNumber();
results.close();
return ++rowNumber;

but this isn't as efficient as issuing the COUNT(*) to you DBMS.

regards,

antopaul · **Posted:** Thu Apr 24, 2008 6:14 am

I tried concatenating as you said but it gives error

Code:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select count(*) from (select * from Employee)]
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
   at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
   at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
   at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
more stack........................

It must be performance efficient.

sanne.grinovero · **Posted:** Thu Apr 24, 2008 6:23 am

try the scroll, it isn't the most efficient way possible but is quite fast anyway.

My example is wrong, dont use "createCriteria" but "createQuery" of course.

hanna · **Joined:** Fri Apr 25, 2008 10:12 am **Posts:** 1

antopaul wrote:

Hi,
How do I convert this query to HQL ?
select count(*) from (select * from emp e order by empno)

I need to find out how many records were returned by the inner query.

Telling how to do it in Criteria is also helpful.

Anto

what you could do is substring your hql string starting from the index of the first 'from' and replace that with your select count(*). this could be tricky in cases where there's order by, you'll need to substring that as well.

something along this:

String lowerCasedHql = hql.toLowerCase();
int fromIndex = lowerCasedHql.indexOf("from");
int orderyByIndex = lowerCasedHql.lastIndexOf("order by");
String qs = null;

if(orderyByIndex != -1){
qs = hql.substring(fromIndex, orderyByIndex);
}else{
qs = hql.substring(fromIndex);
}

session.createQuery("select count(*) " + qs);

SIau_Tie · **Posted:** Fri Apr 25, 2008 9:03 pm

antopaul wrote:

Quote:

I think Hibernate will return select count(*) from empno

suppose the query is
select count(*) from (select * from emp e where sal > 1000 order by empno)

How do I translate it. The query in bold is obtained at run time and it will be in HQL. I have to add the HQL to find out how many records are returned by the actual HQL query.

I have try with criteria like this

Code:

Criteria criteria = session.createCriteria(EmpVO.class);     criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
criteria.add(Restrictions.gt("sal", new BigDecimal(1000)));

it will return

Code:

select count(*) as y0_ from emp this_ where salary > 1000