JPA 区间查询 离散查询

JPA 扩展区间查询 离散查询

常用情况及原因

列表查询中，需要支持 起止时间 查询，多状态 查询，JPA本身带的Example的查询并不支持，又不想手写SQL去进行参数判空拼接SQL，则需要对JPA进行扩展，本文基于SpringDataJpa进行扩展。

扩展类

import org.springframework.data.domain.Example;
import org.springframework.data.jpa.convert.QueryByExamplePredicateBuilder;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.query.EscapeCharacter;
import org.springframework.lang.NonNull;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;

/**
 * Summary : JPA example 条件匹配 扩展
 *
 * @Author Ray
 * @Create 2020-09-10 11:46
 */
public class ExamplePredicate<T> implements Specification<T> {

    public ExamplePredicate(Example<T> example,List<FieldRestriction<T>> fieldRestrictionList) {
        this.fieldRestrictionList = fieldRestrictionList;
        this.example = example;
    }
    private final List<FieldRestriction<T>> fieldRestrictionList;
    private final Example<T> example;

    @Override
    public Predicate toPredicate(@NonNull Root<T> root, @NonNull CriteriaQuery<?> query, @NonNull CriteriaBuilder criteriaBuilder) {
        List<Predicate> extPredicates =  new ArrayList<>();
        // 先加进去原有的匹配条件
        Predicate predicate = QueryByExamplePredicateBuilder.getPredicate(root, criteriaBuilder, example, EscapeCharacter.DEFAULT);
        extPredicates.add(predicate);
        //　加入自定义的陪陪条件
        for (FieldRestriction<T> fieldRestriction : fieldRestrictionList) {
            extPredicates.addAll(fieldRestriction.addRestriction(root, criteriaBuilder));
        }
        return criteriaBuilder.and(extPredicates.toArray(new Predicate[0]));
    }
}

条件匹配接口

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.List;

/**
 * Summary : 字段 匹配条件
 *
 * @Author Ray
 * @Create 2020-09-10 15:06
 */
public interface FieldRestriction<T> {
    /**
     * JPA 条件扩展查询
     * @param root 查询根
     * @param criteriaBuilder  标准
     * @return 扩展后的查询条件
     */
    List<Predicate> addRestriction(Root<T> root, CriteriaBuilder criteriaBuilder);
}

条件匹配接口实现=> 日期区间

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * Summary :日期区间的匹配条件扩展
 *
 * @Author Ray
 * @Create 2020-09-10 15:15
 */
public class TimestampRangePredicate<T> implements FieldRestriction<T> {
    public TimestampRangePredicate(String fieldName, Long startTime, Long endTime) {
        this.fieldName = fieldName;
        this.startTime = new Timestamp(startTime);
        this.endTime = new Timestamp(endTime);
    }

    private final String fieldName;
    private final Timestamp startTime;
    private final Timestamp endTime;

    @Override
    public List<Predicate> addRestriction(Root<T> root,CriteriaBuilder criteriaBuilder) {
        List<Predicate> extPredicates = new ArrayList<>();
        extPredicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get(fieldName), startTime));
        extPredicates.add(criteriaBuilder.lessThan(root.get(fieldName), endTime));
        return extPredicates;
    }
}

条件匹配接口实现=> 离散值

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * Summary : 离散的值限制 in (1,2,3)
 *
 * @Author Ray
 * @Create 2020-09-10 15:37
 */
public class ValueInPredicate<T,E> implements  FieldRestriction<T>{
    private final List<E> valueList;
    private final String fieldName;

    public ValueInPredicate(String fieldName,List<E> valueList) {
        this.valueList = valueList;
        this.fieldName = fieldName;
    }

    @Override
    public List<Predicate> addRestriction(Root<T> root, CriteriaBuilder criteriaBuilder) {
        List<Predicate> extPredicates = new ArrayList<>();
        if (Objects.nonNull(valueList)&&!valueList.isEmpty()){
            extPredicates.add(root.get(fieldName).in(valueList));
        }
        return extPredicates;
    }
}