JPA字段取值范围查询，如何精准定位特定数据区间？

本文共计748个文字，预计阅读时间需要3分钟。

javapublic class Range implements Serializable { @Data @AllArgsConstructor private final Integer min; private final Integer max;}

Range取值范围

import lombok.AllArgsConstructor; import lombok.Data; import java.io.Serializable; /** * 查询范围 * Created by yupaits on 2017/10/27. */ @Data @AllArgsConstructor public class Range implements Serializable { private static final long serialVersionUID = 1L; private String field; private Comparable from; private Comparable to; private Boolean includeNull; public Range(String field) { this.field = field; } public Range(String field, Comparable from, Comparable to) { this.field = field; this.from = from; this.to = to; } public Range(Range other) { this.field = other.getField(); this.from = other.getFrom(); this.to = other.getTo(); this.includeNull = other.getIncludeNull(); } public boolean isFromSet() { return getFrom() != null; } public boolean isToSet() { return getTo() != null; } public boolean isIncludeNullSet() { return getIncludeNull() != null; } public boolean isBetween() { return isFromSet() && isToSet(); } public boolean isSet() { return isFromSet() || isToSet() || isIncludeNullSet(); } public boolean isValid() { return !isBetween() || getFrom().compareTo(getTo()) <= 0; } } ExampleSpecification字段查询规范

import org.springframework.data.domain.Example; import org.springframework.data.jpa.convert.QueryByExamplePredicateBuilder; 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; /** * 样板查询规范 * Created by yupaits on 2017/10/27. */ public class ExampleSpecification implements Specification { private final Example example; public ExampleSpecification(Example example) { Assert.notNull(example, "Example must not be null!"); this.example = example; } @Override public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) { return QueryByExamplePredicateBuilder.getPredicate(root, criteriaBuilder, example); } } RangeSpecification取值范围查询规范

import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import org.springframework.data.jpa.domain.Specification; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import java.util.List; /** * 范围查询规范 * Created by yupaits on 2017/10/27. */ public class RangeSpecification implements Specification { private final List > ranges; public RangeSpecification(List > ranges) { this.ranges = ranges; } @Override public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) { List

predicateList = Lists.newArrayList(); for (Range range : ranges) { if (range.isSet()) { Predicate rangePredicate = buildRangePredicate(range, root, criteriaBuilder); if (rangePredicate != null) { if (!range.isIncludeNullSet() || range.getIncludeNull() == Boolean.FALSE) { predicateList.add(rangePredicate); } else { predicateList.add(criteriaBuilder.or(rangePredicate, criteriaBuilder.isNull(root.get(range.getField())))); } } if (Boolean.TRUE == range.getIncludeNull()) { predicateList.add(criteriaBuilder.isNull(root.get(range.getField()))); } else if (Boolean.FALSE == range.getIncludeNull()) { predicateList.add(criteriaBuilder.isNotNull(root.get(range.getField()))); } } } return predicateList.isEmpty() ? criteriaBuilder.conjunction() : criteriaBuilder.and(Iterables.toArray(predicateList, Predicate.class)); } private Predicate buildRangePredicate(Range range, Root root, CriteriaBuilder criteriaBuilder) { if (range.isBetween()) { return criteriaBuilder.between(root.get(range.getField()), range.getFrom(), range.getTo()); } else if (range.isFromSet()) { return criteriaBuilder.greaterThanOrEqualTo(root.get(range.getField()), range.getFrom()); } else if (range.isToSet()) { return criteriaBuilder.lessThanOrEqualTo(root.get(range.getField()), range.getTo()); } return null; } } BaseRepository基础仓库类

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.io.Serializable; import java.util.List; /** * 基础仓库类 * Created by yupaits on 2017/10/27. */ @NoRepositoryBean public interface BaseRepository extends JpaRepository { Page findByExampleWithRange(Example example, List > ranges, Pageable pageable); } BaseRepositoryImpl基础仓库实现类

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.domain.Specifications; import org.springframework.data.jpa.repository.support.JpaEntityInformation; import org.springframework.data.jpa.repository.support.SimpleJpaRepository; import javax.persistence.EntityManager; import java.io.Serializable; import java.util.List; /** * 字段、取值范围查询 * Created by yupaits on 2017/10/27. */ public class BaseRepositoryImpl extends SimpleJpaRepository implements BaseRepository { private final EntityManager entityManager; public BaseRepositoryImpl(JpaEntityInformation entityInformation, EntityManager entityManager) { super(entityInformation, entityManager); this.entityManager = entityManager; } @Override public Page findByExampleWithRange(Example example, List > ranges, Pageable pageable) { Specification exampleSpecification = new ExampleSpecification<>(example); Specification rangesSpecification = new RangeSpecification<>(ranges); return findAll(Specifications.where(exampleSpecification).and(rangesSpecification), pageable); } }