jpa的JpaSpecificationExecutor使用对象自定义sql条件；JPA的Predicate排序/复杂查询

Specification的使用

Spring Data Jpa同样提供了类似Hibernated 的Criteria的查询方式，要使用这种方式只要继承JpaSpecificationExecutor,该接口提供了如下一些方法

T findOne(Specification<T> var1);
List<T> findAll(Specification<T> var1);
Page<T> findAll(Specification<T> var1, Pageable var2);
List<T> findAll(Specification<T> var1, Sort var2);
long count(Specification<T> var1);

比如继承JpaSpecificationExecutor的dao层

@Repository
public interface ShopUserMiddleRepository extends JpaRepository<ShopUserMiddleDO, Long>, JpaSpecificationExecutor<ShopUserMiddleDO> {

    ShopUserMiddleDO findByUserId(Long userId);
}

该接口通过Specification来定义查询条件，很多朋友可能使用的方式都是基于SQL的，对这种方式可能不太习惯，其实就是类似mybatisplus的wrapper，将sql条件用对象来实现。这里先简单看一下示例。

@Test
public void testSpecificaiton() {
    List<Student> stus = studentSpecificationRepository.findAll(new Specification<Student>() {
        @Override
        public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
            //root.get("address")表示获取address这个字段名称（获取实体类的属性名对应的表字段名）,like表示执行like查询,%zt%表示值
            
            Predicate p1 = criteriaBuilder.like(root.get("address"), "%zt%");
            Predicate p2 = criteriaBuilder.greaterThan(root.get("id"),3);
            //将两个查询条件联合起来之后返回Predicate对象
            return criteriaBuilder.and(p1,p2);
        }
    });
    Assert.assertEquals(2,stus.size());
    Assert.assertEquals("oo",stus.get(0).getName());
}



public static Specification<HousekeepingServicesAreaDO> getSpecification(GetHousekeepingServicesAreaParams param){
    return (root,query,cb) -> {
        List<Predicate> list = Lists.newArrayList();
        if (Objects.nonNull(param.getAreaRange())) {

            Predicate equal = cb.equal(root.get("areaRange"), param.getAreaRange());
            list.add(equal);

        }

        list.add(cb.equal(root.get("isDeleted"), Boolean.FALSE));

        Predicate[] predicates = new Predicate[list.size()];

        query.where(cb.and(list.toArray(predicates)));

        if(StringUtils.isNotBlank(param.getSortColumn())){
            if("asc".equalsIgnoreCase(param.getSort())){
                query.orderBy(cb.asc(root.get(param.getSortColumn())));
            }else if("desc".equalsIgnoreCase(param.getSort())){
                query.orderBy(cb.desc(root.get(param.getSortColumn())));
            }
        }

        return query.getRestriction();
    };
}

多个Specification组合

使用Specification的要点就是CriteriaBuilder，通过这个对象来创建条件，之后返回一个Predicate对象。这个对象中就有了相应的查询需求，我们同样可以定义多个Specification，之后通过Specifications对象将其连接起来。以下是一个非常典型的应用

@Test
public void testSpecificaiton2() {
//第一个Specification定义了两个or的组合
Specification<Student> s1 = new Specification<Student>() {
    @Override
    public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Predicate p1 = criteriaBuilder.equal(root.get("id"),"2");
        Predicate p2 = criteriaBuilder.equal(root.get("id"),"3");
        return criteriaBuilder.or(p1,p2);
    }
};
//第二个Specification定义了两个or的组合
Specification<Student> s2 = new Specification<Student>() {
    @Override
    public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
        Predicate p1 = criteriaBuilder.like(root.get("address"),"zt%");
        Predicate p2 = criteriaBuilder.like(root.get("name"),"foo%");
        return criteriaBuilder.or(p1,p2);
    }
};
//通过Specifications将两个Specification连接起来，第一个条件加where，第二个是and
List<Student> stus = studentSpecificationRepository.findAll(Specifications.where(s1).and(s2));

    Assert.assertEquals(1,stus.size());
    Assert.assertEquals(3,stus.get(0).getId());
}

JPAPredicate查询排序

