spring mybatis 动态SQL的update

在UPDATE 更新列中使用if标签有时候不希望更新所有的字段，只更新有变化的字段。

（1）需求：只更新有变化的字段，空值不更新。

 

 数据库，建表

create table z_student(
	id int not null auto_increment primary key,
	name varchar(20) not null,
	phone varchar(20) null,
	email varchar(50) null,
	sex tinyint(4) null comment '0女，1男',
	locked tinyint null comment '状态，0正常，1锁定',
	gmt_created datetime default CURRENT_TIMESTAMP comment '存入库的时间',
	gmt_modified datetime default CURRENT_TIMESTAMP comment '修改时间',
	deletes int(11) null
) comment '学生表'


insert into z_student values(null,'tom','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom2','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom3','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom4','130','xx@xx.com',1,0,null,null,0);

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springtest09</artifactId>
        <groupId>com.shrimpking</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>springmybatis-04-dynamic</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.3.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>
</project>

 Student.java

package com.shrimpking.code02;

import java.util.Date;

/**
 * @author user1
 */
public class Student
{
    private int id;
    private String name;
    private String phone;
    private String email;
    private int sex;
    private int locked;
    private Date gmtCreated;
    private Date gmtModified;
    private int deletes;

    public Student()
    {
    }

    public Student(int id, String name, String phone, String email, int sex, int locked, Date gmtCreated, Date gmtModified, int deletes)
    {
        this.id = id;
        this.name = name;
        this.phone = phone;
        this.email = email;
        this.sex = sex;
        this.locked = locked;
        this.gmtCreated = gmtCreated;
        this.gmtModified = gmtModified;
        this.deletes = deletes;
    }

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return name;
    }

    public void setName(String name)
    {
        this.name = name;
    }

    public String getPhone()
    {
        return phone;
    }

    public void setPhone(String phone)
    {
        this.phone = phone;
    }

    public String getEmail()
    {
        return email;
    }

    public void setEmail(String email)
    {
        this.email = email;
    }

    public int getSex()
    {
        return sex;
    }

    public void setSex(int sex)
    {
        this.sex = sex;
    }

    public int getLocked()
    {
        return locked;
    }

    public void setLocked(int locked)
    {
        this.locked = locked;
    }

    public Date getGmtCreated()
    {
        return gmtCreated;
    }

    public void setGmtCreated(Date gmtCreated)
    {
        this.gmtCreated = gmtCreated;
    }

    public Date getGmtModified()
    {
        return gmtModified;
    }

    public void setGmtModified(Date gmtModified)
    {
        this.gmtModified = gmtModified;
    }

    public int getDeletes()
    {
        return deletes;
    }

    public void setDeletes(int deletes)
    {
        this.deletes = deletes;
    }

    @Override
    public String toString()
    {
        return "Student{" + "id=" + id + ", name='" + name + '\'' + ", phone='" + phone + '\'' + ", email='" + email + '\'' + ", sex=" + sex + ", locked=" + locked + ", gmtCreated=" + gmtCreated + ", gmtModified=" + gmtModified + ", deletes=" + deletes + '}';
    }
}

StudentMapper.java

package com.shrimpking.code02;

import java.util.List;

public interface StudentMapper
{
    public int updateById(Student student);
}

StudentMapper.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="com.shrimpking.code02.StudentMapper">

    <update id="updateById" parameterType="Student">
        update z_student
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="phone != null">
                phone = #{phone,jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="sex != null">
                sex = #{sex,jdbcType=TINYINT},
            </if>
            <if test="locked != null">
                locked = #{locked,jdbcType=TINYINT},
            </if>
            <if test="gmtCreated != null">
                gmt_created = #{gmtCreated,jdbcType=TIMESTAMP}
            </if>
            <if test="gmtModified != null">
                gmt_Modified = #{gmtModified,jdbcType=TIMESTAMP}
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <!--  起别名  -->
    <typeAliases>
        <package name="com.shrimpking.code02"/>
    </typeAliases>

    <!--  环境  -->
    <environments default="development">
        <environment id="development">
            <!-- 默认事务管理           -->
            <transactionManager type="JDBC"/>
            <!-- 默认的数据库连接            -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimeZone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="mysql123"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/shrimpking/code02/StudentMapper.xml"/>
    </mappers>
</configuration>

StudentTest.java

package com.shrimpking.code02;

import com.shrimpking.utils.DaoUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.io.IOException;

public class StudentTest
{
    @Test
    public void update() throws IOException
    {
        SqlSession sqlSession = DaoUtils.getSqlSession("code02/mybatis.xml");
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student stu = new Student();
        stu.setId(1);
        stu.setName("虾米大王");
        stu.setPhone("119");
        stu.setEmail("yy@yy.com");
        stu.setSex(0);
        int i = mapper.updateById(stu);
        if (i > 0)
        {
            System.out.println("更新成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

DaoUtils.java

package com.shrimpking.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

/**
 * @author user1
 */
public class DaoUtils
{
    private static Reader reader;
    private static SqlSessionFactory sqlSessionFactory;

    public static SqlSession getSqlSession(String config) throws IOException
    {
        try
        {
            reader = Resources.getResourceAsReader(config);
            //
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            reader.close();
        }
        return sqlSessionFactory.openSession();
    }
}

运行截图