自定义sql分页实现

（1）mapper 接口以及 xml

  1. /**
  2.  * @Description: 系统通告表
  3.  * @Author: jeecg-boot
  4.  * @Date:  2019-01-02
  5.  * @Version: V1.0
  6.  */
  7. public interface SysAnnouncementMapper extends BaseMapper<SysAnnouncement> {
  10.     List<SysAnnouncement> querySysCementListByUserId(Page<SysAnnouncement> page, String userId,String msgCategory);
  12. }

这里要注意的是，这个 Page page 是必须要有的，否则 Mybatis-Plus 无法为你实现分页。

  1. <resultMap id="SysAnnouncement" type="org.jeecg.modules.system.entity.SysAnnouncement" >
  2.         <result column="id" property="id" jdbcType="VARCHAR"/>
  3.         <result column="titile" property="titile" jdbcType="VARCHAR"/>
  4.         <result column="msg_content" property="msgContent" jdbcType="VARCHAR"/>
  5.         <result column="start_time" property="startTime" jdbcType="TIMESTAMP"/>
  6.         <result column="end_time" property="endTime" jdbcType="TIMESTAMP"/>
  7.         <result column="sender" property="sender" jdbcType="VARCHAR"/>
  8.         <result column="priority" property="priority" jdbcType="VARCHAR"/>
  9.         <result column="msg_category" property="msgCategory" jdbcType="VARCHAR"/>
  10.         <result column="msg_type" property="msgType" jdbcType="VARCHAR"/>
  11.         <result column="send_status" property="sendStatus" jdbcType="VARCHAR"/>
  12.         <result column="send_time" property="sendTime" jdbcType="VARCHAR"/>
  13.         <result column="cancel_time" property="cancelTime" jdbcType="VARCHAR"/>
  14.         <result column="del_flag" property="delFlag" jdbcType="VARCHAR"/>
  15.         <result column="create_by" property="createBy" jdbcType="VARCHAR"/>
  16.         <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
  17.         <result column="update_by" property="updateBy" jdbcType="VARCHAR"/>
  18.         <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
  19.         <result column="user_ids" property="userIds" jdbcType="VARCHAR"/>
  20.     </resultMap>
  23.     <select id="querySysCementListByUserId" parameterType="String"  resultMap="SysAnnouncement">
  24.        select sa.* from sys_announcement sa,sys_announcement_send sas 
  25.        where sa.id = sas.annt_id 
  26.        and sa.send_status = '1'
  27.        and sa.del_flag = '0'
  28.        and sas.user_id = #{userId}
  29.        and sa.msg_category = #{msgCategory}
  30.        and sas.read_flag = '0'
  31.     </select>

（3） service 实现

  1. @Override
  2.     public Page<SysAnnouncement> querySysCementPageByUserId(Page<SysAnnouncement> page, String userId,String msgCategory) {
  3.          return page.setRecords(sysAnnouncementMapper.querySysCementListByUserId(page, userId, msgCategory));
  4.     }

（4） controller 实现

  1. @RequestMapping(value = "/list", method = RequestMethod.GET)
  2.     public Result<Page<SysAnnouncement>> queryPageList(SysAnnouncement sysAnnouncement,
  3.                                       @RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
  4.                                       @RequestParam(name="pageSize", defaultValue="10") Integer pageSize,
  5.                                       HttpServletRequest req) {
  6.         Result<Page<SysAnnouncement>> result = new Result<Page<SysAnnouncement>>();
  7.         Page<SysAnnouncement> pageList = new Page<SysAnnouncement>(pageNo,pageSize);
  8.         pageList = sysAnnouncementService.querySysCementPageByUserId(pageList,"","1");//通知公告消息
  9.         log.info("查询当前页："+pageList.getCurrent());
  10.         log.info("查询当前页数量："+pageList.getSize());
  11.         log.info("查询结果数量："+pageList.getRecords().size());
  12.         log.info("数据总数："+pageList.getTotal());
  13.         result.setSuccess(true);
  14.         result.setResult(pageList);
  15.         return result;
  16.     }