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

简介

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

3 pom.xml中添加

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>mybatisartifactId>
        groupId>com.examplegroupId>
        version>1.0-SNAPSHOTversion>
    parent>
    modelVersion>4.0.0modelVersion>

    groupId>com.biemgroupId>
    artifactId>dynamaicSqlartifactId>

    properties>
        maven.compiler.source>8maven.compiler.source>
        maven.compiler.target>8maven.compiler.target>
    properties>

    dependencies>
        
        dependency>
            groupId>org.mybatisgroupId>
            artifactId>mybatisartifactId>
            version>3.4.6version>
        dependency>
        
        dependency>
            groupId>junitgroupId>
            artifactId>junitartifactId>
            version>4.12version>
            scope>testscope>
        dependency>

        
        dependency>
            groupId>mysqlgroupId>
            artifactId>mysql-connector-javaartifactId>
            version>8.0.18version>
            scope>runtimescope>
        dependency>

        
        dependency>
            groupId>log4jgroupId>
            artifactId>log4jartifactId>
            version>1.2.17version>
        dependency>

        dependency>
            groupId>org.projectlombokgroupId>
            artifactId>lombokartifactId>
            version>1.18.16version>
        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

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文件

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用户配置文件

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

项目结构如下

7 标签功能测试

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

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

public ListCustomer> 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);
        ListCustomer> 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);
        ListCustomer> 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);
        ListCustomer> 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);
        ListCustomer> customers = mapper.findCustomerByCondition(customer);

        System.out.println("customers = " + customers);

        // 关闭SqlSession
        session.close();
    }
}