Search code examples
jpajoinjpqlquerydsl

JPQL inner join and left join

I know what joins are and how to use them in plain SQL but i can't find any explanation of internet to query object with relations. I use entity,getList.size() to query objects that has oneToMany or ManyToMany associations with my entity. I'm wondering is there any way to query object with all his relations.

public Person
@OneToMany
List<Cat>
@ManyToMany
List<DormRoom>

what is do to get all object is;

Person p=PersonDAO.getWithId(1L);
p.getCats.size();
p.getsDormRooms.size();

Now i'm wondering the get fully evulated object with JPQL, CriteriaBuilder and maybe with QueryDSL.

Solution

  • It's called a fetch join:

    select distinct p from Person p left join fetch p.cats left join fetch p.dormRooms
where p.id = :id

    Beware though:

    • in Hibernate at least, that will only work if at most 1 of the collections is a bag (i.e. a list without any specified order column).
    • that will create a query returning the cartesian product of cats * dormRooms. So if a person has 100 cats and 100 dormRooms, 10,000 rows will be retrieved.