SpringMVC分页查询，从controller到mapper

1、controller

需要获取当前页码、页面大小、其他查询条件。

@RequestMapping(value="/getMortgageList", method = RequestMethod.POST)
public String getMortgageList(MortgageQueryReqDto queryDto, Model model, HttpServletRequest request){
        try {
            // 页码信息
            int currentPageNo = Integer.valueOf(request.getParameter("currentPageNo"));
            int pageSize = Integer.valueOf(request.getParameter("pageSize"));
            // 封装请求参数（包含其他查询条件）
            queryDto.setCurrentPageNo(currentPageNo);
            queryDto.setPageSize(pageSize);
            // 调用Dubbo接口
            Message<MortgageQueryResPageDto> message = mortgageInterface.queryMortgageList(queryDto);
            // 解析返回结果
            if (Messages.isSuccess(message)) {
                MortgageQueryResPageDto queryResPageDto = message.getData();
                model.addAttribute("mortgageList", queryResPageDto.getMortgageQueryResDtoList());
                model.addAttribute("currentPageNo", currentPageNo);
                model.addAttribute("totalPageCount", PublicUtils.getTotalPageCount(
                        queryResPageDto.getTotalCount(), pageSize));
                model.addAttribute("totalCount", queryResPageDto.getTotalCount());
                model.addAttribute("pageSize", pageSize);
            }
        } catch(Exception e){
            log.error(e);
        }
        return "/mortgage_list";
}

2、Interface/Service

规范写法是dubbo接口调用service方法。为简洁说明，下面例子将service方法代码逻辑合并到interface中。

public Message<MortgageQueryResPageDto> queryMortgageList(MortgageQueryReqDto mortgageQueryReqDto) {
        // ...省略异常处理, 省略返回失败消息

        // 通过其他条件获取查询记录总数目
        queryResPageDto.setTotalCount(mortgageInfoMapper.countMortgage(mortgageQueryReqDto.getStatus()));

        // 计算查询起始记录位置
        Integer startIndex = (mortgageQueryReqDto.getCurrentPageNo()-1) * mortgageQueryReqDto.getPageSize();
        
        // 查询记录列表
        List<MortgageInfo> mortgageList = mortgageInfoMapper.selectMortgageInfoPage(
                mortgageQueryReqDto.getStatus(), startIndex, mortgageQueryReqDto.getPageSize());
        
        // 将记录列表转为返回列表
        List<MortgageQueryResDto> mortgageQueryResList = Lists.transform(mortgageList, mortgageInfo -> {
            MortgageQueryResDto mortgageQueryResDto = new MortgageQueryResDto();
            BeanUtils.copyProperties(mortgageInfo, mortgageQueryResDto);
            // ...
            return mortgageQueryResDto;
        });
        queryResPageDto.setMortgageQueryResDtoList(mortgageQueryResList);
        return Messages.success(queryResPageDto);
 }

3、Mapper

Mapper接口方法

/**
  * <P>description: 按条件查询数据总数</P>
  **/
Integer countMortgage(@Param("status") Integer status);

/**
  * <P>description: 按条件查询分页数据</P>
  **/
List<MortgageInfo> selectMortgageInfoPage(@Param("status") Integer status, 
                     @Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);

Mybatis文件

<!-- 按条件查询数据总数 -->
<select id="countMortgage" resultType="java.lang.Integer">
    select count(ID) from MORTGAGE_INFO
    <where>
      <if test="status != null" >
        AND `STATUS` = #{status,jdbcType=INTEGER}
      </if>
    </where>
</select>

<!-- 按条件查询分页数据 -->
<select id="selectMortgageInfoPage" resultMap="BaseResultMap">
    select MORTGAGE_ID, DOCUMENT_NO, `TYPE`, STATUS, LOAN_APPLY_NO, CREDIT_APPLY_NO from ACCT_LOAN_MORTGAGE_INFO
    <where>
      <if test="status != null" >
        AND `STATUS` = #{status,jdbcType=INTEGER}
      </if>
    </where>
    limit #{startIndex, jdbcType=INTEGER}, #{pageSize, jdbcType=INTEGER}
</select>