Select statement in from clause in Hibernate

2
votes

I have three tables named:

  • schedule (id, status)
  • criteis (id, name, number, bank)
  • info (id, compleInfo)

id is common for all three tables.

My req is that i want bank, number from crities table & compleInfo from info table and count of status from Schedule table.

I have used this query 

select count(s.status), a.compleInfo, a.bank, a.number 
from (select in.compleInfo, cr.bank, cr.number 
      from criteis cr, info in  where cr.id=in.id ) a
left join schedule s on a.id = s.id group by a.id

It works fine on MYSQL Editor but when i run it on Hibernate i am getting below exception

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, [ select count(s.status), a.compleInfo, a.bank, a.number from (select in.compleInfo, cr.bank, cr.number from criteis cr, info in where cr.id=in.id ) a left join schedule s on a.id = s.id group by a.id]

i googled it and found Nhibernate HQL Subselect queries that hql does not support subqueries in the from clause.

Is there any other way to write the above query in Hibernate?

1
hibernatesql

1 Answers

2
votes

HQL allows you to walk the object tree but it's not SQL.

If you want to run SQL queries, you must use native SQL queries.