java - 使用@OrderColumn 的JPA 多重获取返回多个空值

Question

有 3 个实体
群组

@Entity
@Table(name = "`group`")
public class Group implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_group")
    private Long id;

    @OneToMany(mappedBy = "group",cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    @OrderColumn(name = "id_student")
    private List<Student> students = new ArrayList<>();

    @ManyToOne
    @JoinColumn(name = "id_faculty")
    private Faculty faculty;
     .....getters/setters 
}

学生

@Entity
@Table(name = "student")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id", scope = Long.class)
public class Student implements Serializable {
    @Id
    @Column(name = "id_student")
    private Long id;

    ......

    @OneToMany(mappedBy = "student",cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    private List<Rating> ratings = new ArrayList<>();

    @ManyToOne
    @JoinColumn(name = "id_okr")
    private OKR okr;

    @ManyToOne
    @JoinColumn(name = "id_group")
    private Group group;
.....getters/setters 
}

评级

@Entity
@Table(name = "rating")
public class Rating implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_rating")
    private Long id;

    @Temporal(TemporalType.DATE)
    @Column
    private Date date;

    @ManyToOne
    @JoinColumn(name = "id_student")
    private Student student;

    @ManyToOne
    @JoinColumn(name = "id_paragraph")
    private Paragraph paragraph;
    .....getters/setters 
}

JPA 查询

@Query(value = "SELECT g FROM Group g INNER JOIN FETCH g.students s LEFT JOIN FETCH s.ratings r WHERE g.id = :id AND s.group.id = g.id AND (r.student.id = s.id AND r.date BETWEEN :startMonth and :endMonth OR r IS NULL) GROUP BY s.id")
Group findGroupByStudentGroupId(@Param("id") Long id ,@Param("startMonth") Date startMonth, @Param("endMonth") Date endMonth );

我有一个 ID 为 8000 的学生，在提取查询后，结果列表包含 8001 个元素，其中包含我拥有的 8 个学生和 7993 个空值。如果我删除注释 @OrderColumn 我有 MultiBagException(不能同时获取)。如果将 @OrderColumn 添加到实体 Student 中的评级 @OneToMany 关联，我在 Rating 集合和 Student 集合中有空值。对我来说，@OrderColumn 将空值作为集合中最大的 id 返回的逻辑似乎很奇怪。有什么办法可以解决吗？
Hibernate 5.1.0 最终版本
Spring 数据 JPA 1.8.2

Answer 1

当您尝试获取具有特定组和评分的学生时，您可以从学生实体获取查询并加入评分实体。您不必为组实体显式添加加入，因为它已经在与学生的多对一映射中。

您可以按如下方式更改您的查询:

@Query(value = "SELECT s FROM Student s LEFT JOIN s.ratings r WHERE s.group.id = :id  AND (r.date BETWEEN :startMonth and :endMonth OR r IS NULL) GROUP BY s.id")
List<Student> findGroupByStudentGroupId(@Param("id") Long id ,@Param("startMonth") Date startMonth, @Param("endMonth") Date endMonth );