MyBatis动态SQL笔记

1.if：根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接执行

    <select id="getEmpByCondition" resultType="com.mybatis.mapper.pojo.Emp">
        select * from t_emp where   1=1
        <if test="empName != null and empName!=''">
             emp_name = #{empName}
        </if>
        <if test="age  != null and age!= ''">
            and age = #{age}
        </if>
        <if test="sex  != null and sex!= ''">
            and sex = #{sex}
        </if>
        <if test="email  != null and email!= ''">
            and email = #{email}
        </if>
    </select>

2.where:
*      当where标签中有内容时，会自动生成where关键字，并且将内容前多余的and/or去掉
*      当where标签中没有内容时，此时where标签没有任何效果
*      注意：where标签不能将其中内容后面的多余and/or去掉

 <select id="getEmpByConditionOne" resultType="com.mybatis.mapper.pojo.Emp">
        select * from t_emp
        <where>
            <if test="empName != null and empName!=''">
                emp_name = #{empName}
            </if>
            <if test="age  != null and age!= ''">
                and age = #{age}
            </if>
            <if test="sex  != null and sex!= ''">
                and sex = #{sex}
            </if>
            <if test="email  != null and email!= ''">
                and email = #{email}
            </if>
         </where>
    </select>

3.trim：
*       prefix/suffix:将trim标签中内容前或者后面添加指定内容
*       suffixOverrides/prefixOverrides:将trim标签中前面或后面去除指定内容
*       诺标签中没有内容，trim标签没有任何效果

<select id="getEmpByConditionTwo" resultType="com.mybatis.mapper.pojo.Emp">
        select <include refid="empColumns"></include> from t_emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empName != null and empName!=''">
                emp_name = #{empName}  and
            </if>
            <if test="age  != null and age!= ''">
                age = #{age} and
            </if>
            <if test="sex  != null and sex!= ''">
               sex = #{sex}  and
            </if>
            <if test="email  != null and email!= ''">
                email = #{email}
            </if>
        </trim>
    </select>

* 4.choose，when，otherwise,相当于if...else if...else
* when最少要有一个，otherwise最多只能有一个

    <select id="getEmpByChoose" resultType="com.mybatis.mapper.pojo.Emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != null and empName!=''">
                    emp_name=#{empName}
                </when>
                <when test="age != null and age!=''">
                    age=#{age}
                </when>
                <when test="sex != null and sex!=''">
                    sex=#{sex}
                </when>
                <when test="email!= null and email!=''">
                    email=#{email}
                </when>
                <otherwise>
                    did  = 1
                </otherwise>
            </choose>
        </where>
    </select>

5.foreach
*      collection:设置需要访问的数组
*      item：表示数组或集合中的每一个数据
*      separate：循环体之间的分隔符
*      open：foreach标签循环的所有内容的开始符
*      close：foreach标签循环的所有内容的结束符

   <delete id="deleteMoreByArray">
        delete from t_emp where eid in
        <foreach collection="eids" item="eid" separator="or">
            eid = #{eid}
        </foreach>
<!--        delete from t_emp where eid in;    -->
<!--        <foreach collection="eids" item="eid" separator="," open="(" close=")">-->
<!--            #{eid}    -->
<!--        </foreach>    -->
    </delete>

6.sql标签
*       设置sql片段<sql id="empColumns">eid,emp_name,age,sex,email</sql>
*       引用sql片段select <include refid="empColumns"></include> from t_emp

    <sql id="empColumns">eid,emp_name,age,sex,email</sql>

    <select id="getEmpByConditionTwo" resultType="com.mybatis.mapper.pojo.Emp">
        select <include refid="empColumns"></include> from t_emp
    </select>