MyBatis 动态SQL之＜set＞标签

简介

在 Mybatis 中，update 语句可以使用 set 标签动态更新列。set 标签可以为 SQL 语句动态的添加 set 关键字，剔除追加到条件末尾多余的逗号。
作用:
自动在要修改的第一个字段之前添加SET关键字
去掉要修改的第一个字段前的连接符(,)

语法

<set></set>

网络案例

<!--使用set元素动态修改一个网站记录 --><updateid="updateWebsite"parameterType="net.biancheng.po.Website">
        UPDATE website
        <set><iftest="name!=null">name=#{name}，</if><iftest="url!=null">url=#{url}，</if></set>
        WHERE id=#{id}
    </update>

基础环境

1.数据库准备

# 创建一个名称为t_customer的表CREATETABLE t_customer (
    id int(32)PRIMARYKEYAUTO_INCREMENT,
    username varchar(50),
    jobs varchar(50),
    phone varchar(16));# 插入3条数据INSERTINTO t_customer VALUES('1','joy','teacher','13733333333');INSERTINTO t_customer VALUES('2','jack','teacher','13522222222');INSERTINTO t_customer VALUES('3','tom','worker','15111111111');

2.新建项目或Module

3 pom.xml中添加

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="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>mybatis</artifactId><groupId>com.example</groupId><version>1.0-SNAPSHOT</version></parent><modelVersion>4.0.0</modelVersion><groupId>com.biem</groupId><artifactId>dynamaicSql</artifactId><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><dependencies><!--1.引入mybatis包--><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.4.6</version></dependency><!--2.单元测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><!--3.mysql驱动--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.18</version><scope>runtime</scope></dependency><!--4.log4j日志--><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.16</version></dependency></dependencies></project>

4.创建package和文件夹

src/main/java/下创建package
com.biem.pojo
com.biem.mapper
com.biem.util
src/main/resources/下创建文件夹
com/biem/mapper
src/test/java下创建package
com.biem.test

5 框架配置文件

5.1 mybatis核心配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEconfigurationPUBLIC"-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><!--引入properties文件--><propertiesresource="jdbc.properties"></properties><!--将下划线映射为驼峰--><settings><settingname="mapUnderscoreToCamelCase"value="true"/></settings><!--设置类型别名--><typeAliases><!--
            以包为单位，将包下所有的类型设置设置默认的类型别名，即类名且不区分大小写
        --><packagename="com.biem.pojo"/></typeAliases><environmentsdefault="development"><environmentid="development"><transactionManagertype="JDBC"/><dataSourcetype="POOLED"><propertyname="driver"value="${jdbc.driver}"/><propertyname="url"value="${jdbc.url}"/><propertyname="username"value="${jdbc.username}"/><propertyname="password"value="${jdbc.password}"/></dataSource></environment></environments><!-- 引入映射文件 --><mappers><!--
            以包为单位引入映射文件
            要求：
            1. mapper接口所在的包要和映射文件所在的包一致
            2. mapper接口要和映射文件的名字一致
        --><packagename="com.biem.mapper"/></mappers></configuration>

5.2 mybatis属性文件jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root

5.3 log4j.xml文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPElog4j:configurationSYSTEM"log4j.dtd"><log4j:configurationxmlns:log4j="http://jakarta.apache.org/log4j/"><appendername="STDOUT"class="org.apache.log4j.ConsoleAppender"><paramname="Encoding"value="UTF-8"/><layoutclass="org.apache.log4j.PatternLayout"><paramname="ConversionPattern"value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/></layout></appender><loggername="java.sql"><levelvalue="debug"/></logger><loggername="org.apache.ibatis"><levelvalue="info"/></logger><root><levelvalue="debug"/><appender-refref="STDOUT"/></root></log4j:configuration>

6 用户配置文件

6.1 实体类

