1.适用范围

• 动态构建查询语句
• 支持所有的查询条件
• 支持子查询、连接查询、排序、分页
• 不支持自定义模型，无法做到将查询的结果封装为自定义的model，或类似List的单列结果。只能返回dao定义的entity。
  即，只支持select * from ...

1.1 JpaSpecificationExecutor接口方法介绍

public interface JpaSpecificationExecutor {// 查询单个对象Optional findOne(@Nullable Specification spec);// 查询列表List findAll(@Nullable Specification spec);// 查询全部，分页；可以使用PageRequest#of(int, int, Sort)方法创建pageable对象实现分页+排序Page findAll(@Nullable Specification spec, Pageable pageable);// 查询全部，排序List findAll(@Nullable Specification spec, Sort sort);// 统计查询long count(@Nullable Specification spec);
}

1.2 Specification类

Specification类是一个用于构建动态查询条件的抽象类。

public interface Specification {Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder builder);
}

toPredicate方法

Specification类通过实现抽象方法Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder criteriaBuilder)来构建动态查询条件，查询条件最终会生成一个查询条件对象（Predicate对象）

• Root类：用于获取需要查询的列
  • 如：root.get("lastName")
• CriteriaQuery类：用于自定义查询方式
  • 如：query.distinct(true).select(...).where(...).groupBy(...).having(...).getRestriction()。最后通过getRestriction()方法，可以获得一个Predicate对象
  • 详见
• CriteriaBuilder类：构造查询条件（即，用于构建Predicate对象），内部封装了很多的查询条件
  • 如：criteriaBuilder.and(...)、criteriaBuilder.equal(...)、criteriaBuilder.greaterThan)、criteriaBuilder.greaterThanOrEqualTo)等，返回的结果是一个Predicate对象
  • 详见

1.3 示例

1.3.1 简单示例

(1) 编写查询条件

