App下載

怎么實現(xiàn)MyBatis limit分頁設置?

猿友 2021-07-14 16:42:16 瀏覽數(shù) (2546)
反饋

本文將為您介紹MyBatis limit一種錯誤的寫法和兩種正確的寫法,其中一種正確寫法還需要在Java語言中額外設置get函數(shù)。

錯誤的寫法:

<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之后的語句不允許的變量不允許進行算數(shù)運算,會報錯。

 正確的寫法一:

<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>

 分析:方法二的寫法,需要再請求參數(shù)中額外設置兩個get函數(shù),如下:

@Data
public class QueryParameterVO {
 
 private List<String> ids;
 
 private List<Integer> statusList;
 
 // 前端傳入的頁碼
 private int pageNo; // 從1開始
 
 // 每頁的條數(shù)
 private int pageSize;
 
 // 數(shù)據庫的偏移
 private int offSet;
 
 // 數(shù)據庫的大小限制
 private int limit;
 
 // 這里重寫offSet和limit的get方法
 public int getOffSet() {
  return (pageNo-1)*pageSize;
 }
 
 public int getLimit() {
  return pageSize;
 }
}

到此這篇關于MyBatis limit分頁設置的錯誤寫法和兩種正確實現(xiàn)寫法以及第二種正確寫法關聯(lián)get函數(shù)的文章內容到此結束了,更多相關MyBatis limit分頁內容請搜索W3Cschool以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持W3Cschool!


0 人點贊