mybatis之动态SQL<if><where><set><foreach><sql><include>

标签主要分为<if> <foreach><sql><include>

动态查询<if><where>

只根据姓名进行查询。

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

    }

<mapper namespace="com.example.springbootmybatis.mapper.EmpMapper">

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

        <!-- resultType: 单条记录所封装的类型 -->

        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>
        order by update_time desc
    </select>

</mapper>

加上where注释

<mapper namespace="com.example.springbootmybatis.mapper.EmpMapper">

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

        <!-- resultType: 单条记录所封装的类型 -->

        select *
        from emp
        <where>/*自动去除and或者or  ，动态条件查询，条件为空不添加条件*/
        <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>

动态更新信息<if><set>

问题

//更新某些选型，会导致其他字段被替换为null
@Test
public void testUpdate2() {
    // 构造员工对象
    Emp emp = new Emp();
    emp.setId(18);
    emp.setUsername("Tom11");
    emp.setName("汤姆111");
    emp.setGender((short) 2);
    emp.setUpdateTime(LocalDateTime.now());

    // 执行更新员工操作
    empMapper.update(emp);
}

---

编写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>

EmpMapper.java

public void update2(Emp emp);

test

@Test
public void testUpdate2() {
    // 构造员工对象
    Emp emp = new Emp();
    emp.setId(18);
    emp.setUsername("Tom11");
    emp.setName("汤姆111");
    emp.setGender((short) 2);
    emp.setUpdateTime(LocalDateTime.now());

    // 执行更新员工操作2
    empMapper.update2(emp);
}

<foreach>

xml

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

接口

public void deleteByIds(List<Integer> ids);

test

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

<sql><include>

把内容抽取出来打包并使用

xml

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

test

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

}