MyBatis 动态SQL之＜set＞标签

柔光的暖阳◎ 2024-03-24 15:24 155阅读 0赞

## 简介 ##

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

## 语法 ##

## 网络案例 ##

<update id="updateWebsite"
            parameterType="net.biancheng.po.Website">
            UPDATE website
            <set>
                <if test="name!=null">name=#{name}，</if>
                <if test="url!=null">url=#{url}，</if>
            </set>
            WHERE id=#{id}
        </update>

## 基础环境 ##

### 1.数据库准备 ###

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

### 2.新建项目或Module ###

![在这里插入图片描述][002bc578219545d3a5ce3e5e9536c694.png]

### 3 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>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>
            
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.6</version>
            </dependency>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.18</version>
                <scope>runtime</scope>
            </dependency>
    
            
            <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" ?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        
        <properties resource="jdbc.properties"></properties>
    
        
        <settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
    
        
        <typeAliases>
            
            <package name="com.biem.pojo"/>
        </typeAliases>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC"/>
                <dataSource type="POOLED">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
    
        </environments>
    
        
        <mappers>
            
            <package name="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" ?>
    <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
    
    <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
    
        <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
            <param name="Encoding" value="UTF-8"/>
            <layout class="org.apache.log4j.PatternLayout">
                <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
            </layout>
        </appender>
        <logger name="java.sql">
            <level value="debug"/>
        </logger>
        <logger name="org.apache.ibatis">
            <level value="info"/>
        </logger>
        <root>
            <level value="debug"/>
            <appender-ref ref="STDOUT"/>
        </root>
    </log4j:configuration>

### 6 用户配置文件 ###

#### 6.1 实体类 ####

package com.biem.pojo;
    
    import lombok.*;
    
    /**
     * ClassName: Customer
     * Package: com.biem.pojo
     * Description:
     *
     * @Create 2023/4/5 22:17
     * @Version 1.0
     */
    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    @ToString
    public class Customer {
        
        private Integer id;
        private String username;
        private String jobs;
        private String phone;
    }

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

#### 6.2 mybatis接口类 ####

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

#### 6.3 mybatis用户配置文件 ####

<?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.biem.mapper.CustomerMapper">
        
    
        
    
    </mapper>

#### 6.4 mybatis工具类 ####

package com.biem.util;
    
    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.InputStream;
    
    /**
     * ClassName: MybatisUtil
     * Package: com.biem.utils
     * Description:
     *
     * @Create 2023/4/5 22:23
     * @Version 1.0
     */
    public class MybatisUtil {
        
        //利用static(静态)属于类不属于对象,且全局唯一
        private static SqlSessionFactory sqlSessionFactory = null;
        //利用静态块在初始化类时实例化sqlSessionFactory
        static {
        
            InputStream is= null;
            try {
        
                is = Resources.getResourceAsStream("mybatis-config.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
            } catch (IOException e) {
        
                e.printStackTrace();
                throw new ExceptionInInitializerError(e);
            }
        }
    
        /**
         * openSession 创建一个新的SqlSession对象
         * @return SqlSession对象
         */
        public static SqlSession openSession(boolean autoCommit){
        
            return sqlSessionFactory.openSession(autoCommit);
        }
    
        public static SqlSession openSession(){
        
            return sqlSessionFactory.openSession();
        }
        /**
         * 释放一个有效的SqlSession对象
         * @param session 准备释放SqlSession对象
         */
        public static void closeSession(SqlSession session){
        
            if(session != null){
        
                session.close();
            }
        }
    }

#### 6.5 测试类 ####

package com.biem.test;
    
    import com.biem.mapper.CustomerMapper;
    import com.biem.pojo.Customer;
    import com.biem.util.MybatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    /**
     * ClassName: TestCustomer
     * Package: com.biem.test
     * Description:
     *
     * @Create 2023/4/5 22:32
     * @Version 1.0
     */
    public class TestCustomer {
        
    }

### 7 项目结构如下 ###

![在这里插入图片描述][2edbd7c1e35d4110a1328337f980fb99.png]

## 使用set标签更新记录 ##

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

public int updateCustomerBySet(Customer customer);

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

<update id="updateCustomerBySet" parameterType="customer">
            update t_customer
            <set>
                <if test="username != null and username != ''">
                    username=#{username}
                </if>
                <if test="jobs != null and jobs != ''">
                    jobs=#{jobs}
                </if>
                <if test="phone != null and phone != ''">
                    phone=#{phone}
                </if>
            </set>
            where id = #{id}
        </update>

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

@Test
        public void testUpdateCustomerBySet(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
            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();
        }

[002bc578219545d3a5ce3e5e9536c694.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/adacc4881af84350a1482936c05f3aa0.png
[2edbd7c1e35d4110a1328337f980fb99.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/fcc9cf00fbbd4a0f8910e8003914026f.png