JPA复杂分页查询

1.分页查询

public Page<User> page(int pageNo, int pageSize, User user) {
        Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序
        Pageable pageable = PageRequest.of(pageNo - 1,pageSize,sort);
        return this.userRepository.page(pageable,user);
    }

2.多条件分页查询

public Page<WageData> getWageData(int pageNo,int pageSize,WageData wageData,String nameField,String phoneField,String input) {
        Sort sort = new Sort(Sort.Direction.ASC,"id"); //id升序
        Pageable pageable = PageRequest.of(pageNo - 1,pageSize,sort);
        Specification<WageData> specification = (Specification<WageData>) (root, criteriaQuery, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            if(null != wageData.getVersion()){
                Predicate version = criteriaBuilder.equal(root.get("version").as(String.class),wageData.getVersion());
                predicates.add(version);
            }
            if(StringUtils.isNotBlank(nameField) && StringUtils.isNotBlank(phoneField)){
                Predicate name = criteriaBuilder.like(root.get(nameField).as(String.class),"%"+input+"%");
                Predicate phone = criteriaBuilder.like(root.get(phoneField).as(String.class),"%"+input+"%");
                predicates.add(criteriaBuilder.or(name,phone));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        };
        return this.wageDataRepository.findAll(specification,pageable);
    }

3.分页查询后对Page数据指定查询(for循环里面调用持久层需慎用)

public Page<User> page(int pageNo,int pageSize,User user) {
        Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序
        Pageable pageable = PageRequest.of(pageNo - 1,pageSize,sort);
        Page<User> page = this.userRepository.findUserBy(pageable,user);
        //查询等级名称
        Iterator<User> it = page.iterator();
        while(it.hasNext()){
            User u = it.next();
            if(u.getUserLevelId() != null){
                Optional<UserLevel> userLevel = this.userLevelRepository.findById(u.getUserLevelId());
                if(userLevel.isPresent()){
                    u.setUserLevelName(userLevel.get().getLevelName());
                }
            }
        }
        return page;
    }

4.原生态sql分页查询(原生态sql太过于复杂，count不能查询需要手动指定count语句countQuery="select count(1) from article")

@Query(value = "select * from article where 1=1 and (userName=:#{#article.userName} or :#{#article.userName} is null) and (state=:#{#article.state} or :#{#article.state} is null) and (articleCategoryId=:#{#article.articleCategoryId} or :#{#article.articleCategoryId} is null) and (articleCategoryId in(:#{#category}))",countQuery = "select count(*) from article where 1=1 and (userName=:#{#article.userName} or :#{#article.userName} is null) and (state=:#{#article.state} or :#{#article.state} is null) and (articleCategoryId=:#{#article.articleCategoryId} or :#{#article.articleCategoryId} is null) and (articleCategoryId in (:#{#category}))",nativeQuery = true)
    Page<Article> findArticleBy(Pageable pageable,@Param("article") Article article,@Param("category") List<Integer> category);