packagecom.biem.pojo;importlombok.*;/**
 * ClassName: Customer
 * Package: com.biem.pojo
 * Description:
 *
 * @Create 2023/4/5 22:17
 * @Version 1.0
 */@Getter@Setter@NoArgsConstructor@AllArgsConstructor@Builder@ToStringpublicclassCustomer{privateInteger id;privateString username;privateString jobs;privateString phone;}

需要在pom.xml中引入lombok，简化原来的实体类的代码

6.2 mybatis接口类

packagecom.biem.mapper;/**
 * ClassName: CustomerMapper
 * Package: com.biem.mapper
 * Description:
 *
 * @Create 2023/4/5 22:19
 * @Version 1.0
 */publicinterfaceCustomerMapper{}

6.3 mybatis用户配置文件

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.biem.mapper.CustomerMapper"><!-- namespace要和mapper接口的全类名保持一致，例如com.biem.mybatis.mapper.xxxMapper --><!-- sql语句要和接口的方法名保持一致 --></mapper>

6.4 mybatis工具类

packagecom.biem.util;importorg.apache.ibatis.io.Resources;importorg.apache.ibatis.session.SqlSession;importorg.apache.ibatis.session.SqlSessionFactory;importorg.apache.ibatis.session.SqlSessionFactoryBuilder;importjava.io.IOException;importjava.io.InputStream;/**
 * ClassName: MybatisUtil
 * Package: com.biem.utils
 * Description:
 *
 * @Create 2023/4/5 22:23
 * @Version 1.0
 */publicclassMybatisUtil{//利用static(静态)属于类不属于对象,且全局唯一privatestaticSqlSessionFactory sqlSessionFactory =null;//利用静态块在初始化类时实例化sqlSessionFactorystatic{InputStream is=null;try{
            is =Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory =newSqlSessionFactoryBuilder().build(is);}catch(IOException e){
            e.printStackTrace();thrownewExceptionInInitializerError(e);}}/**
     * openSession 创建一个新的SqlSession对象
     * @return SqlSession对象
     */publicstaticSqlSessionopenSession(boolean autoCommit){return sqlSessionFactory.openSession(autoCommit);}publicstaticSqlSessionopenSession(){return sqlSessionFactory.openSession();}/**
     * 释放一个有效的SqlSession对象
     * @param session 准备释放SqlSession对象
     */publicstaticvoidcloseSession(SqlSession session){if(session !=null){
            session.close();}}}

6.5 测试类

packagecom.biem.test;importcom.biem.mapper.CustomerMapper;importcom.biem.pojo.Customer;importcom.biem.util.MybatisUtil;importorg.apache.ibatis.session.SqlSession;importorg.junit.Test;importjava.util.List;/**
 * ClassName: TestCustomer
 * Package: com.biem.test
 * Description:
 *
 * @Create 2023/4/5 22:32
 * @Version 1.0
 */publicclassTestCustomer{}

7 项目结构如下

使用set标签更新记录

1 com.biem.mapper.CustomerMapper.class中添加

publicintupdateCustomerBySet(Customer customer);

2 com/biem/mapper/CustomerMapper.xml中添加

<!--public int updateCustomerBySet(Customer customer);--><updateid="updateCustomerBySet"parameterType="customer">
        update t_customer
        <set><iftest="username != null and username != ''">
                username=#{username}
            </if><iftest="jobs != null and jobs != ''">
                jobs=#{jobs}
            </if><iftest="phone != null and phone != ''">
                phone=#{phone}
            </if></set>
        where id = #{id}
    </update>

3.功能测试 com.biem.test.TestMybatis.java添加测试方法

@TestpublicvoidtestUpdateCustomerBySet(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
        customer.setId(3);
        customer.setPhone("15111001100");CustomerMapper mapper = session.getMapper(CustomerMapper.class);int rows = mapper.updateCustomerBySet(customer);if(rows>0){System.out.println("您成功的更新了"+rows+"条记录");}else{System.out.println("更新失败");}// 关闭SqlSession
        session.close();}