法一(本地sql查询,注意表名啥的都用数据库中的名称,适用于特定数据库的查询)
public interface UserRepository extends JpaRepository { @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1", countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1", nativeQuery = true) Page findByLastname(String lastname, Pageable pageable); }
法二(jpa已经实现的分页接口,适用于简单的分页查询)
public interface PagingAndSortingRepositoryextends CrudRepository { Iterable findAll(Sort sort); Page findAll(Pageable pageable); } Accessing the second page of User by a page size of 20 you could simply do something like this: PagingAndSortingRepository repository = // … get access to a beanPage users = repository.findAll(new PageRequest(1, 20)); User findFirstByOrderByLastnameAsc(); User findTopByOrderByAgeDesc(); Page queryFirst10ByLastname(String lastname, Pageable pageable); Slice findTop3ByLastname(String lastname, Pageable pageable); List findFirst10ByLastname(String lastname, Sort sort); List findTop10ByLastname(String lastname, Pageable pageable); //service Sort sort = new Sort(Sort.Direction.DESC,"createTime"); //创建时间降序排序 Pageable pageable = new PageRequest(pageNumber,pageSize,sort); this.depositRecordRepository.findAllByUserIdIn(userIds,pageable);//repositoryPage findAllByUserIdIn(List userIds,Pageable pageable);
法三(Query注解,hql语局,适用于查询指定条件的数据)
@Query(value = "select b.roomUid from RoomBoard b where b.userId=:userId and b.lastBoard=true order by b.createTime desc") PagefindRoomUidsByUserIdPageable(@Param("userId") long userId, Pageable pageable); Pageable pageable = new PageRequest(pageNumber,pageSize); Page page = this.roomBoardRepository.findRoomUidsByUserIdPageable(userId,pageable); List roomUids = page.getContent(); 可以自定义整个实体(Page ),也可以查询某几个字段(Page
法四(扩充findAll,适用于动态sql查询)
public interface UserRepository extends JpaRepository{ Page findAll(Specification spec, Pageable pageable); } @Servicepublic class UserService { @Autowired private UserRepository userRepository; public Page getUsersPage(PageParam pageParam, String nickName) { //规格定义 Specification specification = new Specification () { /** * 构造断言 * @param root 实体对象引用 * @param query 规则查询对象 * @param cb 规则构建对象 * @return 断言 */ @Override public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) { List predicates = new ArrayList<>(); //所有的断言 if(StringUtils.isNotBlank(nickName)){ //添加断言 Predicate likeNickName = cb.like(root.get("nickName").as(String.class),nickName+"%"); predicates.add(likeNickName); } return cb.and(predicates.toArray(new Predicate[0])); } }; //分页信息 Pageable pageable = new PageRequest(pageParam.getPage()-1,pageParam.getLimit()); //页码:前端从1开始,jpa从0开始,做个转换 //查询 return this.userRepository.findAll(specification,pageable); } } 法五(使用entityManager,适用于动态sql查询) IncomeService Page = "select count(*) from IncomeDaily po where 1=1 "= "from IncomeDaily po where 1=1 " params = HashMap<>= " and cpId=:cpId ""cpId"" and appId=:appId ""appId"" and sp=:sp ""sp" (start == = DateUtil.getStartOfDate(" and po.bizDate >= :startTime""startTime" (end != " and po.bizDate <= :endTime""endTime"= = .entityManager.createQuery(countSql,Long.== = .entityManager.createQuery(querySql,IncomeDaily.(pageParam != ){ incomeDailyList =(pageParam != ) { Pageable pageable = incomeDailyPage = PageImpl { PageImpl setParameters(Query query,Map (Map.Entry
springboot 2.x 分页 单字段排序 modelAttrDao.findAll(Sort.by(Sort.Direction.DESC,"isWrite")); 多字段排序 Sort.Order order1 = new Sort.Order(Sort.Direction.ASC, "isWrite"); Sort.Order order2 = new Sort.Order(Sort.Direction.DESC, "id"); Listlist = new ArrayList<>(); list.add(order1); list.add(order2); modelAttrDao.findAll(Sort.by(list)); 扩展分页加排序 Sort.Order order= new Sort.Order(Sort.Direction.DESC,"id"); Sort sort =Sort.by(order); PageRequest request = PageRequest.of(page,limit,sort); Sort.Order order1 = new Sort.Order(Sort.Direction.ASC, "isWrite"); Sort.Order order2 = new Sort.Order(Sort.Direction.DESC, "id"); List list = new ArrayList<>(); list.add(order1); list.add(order2); Sort sort =Sort.by(list); PageRequest request = PageRequest.of(page,limit,sort); //分页加排序实例 public Page getUserPage(UserInfo userInfo, CmsPage cmsPage){ //getBean 为非spring管理环境中获取bean UserDao userDao =SpringUtils.getBean(UserDao.class); try{ Specification query=new Specification (){ @Override public Predicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder criteriaBuilder) { Predicate predicate = criteriaBuilder.conjunction(); //id if(userInfo.getId() != null){ predicate=criteriaBuilder.and(predicate,criteriaBuilder.equal(root.get("id"),userInfo.getId())); } //用户名 多条件 if(StringUtils.isNotEmpty(userInfo.getUserName())){ predicate=criteriaBuilder.and(predicate,criteriaBuilder.like(root.get("userName"),"%"+userInfo.getUserName()+"%")); predicate=criteriaBuilder.or(predicate,criteriaBuilder.equal(root.get("phone"),"%"+userInfo.getUserName()+"%")); predicate=criteriaBuilder.or(predicate,criteriaBuilder.equal(root.get("email"),"%"+userInfo.getUserName()+"%")); } if(userInfo.getGroupId() !=null){ predicate=criteriaBuilder.and(predicate,criteriaBuilder.equal(root.get("groupId"),userInfo.getGroupId())); } return predicate; } }; Sort.Order order1 = new Sort.Order(Sort.Direction.ASC, "isWrite"); Sort.Order order2 = new Sort.Order(Sort.Direction.DESC, "id"); List list = new ArrayList<>(); list.add(order1); list.add(order2); Sort sort =Sort.by(list); //0起始页 100 每页显示多少 第三参数排序 PageRequest request = PageRequest.of(0,100,sort); Page user =userDao.findAll(query,request); return user; }catch (Exception e){ e.printStackTrace(); } return null; }
关注"都市百货" 了解南陵
微信咨询wanglf2r(不拉群 发广告者勿加)
热门评论