spring data jpa 动态查询Specification（包括各个In、like、Between等等各种工具类，及完整（分页查询）用法步骤（到返回给前端的结果））

最新推荐文章于 2024-03-14 13:37:33 发布

AmbroseLe

最新推荐文章于 2024-03-14 13:37:33 发布

阅读量1.9w

点赞数 10

分类专栏： java基础文章标签： spring data jpa 动态查询 Specification 分页查询 java

本文链接：https://blog.csdn.net/qq_32786139/article/details/86473544

版权

java基础专栏收录该内容

50 篇文章 0 订阅

订阅专栏

一、

二、工具类

1、AbstractSpecification（抽象动态查询条件）

2、各属性查询条件（IsNull、In、Number、String、Like等）

（1）字符串属性查询条件StringSpecification

（2）、Between动态查询条件BetweenSpecification

（3）相等动态查询条件EqualSpecification

（4）In动态查询条件InSpecification

（5）判空查询条件IsNullSpecification

（6）Like动态查询条件LikeSpecification

（7）不为空动态查询条件NotNullSpecification

（8）数字属性查询条件NumberSpecification

（9）SpecificationGroup

（10）SpecificationHelper

（11）分组查询

（12）2019-06-17补充 or 用法

三、用法

适用于多条件动态查询的业务场景。

刚毕业技术比较差，所以记录的比较详细。

一、

repository继承JpaSpecificationExecutor<VehEntity>

public interface VehRepository extends JpaRepository<VehEntity, String>, JpaSpecificationExecutor<VehEntity>
{
}

二、工具类

1、AbstractSpecification（抽象动态查询条件）

import org.springframework.data.jpa.domain.Specification;

/**
 * 抽象动态查询条件
 *
 */
public abstract class AbstractSpecification<T, ATTR> implements Specification<T>
{
	/**
	 * 逻辑运算：等于
	 */
	public static final int LOGICAL_OPERATOR_EQUAL = 1;
	/**
	 * 逻辑运算：小于
	 */
	public static final int LOGICAL_OPERATOR_LESS = 2;
	/**
	 * 逻辑运算：大于
	 */
	public static final int LOGICAL_OPERATOR_GREATER = 3;
	/**
	 * 逻辑运算：小于等于
	 */
	public static final int LOGICAL_OPERATOR_LESS_EQUAL = 4;
	/**
	 * 逻辑运算：大于等于
	 */
	public static final int LOGICAL_OPERATOR_GREATER_EQUAL = 5;
	/**
	 * 逻辑运算：不等于
	 */
	public static final int LOGICAL_OPERATOR_NOT_EQUAL = 6;
	/**
	 * 逻辑运算：包含
	 */
	public static final int LOGICAL_OPERATOR_LIKE = 7;
	/**
	 * 逻辑运算：左包含
	 */
	public static final int LOGICAL_OPERATOR_STARTWITH = 8;
	/**
	 * 逻辑运算：右包含
	 */
	public static final int LOGICAL_OPERATOR_ENDWITH = 9;

	/**
	 * 逻辑运算：非空
	 */
	public static final int LOGICAL_OPERATOR_NOT_NULL = 10;
	/**
	 * 逻辑运算：In
	 */
	public static final int LOGICAL_OPERATOR_IN = 11;
    /**
     * 逻辑运算：包含（自定义）
     */
	public static final int LOGICAL_OPERATOR_LIKE_CUSTOM = 99;
	public static final int LOGICAL_OPERATOR_CUSTOM = 100;
	protected String attrName;
	protected ATTR attrValue;
	protected ATTR[] attrValues;
	protected int logicalOperator;
	/**
	 * 构造方法
	 * @param attrName			属性名称
	 * @param attrValue			属性值
	 * @param logicalOperator	逻辑运算符
	 */
	public AbstractSpecification(String attrName, ATTR attrValue, int logicalOperator) {
		super();
		this.attrName = attrName;
		this.attrValue = attrValue;
		this.logicalOperator = logicalOperator;
	}

	public AbstractSpecification(String attrName, int logicalOperator, ATTR ... attrValues) {
		super();
		this.attrName = attrName;
		this.attrValues = attrValues;
		if (this.attrValues.length > 0) {
			this.attrValue = this.attrValues[0];
		}
		this.logicalOperator = logicalOperator;
	}
}

2、各属性查询条件（IsNull、In、Number、String、Like等）

（1）字符串属性查询条件StringSpecification

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.persistence.criteria.*;

/**
 * 字符串属性查询条件
 */
public class StringSpecification<T> extends AbstractSpecification<T, String> {
    private static Logger log = LoggerFactory
            .getLogger(StringSpecification.class);

