JPA Hibernate FetchType.EAGER and FetchMode.JOIN for non primary key

Question

I have come across a very interesting scenario. I know about the n+1 problem and FetchType.EAGER and FetchMode.JOIN. I have a parent entity School which has 2 @OneToMany children entity ie Student and Teacher. I need all 3 so using FetchType.EAGER and FetchMode.JOIN.

school entity

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import javax.persistence.*;
import java.util.Set;
@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class School {
    @Id
    @GeneratedValue(generator = "sequence", strategy = GenerationType.IDENTITY)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    int schoolId;
    String schoolName;
    float schoolRating;
    @OneToMany(mappedBy = "school", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set teachers;
    @OneToMany(mappedBy = "school", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set students;
 }

Student entity

import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import javax.persistence.*;
import java.util.Date;
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Student {
    @Id
    @GeneratedValue(generator = "sequence", strategy = GenerationType.IDENTITY)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    public int studentId;
    public byte studentByte;
    public Date date;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "schoolId", referencedColumnName = "schoolId")
    private School school;
}

Teacher entity

import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;    
import javax.persistence.*;
import java.util.Date;
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Teacher {
    @Id
    @GeneratedValue(generator = "sequence", strategy = GenerationType.IDENTITY)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    public int teacherId;
    public byte teacherByte;
    public Date date;
    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "schoolId", referencedColumnName = "schoolId")
    private School school;
}

School Repo

@Repository
public interface SchoolRepository extends JpaRepository {
ListfindBySchoolName(String schoolName);
}

if I get School object via the findById method, ie the primary key of the table.

Optional schoolById = schoolRepository.findById(1);

generated SQL is a join of school, student, and teacher entity.

select school0_.schoolId as schoolid1_0_0_, school0_.schoolName as schoolna2_0_0_, school0_.schoolRating as schoolra3_0_0_, students1_.schoolId as schoolid4_1_1_, students1_.studentId as studenti1_1_1_, students1_.studentId as studenti1_1_2_, students1_.date as date2_1_2_, students1_.schoolId as schoolid4_1_2_, students1_.studentByte as studentb3_1_2_, teachers2_.schoolId as schoolid4_2_3_, teachers2_.teacherId as teacheri1_2_3_, teachers2_.teacherId as teacheri1_2_4_, teachers2_.date as date2_2_4_, teachers2_.schoolId as schoolid4_2_4_, teachers2_.teacherByte as teacherb3_2_4_ from School school0_ left outer join Student students1_ on school0_.schoolId=students1_.schoolId left outer join Teacher teachers2_ on school0_.schoolId=teachers2_.schoolId where school0_.schoolId=?

but if I find a school with some other variable which is not a primary key.

List schoolByName = schoolRepository.findBySchoolName("school1");

generated SQL is 3 different hits on DB.

Hibernate: select school0_.schoolId as schoolid1_0_, school0_.schoolName as schoolna2_0_, school0_.schoolRating as schoolra3_0_ from School school0_ where school0_.schoolName=?
Hibernate: select teachers0_.schoolId as schoolid4_2_0_, teachers0_.teacherId as teacheri1_2_0_, teachers0_.teacherId as teacheri1_2_1_, teachers0_.date as date2_2_1_, teachers0_.schoolId as schoolid4_2_1_, teachers0_.teacherByte as teacherb3_2_1_ from Teacher teachers0_ where teachers0_.schoolId=?
Hibernate: select students0_.schoolId as schoolid4_1_0_, students0_.studentId as studenti1_1_0_, students0_.studentId as studenti1_1_1_, students0_.date as date2_1_1_, students0_.schoolId as schoolid4_1_1_, students0_.studentByte as studentb3_1_1_ from Student students0_ where students0_.schoolId=?

I realized that join only works if we are getting by id ie primary key, but I don't have a primary key of School. I have the name of the school which is unique and indexed and needs student entity and teacher entity also. is there a way to get them all using join in hibernate. I know if student and teacher records are more then it will be performance degradation, but in my case, it will be at most 3-4 records only. that's why I want to join them all.

user_7875623 · Answer

It is not advisable to map more than one associated collection fields of an Entity with FetchMode.JOIN. This is to avoid Cartesian product issue. I am surprised it did a sql join even when you selected by Id
When you are fetching School other than it's Id field, hibernate does not know how many Schools you will be fetching, so if it did a join fetch rather than separate selects, it will end up with a Cartesian product issue
Say you have 10 schools, each school has 20 teachers and 400 students. If hibernate did a join it will have to bring 80,000 (10*20*400) records from db. But since it is doing separate select, it will bring 4,210 (10 + 200 + 4000) records. Even in the case selecting by Id it is 420 records vs 8000 records

Short Answer

Do not retrieve Parent entity and more than one of its associated collections using join even if you find a way to do that because performance will be worse than multiple selects.

Update:

If you are sure that the school name is unique and there is only a few teachers per school and students size is small, you can do the following: (currently your findBySchoolName returns List, you can change that to return an optional school)

@Repository
public interface SchoolRepository extends JpaRepository {
    
    @Query("SELECT s from School s left join fetch s.teachers " +
            "left join fetch s.students where s.schoolName = :name")
    Optional findBySchoolName(String name);
}

JPA Hibernate FetchType.EAGER and FetchMode.JOIN for non primary key

1 Answers