File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes subquery sql to hql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "subquery sql to hql" Watch "subquery sql to hql" New topic

subquery sql to hql

nimo frey
Ranch Hand

Joined: Jun 28, 2008
Posts: 580
How can I express this sql in hql?:


I have tried it with this HQL, but it s failed because of "org.hibernate.hql.ast.QuerySyntaxException: unexpected token":

any ideas?
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

HQL does not support union. Just use two queries.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
nimo frey
Ranch Hand

Joined: Jun 28, 2008
Posts: 580
oh okay,

hmm and what about this kind of subquery:


in HQL??

naveen velicheti

Joined: Apr 02, 2010
Posts: 8
Hey I have the same problem

Did you get this resolved

This is a sample query
"select r1.roleId from (select r1.roleId from Roles r1)r1" (I have much complex query than this)

I get this as error when I run the above

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select r1.roleId from (select r1.roleId from r1)r1]
at org.hibernate.hql.ast.QuerySyntaxException.convert(
at org.hibernate.hql.ast.QuerySyntaxException.convert(
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(
at org.hibernate.engine.query.HQLQueryPlan.<init>(
at org.hibernate.engine.query.HQLQueryPlan.<init>(
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(
at org.hibernate.impl.AbstractSessionImpl.createQuery(
at org.hibernate.impl.SessionImpl.createQuery(
at com.jci.hibernate.HibernateDAO.getQueryResultList(
... 1 more

Can you tell me why it is not working
I agree. Here's the link:
subject: subquery sql to hql
jQuery in Action, 3rd edition