关于动态更新语句还可以使用set元素,set元素可以被用于动态包含需要更新的列,而舍去其他的.

1.EmployeeMapper.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">
<mapper namespace="org.fkit.mapper.EmployeeMapper">
    <select id="selectEmployeeByIdLike" resultType="org.fkit.domain.Employee">
        SELECT * FROM tb_employee
        <where>
            <if test="state != null">
             STATE=#{state}
            </if>
            <if test="id != null">
             AND ID=#{id}
            </if>
            <if test="loginname != null and password != null">
             AND LOGINNAME=#{loginname} AND PASSWORD=#{password}
            </if>
        </where>
    </select>
    <select id="selectEmployeeWithId" parameterType="int" resultType="org.fkit.domain.Employee">
        SELECT * FROM tb_employee WHERE id=#{id}
    </select>
    <update id="updateEmployeeIfNecessary" parameterType="org.fkit.domain.Employee">
        UPDATE tb_employee
        <set>
            <if test="loginname != null">LOGINNAME=#{loginname},</if>
            <if test="password != null">PASSWORD=#{password},</if>
            <if test="name != null">NAME=#{name},</if>
            <if test="sex != null">SEX=#{sex},</if>
            <if test="age != null">AGE=#{age},</if>
            <if test="phone != null">PHONE=#{phone},</if>
            <if test="sal != null">SAL=#{sal},</if>
            <if test="state != null">STATE=#{state},</if>
        </set>
        WHERE id=#{id}
    </update>
</mapper>

set元素会动态前置SET关键字,同时也会消除无关的逗号,因为使用了条件语句之后,很可能就会在生成的赋值语句的后面留下这些逗号

2.EmployeeMapper.java

public interface EmployeeMapper
{
    List<Employee> selectEmployeeByIdLike(HashMap<String,Object> params);
    
    Employee selectEmployeeWithId(Integer id);
    void updateEmployeeIfNecessary(Employee employee);
}

需要注意的是,updateEmployeeIfNecessary方法传递的参数不是之前使用的HashMap,而是一个Employee对象,因为通常更新操作时都是先查询出一个实体对象再进行更新操作.

3.控制器

    @GetMapping("/mysql/DynamicSQL/set")
    public void dynamicSQLSet()
    {
        //获得SqlSession实例
        SqlSession sqlSession = FKSqlSessionFactory.getSqlSession();
        //获得Mapper接口代理对象
        EmployeeMapper em = sqlSession.getMapper(EmployeeMapper.class);
        //查询ID=2的员工信息
        Employee employee = em.selectEmployeeWithId(2);
        //设置需要修改的属性
        employee.setAge(99);
        employee.setPassword("321");
        em.updateEmployeeIfNecessary(employee);
    }

输出:

17:13:52,634 DEBUG selectEmployeeWithId:159 - ==>  Preparing: SELECT * FROM tb_employee WHERE id=?
17:13:52,684 DEBUG selectEmployeeWithId:159 - ==> Parameters: 2(Integer)
17:13:52,707 DEBUG selectEmployeeWithId:159 - <==      Total: 1
17:13:52,761 DEBUG updateEmployeeIfNecessary:159 - ==>  Preparing: UPDATE tb_employee SET LOGINNAME=?, PASSWORD=?, NAME=?, SEX=?, AGE=?, PHONE=?, SAL=?, STATE=? WHERE id=?
17:13:52,762 DEBUG updateEmployeeIfNecessary:159 - ==> Parameters: bbb(String), 321(String), BBB(String), M(String), 99(Integer), 123456(String), 6666.66(Double), DISACTIVE(String), 2(Integer)
17:13:52,768 DEBUG updateEmployeeIfNecessary:159 - <==    Updates: 1

可以看出,首先执行了一个查询语句,查询id=2的员工,之后执行了一条update语句,根据传入的Employee对象更新员工信息.