@Test
public void testSimpleCbSpecification() {Specification specification = (root, query, cb) -> {// 等于Predicate predicate1 = cb.equal(root.get("crToolRunMode"), "CLN");// 不等于Predicate predicate2 = cb.notEqual(root.get("prdSeqId"), "L000001");// 大于Predicate predicate3 = cb.greaterThan(root.get("prdQty"), 1);// 大于等于Predicate predicate4 = cb.greaterThanOrEqualTo(root.get("prdStdQty"), 1);// 小于Predicate predicate5 = cb.lessThan(root.get("prdQty"), 2);// 小于等于Predicate predicate6 = cb.lessThanOrEqualTo(root.get("prdStdQty"), 2);// betweenTimestamp startTime = Timestamp.valueOf(LocalDateTime.of(2021, 1, 1, 8, 0, 0));Timestamp endTime = Timestamp.valueOf(LocalDateTime.of(2022, 1, 1, 8, 0, 0));Predicate predicate7 = cb.between(root.get("evtTimestamp"), startTime, endTime);// 非空Predicate predicate8 = cb.isNotNull(root.get("prty"));// 为空Predicate predicate9 = cb.isNull(root.get("costCode"));// 模糊查询Predicate predicate10 = cb.like(root.get("tempPrdSeqId"), "L%");// inPredicate predicate11 = root.get("evtUsr").in("AA", "BB");Predicate[] predicateArray = {predicate1, predicate2, predicate3, predicate4, predicate5, predicate6, predicate7, predicate8, predicate9, predicate10, predicate11};return query.where(predicateArray).getRestriction();};List retPrdInfoList = retPrdInfoDao.findAll(specification);retPrdInfoList.stream().forEach(System.out::println);
}

生成的sql语句：

select 略
from ret_prd_info
where cr_tool_run_mode = ?and prd_seq_id <> ?and prd_qty > 1and prd_std_qty >= 1and prd_qty < 2and prd_qty <= 2and (evt_timestamp between ? and ?)and (prty is not null)and (cost_code is null)and (temp_prd_seq_id like ?)and (retprdinfo0_.evt_usr in (?, ?));

(2) 查询条件的连接

1️⃣ 写法一

@Test
public void testConjunctionSpecification1() {Specification specification = (root, query, cb) -> {Predicate predicate1 = cb.greaterThan(root.get("prdQty"), 1);Predicate predicate2 = cb.lessThan(root.get("prdQty"), 2);Predicate orPredicate1 = cb.or(predicate1, predicate2); // 使用or连接两个条件Predicate predicate3 = cb.greaterThanOrEqualTo(root.get("prdStdQty"), 1);Predicate predicate4 = cb.lessThanOrEqualTo(root.get("prdStdQty"), 2);Predicate orPredicate2 = cb.or(predicate3, predicate4); // 使用or连接两个条件Predicate andPredicate = cb.and(orPredicate1, orPredicate2);return query.where(andPredicate).getRestriction();      // 使用and连接两个条件};List retPrdInfoList = retPrdInfoDao.findAll(specification);retPrdInfoList.stream().forEach(System.out::println);
}

生成的sql语句：

select 略
from ret_prd_info
where prd_qty > 1 and prd_qty < 2or prd_std_qty >= 1 and prd_std_qty <= 2

where 后面如果有and、or的条件，则or自动会把左右的查询条件分开，即先执行and，再执行or。

原因就是：and的执行优先级最高。 关系型运算符优先级高到低为：not and or

2️⃣ 写法二

@Test
public void testConjunctionSpecification2() {Specification specification = (root, query, cb) -> {Predicate predicate1 = cb.greaterThan(root.get("prdQty"), 1);Predicate predicate2 = cb.lessThan(root.get("prdQty"), 2);Predicate[] orArray1 = {predicate1, predicate2};Predicate orPredicate1 = cb.or(orArray1);              // 用or连接数组中的每个条件Predicate predicate3 = cb.greaterThanOrEqualTo(root.get("prdStdQty"), 1);Predicate predicate4 = cb.lessThanOrEqualTo(root.get("prdStdQty"), 2);Predicate[] orArray2 = {predicate3, predicate4};Predicate orPredicate2 = cb.or(orArray2);               // 用or连接数组中的每个条件Predicate[] andArray = {orPredicate1, orPredicate2};Predicate andPredicate = cb.and(andArray);              // 用and连接数组中的每个条件return query.where(andPredicate).getRestriction();};List retPrdInfoList = retPrdInfoDao.findAll(specification);retPrdInfoList.stream().forEach(System.out::println);
}

3️⃣ 写法三

@Test{
public void testConjunctionSpecification3() {Specification specification = (root, query, cb) -> {Predicate predicate1 = cb.greaterThan(root.get("prdQty"), 1);Predicate predicate2 = cb.lessThan(root.get("prdQty"), 2);Predicate[] predicateArray = {predicate1, predicate2};return query.where(predicateArray).getRestriction(); // where中放入predicate数组，使用and连接数组中的每个条件};List retPrdInfoList = retPrdInfoDao.findAll(specification);retPrdInfoList.stream().forEach(System.out::println);
}}

生成的sql：

select 略
from ret_prd_info
where prd_qty > 1and prd_qty < 2

(3) 对查询结果排序

• Sort sort = Sort.by("firstname").ascending().and(Sort.by("lastname").descending());

• TypedSort person = Sort.sort(Person.class);
  Sort sort = person.by(Person::getFirstname).ascending().and(person.by(Person::getLastname).descending());
  

@Test
public void testOrder() {Specification spec = (root, query, cb) -> cb.equal(root.get("crToolRunMode"), "CLN");Sort sort = Sort.by(Sort.Direction.DESC, "id");List list = customerDao.findAll(spec, sort);
}

(4) 分页查询

@Test
public void testPagging() {Specification spec = (root, query, cb) -> cb.equal(root.get("crToolRunMode"), "CLN");Pageable pageable = PageRequest.of(0, 20);// 可以使用`Pageable pageable = PageRequest.of(0, 20, Sort.by("evtTimestamp"));`实现分页+排序Page page = customerDao.findAll(spec, pageable);List list = page.getContent();      // 该页的内容long totalElements = page.getTotalElements(); // 全表的记录数目
}

1.3.2 多条件查询示例（and、equal、like、sort、page）

每一个Predicate就是一个条件

public void queryByCondition(FbpbisdatI inTrx, FbpbisdatO outTrx) {FbpbisdatIA iary = inTrx.getIary().get(0);Specification specification = (root, query, cb) -> {List predicateList = new ArrayList<>();if (StringUtils.isNotBlank(iary.getDataCate())) {predicateList.add(cb.equal(root.get("dataCate"), iary.getDataCate()));}if (StringUtils.isNotBlank(iary.getDataSeqId())) {predicateList.add(cb.like(root.get("dataSeqId"), "%" + ia.getMdlId() + "%"));// `"%" + ia.getMdlId() + "%"` 可以改写为 `Util.liktStr(ia.getmdlId())`}// 略Predicate[] p = new Predicate[predicateList.size()];query.where(predicateList.toArray(p));return query.getRestriction();};Sort sort = Sort.by("dataCate", "dataId", "dataExt");List bisDataList;// 判断是否需要分页if (Util.shouldPage(inTrx)) {Page bisDataPage = bisDataDao.findAll(specification, PageRequest.of(inTrx.getPageNum() - 1, inTrx.getPageSize(), sort));// `PageRequest.of(inTrx.getPageNum() - 1, inTrx.getPageSize(), sort)`// 可以改写为 `Util.getPageable(inTrx, sort)`bisDataList = bisDataPage.getContent();long tblCnt = bisDataPage.getTotalElements();outTrx.setTblCnt(tblCnt);} else {bisDataList = bisDataDao.findAll(specification, sort);}List oary = CloneUtil.cloneList(bisDataList, FbpbisdatOA::new);outTrx.setOary(oary);
}

1.3.3 子查询示例

public void testSubSpecification() {Specification specification = (root, query, cb) -> {// 1).父查询条件Predicate parentPredicate = cb.equal(root.get("crToolRunMode"), "CLN");// 2).子查询Subquery subQuery = query.subquery(RetPrdAbn.class);Root subRoot = subQuery.from(RetPrdAbn.class);subQuery = subQuery.select(subRoot.get("prdSeqIdFk"));// 子查询的cb和父查询的cb相同Predicate subPredicate1 = cb.equal(subRoot.get("lotIdFk"), "LW0001");Predicate subPredicate2 = cb.equal(subRoot.get("lotSpltIdFk"), "00");subQuery.where(cb.and(subPredicate1, subPredicate2));// 3).将父查询条件和子查询合并Predicate parentAndSubConjunctPredicate = root.get("prdSeqId").in(subQuery);return query.where(parentPredicate, parentAndSubConjunctPredicate).getRestriction();};List retPrdInfoList = retPrdInfoDao.findAll(specification);retPrdInfoList.stream().forEach(System.out::println);
}

生成的sql语句：

select略
from ret_prd_info
where cr_tool_run_mode = ?and (prd_seq_id in (select prd_seq_id_fkfrom ret_prd_abnwhere lot_id_fk = ? and retprdabn1_.lot_splt_id_fk = ?))