Java Web 学习09

动态sql：if标签

原因：单搜索其他为null无法搜索出结果，与实际不符

结局方法：

select *
from emp
<if test="name != null">
    where 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>
and entrydate between #{begin} and #{end}
order by update_time desc

如果名字为null则sql语句会错误

解决方法：

select *
from 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>
    and entrydate between #{begin} and #{end}
</where>
    order by update_time desc

 使用<where>标签 会自动去除and并且在都为null时不生成where

修改语句：

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>
where id = #{id}
</set>

<set>标签自动去除sql语句后的逗号

<foreach>标签

批量删除：

@Test
public void testDeleteByIds(){
    List<Integer> integers = Arrays.asList(13, 14, 15);
    empMapper.deleteByIds(integers);
}

<!--    collection遍历的集合-->
<!--    item遍历出来的元素-->
<!--    separator分隔符-->
<!--    open遍历开始前拼接的sql语句-->
<!--    close遍历结束后拼接的sql语句-->

public void deleteByIds(List<Integer> ids);

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

<sql>标签<include>标签

把常用sql语句放入sql标签中

例如：

<sql id="commonSql">//id表示名字
    delete
    from emp
    where id in
</sql>

<include refid="commonSql"/>  //用include来写sql语句