Mybatis 的分页条件查询语句编写

刚来到一家新公司, 翻看项目代码, 发现一位同事写的查询逻辑很好, 不用插件, 一个语句完成了分页条件查询.

而我之前一般都是在业务层对参数进行判断, 如果有条件,就调用条件查询的方法, 如果没有条件, 就调用查询所有的方法, 代码冗余较多

贴下代码:

1, 首先定义resultMap:

　　

<resultMap id="xxxModel" type="com.aaa.XxxModel">
    <id column="id" javaType="java.lang.Long" jdbcType="BIGINT" property="id" />
    <result column="ip" javaType="java.lang.String" jdbcType="VARCHAR" property="ip" />
    <result column="port" javaType="java.lang.Integer" jdbcType="INTEGER" property="port" />
    <result column="userName" javaType="java.lang.String" jdbcType="VARCHAR" property="userName" />
    <result column="password" javaType="java.lang.String" jdbcType="VARCHAR" property="password" />
    <result column="lineNum" javaType="java.lang.Integer" jdbcType="INTEGER" property="lineNum" />
    <result column="isInternation" javaType="java.lang.Integer" jdbcType="INTEGER" property="isInternation" />
    <result column="createDate" javaType="java.lang.String" jdbcType="VARCHAR" property="createDate" />
    <result column="updateDate" javaType="java.lang.String" jdbcType="VARCHAR" property="updateDate" />
</resultMap>


2, 定义sql片段, 方便阅读:

//条件和分页参数的封装, 利用动态sql, 特别是模糊查询的%拼接, 很赞:
<sql id="pageListCount">
  from anti_http_proxy a
  <if test="groupId != null and groupId !=''">
    LEFT JOIN anti_proxy_group b on a.`host` = b.`host`
    LEFT JOIN anti_group_server c on b.group_id = c.group_id
  </if>
  where 1=1
  <if test="ip != null and ip !=''">
    and a.`host` like CONCAT('%','${ip}','%')
  </if>
  <if test="groupId != null and groupId !=''">
    and c.id = #{groupId}
  </if>
  ORDER BY a.gmt_modified desc
  <if test="(pageNumber != null and pageNumber != '' or pageNumber == 0) and pageSize != null and pageSize != ''">
    limit #{pageNumber}, #{pageSize}
  </if>
</sql>


<sql id="resultCol">
  a.id
  ,a.`host` as ip
  ,a.`port`
  ,a.username as userName
  ,a.`password`
  ,a.band as lineNum
  ,a.international as isInternation
  ,DATE_FORMAT(a.gmt_create,'%Y-%m-%d %H:%i:%s') as createDate
  ,DATE_FORMAT(a.gmt_modified,'%Y-%m-%d %H:%i:%s') as updateDate
</sql>

3, 完成查询方法

<select id="selectAllByPage" resultMap="xxxModel">
  select
  <include refid="resultCol" />
  <include refid="pageListCount" />
</select>