扩展SpringDataQBE实现动态范围查询-创新互联

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;

@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许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。