简介

where 标签主要用来简化 SQL 语句中的条件判断，可以自动处理 AND/OR 条件。
在if标签和choose-when-otherwise标签的案例中，SQL语句加入了一个条件’1=1’，它既保证了where后面的条件成，页避免了where后面出现的第一个词语是and 或者or之类的关键字。
假设把条件‘1=1’去掉，可以出现以下语句

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

上面语句因为出现了where后直接是and，在sql运行时会报语法错误。
这个时候可以使用where标签处理

语法

<where>    <if test="判断条件">        AND/OR ...    if>where>

if 语句中判断条件为 true 时，where 关键字才会加入到组装的 SQL 里面，否则就不加入。where 会检索语句，它会将 where 后的第一个 SQL 条件语句的 AND 或者 OR 关键词去掉。

网络案例

<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标签-完整案例

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

class="lazy" data-src/main/java/下创建package
com.biem.pojo
com.biem.mapper
com.biem.util
class="lazy" data-src/main/resources/下创建文件夹
com/biem/mapper
class="lazy" data-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.Driverjdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTCjdbc.username=rootjdbc.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.*;@Getter@Setter@NoArgsConstructor@AllArgsConstructor@Builder@ToStringpublic class Customer {    private Integer id;    private String username;    private String jobs;    private String phone;}

需要在pom.xml中引入lombok，简化原来的实体类的代码

6.2 mybatis接口类

package com.biem.mapper;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;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);        }    }        public static SqlSession openSession(boolean autoCommit){        return sqlSessionFactory.openSession(autoCommit);    }    public static SqlSession openSession(){        return sqlSessionFactory.openSession();    }        public static void closeSession(SqlSession session){        if(session != null){            session.close();        }    }}

项目结构如下

7 标签功能测试

if 语句中判断条件为 true 时，where 关键字才会加入到组装的 SQL 里面，否则就不加入。where 会检索语句，它会将 where 后的第一个 SQL 条件语句的 AND 或者 OR 关键词去掉。

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中添加

    <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>        <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 功能测试

在class="lazy" data-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;public class TestCustomer {    @Test    public void testFindCustomerByIf(){        // 通过工具类获取SqlSession对象        SqlSession session = MybatisUtil.openSession();        // 创建Customer对象，封装需要组合查询的条件        Customer customer = new Customer();        customer.setJobs("teacher");        CustomerMapper mapper = session.getMapper(CustomerMapper.class);        List<Customer> customers = mapper.findCustomerByIf(customer);        System.out.println("customers = " + customers);        // 关闭SqlSession        session.close();    }    @Test    public void testFindCustomerByWhere(){        // 通过工具类获取SqlSession对象        SqlSession session = MybatisUtil.openSession();        // 创建Customer对象，封装需要组合查询的条件        Customer customer = new Customer();        customer.setJobs("teacher");        CustomerMapper mapper = session.getMapper(CustomerMapper.class);        List<Customer> customers = mapper.findCustomerByWhere(customer);        System.out.println("customers = " + customers);        // 关闭SqlSession        session.close();    }}

结果分析：testFindCustomerByIf在username为null的时候会因为语法错误报错

com.biem.test.TestCustomer,testFindCustomerByIfDEBUG 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=?

来源地址：https://blog.csdn.net/yandao/article/details/129982381