随着用户的输入或外部条件的变化而变化的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);