SpringCloud+MyBatis分頁處理（前后端分離）

更新時(shí)間：2019年10月10日 08:29:50 作者：編程機(jī)器貓

這篇文章主要為大家詳細(xì)介紹了SpringCloud+MyBatis分頁處理，前后端分離，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

分頁處理，這是做JavaWeb項(xiàng)目中常見的場景。

背景：

1.系統(tǒng)架構(gòu)：SpringCloud分布式
2.持久層：MyBatis
3.前端：前后分離vue.js/bootstrap等.

后臺提供restful api 接口，前端訪問后端接口展示數(shù)據(jù)。

2種方式提供分頁處理方案：

一、直接MyBatis數(shù)據(jù)庫進(jìn)行分頁

controller接口

@ApiImplicitParams({
 @ApiImplicitParam(name = "categoryId", value = "支付渠道大類Id", required = false, dataType = "Long", paramType = "query"),
 @ApiImplicitParam(name = "payChannelId", value = "支付渠道ID", required = false, dataType = "Long", paramType = "query"),
 @ApiImplicitParam(name = "bankCode", value = "銀行名稱code", required = false, dataType = "String", paramType = "query"),
 @ApiImplicitParam(name = "startTime", value = "創(chuàng)建時(shí)間起始（示例：2018-09-05）", required = false, dataType = "String", paramType = "query", length = 10),
 @ApiImplicitParam(name = "endTime", value = "創(chuàng)建時(shí)間截止（示例：2018-09-05）", required = false, dataType = "String", paramType = "query", length = 10),
 @ApiImplicitParam(name = "pageNum", value = "查詢開始頁", required = true, dataType = "int", paramType = "query"),
 @ApiImplicitParam(name = "pageSize", value = "查詢的頁面大小不需要分頁則把此值填大一點(diǎn)", required = true, dataType = "int", paramType = "query")})
 public PageResult<ChannelBaseDataResp> queryPayChannel(
   @RequestParam(value = "categoryId", required = false) Long categoryId,
   @RequestParam(value = "payChannelId", required = false) Long payChannelId,
   @RequestParam(value = "bankCode", required = false) String bankCode,
   @RequestParam(value = "startTime", required = false) String startTime,
   @RequestParam(value = "endTime", required = false) String endTime,
   @RequestParam(value = "pageNum", required = true) Integer pageNum,
   @RequestParam(value = "pageSize", required = true) Integer pageSize) {
 PageResult<ChannelBaseDataResp> response = new PageResult<ChannelBaseDataResp>();
 try {
 int endRowNo = pageNum * pageSize;
 int beginRowNo = (pageNum - 1) * pageSize + 1;
 return channelBaseDataService.queryBaseData(categoryId,payChannelId,bankCode,startTime,endTime,beginRowNo,endRowNo);
 } catch (Exception e) {
 response = new PageResult<ChannelBaseDataResp>();
 if (e instanceof AppException) {
 response.setCode(((AppException) e).getErrorCode());
 response.setResult(((AppException) e).getErrorMsg());
 } else {
 response.setCode(CumReturnCode.SYSTEM_EXCEPTION.code);
 response.setResult(CumReturnCode.SYSTEM_EXCEPTION.message);
 }
 return response;
 }
 }

Swagger-ui接口界面如下：

pageNum 和 pageSize 用于前端分頁的參數(shù)，pageNum：表示頁碼第幾頁，pageSize：表示每頁展示數(shù)據(jù)數(shù)量。

所有查詢條件參數(shù)，在mapper-xml里進(jìn)行處理，再利用數(shù)據(jù)庫oracle本身的rownum行值進(jìn)行分頁。

xml代碼：

