JPA JPQL LEFT JOIN FETCH - Left Outer Join Gibi Tüm Doktor'ları Yükler, Varsa Randevularını da Yükler

Giriş

LEFT JOIN FETCH ise tüm doktor'ları yükler, varsa randevularını da yükler

Not : N + 1 Select problem ile ilgili çözümler şöyle

Perhaps you are facing an N+1 select issue

  * With Hibernate, you may fix it by using JOIN FETCH

                                         or LEFT JOIN FETCH

                                 or FetchType.LAZY

                                 or ...

  * With Spring Data JPA, you may fix it by adding @EntityGraph(attributePaths = { "..." })

    on repository method: https://docs.spring.io/spring-data/jpa/docs/current/reference/ht

Özet

SpringBoot kullanıyorsak Repository sınıfına @Query yazarız. Şöyle yaparız

public interface ZooRepository extends JpaRepository<Zoo, Long> {
  @Query("FROM Zoo z LEFT JOIN FETCH z.animals")
  List<Zoo> findAllWithAnimalsJoined();
}

Örnek - 2 Nesne İlişkisi

Şöyle yaparız

SELECT d FROM Doctor d
LEFT JOIN FETCH d.appointments

Açıklaması şöyle

If you’re willing to fetch the full set of Doctors including the ones who do not have any Appointments it’s best to use left join fetch query.

Üretilen SQL şöyledir

SELECT doctor0_.id as id1_1_0_, 
appointmen1_.id as id1_0_1_, 
doctor0_.name as name2_1_0_, 
appointmen1_.appointmentTime as appointm2_0_1_, 
appointmen1_.doctor_id as doctor_i3_0_1_, 
appointmen1_.doctor_id as doctor_i3_0_0__, 
appointmen1_.id as id1_0_0__ 
FROM Doctor doctor0_ 
INNER JOIN
Appointment appointmen1_ on doctor0_.id=appointmen1_.doctor_id

Örnek

Elimizde şöyle bir kod olsun. 

@Entity
public class Item {

  @Id
  @GeneratedValue
  private Long id;
  ...
  @ManyToOne
  private Basket basket;
}

Bu koda ait elimizde şöyle bir repository olsun. 

public interface ItemRepository extends CrudRepository<Item, Long> {

}

@ManyToOne EAGER olmasına rağmen N + 1 Select Problem var. Açıklaması şöyle

Now, when you use findAll() it is equivalent to firing a JPQL query like entityManager.createQuery(...) and it by default loads the items first and subsequently for each item it loads the basket entity and causing N+1 problem.

Üretilen SQL şöyle

Hibernate: select item0_.id as ... from item item0_

Hibernate: select basket0_.id as ... where basket0_.id=?

Hibernate: select basket0_.id as ... where basket0_.id=?

Hibernate: select basket0_.id as ... where basket0_.id=?

Repository şöyle olsun

public interface ItemRepository extends CrudRepository<Item, Long> {
  @Override
  @Query("select item from Item item left join fetch item.basket")
  Iterable<Item> findAll();
}

Bu sefer üretilen SQL şöyle

Hibernate: select item0_.id as id1_1_0_, basket1_.id as id1_0_1_, item0_.basket_id as basket_i3_1_0_, 

... 

from item item0_ left outer join basket basket1_ on item0_.basket_id=basket1_.id

Örnek - 2 Nesne İlişkisi

Elimizde şöyle bir kod olsun.

@Entity
@Table(name="users")
public class User {
  ...
  @OneToMany(mappedBy="user")
  private List<Comment> comments;
}

Elimizde şöyle bir kod olsun.

@Entity
@Table(name="comments")
public class Comment {
  ...
  @ManyToOne
  @JoinColumn(name="user_id")
  private User user;

}

Şöyle yaparız.

TypedQuery<User> query = entityManager.createQuery(
  "SELECT e from User e left join fetch e.comments", User.class);

Örnek - 4 Nesne İlişkisi

Şeklen şöyle

Şöyle yaparız

public List<Country> getAll() {
  List<Country> countries = em.createQuery(
    "SELECT c FROM Country c left join c.authors a "
    + "left join a.posts p left join p.likes l"
    + " order by c.COUNTRY_NAME,a.AUTHOR_NAME,p.POST_NAME,l.AUTHOR_NAME")
  .setHint("eclipselink.left-join-fetch", "c.authors.posts.likes")
  .setHint("eclipselink.read-only", true)
  .getResultList();

  countries.stream().flatMap(c -> c.getAuthors().stream())
    .flatMap(a -> a.getPosts().stream()) // all posts
    .forEach(p -> p.removePostsInFans());

  return countries;
}

çıklaması şöyle. Burada JPQL ile Likes için Left Join yazılsa bile, Left Join Fetch yapılıyor

