MyBatis 动态SQL

动态 SQL

• MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。—》官方说法
• 有那些动态标签呢？
  • if 判断标签
  • choose (when, otherwise) 选择标签
  • trim (where, set) 字符拼接和去过滤and or
  • foreach 循环

if 判断

传入一个参数

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE state = ‘ACTIVE’ 
  <if test="title != null and titel != ''">
    AND title like #{title}
  </if>
</select>

假如title不为空，则传入titel

传入两个参数

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’ 
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

choose, when, otherwise 类似于Java 的 switch

放码过来

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

title有值就按照title查找，

author.name有值就按照author.name查找，

两者都没有则写入AND featured = 1;(记住只能三选一。switch你懂的)

trim, where, set

看这条语句

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 
  <if test="state != null">
    state = #{state}
  </if> 
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

• 假如没有一个参数有值，就会变成

SELECT * FROM BLOG
WHERE

查询失败

• 第二个参数有值，其他没有值

SELECT * FROM BLOG
WHERE 
AND title like ‘someTitle’

查询失败

• 解决方案一：
  
  用where包含if，至少有一个参数有值才会出现where的子句，假如语句开头的有AND和OR，where会自动忽视，就不会出现上面的情况了。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

• 解决方案二：利用trim标签
  
  • prefix=”“：前缀：trim标签体中是整个字符串拼串后的结果。prefix给拼串后的整个字符串加一个前缀。
  • prefixOverrides=”“：前缀覆盖： 去掉整个字符串前面多余的字符。
  • suffix=”“：后缀：suffix给拼串后的整个字符串加一个后缀。
  • suffixOverrides=”“：后缀覆盖：去掉整个字符串后面多余的字符。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  <trim prefix="WHERE" prefixOverrides="AND | OR "> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </trim>
</select>

• 解决方案三：1=1 （推荐很方便）

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 1=1
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</select>

是不是很有意思！

SET 动态更新语句

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

其实set和where有类似的功能，set会自动忽略子句结尾中的“，”

假如你想用trim的也行

<update id="updateAuthorIfNecessary">
  update Author
    <trim prefix="SET" suffixOverrides=",">
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </trim>
  where id=#{id}
</update>

foreach

• 集合遍历

  • collection表示数据类型，List就是list，array就是array，
  • item表示集合中每一个元素进行迭代时的别名。
  • index指定一个名字，用于表示在迭代过程中，每次迭代到的位置。
  • open表示该语句以什么开始。
  • separator表示在每次进行迭代之间以什么符号作为分隔符。
  • close表示以什么结束。

• 批量查询

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
        #{item}
  </foreach>
</select>