Spring Data JPA:使用联接表进行排序和分页 [英] Spring Data JPA: Sorting and paging with joined tables
问题描述
我有一种情况,我希望对3个表参与的结果进行过滤,排序和分页.
I have a scenario where I want to filter, sort and page over a result where 3 tables take part.
此刻,我使用Spring Data JPA的Specification功能在单个实体上进行操作:repository.findAll(specification, pageRequest)
.
At the moment I use Spring Data JPA's Specification feature to do it on a single entity: repository.findAll(specification, pageRequest)
.
这很好用,但是现在我遇到另一种情况,其中sort/filter属性分布在通过一对多关系连接的3个表上.
This works great, but now I have another scenario where the sort / filter attributes are spread over 3 tables which are connected by one-to-many relations.
这是我的情况:
@Entity
public class CustomerEntity ... {
...
@Column(nullable = false)
public String customerNumber;
@OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, orphanRemoval = true)
public List<CustomerItemEntity> items;
}
@Entity
public class CustomerItemEntity ... {
...
@Column(nullable = false)
public String itemNumber;
@ManyToOne(optional = false)
@JoinColumn(name = "customerId")
public CustomerEntity customer;
@OneToMany(mappedBy = "item", cascade = CascadeType.ALL, orphanRemoval = true)
public List<DocumentEntity> documents;
}
@Entity
public class DocumentEntity ... {
...
@Column(nullable = false)
public LocalDate validDate;
@ManyToOne(optional = false)
@JoinColumn(name = "itemId")
public CustomerItemEntity item;
}
是否有一种使用PageRequest
和Specification
的方法,其中customerNumber
,itemNumber
和validDate
同时用于过滤,排序和分页?
Is there a way to use PageRequest
and Specification
where customerNumber
, itemNumber
and validDate
are used for filtering, sorting and paging at the same time?
推荐答案
尝试如下操作:
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
?
But I don't know why do you need 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
.然后查询方法变得更加简单:
But all this does not make sense since your three entities have sequential one-to-many associations. In this case instead of three conditions you can use only last one: where d.validDate = ?1
. Then a query method became even easier:
@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);
更新
要按连接实体的字段添加排序,我们可以使用query
的orderBy
方法:
To add sorting by a field of joined entity we can use orderBy
method of the 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
分隔的方法:
To sort by several parameters you can pass them to the method separated by commas or by a List
:
query.orderBy(cb.asc(items.get("customerNumber")), cb.desc(documents.get("itemNumber")));
这篇关于Spring Data JPA:使用联接表进行排序和分页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!