spring - 如何使用 JPA 查询严格的 IN 子句
问题描述
我有 3 个表:student、subject、student_subject_mapping。学生和学科有多对多的关系,第三个表包含该特定学科的映射和标记。
Student:
| id | name | gpa |
|:---- |:------:| -----:|
| 1 | Tom | 7.0 |
| 2 | Jerry | 8.0 |
| 3 | Popeye | 7.5 |
Subject:
| id | name |
|:---- |:----------------:|
| 1 | Physics |
| 2 | Chemistry |
| 3 | Math |
| 4 | Computer Science |
student_subject_mapping:
| student_id | subject_id | score |
|:---------- |:----------:| -----:|
| 1 | 1 | 5.0 |
| 1 | 2 | 6.0 |
| 1 | 3 | 7.5 |
| 1 | 4 | 8.0 |
| 2 | 2 | 6.0 |
| 2 | 3 | 7.0 |
| 2 | 4 | 8.0 |
| 3 | 1 | 7.0 |
| 3 | 3 | 6.0 |
| 3 | 4 | 8.5 |
我想根据科目选择学生,比如说物理、化学、数学、计算机科学,所以组合查询将类似于:
- 选择所有4 个科目且分数> 5 且gpa > 6的学生
- 选择至少3 门以上科目且成绩> 6 且gpa > 7的学生
- 选择上述科目中至少“n”个且分数> 7 且gpa > 7的学生
科目、“n”、分数和 gpa 是动态的
学生.java
@Getter
@Setter
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
public class Student implements BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Double gpa;
@OneToMany(mappedBy = "student", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
private Set<StudentSubjectMapping> studentSubjectMapping = new HashSet<>();
}
主题.java
@Getter
@Setter
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
public class Subject implements BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@JsonIgnore
@OneToMany(mappedBy = "subject")
private Set<StudentSubjectMapping> studentSubjectMapping = new HashSet<>();
}
@Entity
@Table
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class StudentSubjectMapping{
@EmbeddedId
StudentSubjectMappingId StudentSubjectMappingId;
@JsonIgnore
@ManyToOne(fetch = FetchType.LAZY)
@MapsId("studentId")
private Student student;
@JsonIgnore
@ManyToOne(fetch = FetchType.LAZY)
@MapsId("subjectId")
private Subject subject;
private Double score;
}
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Embeddable
public class StudentSubjectMappingId implements Serializable {
private static final long serialVersionUID = 1L;
private Long studentId;
private Long subjectId;
}
我尝试了不同的东西(之前没有embeddedId)但没有任何帮助:
@Query("from Student student where student.subjects in :subjects group by student.subjects HAVING count(DISTINCT student.subjects) =:count ")
@Query("SELECT new com.nsia.model.Form(f.id, f.name, f.description, f.createdAt, g, COUNT(i.id)) from Form f " +
"LEFT JOIN f.instances JOIN f.groups g WHERE f.groups IN (?1) group by f.id")
@Query("from Student student LEFT JOIN student.subjects subjects WHERE subjects.id in :subjects group by student.subjects HAVING count(DISTINCT student.subjects.size) =:count ")
@Query("from Student student where student.id not in (SELECT stud.id from Student stud where stud.subjects not in (:subjects))")
@Query("select student from Student as student " +
"where student.id not in " +
"(select students.id from Student as students " +
"join students.subjects as subjects " +
"where subjects.id not in (:subjectIds))")
@Query(value = "select students.id from Student as students " +
"left join fetch students.subjects as subjects " +
"where subjects.id not in (:subjectIds) and students.id in (1,2,3)",
countQuery = "select count (student) from Student student left join student.subjects")
List<Long> getstudentIds(@Param("subjectIds") List<Long> subjectIds);
解决方案
您可以使用这样的查询:
@Query("select student from Student as student " +
"where student.gpa >= :minGpa and :count >= " +
"(select count(*) from student.studentSubjectMapping as m " +
"where m.subject.id in (:subjectIds) " +
"and m.score >= :minScore)")
List<Long> getStudents(@Param("count") long count, @Param("minGpa") double minGpa, @Param("minScore") double minScore, @Param("subjectIds") List<Long> subjectIds);
default List<Long> getStudents(double minGpa, double minScore, List<Long> subjectIds) {
return getStudents(subjectIds.size(), minGpa, minScore, subjectIds);
}
推荐阅读
- java - 实现 Spring Batch 的应用中 CPU 使用率高
- javascript - 如何在循环中设置状态
- python - 创建一个将分数作为参数输出到打印函数的函数
- google-apps-script - Google Appscript - 如何设置具有多个范围的条件格式
- sqlite - 创建sqlite表时golang出错
- wordpress - Wordpress BO post.php - 按钮发布/更新不起作用(错误:GET 400 错误请求,rest_invalid_param,orderby=menu_order)
- jenkins - 如何搜索特定电子邮件收件人的所有詹金斯工作
- c# - 如何在 PlayFab 中从玩家获取属性以发送给其他人?
- java - 在 Spring Webflux 中注入中间件
- php - 在另一个表中显示一个表的值(Codeigniter 4)