spring-data-jpa - 如何使用 Spring Data Rest 和 queryDSL 在连接表中查询多个值

Question

我正在为现有且相当旧的 mySQL 数据库编写新的 Spring Data REST 后端。我需要支持自由格式的搜索查询，而 queryDSL 似乎是一个不错的选择。一切正常，除了我无法在连接表中查询多个值。

这是我的实体和学生存储库（在 queryDSL 中，我想将所有字符串匹配查询默认为 LIKE）：

@Data
@Entity
@Table(name = "students")
public class Students  {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "id", unique = true, nullable = false)
   private Integer id;

   @Column(name = "first_name", nullable = false)
   private String firstName;

   @Column(name = "last_name", nullable = false)
   private String lastName;

   @Column(name = "major", nullable = false)
   private String major;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  @JoinTable(name = "students_courses_joins", joinColumns = {
       @JoinColumn(name = "student_id") }, inverseJoinColumns = {
                   @JoinColumn(name = "course_id") })
   private List<Course> courses;
}

@Data
@Entity
@Table(name = "courses")
public class Courses {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", unique = true, nullable = false)
    private Integer id;
    
    @Column(name = "name", nullable = false)
    private String name;
    
    @Column(name = "code", nullable = false)
    private String code;
    
    @ManyToMany(mappedBy = "courses")
    List<Student> students;
}



RepositoryRestResource(collectionResourceRel = "students", path = "students")
public interface StudentsRepository extends JpaRepository<Students, Integer>, 
        QuerydslPredicateExecutor<Students>, QuerydslBinderCustomizer<QStudents> {

  @Override
  default void customize(@NonNull QuerydslBindings bindings, @NonNull QStudents Students) {
    bindings.bind(String.class).first((StringPath path, String value) -> path.containsIgnoreCase(value));
}

DDL 创建连接表：

create table  students_courses_joins
(
`id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `student` (`student_id`),
 KEY `course` (`course_id`),
 CONSTRAINT `student` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) 
 ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `course` FOREIGN KEY (`course_id`) REFERENCES  `courses`(`id`) ON 
 DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

像这样的查询

http://localhost:8081/students?courses.name=XYZ&major=ABC

工作正常，但是一旦我尝试查询参加多门课程的学生，例如

http://localhost:8081/students?courses.name=XYZ&courses.name=MN

只接受连接表中的第一个参数，其余的被忽略。所以我只能查询学生是否参加了一门特定的课程，而不能查询多门课程。是 queryDSL 的限制还是我的设置有问题？

根据https://www.logicbig.com/tutorials/spring-framework/spring-data/web-query-dsl-collection-properties.html它应该可以正常工作，但是引用连接表的第二个查询参数总是被忽略. 我还尝试实现自定义 REST 控制器，而不是依赖 Spring 提供的控制器，但结果是一样的/

标签： spring-data-jpaspring-data-restquerydsl

Answer 1

您必须像这样修改customize实现StudentRepository（使用allthat takeMultiValueBinding而不是firstthat takes SingleValueBinding）：

@Override                                                                                   
default void customize(@NonNull QuerydslBindings bindings, @NonNull QStudent Students) {    
  bindings.bind(String.class).all((StringPath path, Collection<? extends String> value) -> {
    BooleanBuilder builder = new BooleanBuilder();                                          
    Iterator<? extends String> iterator = value.iterator();                                 
    while (iterator.hasNext()) {                                                            
      builder.or(path.containsIgnoreCase(iterator.next()));                                 
    }                                                                                       
    return Optional.of(builder);                                                            
  });                                                                                       
}                                                                                           

这样，您将收到如下查询：

SELECT student0_.id         AS id1_1_,
       student0_.first_name AS first_na2_1_,
       student0_.last_name  AS last_nam3_1_,
       student0_.major      AS major4_1_
FROM   students student0_
WHERE  EXISTS (SELECT 1
               FROM   students_courses_joins courses1_,
                      courses course2_
               WHERE  student0_.id = courses1_.student_id
                      AND courses1_.course_id = course2_.id
                      AND ( Lower(course2_.name) LIKE ? escape '!' ))
        OR EXISTS (SELECT 1
                   FROM   students_courses_joins courses3_,
                          courses course4_
                   WHERE  student0_.id = courses3_.student_id
                          AND courses3_.course_id = course4_.id
                          AND ( Lower(course4_.name) LIKE ? escape '!' ))
LIMIT  ?