java - 当实体具有多对多关系时如何使用条件查询？

Question

我有 2 个表(人员和技能)；它们之间是多对多的关系。

这来自 person 实体:

    @ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY)
@JoinTable(name = "person_skill",
        joinColumns = @JoinColumn(name = "person_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "skill_id", referencedColumnName = "id"))
private Set<Skill> skills;

这来自技能:

 @JsonBackReference
@ManyToMany(mappedBy = "skills")
private Set<Person> persons;

我尝试执行一个查询，您可以通过许多参数找到一个人，但所有参数都应该是可选的。所以我创建了一些规​​范:

 public static Specification<Person> firstNameContains(String firstName) {
    return (person, cq, cb) -> cb.like(person.get("firstName"), "%" + firstName + "%");
}

public static Specification<Person> isStudent(Boolean isStudent) {
    return (person, cq, cb) -> cb.equal(person.get("isStudent"), isStudent);
}

我在服务中创建了这个函数:

 @Override
public Page<Person> findByParams(Long id,
                                 String firstName,
                                 String lastName,
                                 Boolean isStudent,
                                 Boolean isActive,
                                 String email,
                                 String phone,
                                 Set<Long> skills,
                                 Pageable pageable) {
    List<Specification> specifications = new ArrayList<>();
    if(firstName != null){
        specifications.add(PersonSpecifications.firstNameContains(firstName));
    }
    if (isStudent != null){
        specifications.add(PersonSpecifications.isStudent(isStudent));
    }
    if (skills != null){
        specifications.add(PersonSpecifications.hasSkills(skills));
    }
    Optional<Specification> spec = specifications.stream().reduce((s, acc) -> acc.and(s));
    if(spec.isPresent()) {
        return personRepository.findAll(spec.get(), pageable);
    }
    return personRepository.findAll(pageable);
}

由 Controller 中的此函数调用:

    @GetMapping(FIND)
Page<Person> findByParams(@RequestParam(value = "id", required = false) Long id,
                          @RequestParam(value = "firstName", required = false) String firstName,
                          @RequestParam(value = "lastName", required = false) String lastName,
                          @RequestParam(value = "isStudent", required = false) Boolean isStudent,
                          @RequestParam(value = "isActive", required = false) Boolean isActive,
                          @RequestParam(value = "email", required = false) String email,
                          @RequestParam(value = "phone", required = false) String phone,
                          @RequestParam(value = "skills", required = false) Set<Long> skills,
                          Pageable pageable) {
    return personService.findByParams(id, firstName, lastName, isStudent, isActive, email, phone, skills, pageable);
}

我的问题是 hasSkills 规范应该是什么样子才能发挥作用。

Answer 1

您可以在规范中使用连接。

person.join("skills").("id").in(skills);

这里id将是技能类别中的主键属性名称。