MyBatis limit分页设置的实现

错误的写法：

<select id="querymyapplicationrecord" parametertype="myapplicationrequest" resultmap="myapplicationmap">
 select
 a.*,
 from
 tb_user a
 where 1=1
 <if test="ids != null and ids.size()!=0">
  and a.id in
  <foreach collection="ids" item="id" index="index"
     open="(" close=")" separator=",">
   #{id}
  </foreach>
 </if>
 <if test="statuslist != null and statuslist.size()!=0">
  and a.status in
  <foreach collection="statuslist" item="status" index="index"
     open="(" close=")" separator=",">
   #{status}
  </foreach>
 </if>
 order by a.create_time desc
 limit (#{pageno}-1)*#{pagesize},#{pagesize}; // 错误
</select>

 在mybatis中limit之后的语句不允许的变量不允许进行算数运算，会报错。

 正确的写法一：

<select id="querymyapplicationrecord" parametertype="myapplicationrequest" resultmap="myapplicationmap">
 select
 a.*,
 from
 tb_user a
 where 1=1
 <if test="ids != null and ids.size()!=0">
  and a.id in
  <foreach collection="ids" item="id" index="index"
     open="(" close=")" separator=",">
   #{id}
  </foreach>
 </if>
 <if test="statuslist != null and statuslist.size()!=0">
  and a.status in
  <foreach collection="statuslist" item="status" index="index"
     open="(" close=")" separator=",">
   #{status}
  </foreach>
 </if>
 order by a.create_time desc
 limit ${(pageno-1)*pagesize},${pagesize}; （正确）
</select>

 正确的写法二：（推荐）

<select id="querymyapplicationrecord" parametertype="myapplicationrequest" resultmap="myapplicationmap">
 select
 a.*,
 from
 tb_user a
 where 1=1
 <if test="ids != null and ids.size()!=0">
  and a.id in
  <foreach collection="ids" item="id" index="index"
     open="(" close=")" separator=",">
   #{id}
  </foreach>
 </if>
 <if test="statuslist != null and statuslist.size()!=0">
  and a.status in
  <foreach collection="statuslist" item="status" index="index"
     open="(" close=")" separator=",">
   #{status}
  </foreach>
 </if>
 order by a.create_time desc
 limit #{offset},#{limit}; （推荐，代码层可控）
</select>

 分析：方法二的写法，需要再请求参数中额外设置两个get函数，如下：

@data
public class queryparametervo {
 
 private list<string> ids;
 
 private list<integer> statuslist;
 
 // 前端传入的页码
 private int pageno; // 从1开始
 
 // 每页的条数
 private int pagesize;
 
 // 数据库的偏移
 private int offset;
 
 // 数据库的大小限制
 private int limit;
 
 // 这里重写offset和limit的get方法
 public int getoffset() {
  return (pageno-1)*pagesize;
 }
 
 public int getlimit() {
  return pagesize;
 }
}

到此这篇关于mybatis limit分页设置的实现的文章就介绍到这了,更多相关mybatis limit分页内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！