How to use subquery into "from" clause in Hibernate?

I have three tables: class, student, and teacher

table class
{
    class_id(PK)
}

table student
{
    student_id(PK)
    class_id(PK+FK)
}

table teacher
{
    teacher_id(PK)
    class_id(PK+FK)
}

I have a query in SQL, which works fine.

SELECT data.class_id, count(data.class_id) AS count
FROM ((SELECT class_id FROM student)
        union all
        (SELECT class_id FROM teacher)) AS data
GROUP BY data.user_id
ORDER BY count desc

The query contains sub query in the from clause and union operation. I unable to convert it to the HQL.

Please, give me the efficient HQL query from the above SQL query.

Unfortunately HQL does not support UNION queries. Two alternative strategies to solve your problem are:

• Mapping a Pojo to a a view

• Or Inheritance mapping. Particularly Table per concrete class strategy with an abstract Person superclass inherited both by Student and Teacher seems to fit your problem well:

  select p.id, count(c)
  from Person p join p.classes c
  group by p.id
  order by count(c) desc