尝试这样的事情:
Specification<CustomerEntity> joins = (customer, query, cb) -> {
// from CustomerEntity c
// join c.items i
Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");
// join i.documents d
Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");
// // where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3
return cb.and(
customer.equal(customer.get("customerNumber", customerNumber)),
items.equal(items.get("itemNumber", itemNumber)),
documents.equal(documents.get("validDate", validDate))
);
};
// sort by c.customerNumber asc
PageRequest pageRequest = new PageRequest(0, 2, new Sort(Sort.Direction.ASC, "customerNumber"));
Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, pageRequest);
但我不知道你为什么需要Specification
here?
您可以使同样的事情变得更简单:
@Query("select c from CustomerEntity c join c.items i join i.documents d where c.customerNumber = ?1 and i.itemNumber = ?2 and d.validDate = ?3")
Page<CustomerEntity> getCustomers(String customerNumber, String itemNumber, LocaleDate validDate, Pageable pageable);
但所有这些都没有意义,因为您的三个实体具有连续的一对多关联。在这种情况下,您可以只使用最后一个条件,而不是三个条件:where d.validDate = ?1
。那么查询方法就变得更加简单了:
@Query("select c from CustomerEntity c join c.items i join i.documents d where d.validDate = ?1")
Page<CustomerEntity> getCustomers(LocaleDate validDate, Pageable pageable);
UPDATE
要按连接实体的字段添加排序,我们可以使用orderBy
的方法query
:
Specification<CustomerEntity> joins = (customer, query, cb) -> {
Join<CustomerEntity, CustomerItemEntity> items = customer.join("items");
Join<CustomerItemEntity, DocumentEntity> documents = items.join("documents");
// Ascending order by 'Document.itemNumber'
query.orderBy(cb.asc(documents.get("itemNumber")));
return cb.and(
customer.equal(customer.get("customerNumber", customerNumber)),
items.equal(items.get("itemNumber", itemNumber)),
documents.equal(documents.get("validDate", validDate))
);
};
Page<CustomerEntity> customerPage = CustomerRepo.findAll(joins, new PageRequest(0, 2));
要按多个参数排序,您可以将它们传递给以逗号或List
:
query.orderBy(cb.asc(items.get("customerNumber")), cb.desc(documents.get("itemNumber")));