JPA Hibernate fetchtype.eager和fetchmode.join用于非主键

Question

我遇到了一个非常有趣的场景。我知道n+1问题和FetchType.Eager和FetchMode.join。我有一个家长实体学校，它有2@onetomany儿童实体ie学生和教师。我需要所有3个，所以使用fetchtype.eager和fetchmode.join。

学校实体

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<Teacher> teachers;
    @OneToMany(mappedBy = "school", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Set<Student> students;
 }

学生实体

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;
}

教师实体

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;
}

学校回购

@Repository
public interface SchoolRepository extends JpaRepository<School, Integer> {
List<School>findBySchoolName(String schoolName);
}

如果我通过findById方法获取School对象，即表的主键。

Optional<School> schoolById = schoolRepository.findById(1);

生成的SQL是学校、学生和教师实体的联接。

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=?

List<School> schoolByName = schoolRepository.findBySchoolName("school1");

生成的SQL是在数据库上的3个不同的命中。

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=?

我意识到join只有在我们使用id ie主键的情况下才有效，但我没有学校的主键。我有学校的名字，这是唯一的，索引，并需要学生实体和教师实体也。有没有办法在Hibernate中使用join来获得它们。我知道如果学生和老师的记录更多，那么它将是性能下降，但在我的情况下，它将最多只有3-4个记录。这就是我想加入他们所有人的原因。

Answer 1

>

不宜用fetchmode.join映射一个实体的多个关联集合字段。这是为了避免笛卡尔积问题。我很惊讶，即使在您通过id选择时，它也进行了sql联接

当您获取ID字段以外的学校时，hibernate不知道您将获取多少个学校，因此如果它执行联接获取而不是单独选择，它将以cartesian product问题结束

假设你有10所学校，每所学校有20名教师和400名学生。如果hibernate进行了联接，它将不得不从DB带来80,000(10*20*400)记录。但是由于它执行单独的选择，它将带来4,210(10+200+4000)记录。即使在通过id选择的情况下，也是420条记录与8000条记录

简短回答

不要使用join检索父实体及其多个关联集合，即使您找到了这样做的方法，因为性能会比多次选择差。

更新：

• 如果确定学校名称是唯一的，并且每个学校只有几名教师，学生人数也很少，则可以执行以下操作：（当前findbyschoolname返回列表 ，您可以将其更改为返回可选的学校）

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