当Repository接口继承了JpaSpecificationExecutor后，我们就可以使用如下接口进行分页查询：
/**
* Returns a {@link Page} of entities matching the given {@link Specification}.
*
* @param spec can be {@literal null}.
* @param pageable must not be {@literal null}.
* @return never {@literal null}.
*/
Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
结合jpa-spec可以很容易构造出Specification:
jpa-spec github地址:https://github.com/wenhao/jpa-spec
public Page<Person> findAll(SearchRequest request) {
Specification<Person> specification = Specifications.<Person>and()
.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
.gt(Objects.nonNull(request.getAge()), "age", 18)
.between("birthday", new Date(), new Date())
.like("nickName", "%og%", "%me")
.build();
return personRepository.findAll(specification, new PageRequest(0, 15));
}
单表查询确实很简单，但对复杂查询，就复杂上些了：
public List<Phone> findAll(SearchRequest request) {
Specification<Phone> specification = Specifications.<Phone>and()
.eq(StringUtils.isNotBlank(request.getBrand()), "brand", "HuaWei")
.eq(StringUtils.isNotBlank(request.getPersonName()), "person.name", "Jack")
.build();
return phoneRepository.findAll(specification);
}
这里主表是phone，使用了person的name做条件,使用方法是person.name。
jpa-spec内部会分析person.name，如下代码：
public From getRoot(String property, Root<T> root) {
if (property.contains(".")) {
String joinProperty = StringUtils.split(property, ".")[0];
return root.join(joinProperty, JoinType.LEFT);
} else {
return root;
}
}
就可看到它用了root.join，那就有一个问题，如果有两个person字段的条件，那就要再join一次，就会生成这样的sql:
select * from phone left outer join person on XX=XX left outer join person XX=XX.
这样肯定不满足需求。这应该也是jpa-spec的一个bug吧
为了解决这个问题，可以使用它提供的另一种方式查询：
public List<Phone> findAll(SearchRequest request) {
Specification<Person> specification = Specifications.<Person>and()
.between("age", 10, 35)
.predicate(StringUtils.isNotBlank(jack.getName()), ((root, query, cb) -> {
Join address = root.join("addresses", JoinType.LEFT);
return cb.equal(address.get("street"), "Chengdu");
}))
.build();
return phoneRepository.findAll(specification);
}
这要就可以解决大多数情况了，除了分页
看下正常的单表分页+排序查询：
public Page<Person> findAll(SearchRequest request) {
Specification<Person> specification = Specifications.<Person>and()
.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
.gt("age", 18)
.between("birthday", new Date(), new Date())
.like("nickName", "%og%")
.build();
Sort sort = Sorts.builder()
.desc(StringUtils.isNotBlank(request.getName()), "name")
.asc("birthday")
.build();
return personRepository.findAll(specification, new PageRequest(0, 15, sort));
}
如果在此基础上增加关联，如下代码：
public Page<Person> findAll(SearchRequest request) {
Specification<Person> specification = Specifications.<Person>and()
.predicate(StringUtils.isNotBlank(jack.getName()), ((root, query, cb) -> {
Join address = root.join("addresses", JoinType.LEFT);
return cb.equal(address.get("street"), "Chengdu");
}))
.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
.gt("age", 18)
.between("birthday", new Date(), new Date())
.like("nickName", "%og%")
.build();
Sort sort = Sorts.builder()
.desc(StringUtils.isNotBlank(request.getName()), "name")
.asc("birthday")
.build();
return personRepository.findAll(specification, new PageRequest(0, 15, sort));
}
就会发现addresses的延迟加载失效，生成很多查询addresses的语句，解决方案如下：
public Page<Person> findAll(SearchRequest request) {
Specification<Person> specification = Specifications.<Person>and()
.predicate(StringUtils.isNotBlank(jack.getName()), ((root, query, cb) -> {
Join address;
if (Long.class != query.getResultType()) {
address = (Join) root.fetch("addresses", JoinType.LEFT);
} else {
address = root.join("addresses", JoinType.LEFT);
}
return cb.equal(address.get("street"), "Chengdu");
}))
.eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
.gt("age", 18)
.between("birthday", new Date(), new Date())
.like("nickName", "%og%")
.build();
Sort sort = Sorts.builder()
.desc(StringUtils.isNotBlank(request.getName()), "name")
.asc("birthday")
.build();
return personRepository.findAll(specification, new PageRequest(0, 15, sort));
}
至此，用Specification查询就应该够用了，再配合JpaRepository （SimpleJpaRepository）提供的方法 和@Query注解方法，和criteria api查询，这四种JPA查询就可以解决大多数应用问题了。