Spring Data JPA提供了Query by Example (QBE) 查询技术,实现了动态条件查询,不必再写烦琐的条件判断。但QBE不支持范围查询,本文结合QBE和Specification实现了动态范围查询。
成都创新互联专注于凌河网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供凌河营销型网站建设,凌河网站制作、凌河网页设计、凌河网站官网定制、小程序制作服务,打造凌河网络公司原创品牌,更为您提供凌河网站排名全网营销落地服务。本文以汪云飞-Spring Data JPA实现动态条件与范围查询实例代码为基础修改,利用org.springframework.data.domain.Range替换了自定义实现,支持Matching Any,保持了原代码的基本结构。源码地址 https://github.com/sunjc/heroes-api
import org.springframework.data.domain.Range;
import org.springframework.data.domain.Range.Bound;
import static org.springframework.data.domain.Range.Bound.inclusive;
public class FieldRange> {
private String field;
private Range range;
public FieldRange(String field, T lower, T upper) {
this.field = field;
this.range = of(lower, upper);
}
private Range of(T lower, T upper) {
Bound lowerBound = Bound.unbounded();
Bound upperBound = Bound.unbounded();
if (lower != null) {
lowerBound = inclusive(lower);
}
if (upper != null) {
upperBound = inclusive(upper);
}
return Range.of(lowerBound, upperBound);
}
public String getField() {
return field;
}
public Range getRange() {
return range;
}
}
提取Example的Specification,SimpleJpaRepository内含有此类,是私有的。
import org.springframework.data.domain.Example;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.Assert;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import static org.springframework.data.jpa.convert.QueryByExamplePredicateBuilder.getPredicate;
public class ExampleSpecification implements Specification {
private static final long serialVersionUID = 1L;
private final Example example; //NOSONAR
public ExampleSpecification(Example example) {
Assert.notNull(example, "Example must not be null!");
this.example = example;
}
@Override
public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder criteriaBuilder) {
return getPredicate(root, criteriaBuilder, example);
}
}
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.util.Optional;
public class RangeSpecification> implements Specification {
private FieldRange fieldRange; //NOSONAR
public RangeSpecification(FieldRange fieldRange) {
this.fieldRange = fieldRange;
}
@Override
public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder builder) {
Optional lower = fieldRange.getRange().getLowerBound().getValue();
Optional upper = fieldRange.getRange().getUpperBound().getValue();
Path path = root.get(fieldRange.getField());
if (lower.isPresent() && upper.isPresent()) {
return builder.between(path, lower.get(), upper.get());
}
if (lower.isPresent()) {
return builder.greaterThanOrEqualTo(path, lower.get());
}
if (upper.isPresent()) {
return builder.lessThanOrEqualTo(path, upper.get());
}
return null;
}
}
WiselyRepository接口
import org.itrunner.heroes.repository.specifications.FieldRange;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;
import java.util.List;
@NoRepositoryBean
public interface WiselyRepository extends JpaRepository { //NOSONAR
Page findByExampleAndRange(Example example, List> fieldRanges, Pageable pageable);
List findByExampleAndRange(Example example, List> fieldRanges);
}
WiselyRepository实现
import org.itrunner.heroes.repository.WiselyRepository;
import org.itrunner.heroes.repository.specifications.ExampleSpecification;
import org.itrunner.heroes.repository.specifications.FieldRange;
import org.itrunner.heroes.repository.specifications.RangeSpecification;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import javax.persistence.EntityManager;
import java.util.ArrayList;
import java.util.List;
import static org.springframework.data.jpa.domain.Specification.where;
public class WiselyRepositoryImpl extends SimpleJpaRepository implements WiselyRepository { //NOSONAR
public WiselyRepositoryImpl(JpaEntityInformation entityInformation, EntityManager entityManager) {
super(entityInformation, entityManager);
}
@Override
public Page findByExampleAndRange(Example example, List> fieldRanges, Pageable pageable) {
return findAll(specifications(example, fieldRanges), pageable);
}
@Override
public List findByExampleAndRange(Example example, List> fieldRanges) {
return findAll(specifications(example, fieldRanges));
}
private Specification specifications(Example example, List> fieldRanges) {
boolean allMatching = example.getMatcher().isAllMatching();
Specification byExample = new ExampleSpecification<>(example);
List> byRanges = getRangeSpecifications(fieldRanges);
return conjunction(byExample, byRanges, allMatching);
}
private List> getRangeSpecifications(List> fieldRanges) {
List> rangeSpecifications = new ArrayList<>();
for (FieldRange fieldRange : fieldRanges) {
rangeSpecifications.add(new RangeSpecification<>(fieldRange));
}
return rangeSpecifications;
}
private Specification conjunction(Specification byExample, List> byRanges, boolean allMatching) {
Specification specification = where(byExample);
for (Specification rangeSpecification : byRanges) {
if (allMatching) {
specification = specification.and(rangeSpecification);
} else {
specification = specification.or(rangeSpecification);
}
}
return specification;
}
}
启用WiselyRepositoryImpl:
@EnableJpaRepositories(repositoryBaseClass = WiselyRepositoryImpl.class)
调用范围查询:
public Page findHeroes(Hero hero, Date startDate, Date endDate, int minAge, int maxAge, Pageable pageable) {
List> fieldRanges = new ArrayList<>();
fieldRanges.add(new FieldRange<>("birthday", startDate, endDate));
fieldRanges.add(new FieldRange<>("age", minAge, maxAge));
return heroRepository.findByExampleAndRange(of(hero), fieldRanges, pageable);
}
另外有需要云服务器可以了解下创新互联scvps.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。