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);