By default JPA executes an individual SQL query for each parent object to load its associations. The EclipseLink-specific query hint eclipselink.left-join-fetch triggers fetch joins along the specified path c.authors.posts.likes. Without the hint, 20 additional queries would be needed to load the tiny data. The hint eclipselink.read-only indicates that the query results should not be registered with the persistence context.

1. LEFT JOIN FETCH İstemiyorsak

Bir diğer çözüm de Hibernate'e mahsus @Batch kullanmak. 

2 LEFT JOIN FETCH ve Full Cartesian Product Problemi

LEFT JOIN FETCH özellikle parent nesnede iki tane ilişki varsa problem olabiliyor.

Örnek

Elimizde şöyle bir JPQL olsun

SELECT a FROM A
  LEFT JOIN FETCH a.bs
  LEFT JOIN FETCH a.cs

Açıklaması şöyle

then you create full Cartesian product between bs and cs and returning count(a.bs) x count(a.cs) rows in the result set for each a which are read one by one and assembled into the entities of A and their collections of bs and cs.

Burada tek bir SQL çalışmasına rağmen problem büyük bir result set dönülmesi. Bu result set'i hibernate ayıklayıp tek nesne haline getirmek zorunda. Açıklaması şöyle

The query which is generated by eager fetching both associated collections results in a big redundancy in returned data set. For a doctor with 3 appointments and 4 patients, we’re receiving 3 x 4 = 12 rows. For this particular example it’s not a big issue, but imagine a doctor with 200 patients and 50 appointments returning 200 x 50 = 10 000 rows. This data needs to be wired from the database to application and later de-duplicated by Hibernate, which may lead to performance and memory issues.

Çözümler

- Çözümlerden birisi her nesneyi teker teker çekmek. Şöyle yaparız. Tabii ki bu durumda Doctor nesnesinden child nesnelere erişemiyoruz

Doctor doctor = entityManager.find(Doctor.class, 1L);
List<Appointment> doctorsAppointments = entityManager
  .createQuery("select a from Appointment a where a.doctor.id = :doctorId")
  .setParameter("doctorId", doctor.getId())
  .getResultList();

List<Patient> doctorsPatients = entityManager
  .createQuery("select p from Patient p where p.leadingDoctor.id = :doctorId")
  .setParameter("doctorId", doctor.getId())
  .getResultList();

- Aslında bu madde bir çözüm değil fakat ilgili olduğu için yazdım. Çözüm ilişkiyi EAGER hale getirip Hibernate'e mahsus @org.hibernate.annotations.Fetch(FetchMode.SELECT) kullanmak

Şöyle yaparız. Burada ilişki kod EAGER halinde. Eğer hibernate anotasyonunu kullanmazsak ve entityManager.find() yaparsak kartezyen çarpım problemi oluyor. 

Bu anotasyonu eklersek ve entityManager.find() yaparsak hibernate sadece 3 tane sql çalıştırıyor ve kocaman bir kartezyen çarpım sonucu gelmiyor.

@Entity
public class Doctor {

  @OneToMany(mappedBy = "doctor", fetch = FetchType.EAGER)
  @org.hibernate.annotations.Fetch(FetchMode.SELECT)
  private Set<Appointment> appointments;

  @OneToMany(mappedBy = "leadingDoctor", fetch = FetchType.EAGER)
  @org.hibernate.annotations.Fetch(FetchMode.SELECT)
  private Set<Patient> patients;
}

Açıklaması şöyle

Please note that this functionality is only available in Hibernate. Hibernate allows configuring a global setting for eagerly fetched collections. This solution to Cartesian Product Problem allows for each associated collection to be fetched with a separate SQL query. In nature, it’s similar to what we manually did above, but it also has a benefit of loading all related entities directly into Doctor entity.

Çalıştırılan SQL şöyle

select
doctor0_.id as id1_1_,
doctor0_.name as name2_1_
from Doctor doctor0_
where doctor0_.id=1

select
patients0_.leadingDoctor_id as leadingD3_2_0_,
patients0_.id as id1_2_0_,
patients0_.id as id1_2_1_,
patients0_.leadingDoctor_id as leadingD3_2_1_,
patients0_.name as name2_2_1_
from Patient patients0_
where patients0_.leadingDoctor_id=?

select
appointmen0_.doctor_id as doctor_i3_0_0_,
appointmen0_.id as id1_0_0_,
appointmen0_.id as id1_0_1_,
appointmen0_.appointmentTime as appointm2_0_1_,
appointmen0_.doctor_id as doctor_i3_0_1_
from Appointment appointmen0_
where appointmen0_.doctor_id=?

- Diğeri ise SQL kullanmak. Şöyle yaparız. Burada entityManager.find() kullanılmıyor. Aslında bu çözüm de dokümante edilmiş bir çözüm değil. Sadece çalışıyor

Doctor doctor = entityManager.createQuery("select d from Doctor d where d.id=1",
  Doctor.class).getSingleResult();