分页查询 · FA+Ecloud互联网应用开发平台综合技术入门指南(READ)

## 一、单表条件单表结果分页 JAP处理单表分页查询非常简单，只需要传入参数Pageable（当查询中有多个参数的时候Pageable做为最后一个参数传入），以下拿实际实例来做示范； ### **（1）简单单表分页** Repository接口定义： ``` Page<SysStaff> findAll(Pageable pageable); Page<SysStaff> findByDomainId(long domainId,Pageable pageable); ``` 接口调用： ``` sysStaffDao.findAll(PagableBuilder.getPageable(curPage, pageSize, orderFieldName, orderDirection)); sysStaffDao.findByDomainId(domainId, PagableBuilder.getPageable(curPage, pageSize, orderFieldName, orderDirection)); ``` >[danger] 平台分页的起始页码是从1开始，而不是JPA规范的0开始，PagableBuilder做了处理； ### **（2）高级单表分页（默认使用）** Service中定义： ``` Specification<CardInfo> spec = new Specification<CardInfo>() { @Override public Predicate toPredicate(Root<CardInfo> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }; model.setPageobject(PageProcessor.reasonablePage(cardInfoDao, spec, curPage, pageSize, orderFieldName, orderDirection)); ``` ## 二、多表条件单表结果分页 ### **（1）基于@Query或命名规则多表关联条件单表结果简单版** ``` @Query("select staff from SysStaff staff where exists (select 1 from SysStaffInvitation siv where siv.staffId=staff.staffId and siv.fromStaffId=?1 and staff.createTime like ?2% )") Page<SysStaff> getInvitedSysStaffList(long staffId, String invitationTime, Pageable pageable); ``` ``` Page<SysStaffFavorite> findByStaffIdAndFavoriteEntity(long staffId, String favoriteEntity, Pageable pageable); ``` ### **（2）基于@Query多表关联条件单表结果高级版（默认使用）** 上述接口中定义的复杂条件单表分页查询，不太方便实现业务逻辑判断，因此，平台提供了一个高级的版本，可以加入业务逻辑，这个是最常用的复杂条件查询版本； #### **基于jpaQuery** jpaQuery中不支持IF条件判断，因此，需要变通； ``` @Query(value = "select staff from SysStaff staff where exists (select 1 from SysStaffRelation ssr where 1=1"// + " and (?1=0L or ssr.relatedStaffId=?1)"// + " and (?2=0L or ssr.relationType=?2) and staff.staffId=ssr.staffId"// + " and (?3 is null or ?3 = '' or staff.staffName like ?3)" // + " and (?4 is null or ?4 = '' or staff.staffCode like ?4)" // + " )") Page<SysStaff> getRelatedStaffListOfSpecRelation(long staffId, long relationType, String staffName, String staffCode, Pageable pageable); ``` 用法： ``` Page<SysStaff> ps1 =sysStaffDao.getRelatedStaffListOfSpecRelation(1, 1, "", "%13%", PagableBuilder.getPageable(1, 3, "", "")); Page<SysStaff> ps2 =sysStaffDao.getRelatedStaffListOfSpecRelation(1, 1, "", "", PagableBuilder.getPageable(2, 3, "", "")); Page<SysStaff> ps3 =sysStaffDao.getRelatedStaffListOfSpecRelation(0, RayiotSysStaffRelationType.topagent_creator, wrapLike(staffName), wrapLike(staffCode), PagableBuilder.getPageable(curPage, pageSize, orderFieldName, orderDirection)) ``` >[danger] 这里，需要注意的是： > 1、?3和?4两个参数由于加入了判断，不要这里加%%，在外面处理，不然会导致空值匹配问题； > 2、?1和?2由于是long类型，外部传入0的时候，会导致转型错误，这里判断为0，需要写成0L； #### **基于nativeQuery** ``` @Query(value = "select * from card_order_package orderpackage where 1=1 " // + "and IF (?1 != '', exists(select 1 from card_info card where card.USER_CODE=?1 and orderpackage.CARD_ID=card.CARD_ID), 1=1) "// + "and IF (?2 != '', exists(select 1 from card_info card where card.ICCID_CODE=?2 and orderpackage.CARD_ID=card.CARD_ID), 1=1) "// + "and IF (?3 != '' , orderpackage.ORDER_TIME>=?3, 1=1) "// + "and IF (?4 != '', orderpackage.ORDER_TIME>=?4, 1=1) "// + "and IF (?5 != 0, orderpackage.ORDER_PACKAGE_ID=?5, 1=1) "// , nativeQuery = true) ``` 调用代码： ``` model.setPageobject(cardOrderPackageDao.getCardOrderPackageOfSpecCondition(userCode, iccidCode, getFirstDateTimePossible(dateTimeScope), getSecondDateTimePossible(dateTimeScope), packageId, PagableBuilder.getPageableNativeQuery(curPage, pageSize, orderFieldName, orderDirection))); ``` >[danger] > 1、这里必须使用nativeQuery，调用代码中，分页对象的构造必须是PagableBuilder.getPageableNativeQuery，而非getPageable； > 2、综合考虑，这种办法推荐使用，作为多表关联动态条件分页的推荐方案； > 3、这里输入分页参数的时候，必须用表字段名，而不是实体字段名； ### **（3）基于@Query多表关联条件动态构建对象版** 生成的分页是由动态创建的对象构成； ``` @Query(value = "select new com.ray.iot.controller.data.meta.CardFlowPackageOrderObject(cfp.packageId, cfp.packageName, cfp.salePrice) from CardFlowPackage cfp where cfp.cardSource=?1 and cfp.packageStatus=?2 "// ) Page<CardFlowPackageOrderObject> getCardFlowPackageOfInnerAndCardSourceAndPackageStatus(long cardSource, long packageStatus, Pageable pageable); @Query(value = "select new com.ray.iot.controller.data.meta.CardFlowPackageOrderObject(cfp.packageId, cfp.packageName, cafp.agentSalePrice) from CardFlowPackage cfp,CardAgentFlowPackage cafp where cfp.cardSource=?1 and cfp.packageStatus=?2 "// + " and cafp.packageId=cfp.packageId and cafp.agentStaffId=?3 and cafp.agentPackageStatus=" + CardAgentPackageStatus.enabled // ) Page<CardFlowPackageOrderObject> getCardFlowPackageOfWechatAccessAndCardSourceAndPackageStatus(long cardSource, long packageStatus, long wechatTopAgentStaffId, Pageable pageable); ``` 动态对象定义： ``` public class CardFlowPackageOrderObject implements Serializable { private long packageId; private String packageName; private Long salePrice; public CardFlowPackageOrderObject(long packageId, String packageName, Long salePrice) { this.packageId = packageId; this.packageName = packageName; this.salePrice = salePrice; } public long getPackageId() { return packageId; } public void setPackageId(long packageId) { this.packageId = packageId; } public String getPackageName() { return packageName; } public void setPackageName(String packageName) { this.packageName = packageName; } public Long getSalePrice() { return salePrice; } public void setSalePrice(Long salePrice) { this.salePrice = salePrice; } } ``` ### **（4）基于Specification多表关联条件单表结果分页** 可以使用Specification实现多表分页，如： ``` public Specification<License> getSpecification() { Specification<License> specification = new Specification<License>() { @Override public Predicate toPredicate(Root<License> root, CriteriaQuery<?> query, CriteriaBuilder cb) { List<Predicate> list = new ArrayList<>(); Join<License, HomeRoom> join = root.join("homeRoom", JoinType.LEFT); Predicate roomSidPre = cb.equal(join.get("ownerPhone"), "13600539847"); list.add(roomSidPre); return cb.and(list.toArray(new Predicate[list.size()])); } }; return specification; } ``` 但是，这里需要在关联实体部分，做标记，不推荐使用； ``` @OneToOne @JoinColumn(name ="room_sid", insertable = false, updatable = false) private HomeRoom homeRoom; ``` >[danger] 考虑到维护的便利性，这种方案会修改生成的实体bean对象，因此，一般不建议采用； ## 三、多表条件多表结果分页 ### **（1）基于@Query多表关联条件单表结果** ``` @Query(value = "select new github.snailclimb.jpademo.model.dto.UserDTO(p.name,p.age,c.companyName,s.name) " + "from Person p left join Company c on p.companyId=c.id " + "left join School s on p.schoolId=s.id ", countQuery = "select count(p.id) " + "from Person p left join Company c on p.companyId=c.id " + "left join School s on p.schoolId=s.id ") Page<UserDTO> getUserInformationList(Pageable pageable); ``` 使用： ``` //分页选项 PageRequest pageRequest = PageRequest.of(0, 3, Sort.Direction.DESC, "age"); Page<UserDTO> userInformationList = personRepository.getUserInformationList(pageRequest); //查询结果总数 System.out.println(userInformationList.getTotalElements());// 6 //按照当前分页大小，总页数 System.out.println(userInformationList.getTotalPages());// 2 System.out.println(userInformationList.getContent()); ``` >[danger] 为了简便和维护方便，我们采用视图来实现这种连接表分页查询（转为单表分页了）； ### **（2）转换视图分页** 将多表转换为视图（虚拟单表） ``` ## 视图脚本 CREATE OR REPLACE VIEW v_sys_staff_with_profile AS SELECT t1.*, t2.`PROFILE_ID`, t2.`CERTIFICATE_TYPE`, t2.`CERTIFICATE_CODE`, t2.`LANG`, t2.`AVATAR`,t2. `EMAIL`,t2. `POST_CODE`, t2.`OFFICE_TEL`, t2.`POSITION`,t2. `IDCARD`, t2.`MOBILE`,t2. `SEX`,t2. `LOACTION`, t2.`POP`,t2. `SMTP`, t2.`MAIL_USER`, t2.`MAIL_PASS`,t2. `IM`, t2.`FAX`,t2. `BIRTHDAY`, t2.`PHOTO`, t2.`PASSWD_QUESTION`,t2. `PASSWD_ANSWER`, t2.`NICK_NAME` FROM SYS_STAFF t1 LEFT JOIN SYS_STAFF_PROFILE t2 on t1.STAFF_ID=t2.STAFF_ID; ``` ``` ## VSysStaffWithProfile是复合了两张表的视图； @Entity @Table(name = "v_sys_staff_with_profile") public class VSysStaffWithProfile extends BaseEntity { ``` ``` ## SysStaffService中，获取视图分页的实现（转为简单的单表分页实现了）： @Autowired private VSysStaffWithProfileDao vsysStaffWithProfileDao; public ResponseObject list(String staffName, String staffCode, String roleNames, int curPage, int pageSize) { ResponseObject responseObj = new ResponseObject(); Pageable pageable = PageRequest.of(curPage - 1, pageSize, Sort.by(Sort.Direction.ASC, "staffId")); Specification<VSysStaffWithProfile> spec = new Specification<VSysStaffWithProfile>() { @Override public Predicate toPredicate(Root<VSysStaffWithProfile> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) { List<Predicate> predicates = new ArrayList<>(); if (!StringUtil.isEmpty(staffName)) { predicates.add(criteriaBuilder.like(root.get("staffName"), staffName)); } if (!StringUtil.isEmpty(staffCode)) { predicates.add(criteriaBuilder.like(root.get("staffCode"), staffCode)); } if (!StringUtil.isEmpty(roleNames)) { predicates.add(criteriaBuilder.like(root.get("roleNames"), roleNames)); } return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()])); } }; responseObj.setResult(PageProcessor.reasonablePage(vsysStaffWithProfileDao, spec, pageable)); return responseObj; } ``` ## 四、分页查询限制返回条目 ### **（1）用@Query实现** 1、传入的参数改为Pageable pageable，返回对象由List改为Page； 2、调用的时候，构造PageRequest；举例： ``` @Query("select topic from DouyinBoardTopic topic order by topic.collectDate desc") Page<DouyinBoardTopic> getLatest(Pageable pageable); ``` 调用： ``` Pageable pageable = PageRequest.of(1, 2, Direction.ASC, "name"); List<DouyinBoardTopic> list =douyinBoardTopicDao.getLatest(pageable).getContent(); ``` 看一个带参数的例子： ``` @Query("select com from DouyinBoardCommon com where boardType=:boardType") Page<DouyinBoardCommon> getTopDouyinBoardCommonsOfSpecBoardType(Pageable pagable, int boardType); ``` 调用： ``` Pageable pageable = PageRequest.of(1, 2, Direction.DESC, "collectDate"); ho.setBraodcastBoardList(douyinBoardCommonDao.getTopDouyinBoardCommonsOfSpecBoardType(pageable, 1).getContent()); ``` ### **（2）命名限制查询** 有时候我们只需要查询前N个元素，或者只取前一个实体； ~~~ User findFirstByOrderByLastnameAsc(); User findTopByOrderByAgeDesc(); Page<User> queryFirst10ByLastname(String lastname, Pageable pageable); List<User> findFirst10ByLastname(String lastname, Sort sort); List<User> findTop10ByLastname(String lastname, Pageable pageable); ~~~