Announcement Announcement Module
Collapse
No announcement yet.
HQL query problem Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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 d.name, e.email from Department d, Employee e where d.id = e.department.id
    and
    select d.name, e.email 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 d.name and count(e) in the same query:
    select d.name, count(e) from Department d inner join d.employees as e
    throws SQLGrammarException.

    I guess that I also have to use "group by d.id" (or "group by d.name") 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:
    http://docs.jboss.org/hibernate/core...ria-projection

    For example:
    Code:
    Criteria criteria = criteria();
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.groupProperty("par"), "parAlias");
    projList.add(Projections.rowCount(), "countAlias");
    criteria.setProjection(projList);
    criteria.setResultTransformer(Transformers.aliasToBean(ObjDto.class));

    Comment

    Working...
    X