    private int start, len;

    /**
     * 构造方法
     *
     * @param attrName        属性名称
     * @param attrValue       属性值
     * @param logicalOperator 逻辑运算符
     */
    public StringSpecification(String attrName, String attrValue, int logicalOperator) {
        this(attrName, logicalOperator, attrValue);
    }

    public StringSpecification(String attrName, int logicalOperator, String... attrValues) {
        super(attrName, logicalOperator, attrValues);
        this.start = 0;
        this.len = 0;
    }

    public StringSpecification(String attrName, int start, int length, int logicalOperator, String... attrValues) {
        super(attrName, logicalOperator, attrValues);
        this.start = start;
        this.len = length;
    }

    private Class<?> getFieldClass(String attr) {
        // TODO
        return null;
    }

    /* (non-Javadoc)
     * @see org.springframework.data.jpa.domain.Specification#toPredicate(javax.persistence.criteria.Root, javax.persistence.criteria.CriteriaQuery, javax.persistence.criteria.CriteriaBuilder)
     */
    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path<String> path = SpecificationHelper.getPath(root, attrName);
        Expression<String> expr = path;
        if (0 < start && 0 < len) {
            expr = cb.substring(path, start, len);
        } else {

        }
        switch (logicalOperator) {
            case StringSpecification.LOGICAL_OPERATOR_EQUAL: {
                return cb.equal(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_GREATER: {
                return cb.greaterThan(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_GREATER_EQUAL: {
                return cb.greaterThanOrEqualTo(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_LESS: {
                return cb.lessThan(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_LESS_EQUAL: {
                return cb.lessThanOrEqualTo(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_NOT_EQUAL: {
                return cb.notEqual(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_LIKE: {
                return cb.like(expr, "%" + attrValue + "%");
            }
            case StringSpecification.LOGICAL_OPERATOR_STARTWITH: {
                return cb.like(expr, attrValue + "%");
            }
            case StringSpecification.LOGICAL_OPERATOR_ENDWITH: {
                return cb.like(expr, "%" + attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_NOT_NULL: {
                return cb.isNotNull(path);
            }
            case StringSpecification.LOGICAL_OPERATOR_LIKE_CUSTOM: {
                return cb.like(expr, attrValue);
            }
            case StringSpecification.LOGICAL_OPERATOR_IN: {
                CriteriaBuilder.In<String> predicate = cb.in(expr);
                for (String item : attrValues) {
                    predicate.value(item);
                }
                return predicate;
            }
            default:
                return null;
        }
    }
}

（2）、Between动态查询条件BetweenSpecification

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;

/**
 * Between动态查询条件
 */
public class BetweenSpecification<T, ATTR extends Comparable<ATTR>> implements Specification<T>
{
	private String attrName;
	private ATTR lowerBound, upperBound;

	/**
	 * 构造方法
	 * @param attrName		属性名称
	 * @param lowerBound	属性值下界
	 * @param upperBound	属性值上界
	 */
	public BetweenSpecification(String attrName, ATTR lowerBound, ATTR upperBound) {
		super();
		this.attrName = attrName;
		this.lowerBound = lowerBound;
		this.upperBound = upperBound;
	}

	/* (non-Javadoc)
	 * @see org.springframework.data.jpa.domain.Specification#toPredicate(javax.persistence.criteria.Root, javax.persistence.criteria.CriteriaQuery, javax.persistence.criteria.CriteriaBuilder)
	 */
	@Override
	public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
		Path<ATTR> path = SpecificationHelper.getPath(root, attrName);
		return cb.between(path, lowerBound, upperBound);
	}

}

（3）相等动态查询条件EqualSpecification

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;

/**
 * 相等动态查询条件
 *
 */
public class EqualSpecification<T, ATTR> implements Specification<T>
{
	private String fieldName;
	private ATTR fieldValue;

	public EqualSpecification(String fieldName, ATTR fieldValue) {
		super();
		this.fieldName = fieldName;
		this.fieldValue = fieldValue;
	}

	@Override
	public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
		Path<ATTR> path = SpecificationHelper.getPath(root, fieldName);
		return cb.equal(path, fieldValue);
	}

}

（4）In动态查询条件InSpecification

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;

/**
 *
 */
public class InSpecification<T, ATTR> implements Specification<T>
{
    private String attrName;
    private ATTR[] values;
    public InSpecification(String attrName, ATTR[] values) {
        this.attrName = attrName;
        this.values = values;
    }

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path<ATTR> path = SpecificationHelper.getPath(root, attrName);
        CriteriaBuilder.In<ATTR> predicate = cb.in(path);
        for(ATTR item : values) {
            predicate.value(item);
        }
        return predicate;
    }
}

（5）判空查询条件IsNullSpecification

import javax.persistence.criteria.*;

/**
 * 
 */
public class IsNullSpecification<T> extends AbstractSpecification<T,Object> {
    public IsNullSpecification(String attrName) {
        super(attrName, null, AbstractSpecification.LOGICAL_OPERATOR_CUSTOM);
    }

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path<Object> path = SpecificationHelper.getPath(root, attrName);

        return cb.isNull(path);
    }
}

（6）Like动态查询条件LikeSpecification

/**
 * Like动态查询条件
 *
 */
public class LikeSpecification<T> extends StringSpecification<T> {
	
	public LikeSpecification(String attrName, String attrValue) {
		super(attrName, attrValue, StringSpecification.LOGICAL_OPERATOR_LIKE);
	}

}

（7）不为空动态查询条件NotNullSpecification

import javax.persistence.criteria.*;

/**
 * 
 */
public class NotNullSpecification<T> extends AbstractSpecification<T,Object> {
    public NotNullSpecification(String attrName) {
        super(attrName, null, AbstractSpecification.LOGICAL_OPERATOR_CUSTOM);
    }

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path<Object> path = SpecificationHelper.getPath(root, attrName);

        return cb.isNotNull(path);
    }
}

（8）数字属性查询条件NumberSpecification

import javax.persistence.criteria.*;

/**
 * 数字属性查询条件
 */
public class NumberSpecification<T> extends AbstractSpecification<T, Number> {

    public NumberSpecification(String attrName, Number attrValue, int logicalOperator) {
        this(attrName, logicalOperator, attrValue);
    }

    public NumberSpecification(String attrName, int logicalOperator, Number... attrValues) {
        super(attrName, logicalOperator, attrValues);
    }

    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Path<Number> path = SpecificationHelper.getPath(root, attrName);
        switch (logicalOperator) {
            case NumberSpecification.LOGICAL_OPERATOR_EQUAL: {
                return cb.equal(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_GREATER: {
                return cb.gt(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_GREATER_EQUAL: {
                return cb.ge(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_LESS: {
                return cb.lt(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_LESS_EQUAL: {
                return cb.le(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_NOT_EQUAL: {
                return cb.notEqual(path, attrValue);
            }
            case NumberSpecification.LOGICAL_OPERATOR_IN: {
                CriteriaBuilder.In<Number> predicate = cb.in(path);
                for (Number item : attrValues) {
                    predicate.value(item);
                }
                return predicate;
            }
            default:
                return null;
        }
    }

}

（9）SpecificationGroup

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;

import java.util.List;

/**
 *
 */
public class SpecificationGroup {
    public static <T> Specifications<T> and(List<Specification<T>> specList) {
        Specifications<T> specs = null;
        for(Specification<T> s : specList) {
            if(specs != null) {
                specs = specs.and(s);
            } else {
                specs = Specifications.where(s);
            }
        }
        return specs;
    }

    public static <T> Specifications<T> or(List<Specification<T>> specList) {
        Specifications<T> specs = null;
        for(Specification<T> s : specList) {
            if(specs != null) {
                specs = specs.or(s);
            } else {
                specs = Specifications.where(s);
            }
        }
        return specs;
    }
}

（10）SpecificationHelper

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;

import javax.persistence.criteria.From;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Root;
import java.util.List;
import java.util.StringTokenizer;

/**
 *
 */
public class SpecificationHelper {
    private static Logger log = LoggerFactory.getLogger(SpecificationHelper.class);

    public static <ATTR, E> Path<ATTR> getPath(Root<E> root, String attrName) {
        From<E, ?> f = root;
        String[] strs = attrName.split("\\.");
        String attr = attrName;
        if (strs.length > 1) {
            for(int i = 0; i < strs.length; i ++) {
                attr = strs[i];
                if(i < strs.length - 1) {
                    boolean hasAttribute = false;
                    if (root.getJoins() != null) {
                        for (Join<E, ?> join : root.getJoins()) {
                            if (attr.equals(join.getAttribute().getName())) {
                                f = join;
                                hasAttribute = true;
                                break;
                            }
                        }
                    }
                    if(!hasAttribute) {
                        f = f.join(attr);
                    }
                }
            }
        }
        return f.get(attr);
    }

    public static <E> From<E, ?> fromCollection(Root<E> root, String attrName) {
        From<E, ?> f = root;
        String attr = attrName;
        StringTokenizer tokenizer = new StringTokenizer(attrName, ".");
        while (tokenizer.hasMoreTokens()) {
            attr = tokenizer.nextToken();
            boolean hasAttribute = false;
            if (root.getJoins() != null) {
                for (Join<E, ?> join : root.getJoins()) {
                    if (attr.equals(join.getAttribute().getName())) {
                        f = join;
                        hasAttribute = true;
                        break;
                    }
                }
            }
            if(!hasAttribute) {
                f = f.join(attr);
            }
        }
        return f;
    }

    public static <T> Specifications<T> and(List<Specification<T>> specList) {
        return SpecificationGroup.and(specList);
    }

    public static <T> Specifications<T> or(List<Specification<T>> specList) {
        return SpecificationGroup.or(specList);
    }

}

（11）分组查询

2019.02.19更新


public class GroupSpecification implements Specification<T> {
    @Override
    public Specification<T> and(Specification<T> other) {
        return null;
    }

    @Override
    public Specification<T> or(Specification<T> other) {
        return null;
    }


    @SuppressWarnings("unchecked")
    @Override
    public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb)
    {
        @SuppressWarnings("rawtypes") List eList=new ArrayList<>();
        eList.add(root.<String>get("detectSn"));

        query.groupBy(eList);
//        query.having(cb.gt( cb.count(root), 1));
        return query.getRestriction();
    }
}

（12）2019-06-17补充 or 用法

List<Specification<VehWarnEntrty>> list = new ArrayList<>();

List<Specification<VehWarnEntrty>> list2 = new ArrayList<>();

//需要做OR操作的字段
list2.add(new EqualSpecification<VehWarnEntrty,String>("ifOutRange","true"));
list2.add(new EqualSpecification<VehWarnEntrty,String>("ifIllegal","true"));
list2.add(new EqualSpecification<VehWarnEntrty,String>("ifHighSpeed","true"));
//添加OR操作的list
list.add(SpecificationHelper.or(list2));

三、用法

创建list

List<Specification<VehEntity>> list = new ArrayList<>();

向list中添加条件

（下面代码的意思是字符串属性 vehNo这个字段右包含某个值）

list.add(new StringSpecification<>("vehNo", StringTool.trim(vehQueryParam.getVehNo()), StringSpecification.LOGICAL_OPERATOR_ENDWITH));

（下面代码的意思是 In 代码中detectResult字段中含有（in）detectResult数组中的值则取出来）

        String [] detectResult = new String[5];
        detectResult[0]="合格";
        detectResult[1]="不合格";
        detectResult[2]="一级";
        detectResult[3]="二级";
        detectResult[4]="三级";
        list.add(new InSpecification<>("detectResult",detectResult));

其他的几个用法就不一一举例说明...

然后是分页查询

//三个参数 1、页码2、取几个值3、排序方式
PageRequest pageRequest = new PageRequest(detectRecordQueryParam.getPageNum() - 1,
                detectRecordQueryParam.getPageSize(), new Sort(new Order(Direction.DESC, "detectDate")));
        Page<VehicleEntity> page;
        if (list.isEmpty()) {
            page = vehRepository.findAll(pageRequest);
        } else {
//前端查询条件传过来的参数 不为空  就是说要用户进行条件查询了 就将list动态查询条件给他
            page = vehRepository.findAll(SpecificationHelper.and(list), pageRequest);
        }

结果操作

使用PageInfo存储查询结果

（这是PageInfo）

import java.util.ArrayList;
import java.util.List;

/**
 */
public class PageInfo<T> extends PageBase
{
    private List<T> records = new ArrayList<T>();
    public List<T> getRecords() {
        return records;
    }
    public void setRecords(List<T> records) {
        this.records = records;
    }
}

PageInfo使用

//对应的Info而不是entity
PageInfo<VehInfo> result = new PageInfo<VehListItem>();
        result.setTotalPages(page.getTotalPages());
        result.setPageNo(page.getNumber());
        result.setPageSize(page.getSize());
        result.setTotalRecords(page.getTotalElements());

对象转换重构

for (VehEntity entity : page.getContent()) {
       VehListItem item = new VehicleListItem();
       // 对象转换重构
       item.setVehicleId(entity.getVehicleId());
       item.setVehicleNo(entity.getVehicleNo());

       result.getRecords().add(item);
 }

这里的result 就是要返回给前端的结果集

AmbroseLe

关注

10
点赞
踩
52

收藏

觉得还不错? 一键收藏
0
评论
spring data jpa 动态查询Specification（包括各个In、like、Between等等各种工具类，及完整（分页查询）用法步骤（到返回给前端的结果））

目录一、二、工具类1、AbstractSpecification（抽象动态查询条件）2、各属性查询条件（IsNull、In、Number、String、Like等）（1）字符串属性查询条件StringSpecification （2）、Between动态查询条件BetweenSpecification （3）相等动态查询条件EqualSp...
复制链接

扫一扫