MyBatis——动态SQL

if

    <select ... >
        SELECT * FROM tb WHERE 1=1
        <!-- 可选条件，在满足test中判断时应用该条件 -->
        <if test="id != null and name != null ">
            AND id = #{id}
        </if>
    </select>

choose(when、otherwise)

  <select ... >
        SELECT * FROM tb WHERE 1=1
        <!-- 相当于java中的swith，满足那个when的条件用那个，若when的条件都不满足，用otherwise -->
        <choose>
            <when test=" id != null">
              AND id = #{id}
            </when>
            <when test="loginname != null and password != null">
              AND loginname = #{loginname} AND password = #{password}
            </when>
            <otherwise>
              AND sex='男'
            </otherwise>
        </choose>
    </select>

where

  <select  ... >
        SELECT * FROM tb
        <!-- where元素在至少有一个if满足的情况下才会插入where条件。
             若if中内容以AND或OR开头，where元素会把它们去除 -->
        <where>
            <if test="id != null">
              id = #{id}
            </if>
            <if test="loginname != null">
              AND loginname = #{loginname}
            </if>
        </where>
    </select>

set

    <update ... >
        UPDATE tb 
        <!-- 在if判断为true时应用if中语句动态设置set关键字，同时也会消除无关逗号 -->
        <set>
            <if test="id != null">id=#{id},</if>
            <if test="loginname != null">loginname=#{loginname},</if>
            <if test="password != null">password=#{password}</if>
        </set>
        WHERE id = #{id}
    </update>

foreach

    <select  ... >
        SELECT * FROM tb WHERE id IN
        <!-- 遍历list集合 -->
        <foreach item="item" index="index" collection="list"
            open="(" separator="," close=")">
           #{item}
        </foreach>
    </select>

bind

    <select id="bindTest">
        <!-- 从OGNL表达式中创建一个变量并绑定到上下文 -->
        <bind name="pattern" value=" '%' + _parameter.getName() + '%' " />
        SELECT * FROM tb WHERE loginname LIKE #{pattern}
    </select>