分頁查詢 · 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); ~~~