动态SQL

XML文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--=================================================================================================================-->
<!--动态SQL-->
<mapper namespace="com.itheima.mapper.EmpMapper">


<!--    更新一个或几个字段-->
    <update id="update2" >
        update emp
<!--            <if>用于判断条件是否成立，如果条件为true，则拼接SQL语句(<if test="name != null ">...</if>)</if>-->
<!--        <set>自动除去多余的逗号，</set>-->
        <set>
            <if test="username != null">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"> job=#{job},</if>
           <if test="entrydate">  entrydate=#{entrydate},</if>
          <if test="deptId"> dept_id=#{deptId},</if>
           <if test="updateTime">update_time=#{updateTime}</if>
        </set>
        where id = #{id}
    </update>


<!--  批量删除  -->
    <delete id="deleteByIds">
        delete from emp where id in
<!--<foreach collection="遍历的集合" item="遍历出来的元素" separator="分隔符" open="遍历开始前拼接的SQL片段" close="遍历拼接的SQL字段"></foreach>-->
                             <foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>
    </delete>


<!--    SQL动态查询，只用一个或多个条件查询-->
    <select id="list" resultType="com.itheima.pojo.Emp">
    select * from emp
<!--<where>自动去除条件中多余的and和or</where>-->
             <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和include的使用-->

<!--    <sql id="定义可重用的SQL片段"></sql>-->
    <sql id="commonSelect">
        select id,username,password, name, gender, image, job, entrydate, dept_id, create_time, update_time
        from emp
    </sql>

<!--include通过属性refid引用上面<sql></sql>中包含的sql片段-->
    <select id="list1" resultType="com.itheima.pojo.Emp">
      <include refid="commonSelect"></include>
        <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>


</mapper>

=========================================================================

Mapper类文件：

//SQL动态查询，只用一个或多个条件查询
    public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);

//sql和include的使用
    public List<Emp> list1(String name, Short gender, LocalDate begin,LocalDate end);

    //更新一个或几个字段
    public  void update2(Emp emp);

//批量删除
 public void deleteByIds(List<Integer> ids);

=========================================================================

测试类SpringbootMybatisCrudApplicationTests：

    //SQL动态查询，只用一个或多个条件查询
    @Test
    public void testList(){
//     List<Emp> empList= empMapper.list("张",(short)1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
//    List<Emp> empList= empMapper.list("张",null,null,null);
//    List<Emp> empList= empMapper.list("张",(short)1,null,null);
//    List<Emp> empList= empMapper.list(null,(short)1,null,null);
    List<Emp> empList= empMapper.list(null,null,null,null);
    System.out.println(empList);
    }

    //更新一个或几个字段
    @Test
    public void testUpdate2(){
        Emp emp=new Emp();
        emp.setId(19);
        emp.setUsername("Ailisi1");
        emp.setName("爱丽丝1");
        emp.setGender((short)2);
        emp.setUpdateTime(LocalDateTime.now());

        empMapper.update2(emp);

    }

    //批量删除
@Test
    public void testDeleteByIds(){
        List<Integer> ids= Arrays.asList(18,19);
        empMapper.deleteByIds(ids);
}

    //sql和include的使用
    @Test
    public void testList1(){
        List<Emp> empList= empMapper.list1("张",null,null,null);
        System.out.println(empList);
    }