MyBatis 动态SQL之＜if＞标签-

谁践踏了优雅 2024-03-24 15:06 69阅读 0赞

## if标签简介 ##

if标签：条件判断  
MyBatis if 类似于 Java 中的 if 语句，是 MyBatis 中最常用的判断语句。使用 if 标签可以节省许多拼接 SQL 的工作，把精力集中在 XML 的维护上。  
if 语句使用方法简单，常常与 test 属性联合使用。语法如下:

## if标签语法结构 ##

<if test="判断条件">
        SQL语句
    </if>

## if标签使用场景 ##

最常见的场景是在 if 语句中包含 where 子句

<select id="selectAllWebsite" resultMap="myResult">
        select id,name,url from website
        <if test="name != null">
            where name like #{name}
        </if>
    </select>

以上代表表示根据网站名称去查找相应的网站信息，但是网站名称是一个可填可不填的条件，不填写的时候不作为查询条件。

可多个 if 语句同时使用。以下语句表示为可以按照网站名称（name）或者网址（url）进行模糊查询。如果您不输入名称或网址，则返回所有的网站记录。但是，如果你传递了任意一个参数，它就会返回与给定参数相匹配的记录：

<select id="selectAllWebsite" resultMap="myResult">
        select id,name,url from website where 1=1
        <if test="name != null">
            AND name like #{name}
        </if>
        <if test="url!= null">
            AND url like #{url}
        </if>
    </select>

DROP TABLE IF EXISTS `website`;
    CREATE TABLE `website` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
      `url` varchar(30) COLLATE utf8_unicode_ci DEFAULT '',
      `age` tinyint(3) unsigned NOT NULL,
      `country` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
      `createtime` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

## if标签案例 ##

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

use mybatis;
    # 创建一个名称为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 ###

![在这里插入图片描述][77844734fd6c45c4a1c8f2aafb889acd.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>if</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.utils  
src/main/resources/下创建文件夹  
com/biem/mapper  
![在这里插入图片描述][bd0658b65889464998ea669db7d00e4a.png]

### 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.utils;
    
    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标签功能测试 ###

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

public List<Customer> findCustomerByNameAndJobs(Customer customer);

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

<select id="findCustomerByNameAndJobs" parameterType="customer" resultType="customer">
            select * from t_customer where 1=1
            <if test="username !=null and username != ''">
                and username like concat('%', #{username}, '%')
            </if>
            <if test="jobs !=null and jobs != ''">
                and jobs=#{jobs}
            </if>
        </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 org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    import com.biem.utils.MybatisUtil;
    
    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.findCustomerByNameAndJobs(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    
        @Test
        public void testFindCustomerByNameAndJobs(){
        
            // 通过工具类获取SqlSession对象
            SqlSession session = MybatisUtil.openSession();
            // 创建Customer对象，封装需要组合查询的条件
            Customer customer = new Customer();
            customer.setUsername("jack");
            customer.setJobs("teacher");
    
            CustomerMapper mapper = session.getMapper(CustomerMapper.class);
            List<Customer> customers = mapper.findCustomerByNameAndJobs(customer);
    
            System.out.println("customers = " + customers);
    
            // 关闭SqlSession
            session.close();
        }
    }

[77844734fd6c45c4a1c8f2aafb889acd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/8ab8974cabd04b9a9b9606c77b1fedd1.png
[bd0658b65889464998ea669db7d00e4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d6921458930d488ab3f6e6e68a8173c1.png