设为首页收藏本站
开启辅助访问

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

 您所在的位置：网站首页 › gsmarena查询手机 › spring data jpa 动态查询Specification（包括各个In、like、Between等等各种工具类，及完整（分页查询）用法步骤（到返回给前端的结果））

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

2023-08-15 10:02| 来源: 网络整理| 查看: 265

目录

一、

二、工具类

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

public interface VehRepository extends JpaRepository, JpaSpecificationExecutor { } 二、工具类 1、AbstractSpecification（抽象动态查询条件） import org.springframework.data.jpa.domain.Specification; /** * 抽象动态查询条件 * */ public abstract class AbstractSpecification implements Specification { /** * 逻辑运算：等于 */ 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 extends AbstractSpecification { 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 root, CriteriaQuery query, CriteriaBuilder cb) { Path path = SpecificationHelper.getPath(root, attrName); Expression 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 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 implements Specification { 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 root, CriteriaQuery query, CriteriaBuilder cb) { Path 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 implements Specification { private String fieldName; private ATTR fieldValue; public EqualSpecification(String fieldName, ATTR fieldValue) { super(); this.fieldName = fieldName; this.fieldValue = fieldValue; } @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) { Path 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 implements Specification { private String attrName; private ATTR[] values; public InSpecification(String attrName, ATTR[] values) { this.attrName = attrName; this.values = values; } @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) { Path path = SpecificationHelper.getPath(root, attrName); CriteriaBuilder.In predicate = cb.in(path); for(ATTR item : values) { predicate.value(item); } return predicate; } }       （5）判空查询条件IsNullSpecification import javax.persistence.criteria.*; /** * */ public class IsNullSpecification extends AbstractSpecification { public IsNullSpecification(String attrName) { super(attrName, null, AbstractSpecification.LOGICAL_OPERATOR_CUSTOM); } @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) { Path path = SpecificationHelper.getPath(root, attrName); return cb.isNull(path); } }       （6）Like动态查询条件LikeSpecification /** * Like动态查询条件 * */ public class LikeSpecification extends StringSpecification { public LikeSpecification(String attrName, String attrValue) { super(attrName, attrValue, StringSpecification.LOGICAL_OPERATOR_LIKE); } }       （7）不为空动态查询条件NotNullSpecification import javax.persistence.criteria.*; /** * */ public class NotNullSpecification extends AbstractSpecification { public NotNullSpecification(String attrName) { super(attrName, null, AbstractSpecification.LOGICAL_OPERATOR_CUSTOM); } @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) { Path path = SpecificationHelper.getPath(root, attrName); return cb.isNotNull(path); } }       （8）数字属性查询条件NumberSpecification import javax.persistence.criteria.*; /** * 数字属性查询条件 */ public class NumberSpecification extends AbstractSpecification { 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 root, CriteriaQuery query, CriteriaBuilder cb) { Path 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 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 Specifications and(List specList) { Specifications specs = null; for(Specification s : specList) { if(specs != null) { specs = specs.and(s); } else { specs = Specifications.where(s); } } return specs; } public static Specifications or(List specList) { Specifications specs = null; for(Specification 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 Path getPath(Root root, String attrName) { From 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 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 From fromCollection(Root root, String attrName) { From 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 join : root.getJoins()) { if (attr.equals(join.getAttribute().getName())) { f = join; hasAttribute = true; break; } } } if(!hasAttribute) { f = f.join(attr); } } return f; } public static Specifications and(List specList) { return SpecificationGroup.and(specList); } public static Specifications or(List specList) { return SpecificationGroup.or(specList); } }       （11）分组查询

    2019.02.19更新

public class GroupSpecification implements Specification { @Override public Specification and(Specification other) { return null; } @Override public Specification or(Specification other) { return null; } @SuppressWarnings("unchecked") @Override public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) { @SuppressWarnings("rawtypes") List eList=new ArrayList(); eList.add(root.get("detectSn")); query.groupBy(eList); // query.having(cb.gt( cb.count(root), 1)); return query.getRestriction(); } }         （12）2019-06-17补充 or 用法 List list = new ArrayList(); List list2 = new ArrayList(); //需要做OR操作的字段 list2.add(new EqualSpecification("ifOutRange","true")); list2.add(new EqualSpecification("ifIllegal","true")); list2.add(new EqualSpecification("ifHighSpeed","true")); //添加OR操作的list list.add(SpecificationHelper.or(list2)); 三、用法

   创建list

List 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 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 extends PageBase { private List records = new ArrayList(); public List getRecords() { return records; } public void setRecords(List records) { this.records = records; } }

PageInfo使用

//对应的Info而不是entity PageInfo result = new PageInfo(); 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 就是要返回给前端的 结果集

 



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3