如何通过使用JpaSpecificationExecutor结合表来创建规范？

Question

我正在使用JpaSpecificationExecutor来创建自定义查询。如何为以下SQL创建一个规范？

select * from employee e, address a where e.id=23415 and e.name="Foo" and a.city="London";

Java类：

public static Specification<Employee> searchEmployee(final Map<String,String> myMap) {
    
    return new Specification<Employee>(){
        @Override
        public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
               
             //Need to query two tables Employee and Address  
             
           }
      }

- DarkCrow

Answer 1

这里有一个可行的测试

@Test
public void test1() {

    repository.save(makeEmployee("billy", "London"));
    repository.save(makeEmployee("noby", "London"));
    repository.save(makeEmployee("fred", "London"));

    assertEquals(3, repository.count());

    final Long id = 3l;
    final String name = "noby";
    final String city = "London";

    Specification<Employee> specification = new Specification<Employee>() {
        public Predicate toPredicate(Root<Employee> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
            List<Predicate> predicates = new ArrayList<Predicate>();
            predicates.add(builder.equal(root.get("id"), id));
            predicates.add(builder.equal(root.get("name"), name));
            predicates.add(builder.equal(root.get("address").get("city"), city));
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    };

    List<Employee> find = repository.findByIdAndNameAndAddressCity(id, name, city);
    assertEquals(1, find.size());

    find = repository.findAll(specification);
    assertEquals(1, find.size());
}

private Employee makeEmployee(String name, String city) {

    Address address = new Address();
    address.setCity(city);

    Employee employee = new Employee();
    employee.setName(name);
    employee.setAddress(address);
    return employee;
}

}

仓库看起来是这样的

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {

    List<Employee> findByIdAndNameAndAddressCity(Long id, String name, String city);
}

实体看起来像这样

@Entity(name = "EMPLOYEE")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "NAME")
    private String name;

    @Column(name = "DATE_OF_BIRTH")
    private Date dob;

    @OneToOne(cascade=CascadeType.ALL)
    @JoinColumn(name = "address_id", referencedColumnName = "id", nullable = false)
    private Address address;

希望这能有所帮助。

- Essex Boy

---

我同意这个方法对于一个表格来说是完美的。但是在上面的代码中，你只有一个表格（EMPLOYEE）来保存员工和地址的详细信息。但是我有两个分别为 EMPLOYEE 和 ADDRESS 的表格。我需要使用规范在这两个表格上进行查询。有什么建议吗？ - DarkCrow

抱歉没有仔细阅读问题，我已经更正了答案。 - Essex Boy

@OneToMany 在 Employee 上，因此它期望 Address PK 的 FK 在 Employee 表上。即员工表应该有一个 address_id 列。不确定为什么会出现上述情况，看起来不正确。 - Essex Boy

我在表中有一个FK引用。问题在于定义谓词。 我使用以下方法来克服它在规范内部 Join<Employee,Address> join = root.join("address");在构建谓词时，我使用了join而不是rootpredicates.add(builder.equal(join.<String>get("city"), city)); - DarkCrow