动态SQL

摘要：本文学习了如何在MyBatis中构建动态的SQL语句。

环境

Windows 10 企业版 LTSC 21H2
MySQL 5.7.40
Java 1.8
Maven 3.6.3
MyBatis 3.5.6

1 if

实现条件判断，根据条件判断的结果来决定是否添加SQL片段：

xml

<select id="selectUserListByUser" resultType="com.example.pojo.User">
  SELECT * FROM user WHERE name = #{name}
  <if test="age != null">
    AND age = #{age}
  </if>
</select>

2 where

动态拼接SQL条件，通常配合if标签使用，自动添加WHERE并去除AND和OR：

xml

<select id="selectUserListByUser" resultType="com.example.pojo.User">
  SELECT * FROM user
  <where>
    <if test="name != null">
      AND name = #{name}
    </if>
    <if test="age != null">
      AND age = #{age}
    </if>
  </where>
</select>

3 choose/when/otherwise

按照从上到下的顺序检查when标签中的判断条件，一旦满足就停止检查其他条件，如果没有满足任何条件，则执行otherwise标签中的内容：

xml

<select id="selectUserListByUser" resultType="com.example.pojo.User">
  SELECT * FROM user WHERE name = #{name}
  <choose>
    <when test="age != null">
      AND age = #{age}
    </when>
    <otherwise>
      AND age = 18
    </otherwise>
  </choose>
</select>

4 foreach

遍历集合，根据集合中的元素生成SQL片段。

范围查询，遍历集合：

xml

<select id="selectUserListByNames" resultType="com.example.pojo.User">
  SELECT * FROM user WHERE name IN
  <foreach collection="list" item="name" separator="," open="(" close=")">
    #{name}
  </foreach>
</select>

范围查询，分割字符串并遍历：

<select id="selectUserListByNames" resultType="com.example.pojo.User">
  SELECT * FROM user WHERE name IN
  <foreach collection="names.split(',')" item="name" separator="," open="(" close=")">
    #{name}
  </foreach>
</select>

批量插入：

xml

<insert id="batchInsert">
  INSERT INTO user (name, age, email) VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.name}, #{user.age}, #{user.email})
  </foreach>
</insert>

5 set

动态拼接更新语句，自动添加SET并去除逗号：

xml

<update id="updateUserById">
  UPDATE user
  <set>
    <if test="name != null">
      name = #{name},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="email != null">
      email = #{email},
    </if>
  </set>
  WHERE id = #{id}
</update>

6 bind

绑定变量，可以在SQL中使用变量：

xml

<select id="selectUserListByName" resultType="com.example.pojo.User">
  <bind name="pattern" value="'%' + name + '%'"/>
  SELECT * FROM user WHERE name LIKE #{pattern}
</select>

7 trim

自定义拼接规则，可以代替where标签和set标签，灵活拼接SQL片段。

常用属性：

属性名	作用	取值
prefix	前缀，默认无前缀	字符串
suffix	后缀，默认无后缀	字符串
prefixOverrides	去除前缀，默认无前缀	字符串
suffixOverrides	去除后缀，默认无后缀	字符串

替代where标签：

xml

<select id="selectUserListByUser" resultType="com.example.pojo.User">
  SELECT * FROM user
  <trim prefix="WHERE" prefixOverrides="AND |OR">
    <if test="name != null">
      AND name = #{name}
    </if>
    <if test="age != null">
      AND age = #{age}
    </if>
  </trim>
</select>

替代set标签：

xml

<update id="updateUserById">
  UPDATE user
  <trim prefix="SET" suffixOverrides=",">
    <if test="name != null">
      name = #{name},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="email != null">
      email = #{email},
    </if>
  </trim>
  WHERE id = #{id}
</update>

批量更新：

xml

<update id="batchUpdate">
  UPDATE user
  <trim prefix="SET" suffixOverrides=",">
    <trim prefix="name = CASE" suffix="ELSE name END,">
      <foreach collection="list" item="user">
        <if test="user.name != null">
          WHEN id = #{user.id} THEN #{user.name}
        </if>
      </foreach>
    </trim>
    <trim prefix="age = CASE" suffix="ELSE age END,">
      <foreach collection="list" item="user">
        <if test="user.age != null">
          WHEN id = #{user.id} THEN #{user.age}
        </if>
      </foreach>
    </trim>
    <trim prefix="email = CASE" suffix="ELSE email END,">
      <foreach collection="list" item="user">
        <if test="user.email != null">
          WHEN id = #{user.id} THEN #{user.email}
        </if>
      </foreach>
    </trim>
  </trim>
  WHERE id IN
  <foreach collection="list" item="user" separator="," open="(" close=")">
    #{user.id}
  </foreach>
</update>