MyBatis 動(dòng)態(tài)SQL之where標(biāo)簽的使用

更新時(shí)間：2024年01月24日 15:00:38 作者：yandao

本文主要介紹了MyBatis 動(dòng)態(tài)SQL之where標(biāo)簽,where 標(biāo)簽主要用來簡(jiǎn)化 SQL 語句中的條件判斷,可以自動(dòng)處理 AND/OR 條件,下面就來具體介紹一下

簡(jiǎn)介

where 標(biāo)簽主要用來簡(jiǎn)化 SQL 語句中的條件判斷，可以自動(dòng)處理 AND/OR 條件。
在if標(biāo)簽和choose-when-otherwise標(biāo)簽的案例中，SQL語句加入了一個(gè)條件’1=1’，它既保證了where后面的條件成，頁避免了where后面出現(xiàn)的第一個(gè)詞語是and 或者or之類的關(guān)鍵字。
假設(shè)把條件‘1=1’去掉，可以出現(xiàn)以下語句

select * from t_customer where and username like concat('%','#{username}','%')

上面語句因?yàn)槌霈F(xiàn)了where后直接是and，在sql運(yùn)行時(shí)會(huì)報(bào)語法錯(cuò)誤。
這個(gè)時(shí)候可以使用where標(biāo)簽處理

語法

<where>
    <if test="判斷條件">
        AND/OR ...
    </if>
</where>

if 語句中判斷條件為 true 時(shí)，where 關(guān)鍵字才會(huì)加入到組裝的 SQL 里面，否則就不加入。where 會(huì)檢索語句，它會(huì)將 where 后的第一個(gè) SQL 條件語句的 AND 或者 OR 關(guān)鍵詞去掉。

網(wǎng)絡(luò)案例

<select id="selectWebsite" resultType="net.biancheng.po.Website">
    select id,name,url from website
    <where>
        <if test="name != null">
            AND name like #{name}
        </if>
        <if test="url!= null">
            AND url like #{url}
        </if>
    </where>
</select>

where標(biāo)簽-完整案例

1.數(shù)據(jù)庫準(zhǔn)備

# 創(chuàng)建一個(gè)名稱為t_customer的表
CREATE TABLE t_customer (
    id int(32) PRIMARY KEY AUTO_INCREMENT,
    username varchar(50),
    jobs varchar(50),
    phone varchar(16)
);
# 插入3條數(shù)據(jù)
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.新建項(xiàng)目或Module

在這里插入圖片描述

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>
        <!--1.引入mybatis包-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <!--2.單元測(cè)試-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!--3.mysql驅(qū)動(dòng)-->
        <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.創(chuàng)建package和文件夾

src/main/java/下創(chuàng)建package
com.biem.pojo
com.biem.mapper
com.biem.util
src/main/resources/下創(chuàng)建文件夾
com/biem/mapper
src/test/java下創(chuàng)建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文件-->
    <properties resource="jdbc.properties"></properties>

    <!--將下劃線映射為駝峰-->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--設(shè)置類型別名-->
    <typeAliases>
        <!--
            以包為單位，將包下所有的類型設(shè)置設(shè)置默認(rèn)的類型別名，即類名且不區(qū)分大小寫
        -->
        <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>
        <!--
            以包為單位引入映射文件
            要求：
            1. mapper接口所在的包要和映射文件所在的包一致
            2. mapper接口要和映射文件的名字一致
        -->
        <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 實(shí)體類

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，簡(jiǎn)化原來的實(shí)體類的代碼

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">
    <!-- namespace要和mapper接口的全類名保持一致，例如com.biem.mybatis.mapper.xxxMapper -->

    <!-- sql語句要和接口的方法名保持一致 -->

</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(靜態(tài))屬于類不屬于對(duì)象,且全局唯一
    private static SqlSessionFactory sqlSessionFactory = null;
    //利用靜態(tài)塊在初始化類時(shí)實(shí)例化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 創(chuàng)建一個(gè)新的SqlSession對(duì)象
     * @return SqlSession對(duì)象
     */
    public static SqlSession openSession(boolean autoCommit){
        return sqlSessionFactory.openSession(autoCommit);
    }

    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }
    /**
     * 釋放一個(gè)有效的SqlSession對(duì)象
     * @param session 準(zhǔn)備釋放SqlSession對(duì)象
     */
    public static void closeSession(SqlSession session){
        if(session != null){
            session.close();
        }
    }
}

項(xiàng)目結(jié)構(gòu)如下

在這里插入圖片描述

7 標(biāo)簽功能測(cè)試

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

    public List<Customer> findCustomerByIf(Customer customer);

    public List<Customer> findCustomerByWhere(Customer customer);

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

<!-- public List<Customer> findCustomerByIf(Customer customer);-->
    <select id="findCustomerByIf" parameterType="customer" resultType="customer">
        select * from t_customer where
        <if test="username !=null and username != ''">
            and username like concat('%', #{username}, '%')
        </if>
        <if test="jobs !=null and jobs != ''">
            and jobs=#{jobs}
        </if>
    </select>

    <!-- public List<Customer> findCustomerByWhere(Customer customer);-->
    <select id="findCustomerByWhere" parameterType="customer" resultType="customer">
        select * from t_customer
        <where>
            <if test="username !=null and username != ''">
                and username like concat('%', #{username}, '%')
            </if>
            <if test="jobs !=null and jobs != ''">
                and jobs=#{jobs}
            </if>
        </where>

8 功能測(cè)試

在src/test/java中創(chuàng)建類com.biem.test.TestCustomer.java，內(nèi)容如下

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 testFindCustomerByIf(){
        // 通過工具類獲取SqlSession對(duì)象
        SqlSession session = MybatisUtil.openSession();
        // 創(chuàng)建Customer對(duì)象，封裝需要組合查詢的條件
        Customer customer = new Customer();
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByIf(customer);

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

        // 關(guān)閉SqlSession
        session.close();
    }

    @Test
    public void testFindCustomerByWhere(){
        // 通過工具類獲取SqlSession對(duì)象
        SqlSession session = MybatisUtil.openSession();
        // 創(chuàng)建Customer對(duì)象，封裝需要組合查詢的條件
        Customer customer = new Customer();
        customer.setJobs("teacher");

        CustomerMapper mapper = session.getMapper(CustomerMapper.class);
        List<Customer> customers = mapper.findCustomerByWhere(customer);

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

        // 關(guān)閉SqlSession
        session.close();
    }

}

結(jié)果分析：testFindCustomerByIf在username為null的時(shí)候會(huì)因?yàn)檎Z法錯(cuò)誤報(bào)錯(cuò)

com.biem.test.TestCustomer,testFindCustomerByIf
DEBUG 04-06 09:56:24,100 ==>  Preparing: select * from t_customer where and jobs=?  (BaseJdbcLogger.java:159) 
DEBUG 04-06 09:56:24,152 ==> Parameters: teacher(String) (BaseJdbcLogger.java:159) 

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and jobs='teacher'' at line 4
### The error may exist in com/biem/mapper/CustomerMapper.xml
### The error may involve com.biem.mapper.CustomerMapper.findCustomerByIf-Inline
### The error occurred while setting parameters
### SQL: select * from t_customer where                                 and jobs=?

到此這篇關(guān)于MyBatis 動(dòng)態(tài)SQL之where標(biāo)簽的使用的文章就介紹到這了,更多相關(guān)MyBatis where標(biāo)簽內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: