MyBatis 动态SQL之＜choose＞＜when＞＜otherwise＞标签-

Bertha 。 2024-03-24 15:11 13阅读 0赞

## 简介 ##

MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签，如果想要达到…… 的效果，可以借助 、、 来实现。

## 语法 ##

<choose>
        <when test="判断条件1">
            SQL语句1
        </when >
        <when test="判断条件2">
            SQL语句2
        </when >
        <when test="判断条件3">
            SQL语句3
        </when >
        <otherwise>
            SQL语句4
        </otherwise>
    </choose>

hoose 标签按顺序判断其内部 when 标签中的判断条件是否成立，如果有一个成立，则执行相应的 SQL 语句，choose 执行结束；如果都不成立，则执行 otherwise 中的 SQL 语句。这类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

## 场景示例 ##

MyBatis 就会根据参数的设置进行判断来动态组装 SQL  
以下示例要求：

*  当网站名称不为空时，只用网站名称作为条件进行模糊查询；
 *  当网站名称为空，而网址不为空时，则用网址作为条件进行模糊查询；
 *  当网站名称和网址都为空时，则要求网站年龄不为空。

<mapper namespace="net.biancheng.mapper.WebsiteMapper">
        <select id="selectWebsite"
            parameterType="net.biancheng.po.Website"
            resultType="net.biancheng.po.Website">
            SELECT id,name,url,age,country
            FROM website WHERE 1=1
            <choose>
                <when test="name != null and name !=''">
                    AND name LIKE CONCAT('%',#{name},'%')
                </when>
                <when test="url != null and url !=''">
                    AND url LIKE CONCAT('%',#{url},'%')
                </when>
                <otherwise>
                    AND age is not null
                </otherwise>
            </choose>
        </select>
    </mapper>

## choose-when-otherwise标签-完整案例 ##

### 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();
            }
        }
    }

项目结构如下  
![在这里插入图片描述][2edbd7c1e35d4110a1328337f980fb99.png]

### 7 标签功能测试 ###

在使用if标签时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际情况下有时只需要从多个选项中选择一个去执行，比如下面的场景：  
当查询数据库t\_customer时，  
客户姓名不为空，则只根据客户名称进行客户筛选，  
当客户名称为null的时候，可客户职业不为空，则根据客户职业进行筛选，  
当客户名称和客户职业都为空的时候，要求查询电话不为空的客户

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

public List<Customer> findCustomerByCondition(Customer customer);

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

<select id="findCustomerByCondition" parameterType="customer" resultType="customer">
            select * from t_customer where 1=1
            
            <choose>
                <when test="username!=null and username!=''">
                    and username like concat('%',#{username},'%')
                </when>
                <when test="jobs!=null and jobs!=''">
                    and jobs=#{jobs}
                </when>
                <otherwise>
                    and phone is not null
                </otherwise>
            </choose>
        </select>

### 8 功能测试 ###

在src/test/java中创建类com.biem.test.TestCustomer.java，内容如下

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 {
        
    
        @Test
        public void testFindAll(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
    
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> customers = mapper.findCustomerByCondition(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    
        @Test
        public void testFindCustomerByName(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
            customer.setUsername("jack");
    
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> customers = mapper.findCustomerByCondition(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    
        @Test
        public void testFindCustomerByJobs(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
            customer.setJobs("teacher");
    
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> customers = mapper.findCustomerByCondition(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    
        @Test
        public void testFindCustomerByPhone(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
            customer.setPhone("1");
    
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> customers = mapper.findCustomerByCondition(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    }

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