动态SQL介绍

动态SQL：if与where标签

动态案例-动态更新

EmpMapper（接口）中对应代码块

//动态更新员工public void update2(Emp emp);

EmpMapper.xml中对应代码块

<!-- 动态更新员工--><update id="update2">update emp<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 != 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>

com.itheima.SpringbootMybatisCrudApplicationTests（测试类）对应代码块

//动态更新员工 - 更新ID为18的员工 username 更新为 Tom111, name更新为 汤姆111, gender更新为2@Testpublic void testUpdate2(){//构造员工对象Emp emp = new Emp();emp.setId(18);emp.setUsername("Tom111");emp.setName("汤姆111");emp.setGender((short)2);emp.setUpdateTime(LocalDateTime.now());//执行更新员工操作empMapper.update2(emp);}

案例分析

、、标签总结

动态条件查询

EmpMapper（接口）中对应代码块

//动态条件查询public List<Emp> list(@Param("name") String name, @Param("gender") Short gender, @Param("begin") LocalDate begin ,@Param("end") LocalDate end);

EmpMapper.xml中对应代码块（SQL片段涉及到sql与include标签）

<sql id="commonSelect">select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_timefrom emp</sql>
<!--resultType:单条记录所封装的类型--><select id="list" resultType="com.itheima.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>order by update_time desc</where></select>

com.itheima.SpringbootMybatisCrudApplicationTests（测试类）对应代码块

//动态条件查询员工@Testpublic void testList(){List<Emp> empList = empMapper.list(null, (short)1, null, null);System.out.println(empList);}

批量删除员工（批量删除涉及-foreach标签）

EmpMapper（接口）中对应代码块

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

EmpMapper.xml中对应代码块

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

com.itheima.SpringbootMybatisCrudApplicationTests（测试类）对应代码块

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

动态SQL总结