Announcement Announcement Module
No announcement yet.
HQL query problem Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • HQL query problem

    Could you help me create a HQL query? I have the objects Employee and Department with one-to-many relationship so that the Department object has a set of Employees. I want to list the Department names and the number of Employees that every Department has.

    I have successfully created the following queries:
    select, from Department d, Employee e where =
    select, from Department d inner join d.employees as e
    that both return the name of the Department and the email of the Employee for every Employee.

    I have also successfully queried the number of employees:
    select count(e) from Department d inner join d.employees as e

    The problems arrive when I try to add both and count(e) in the same query:
    select, count(e) from Department d inner join d.employees as e
    throws SQLGrammarException.

    I guess that I also have to use "group by" (or "group by") in the end of the statement but I didn't succeed to add it to my queries because it also caused SQLGrammarException.

    Thank you for helping!

  • #2
    I suggest you to use Hibernate Projections:

    For example:
    Criteria criteria = criteria();
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.groupProperty("par"), "parAlias");
    projList.add(Projections.rowCount(), "countAlias");