MyBatis 动态 SQL

为什么需要动态 SQL

实际业务中查询条件往往是可选的，动态 SQL 根据参数自动拼接 WHERE 子句，避免编写大量重复 SQL。

if 标签

<select id="findUsers" resultType="User">
  SELECT * FROM user WHERE 1=1
  <if test="name != null and name != ''">
    AND name LIKE CONCAT('%', #{name}, '%')
  </if>
  <if test="age != null">
    AND age = #{age}
  </if>
</select>

where 标签

自动处理第一个 AND/OR，没条件时不生成 WHERE：

<select id="findUsers" resultType="User">
  SELECT * FROM user
  <where>
    <if test="name != null">AND name = #{name}</if>
    <if test="status != null">AND status = #{status}</if>
  </where>
</select>

choose / when / otherwise

<select id="findUsers" resultType="User">
  SELECT * FROM user WHERE 1=1
  <choose>
    <when test="name != null">AND name = #{name}</when>
    <when test="email != null">AND email = #{email}</when>
    <otherwise>AND status = 1</otherwise>
  </choose>
</select>

set 标签

<update id="updateUser">
  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>

foreach 标签

批量查询 IN

<select id="selectByIds" resultType="User">
  SELECT * FROM user WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>

批量插入

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

trim 标签

最灵活的标签，可以添加/移除前缀或后缀：

<!-- 替代 where -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  <if test="name != null">AND name = #{name}</if>
</trim>
<!-- 替代 set -->
<trim prefix="SET" suffixOverrides=",">
  <if test="name != null">name = #{name},</if>
</trim>

script 注解中写动态 SQL

@Select("<script>" +
  "SELECT * FROM user" +
  "<where>" +
  "  <if test='name != null'>AND name = #{name}</if>" +
  "</where>" +
  "</script>")
List<User> findUsers(@Param("name") String name);