SpringBoot之JPA复杂查询

@Repository
public interface PersonRepository extends JpaRepository<Person,Long>, JpaSpecificationExecutor<Person> {
}
复制

 /**
     * todo spring data jpa 使用specification 进行查询 3-11
     * 十分实用和好用
     *
     * @param query
     * @return
     */
    public Page<Person> queryPersonsByLamdaConditions(PersonQuery query) {
//        List<Sort.Order> orders = new ArrayList<>();
//        Sort.Order order1= new Sort.Order(Sort.Direction.ASC,"createTime");
//        Sort.Order order2= new Sort.Order(Sort.Direction.ASC,"personType");
//        orders.add(order2);
//        orders.add(order1);
        // new Sort(orders)
//        多个字段进行排序
        Sort sort = new Sort(Sort.Direction.DESC, "personType").
                and(new Sort(Sort.Direction.ASC, "createTime"));


        Pageable pageable = PageRequest.of(query.getPage() - 1, query.getPageSize(), sort);
        // 先创建一个说明书, 借助这个说明书进行查询
        Specification<Person> specification = new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                // 然后再创建一个查询预测条件的数组
                List<Predicate> list = new ArrayList<>();
                // 模糊查询 like
                if (StringUtils.isNotBlank(query.getUserName())) {
                    list.add(criteriaBuilder.like(root.get("userName"), query.getUserName()));
                }
                // 等于 equal
                if (query.getAge() != null) {
                    list.add(criteriaBuilder.equal(root.get("age"), query.getAge()));
                }
                // where in list集合
                if (CollectionUtils.isNotEmpty(query.getUnitIds())) {
                    list.add(criteriaBuilder.in(root.get("unitId")).value(query.getUnitIds()));
                }
                if (Objects.nonNull(query.getCreateTime())) {
                    // 大于等于
//                  list.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime"), query.getCreateTime()));
                    // 大于
//                  list.add(criteriaBuilder.greaterThan(root.get("createTime"), query.getCreateTime()));
                    // 小于等于
//                    list.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime"), query.getCreateTime()));
                    // 小于
                    list.add(criteriaBuilder.lessThan(root.get("createTime"), query.getCreateTime()));
                }
                // 介于二者之间的
                if (Objects.nonNull(query.getStartTime()) && Objects.nonNull(query.getEndTime())) {
                    list.add(criteriaBuilder.between(root.get("createTime"), query.getStartTime(), query.getEndTime()));
                }


                Predicate[] predicates = new Predicate[list.size()];
                // 将其转换成predicate数组
                return criteriaBuilder.and(list.toArray(predicates));
            }
        };


        Page<Person> page = personRepository.findAll(specification, pageable);
        List<Person> personEntities = page.getContent();
        // 此处可以将 personEntities 可以再转换为vo
//        List<PersonVo> personVoList = MapperUtil.mapperObjectList(PersonVo.class, personEntities);
        // 再新构建一个page页面
        return new PageImpl<>(personEntities, pageable, page.getTotalElements());
    }
复制