Mybatis 动态sql语句if标签、where标签以及choose标签的结合巧妙使用

需求

SELECT
    id ,
    gender ,
    nickname ,
    mobile ,
    avatar
  FROM
    dts_user
  WHERE
    gender = 1
  AND mobile LIKE '%56%'

查询结果

mybatis动态sql配置

进阶一>>查询条件 gender 已固定，模糊查询 mobile 以修改成了动态配置

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
  SELECT id , gender ,nickname ,mobile ,avatar From dts_user
  WHERE gender = 1 AND mobile LIKE '%${mobile}%'
</select>

进阶二>>查询条件 gender（只能查 gender 为 0 或者 1 的用户数据 ）和模糊查询 mobile 都修改成了动态配置

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
  SELECT id , gender ,nickname ,mobile ,avatar From dts_user
  WHERE gender = #{gender} AND mobile LIKE '%${mobile}%'
</select>

进阶三>>if 标签来判断，可选择性来使用查询条件

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
  SELECT id , gender ,nickname ,mobile ,avatar From dts_user
  WHERE 1=1
  <if test="gender != null and gender != ''">
    AND gender = #{gender}
  </if>
  <if test="mobile != null and mobile != ''">
    AND mobile LIKE '%${mobile}%'
  </if>
</select>

进阶四>>查所有用户的信息时，不传 gender 与 username 数据

<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
   SELECT id , gender ,nickname ,mobile ,avatar From dts_user
<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
  <where>
    <if test="gender != null">
      AND gender = #{gender}
    </if>
    <if test="mobile != null and mobile != ''">
     AND mobile LIKE '%${mobile}%'
    </if>
  </where>
</select>

一个实战复杂的示例仅仅提供sql

@Select(" <script> " +
            " select novel.id, novel.title, novel.cover_img, novel.author, novel.intro, novel.score, novel.last_chapter_name, novel.lately_follower, novel.total_follower, novel.retention_ratio, novel.last_chapter_name, novel.category_id, novel.category_name " +
            " from novel_t as novel inner join novel_category_t as cat on novel.category_id = cat.id " +
            " <where> novel.remove = 0 and novel.sell_status='UP' " +

            " <choose>" +
            "   <when test='minCatList!=null and minCatList.size() > 0'>" +
            "       and novel.category_id in " +
            "   <foreach collection=\"minCatList\" item=\"item\" index=\"index\" open=\"(\" separator=\",\" close=\")\"> " +
            "       #{item.id} " +
            "   </foreach>" +
            "   </when> " +
            "   <otherwise>" +
            "   and novel.category_id=#{majorId} " +
            "   </otherwise>" +
            " </choose>" +
            " <if test='genderChannel!=\"\" and genderChannel!=null'> " +
            " and cat.gender_channel=#{genderChannel} " +
            " </if> " +
            " <if test='status!=\"\" and status!=\"all\"'> " +
            " and novel.serial_status=#{status} " +
            " </if> " +
            " </where> " +
            " order by novel.last_modified desc " +
            " </script> "

点击下方卡片/微信搜索，关注公众号“天宇文创意乐派”(ID:gh_cc865e4c536b)

天宇文创意乐派

个人订阅号 主要提供：分享最新资讯 IT教程 免费小说的平台

20篇原创内容

公众号

本文使用 文章同步助手 同步