Can HQL Select on the result set of another query?

Question

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, it just showed me syntax error "unexpected token: ( near line 1, column 22 ..."

Answer 1

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 ) 

Answer 2

there is no way to do subquery in from clause in HQL even if the database support it, I solved this problem by putting the query into the sql as a store procedure, then call the procedure in HQL. For example:

Insert the procedure into your sql:

DELIMITER $$ CREATE PROCEDURE `procedure_name`(   `arg_name` INT, ) BEGIN      your query here END; $$ DELIMITER ; 

Then, if you use hibernate, call this procedure from java code as below:

Query query = session.createSQLQuery("CALL procedure_name(:arg_name)"); query.setParameter("arg_name", args); List list = query.list(); 

Hope this can help you.