扩展Spring Data QBE实现动态范围查询

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

实现代码

FieldRange

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;    }}

ExampleSpecification

提取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);    }}

RangeSpecification

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;    }}

自定义Repository

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;@NoRepositoryBeanpublic 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);}