MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

---

if

<if test="条件">拼接的sql</if>

<select id="getEmployees" parameterType="Employee" resultMap="employeeMap">
    select * from EMPLOYEE
    where
    <if test="id!=null"> ID=#{id}</if>
    <if test="name!=null and name !=''"> and NAME like CONCAT('%', #{name},'%')</if>
</select>

where

<select id="getEmployees" parameterType="Employee" resultMap="employeeMap">
    select * from EMPLOYEE
    <where>
        <if test="id!=null">ID=#{id}</if>
        <if test="name!=null and name !=''">and NAME like CONCAT('%', #{name},'%')</if>
    </where>
</select>

set

<update id="updateEmployee" parameterType="Employee">
    update EMPLOYEE
    <set>
        <if test="name!=null and name !=''">NAME =#{name},</if>
        <if test="salary!=null">SALARY =#{salary},</if>
    </set>
    where ID=#{id}
</update>

trim

注：trim标签可以用来替代where和set标签

<select id="getEmployees2" parameterType="Employee" resultMap="employeeMap">
    select * from EMPLOYEE
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="id!=null">ID=#{id}</if>
        <if test="name!=null and name !=''">and NAME like CONCAT('%', #{name},'%')</if>
    </trim>
</select>
<update id="updateEmployee2" parameterType="Employee">
    update EMPLOYEE
    <trim prefix="SET" suffixOverrides=",">
        <if test="name!=null and name !=''">NAME =#{name},</if>
        <if test="salary!=null">SALARY =#{salary},</if>
    </trim>
    where ID=#{id}
</update>

choose、when、otherwise

<select id="getEmployees3" parameterType="Employee" resultMap="employeeMap">
    select * from EMPLOYEE
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <choose>
            <when  test="id!=null">ID=#{id}</when >
            <when  test="name!=null and name !=''"> NAME =#{name}</when >
	<when  test="salary!=null">SALARY=#{salary}</when >
            <otherwise > DEPT_ID=1</otherwise>
        </choose>
    </trim>
</select>

foreach

注：foreach传入一个list，可以拼接成IN的条件

<select id="getEmployees4" parameterType="List" resultMap="employeeMap">
    select * from EMPLOYEE
    where ID IN
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

bind

注：创建一个变量，绑定到上下文

<select id="getEmployees5" parameterType="Employee" resultMap="employeeMap">
    <bind name="pattern" value="'%' + name+ '%'" />
    select * from EMPLOYEE
    where
    NAME like #{pattern}
</select>

sql，include

注：提取sql语句，可以使用include引用

<sql id="selectEmployee">
    select * from EMPLOYEE
</sql>
<select id="getEmployees6" parameterType="int" resultMap="employeeMap">
    <include refid="selectEmployee"/>
    where ID =#{id}
</select>

_parameter

注：当传递基本数据类型的时候，使用if判断test条件时，需要使用_paramter作为参数名

<select id="getDeptById" parameterType="int" resultMap="deptMap">
    select ID,NAME from DEPT
    <if test="_parameter!=null"> where ID=#{id}</if>
</select>

#{}和${}的区别

#{}是将传入的值当做字符串的形式，并且有预编译功能。

${}是将传入的数据直接显示生成sql语句，没有预编译功能。