对已删除数据和locationInfoId对数据进行过滤，然后根据deliveryType，orderPaytime字段对数据进行排序，前三种deliveryType的订单排在前面，并按orderPaytime进行升序排列，后两种类型的订单排在后面，也按orderPaytime进行升序排列。

Specification specification =
                (Specification<ShipOrderInfo>) (root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> predicates = new ArrayList<>();
 
                    // 排除逻辑删除数据
                    predicates.add(criteriaBuilder.equal(root.get("dataStatus"), AuditModel.DATA_STATUS_ACTVIE));
 
                    if (null != locationInfoId) {
                        predicates.add(criteriaBuilder.equal(root.get("locationInfoId"), locationInfoId));
                    }
                    if (!StringUtils.isEmpty(shipStatus)) {
                        CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("orderLatestStatus"));
                        String[] shipStatusArray = StringUtils.split(shipStatus, ',');
                        Arrays.stream(shipStatusArray).forEach(x -> {
                            in.value(Integer.valueOf(x));
                        });
                        predicates.add(in);
                    }
 
                    criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
                    criteriaQuery.orderBy(
                            //先根据 deliveryType 权重升序排
                            criteriaBuilder.asc(
                                    criteriaBuilder.selectCase()
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "Home delivery 2h"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "C&C 2h"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "tmalldss"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "Home delivery"), 3)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "C&C"), 3)
                                            .otherwise(99)),
                            //再根据 orderPaytime 升序排
                            criteriaBuilder.asc(root.get("orderPaytime"))
                    );
                    return criteriaQuery.getRestriction();
                };

like做模糊匹配，gt筛选大于填入参数的数据，lt筛选小于输入参数的数据

Specification<ShipOrderInfo> spec = new Specification<ShipOrderInfo>() {  
                    public Predicate toPredicate(Root<ShipOrderInfo> root,  
                                                            CriteriaQuery<?> query, CriteriaBuilder cb) {  
                        Predicate p1 = cb.like(root.get("name").as(String.class), "%"+um.getName()+"%");  
                        Predicate p2 = cb.equal(root.get("uuid").as(Integer.class), um.getUuid());  
                        Predicate p3 = cb.gt(root.get("age").as(Integer.class), um.getAge());  
                        //把Predicate应用到CriteriaQuery中去,因为还可以给CriteriaQuery添加其他的功能，比如排序、分组啥的  
                        query.where(cb.and(p3,cb.or(p1,p2)));  
                        //添加排序的功能  
                        query.orderBy(cb.desc(root.get("uuid").as(Integer.class)));  
                          
                        return query.getRestriction();  
                    }  
                                    };

复杂一点的查询demo

Specification specification =
                (Specification<ShipOrderInfo>) (root, criteriaQuery, criteriaBuilder) -> {
                    List<Predicate> predicates = new ArrayList<>();
 
                    // 排除逻辑删除数据
                    predicates.add(criteriaBuilder.equal(root.get("dataStatus"), AuditModel.DATA_STATUS_ACTVIE));
 
                    if (null != locationInfoId) {
                        predicates.add(criteriaBuilder.equal(root.get("locationInfoId"), locationInfoId));
                    }
                    if (!StringUtils.isEmpty(shipStatus)) {
                        CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("orderLatestStatus"));
                        String[] shipStatusArray = StringUtils.split(shipStatus, ',');
                        Arrays.stream(shipStatusArray).forEach(x -> {
                            in.value(Integer.valueOf(x));
                        });
                        predicates.add(in);
                    }
 
                    criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
                    criteriaQuery.orderBy(
                            //先根据 deliveryType 权重升序排
                            criteriaBuilder.asc(
                                    criteriaBuilder.selectCase()
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "Home delivery 2h"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "C&C 2h"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "tmalldss"), 1)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "Home delivery"), 3)
                                            .when(criteriaBuilder.equal(
                                                    root.get("deliveryType"), "C&C"), 3)
                                            .otherwise(99)),
                            //再根据 orderPaytime 升序排
                            criteriaBuilder.asc(root.get("orderPaytime"))
                    );
                    return criteriaQuery.getRestriction();
                };