弹簧JPA属性表达式通过列表中项目的交集查找

Question

如何用属性表达式编写jpa存储库方法，该方法检查列表中的多个项目或这些项目上的属性是否存在？我可以在列表中查找单个项目，请参阅下面的邮政编码，但是我正在尝试编写一种检查多个邮政编码的方法，其中结果集中的每个人都在其地址列表中都有两个邮政编码。

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {
    // Works
    Set<Person> findAllByAddresses_ZipCode(String zip);
    // Doesn't work - does not return expected results
    Set<Person> findAllByAddresses_ZipCode_And_Addresses_ZipCode(String zip1, String zip2);
}

我目前的黑客攻击是为2个邮政编码获取两组，然后找到两组的相交：

public @ResponseBody
    Iterable<Person> personsWithBothZipCodes(@PathVariable("zip1") String zip1,
                                             @PathVariable("zip2") String zip2) {
    Set<Person> a = personRepository.findAllByAddresses_ZipCode(zip1);
    Set<Person> b = personRepository.findAllByAddresses_ZipCode(zip2);
    // Only return results that contain both zip1 and zip2.
    a.retainAll(b);
    return a;
}

实体看起来像这样：

@Entity
public class Person
{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    // zipcode is a String property on an Address.
    @OneToMany(targetEntity = com.data.Address.class, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Address> addresses = new ArrayList<Address>();
    ...
}

有没有办法将其作为方法标头的一部分？相关文档

Answer 1

是的，只需将单词 In添加到查询

Set<Person> findAllByAddresses_ZipCodeIn(Set<String> zip);

然后在您的控制器中您可以做类似的事情：

public @ResponseBody Iterable<Person> personsWithBothZipCodes(@PathVariable("zip1") String zip1, @PathVariable("zip2") String zip2) {
    Set<String> zipSet = new HashSet<>();
    zipSet.add(zip1);
    zipSet.add(zip2);
    Set<Person> a = personRepository.findAllByAddresses_ZipCodeIn(zipSet);
    return a;
}

不知道这是否有效，但可以尝试

Set<Person> findAllByAddresses_ZipCodeInAndZipCodeIn(Set<String> zip1, Set<String> zip2);
public @ResponseBody Iterable<Person> personsWithBothZipCodes(@PathVariable("zip1") String zip1, @PathVariable("zip2") String zip2) {
    Set<String> zipSet1 = new HashSet<>();
    zipSet1.add(zip1);
    Set<String> zipSet2 = new HashSet<>();
    zipSet2.add(zip2);
    Set<Person> a = personRepository.findAllByAddresses_ZipCodeInAndZipCodeIn(zipSet1, zipSet2);
    return a;
}

Answer 2

也许您可以使用jpql query(如注释中建议(？

@Query("from Person as person" +
" join person.addresses as address1 with address1.zipCode = ?1" +
" join person.addresses as address2 with address2.zipCode = ?2")
Set<Person> findByZipCodes(String zipCode1, String zipCode2);

尚未真正复制您的案例，但可能应该起作用。