Mybatis-动态SQL

随着用户的输入或外部条件的变化而变化的SQL语句，我们称为动态SQL（不会改变其他字段）

<if>

<select id="list" resultType="com.yyy.pojo.Emp">




    select * from mybatis01.emp
        <where>
<if test="name!=null">
    name like concat('%',#{name},'%')
</if>
<if test="gender!=null">
    and gender = #{gender}
</if>
<if test="begin!=null and end !=null">
    and entrydate between #{begin} and #{end}
</if>
        </where>
order by update_time desc
</select>

当name为null时，会报错（多出来个and），实验引入where标签

 <update id="update2" >



        update mybatis01.emp
        <set>
        <if test="username!=null">
        set username    = #{username},
        </if>
        <if test="name!=null">
            name        = #{name},</if>
        <if test="gender!=null">
            gender      = #{gender},</if>
        <if test="image!=null">
            image= #{image},</if>
        <if test="job!=null">
            job         = #{job},</if>
        <if test="entrydate!=null">
            entrydate   =#{entrydate},</if>
        <if test="deptId!=null">
            dept_id     = #{deptId},</if>
        <if test="updateTime!=null">
            update_time =#{updateTime}</if>
        </set>
        where id = #{id}



    </update>

<set>标签用于update更新，删除之后的逗号

<foreach>

collection:遍历的集合

item：遍历的元素

separator：分隔符

open：遍历开始前拼接的sql片断

close：遍历结束后拼接的片断

<delete id="deleteById">

    delete from emp where id in 
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>

public void deleteById(List<Integer> ids);

 @Test
    void contextLoads() {
List<Integer> ids= Arrays.asList(13,14);
empMapper.deleteById(ids);

问题：代码复用性差

定义唯一标识sql片断，片断通过include refid引入

<include refid="commonselect"></include>

<select id="list" resultType="com.yyy.pojo.Emp">




   <include refid="commonselect"/>
        <where>
<if test="name!=null">
    name like concat('%',#{name},'%')
</if>
<if test="gender!=null">
    and gender = #{gender}
</if>
<if test="begin!=null and end !=null">
    and entrydate between #{begin} and #{end}
</if>
        </where>
order by update_time desc
</select>

<sql id ="commonselect">
       select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
    </sql>

   @Test
    void contextLoads() {
List<Emp> empList = empMapper.list(null,null,null,null);
        System.out.println(empList);