MyBatis的常用动态标签

1、<sql>

<!-- 
    <sql id=""></sql>：设置一段SQL片段，即公共SQL，可以被当前映射文件中所有的SQL语句所访问
    <include refid="empColumns"></include>：访问某个SQL片段
-->
<sql id="empColumns">
    select eid,ename,age,sex,did from emp
</sql>

2、<include>

<!-- Emp getEmpByEid(String eid); -->
<select id="getEmpByEid" resultType="Emp">
    <include refid="empColumns" /> where eid = #{eid}
</select>

3、<if>, <where>, <trim>

<!-- 
    <if test=""></if>：通过test表达式，拼接SQL
    <where>：添加where关键字，同时去掉多余的and
    <trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">：截取并拼接
        prefix：在操作的SQL语句前加入某些内容
        suffix：在操作的SQL语句后加入某些内容
        prefixOverrides：把操作的SQL语句前的某些内容去掉
        suffixOverrides：把操作的SQL语句后的某些内容去掉
-->

<!-- List<Emp> getEmpListByCondition(); -->
<select id="getEmpListByCondition" resultType="Emp">
    select eid,ename,age,sex,did from emp
    <where>
        <if test="eid != null">
            and eid = #{eid}
        </if>
        <if test="ename != null and ename != ''">
            and ename = #{ename}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
        <if test="sex == 1 or sex == 0">
            and sex = #{sex}
        </if>
    </where>
</select>


<!-- List<Emp> getEmpListByCondition2(); 多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件 -->
<select id="getEmpListByCondition2" resultType="Emp">
    <include refid="empColumns" />
    <trim prefix="where" suffixOverrides="and|or">
        <if test="eid != null">
            eid = #{eid} and
        </if>
        <if test="ename != null and ename != ''">
            ename = #{ename} and
        </if>
        <if test="age != null">
            age = #{age} or
        </if>
        <if test="sex == 1 or sex == 0">
            sex = #{sex}
        </if>
    </trim>
</select>

4、<choose>，<when>，<otherwise>

<!-- 
    <choose>：选择某一个when或otherwise拼接SQL
        <when test=""></when>：通过test表达式拼接SQL
        .
        .
        <otherwise></otherwise>：当when都不符合条件，就会选择otherwise拼接SQL
    </choose>
-->

<!-- 查询 -->
<!-- List<Emp> getEmpListByChoose(Emp emp); -->
<select id="getEmpListByChoose" resultType="Emp">
    select eid,ename,age,sex from emp
    where
    <choose>
        <when test="eid != null">
            eid = #{eid}
        </when>
        <when test="ename != null and ename != ''">
            ename = #{ename}
        </when>
        <when test="age != null">
            age = #{age}
        </when>
        <otherwise>
            sex = #{sex}
        </otherwise>
    </choose>
</select>


<!-- 插入 -->
<!-- void insertEmp(Emp emp); -->
<insert id="insertEmp">
    insert into emp(eid,ename,age,sex) values(
        null,
        #{ename},
        #{age},
        <choose>
            <when test="sex == 0">'女'</when>
            <when test="sex == 1">'男'</when>
            <otherwise>'不详'</otherwise>
        </choose>
    )
</insert>

5、<foreach>

<!-- 
    <foreach collection="" item="" close="" open="" separator="" index=""></foreach>
    对一个数组或集合进行遍历
        collection：指定要遍历的集合或数组
        item：设置别名，循环变量
        close：设置循环体的结束内容
        open：设置循环体的开始内容
        separator：设置每一次循环之间的分隔符
        index：若遍历的是list，index代表下标；若遍历的是map，index代表键
-->

<!-- void deleteEmps(List<Integer> eids); -->
<!-- 示例：delete from emp where eid in (1, 2, 3) -->
<delete id="deleteEmps">
    delete from emp where eid in
    <foreach collection="eids" item="eid" separator="," open="(" close=")">
        #{eid}
    </foreach>
</delete>