简介
MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签，如果想要达到…… 的效果，可以借助 、、 来实现。
语法
<choose><whentest="判断条件1">
SQL语句1
</when><whentest="判断条件2">
SQL语句2
</when><whentest="判断条件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
以下示例要求：
- 当网站名称不为空时，只用网站名称作为条件进行模糊查询；
- 当网站名称为空，而网址不为空时，则用网址作为条件进行模糊查询；
- 当网站名称和网址都为空时，则要求网站年龄不为空。
<mappernamespace="net.biancheng.mapper.WebsiteMapper"><selectid="selectWebsite"parameterType="net.biancheng.po.Website"resultType="net.biancheng.po.Website">
SELECT id,name,url,age,country
FROM website WHERE 1=1
<choose><whentest="name != null and name !=''">
AND name LIKE CONCAT('%',#{name},'%')
</when><whentest="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的表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();}}}
项目结构如下
7 标签功能测试
在使用if标签时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际情况下有时只需要从多个选项中选择一个去执行，比如下面的场景：
当查询数据库t_customer时，
客户姓名不为空，则只根据客户名称进行客户筛选，
当客户名称为null的时候，可客户职业不为空，则根据客户职业进行筛选，
当客户名称和客户职业都为空的时候，要求查询电话不为空的客户
7.1 com.biem.mapper.CustomerMapper.class中添加
publicList<Customer>findCustomerByCondition(Customer customer);
7.2 com/biem/mapper/CustomerMapper.xml中添加
<!-- public List<Customer> findCustomerByCondition(Customer customer);--><selectid="findCustomerByCondition"parameterType="customer"resultType="customer">
select * from t_customer where 1=1
<!--条件判断--><choose><whentest="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when><whentest="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，内容如下
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{@TestpublicvoidtestFindAll(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByCondition(customer);System.out.println("customers = "+ customers);// 关闭SqlSession
session.close();}@TestpublicvoidtestFindCustomerByName(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
customer.setUsername("jack");CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByCondition(customer);System.out.println("customers = "+ customers);// 关闭SqlSession
session.close();}@TestpublicvoidtestFindCustomerByJobs(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
customer.setJobs("teacher");CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByCondition(customer);System.out.println("customers = "+ customers);// 关闭SqlSession
session.close();}@TestpublicvoidtestFindCustomerByPhone(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
customer.setPhone("1");CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByCondition(customer);System.out.println("customers = "+ customers);// 关闭SqlSession
session.close();}}
本文转载自: https://blog.csdn.net/yandao/article/details/129979375
版权归原作者 yandao 所有， 如有侵权，请联系我们删除。
版权归原作者 yandao 所有， 如有侵权，请联系我们删除。