Can HQL Select on the result set of another query?

java hibernate subquery hql
Red Bit picture Red Bit · Apr 3, 2013 · Viewed 38.8k times · Source

Can HQL Select on the result set of another query?
For example:

SELECT COUNT(*) FROM (SELECT * FROM Table)


I can do it in SQL but when I tried like above in HQL but the it just show me syntax error "unexpected token: ( near line 1, column 22 ..."

Answer

Kevin Bowersox picture Kevin Bowersox · Apr 3, 2013

HQL does support subqueries, however they can only occur in the select or the where clause. The example you provide would best be wrote as a straight statement in HQL. For example:

select count(*) from table t  (where table is the entity name)

If the query involves a more complicated statement than (select * from Table), I would recommend putting this logic into a view and then creating an entity based off of this view.

For databases that support subselects, Hibernate supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.

Example

from DomesticCat as cat
where cat.name not in (
    select name.nickName from Name as name
)

Related questions

Hibernate Criteria Subquery

I need to do this SQL query with detachedCriteria: SELECT g.id FROM games g WHERE NOT EXISTS ( SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id) The idea is to get the ids …

Read More
What are the possible values of the Hibernate hbm2ddl.auto configuration and what do they do

I really want to know more about the update, export and the values that could be given to hibernate.hbm2ddl.auto I need to know when to use the update and when not? And what is the alternative? These …

Read More
How to fix the Hibernate "object references an unsaved transient instance - save the transient instance before flushing" error

I receive following error when I save the object using Hibernate object references an unsaved transient instance - save the transient instance before flushing

Read More