springcloud整合mybatis配置

---

分页处理，这是做JavaWeb项目中常见的场景。

背景：

1. 系统架构：SpringCloud分布式
2. 持久层：MyBatis
3. 前端：前后分离vue.js/bootstrap等.
4. 后台提供restful api 接口，前端访问后端接口展示数据。

2种方式提供分页处理方案：

一、直接MyBatis数据库进行分页

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 = "创建时间起始(示例：2018-09-05)", required = false, dataType = "String", paramType = "query", length = 10),            @ApiImplicitParam(name = "endTime", value = "创建时间截止(示例：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 = "查询的页面大小不需要分页则把此值填大一点", required = true, dataType = "int", paramType = "query")})    public PageResult 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 response = new PageResult();        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();            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 用于前端分页的参数，pageNum：表示页码第几页，pageSize：表示每页展示数据数量。所有查询条件参数，在mapper-xml里进行处理，再利用数据库oracle本身的rownum行值进行分页。

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 = ]]>  #{beginCreateTime,jdbcType=TIMESTAMP}      if>      <if test="endCreateTime != null">        AND CREATE_TIME   #{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 >= #{endRowNo,jdbcType=DECIMAL}    )    where RN <= #{beginRowNo,jdbcType=DECIMAL}  select>

优点：直观、方便、易排查问题。 缺点：访问数据库过于频繁，未利用到mybatis本身的缓存优势。

二、Java+缓存分页

这种方法对于前端而言是没变化，无感的。

只是在后端处理稍作变动，思路：

• 先把所有数据记录查询出来
• 数据库实体再次封装为查询实体
• 纯java代码进行分页
• controller接口不变
• service业务处理——>数据库crud操作变动
• 把所有数据库记录查询处理

<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 = ]]>  #{beginCreateTime,jdbcType=TIMESTAMP}      if>      <if test="endCreateTime != null">        AND CREATE_TIME   #{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 descselect>

再按照pageNum和pageSize进行分页处理

//以分页形式输出给前端        List 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));            }        }

优点：利用了mybatis缓存机制，分页查询快速，减少数据库访问次数。

缺点：当数据量大的时候，100W+；在没有条件查询数据库的时候，耗时久

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