<select id="queryBaseDataList" resultMap="BaseResultMap">
 select
 ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,
 BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR
 from (
 select A.*, rownum RN
 from (
 select ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,
 BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR
 from CUM_PAY_CHANNEL_BASE
 <where>
 <if test="beginCreateTime != null">
 CREATE_TIME <![CDATA[ >= ]]> #{beginCreateTime,jdbcType=TIMESTAMP}
 </if>
 <if test="endCreateTime != null">
 AND CREATE_TIME <![CDATA[ <= ]]> #{endCreateTime,jdbcType=TIMESTAMP}
 </if>
 <if test="categoryId != null and categoryId != '' ">
 AND PAY_CHANNEL_MAIN_ID = #{categoryId,jdbcType=DECIMAL}
 </if>
 <if test="payChannelId != null and payChannelId != ''">
 AND PAY_CHANNEL_ID = #{payChannelId,jdbcType=DECIMAL}
 </if>
 <if test="bankCode != null and bankCode != ''">
 AND BANK_CODE = #{bankCode,jdbcType=VARCHAR}
 </if>
 </where>
 order by CREATE_TIME desc
 ) A
 where rownum &lt;= #{endRowNo,jdbcType=DECIMAL}
 )
 where RN &gt;= #{beginRowNo,jdbcType=DECIMAL}
</select>

優(yōu)點(diǎn)：直觀、方便、易排查問題。缺點(diǎn)：訪問數(shù)據(jù)庫過于頻繁，未利用到mybatis本身的緩存優(yōu)勢。

二、Java+緩存分頁

這種方法對于前端而言是沒變化，無感的。

只是在后端處理稍作變動(dòng)，思路：

1.先把所有數(shù)據(jù)記錄查詢出來
2.數(shù)據(jù)庫實(shí)體再次封裝為查詢實(shí)體
3.純java代碼進(jìn)行分頁

controller接口不變

service業(yè)務(wù)處理——>數(shù)據(jù)庫crud操作變動(dòng)

把所有數(shù)據(jù)庫記錄查詢處理

<select id="queryBaseDataList" resultMap="BaseResultMap">
 select
 ID, PAY_CHANNEL_MAIN_ID, PAY_CHANNEL_ID,
 BANK_CODE, CREATE_TIME, UPDATE_TIME,UPDATOR
 from CUM_PAY_CHANNEL_BASE
 <where>
 <if test="beginCreateTime != null">
 CREATE_TIME <![CDATA[ >= ]]> #{beginCreateTime,jdbcType=TIMESTAMP}
 </if>
 <if test="endCreateTime != null">
 AND CREATE_TIME <![CDATA[ <= ]]> #{endCreateTime,jdbcType=TIMESTAMP}
 </if>
 <if test="categoryId != null and categoryId != '' ">
 AND PAY_CHANNEL_MAIN_ID = #{categoryId,jdbcType=DECIMAL}
 </if>
 <if test="payChannelId != null and payChannelId != ''">
 AND PAY_CHANNEL_ID = #{payChannelId,jdbcType=DECIMAL}
 </if>
 <if test="bankCode != null and bankCode != ''">
 AND BANK_CODE = #{bankCode,jdbcType=VARCHAR}
 </if>
 </where>
 order by CREATE_TIME desc
</select>

再按照pageNum和pageSize進(jìn)行分頁處理

//以分頁形式輸出給前端
List<ChannelRouteGroupResp> resultList = new ArrayList<>();
 if(groupRespList.size() >= endRowNo){
 for(int i=(beginRowNo-1); i< endRowNo; i++){
 resultList.add(groupRespList.get(i));
 }
 }else{
 for(int i=(beginRowNo-1); i< groupRespList.size(); i++){
 resultList.add(groupRespList.get(i));
 }
 }

優(yōu)點(diǎn)：利用了mybatis緩存機(jī)制，分頁查詢快速，減少數(shù)據(jù)庫訪問次數(shù)。

缺點(diǎn)：當(dāng)數(shù)據(jù)量大的時(shí)候，100W+；在沒有條件查詢數(shù)據(jù)庫的時(shí)候，耗時(shí)久

前端分頁后的界面展示如下：