Mybatis入门（四）：动态sql

• if

List<Emp> getEmpListByMoreTJ(Emp emp);
 
<select id="getEmpListByMoreTJ" resultType="Emp"> 
    select * from t_emp where 1=1 
    <if test="ename != '' and ename != null"> 
        and ename = #{ename} 
    </if> 
    <if test="age != '' and age != null"> 
        and age = #{age} 
    </if> 
    <if test="sex != '' and sex != null"> 
        and sex = #{sex} 
    </if> 
</select>

• where

    <select id="getEmpByConditionTwo" resultType="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 != ''">
                or sex = #{sex}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
        </where>
    </select>

• trim

trim用于去掉或添加标签中的内容
常用属性：
prefix：在trim标签中的内容的前面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffix：在trim标签中的内容的后面添加某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容
 
    <select id="getEmpByCondition" resultType="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} or
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex} and
            </if>
            <if test="email != null and email != ''">
                email = #{email}
            </if>
        </trim>
    </select>

• choose、when、otherwise

    <select id="getEmpByChoose" resultType="Emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != null and empName != ''">      // 当when标签中有一个满足条件时，直接使用这一个条件即可
                    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>                                          // 当所有when标签都不满足时，使用otherwise标签中的条件
                    did = 1
                </otherwise>
            </choose>
        </where>
    </select>

• foreach

# 批量删除
# 方式1，写法1
int deleteMoreByArray(@Param("eids") Integer[] eids);
 
    <delete id="deleteMoreByArray">
        delete from t_emp where eid in
        ( 
        <foreach collection="eids" item="eid" separator=",">
            #{eid}
        </foreach>
        )
    </delete>
 
# 方式1，写法2
    <delete id="deleteMoreByArray">
        delete from t_emp where eid in
        <foreach collection="eids" item="eid" separator="," open="(" close=")">
            #{eid}
        </foreach>
    </delete>
 
# 方式2
    <delete id="deleteMoreByArray">
        delete from t_emp where
        <foreach collection="eids" item="eid" separator="or">
            eid = #{eid}
        </foreach>
    </delete>
 
# 测试
    @Test
    public void testDeleteMoreByArray(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        int result = mapper.deleteMoreByArray(new Integer[]{6, 7, 8});
        System.out.println(result);
    }
 
# 批量添加
int insertMoreByList(@Param("emps") List<Emp> emps);
    <insert id="insertMoreByList">
        insert into t_emp values
        <foreach collection="emps" item="emp" separator=",">
            (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
        </foreach>
    </insert>
 
# 测试
    @Test
    public void testInsertMoreByList(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(null,"a1",23,"男","123@qq.com");
        Emp emp2 = new Emp(null,"a2",23,"男","123@qq.com");
        Emp emp3 = new Emp(null,"a3",23,"男","123@qq.com");
        List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
        System.out.println(mapper.insertMoreByList(emps));
    }
 
属性：
collection：设置要循环的数组或集合
item：表示集合或数组中的每一个数据
separator：设置循环体之间的分隔符
open：设置foreach标签中的内容的开始符
close：设置foreach标签中的内容的结束符

• sql片段

<sql id="empColumns"> eid,ename,age,sex,did </sql> 
 
select <include refid="empColumns"></include> from t_emp