Search code examples
hibernatejoincriteria

Hibernate Criteria join two tables

I've 3 table: PERSON: ID, NAME, SURNAME TEAM: ID, NAME, PERSON, ROLE ROLE: ID, POSITION where TEAM could have a list of PERSON

I need to create a query, with Hibernate Criteria, like this: select * from PERSON p, TEAM t where t.ROLE = "myRole" and t.PERSON = p.id

I want to get the list of PERSON in TEAM with a given ROLE. Can you help me? Thanks

Solution

  • Team has a list of person. Therefore instead of having Person column in Team table, you should have team_id column in Person table. And I assume Role is associated with Person.Thus your tables should be like the following:

    Person: ID, NAME, SURNAME, TEAM_ID, ROLE_ID Team: ID, NAME Role:ID, POSITION

    Then the query for getting list of Person in a given Team with a given role is:

    Select * from Person p, Team t, Role r where p.team_id=t.id and p.role_id=r.id and r.position = givenPosition and t.name=givenTeam

    In Criteria

    `Criteria c = session.createCriteria(Person.class, "p");
c.createAlias("p.team", "t");
c.createAlias("p.role", "r");
c.add(Restrictions.eq("t.name", givenTeam));
c.add(Restrictions.eq("r.position", givenPosition ));`