Search code examples
javasqlhibernatehql

Hibernate HQL from child to parent table using left join

In the model there are two tables: person , role

PersonRole java class models the N:M relationship. It has two @ManyToOne attributes:
Person person, Role role

I have one HQL query to retrieve all users belonging to a role: 

select role.name, person.name from PersonRole pr join pr.person join pr.role

Need to do the other way round: all the roles for a person. But if a person has no roles still need to retrieve the person (left join)

select person.name, ro.le.name from PersonRole pr join pr.person join pr.role

This is not working as the main entity is PersonRole and returns only users with roles. Dont know how to start the join from Person and go to the optional roles if there is no attribute in the Person entity to reference the role.

how is the best way to do?

Solution

  • UPDATED ANSWER

    Try this:

    Put in your Person class a property as follow:

    List<Role> lstRole;

    In this way when you'll write your query:

    SELECT P FROM Person P

    you'll get for each Person a list af associated roles.