Spring-Data-Jpa实现分页条件查询

本文并非技术博客，为个人学习记录内容。

1 Spring-data-jpa 实现分页条件查询

1. 使用多个Specification进行关联查询

 @Override
 public ResultBean<Page<UserBasicInfo>> cusPageUser(UserReq userList) {
  
  Pageable pageable = PageRequest.of(userList.getPageNumber(), userList.getPageSize(),Sort.by(Direction.ASC, "alphabet"));
  
  Specification<UserBasicInfo> specification = null;
  
  if (!StringUtils.isEmpty(userList.getRealName())) {
   Specification<UserBasicInfo> name = (root, query, criteriaBuilder) -> {
    
    Predicate realName = criteriaBuilder.like(root.get("realName"), "%"+userList.getRealName()+"%"); 
    Predicate firstChar = criteriaBuilder.like(root.get("firstChar"), "%"+userList.getRealName()+"%"); 
    Predicate alphabet = criteriaBuilder.like(root.get("alphabet"), "%"+userList.getRealName()+"%"); 
   
    return criteriaBuilder.or(realName,firstChar,alphabet);
   };
   specification = Specification.where(name);
  }
  
  if (!StringUtils.isEmpty(userList.getDeptId())) {
   Specification<UserBasicInfo> deptSpecification = (root, query, criteriaBuilder) -> {
    Predicate dept = criteriaBuilder.like(root.get("deptIds"), "%"+userList.getDeptId()+"%");
    return criteriaBuilder.and(dept);
   };
   
   if (specification == null) {
    specification = Specification.where(deptSpecification);
   }else {
    specification = specification.and(deptSpecification);
   }
   
  }
  
  ResultBean.ResultBeanBuilder<Page<UserBasicInfo>> resultBeanBuilder = ResultBean.builder();
  
  Page<UserBasicInfo> result = userBasicInfoRepository.findAll(specification,pageable);
  
  return resultBeanBuilder.state(new ResultBeanState(UserBasicInfoEnum.READ_LIST_SUCCESS))
    .data(result)
    .paging(new PagingT<UserBasicInfo>(result))
    .build();
  
 }

2. 使用一个Specification，在内部进行多个条件的拼接查询（1）

 @Override
 public ResultBean<Page<UserBasicInfo>> cusPageUser(UserReq userList) {
  
  Pageable pageable = PageRequest.of(userList.getPageNumber(), userList.getPageSize(),Sort.by(Direction.ASC, "alphabet"));
  
    Page<UserBasicInfo> result = userBasicInfoRepository.findAll(new Specification<UserBasicInfo>() {
   
   @Override
   public Predicate toPredicate(Root<UserBasicInfo> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
    Predicate name = null;
    Predicate dept = null;
    
    if (!StringUtils.isEmpty(userList.getRealName())) {
     Predicate realName = criteriaBuilder.like(root.get("realName"), "%"+userList.getRealName()+"%"); 
     Predicate firstChar = criteriaBuilder.like(root.get("firstChar"), "%"+userList.getRealName()+"%"); 
     Predicate alphabet = criteriaBuilder.like(root.get("alphabet"), "%"+userList.getRealName()+"%"); 
     name = criteriaBuilder.or(realName,firstChar,alphabet);
    }
    
    if (!StringUtils.isEmpty(userList.getDeptId())) {
     Predicate deptIds = criteriaBuilder.like(root.get("deptIds"), "%"+userList.getDeptId()+"%");
     dept = criteriaBuilder.and(deptIds);
    }
    
    if (name == null && dept == null) {
     return query.getRestriction();
    }else if (name == null) {
     return query.where(dept).getRestriction();
    }else if (dept == null) {
     return query.where(name).getRestriction();
    }else {
     return query.where(name,dept).getRestriction();     
    }
   }
  },pageable);
  
  return resultBeanBuilder.state(new ResultBeanState(UserBasicInfoEnum.READ_LIST_SUCCESS))
    .data(result1)
    .paging(new PagingT<UserBasicInfo>(result1))
    .build();
 }

3. 使用一个Specification，在内部进行多个条件的拼接查询（2）

 @Override
 public ResultBean<Page<UserBasicInfo>> cusPageUser(UserReq userList) {
  
  Pageable pageable = PageRequest.of(userList.getPageNumber(), userList.getPageSize(),Sort.by(Direction.ASC, "alphabet"));
  
    Page<UserBasicInfo> result = userBasicInfoRepository.findAll(new Specification<UserBasicInfo>() {
   
   @Override
   public Predicate toPredicate(Root<UserBasicInfo> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
    
    Predicate name = null;
    
    if (!StringUtils.isEmpty(userList.getRealName())) {
     Predicate realName = criteriaBuilder.like(root.get("realName"), "%"+userList.getRealName()+"%"); 
     Predicate firstChar = criteriaBuilder.like(root.get("firstChar"), "%"+userList.getRealName()+"%"); 
     Predicate alphabet = criteriaBuilder.like(root.get("alphabet"), "%"+userList.getRealName()+"%"); 
     name = criteriaBuilder.or(realName,firstChar,alphabet);
    }

    if (!StringUtils.isEmpty(userList.getDeptId())) {
     Predicate deptIds = criteriaBuilder.like(root.get("deptIds"), "%"+userList.getDeptId()+"%");
     if (name==null) {
      name = criteriaBuilder.and(deptIds);
     }else {
      name = criteriaBuilder.and(name,deptIds);
     }
    }
       
    if (name == null) {
     return query.getRestriction();
    }else {
     return query.where(name).getRestriction();     
    }
   }
  },pageable);
  
  return resultBeanBuilder.state(new ResultBeanState(UserBasicInfoEnum.READ_LIST_SUCCESS))
    .data(result1)
    .paging(new PagingT<UserBasicInfo>(result1))
    .build();
 }

本文使用 mdnice 排版