Hibernate ResultTransformer and hibernate relations

msaleh · **Joined:** Sun Oct 30, 2011 5:43 am **Posts:** 13

i have Employee , Role , Department domains

- Employee , Role have many to many relationship.
- Employee , Department have many to one relationship.

Code:

@ManyToOne(cascade = CascadeType.ALL)
           @JoinColumn(name = "fk_department_id")
           private Department department;
    
       @ManyToMany(fetch = FetchType.EAGER)
       @JoinTable(name = "employee_role", joinColumns = { @JoinColumn(name = "employee_id") }, inverseJoinColumns = { @JoinColumn(name = "role_id") })
       private Set<Role> roles = new HashSet<Role>(0);

-Requirement: get all users with role admin:

so here's what i am trying to do:

Code:

 List<Employee> employees = getCurrentSession()
                .createSQLQuery(
                      "select"
                            + e.id as id,e.first_name as firstName,e.password as password
                            + "from employee e,employee_role er,role r where e.employee_id=er.employee_id and er.role_id=r.role_id and r.name='ROLE_ADMIN' ")
                .setResultTransformer(Transformers.aliasToBean(Employee.class))
                .list();

Problem: i can get all the properties easily as in above query, except for the Department property(there's column fk_department_id in employee table) and the roles property (there's a join table employee_role), any ideas how to get them with the query and set them in the bean with ResultTransFormer or i will have to do that in a separate queries and set each property separately ?

shenliuyang · **Joined:** Wed Nov 23, 2011 3:05 am **Posts:** 3

why not use hql?

Query query=getSession().createQuery("select e from Employee e join e.roles r where r.name='ROLE_ADMIN'");
List<Employee> result=query.list();