MyBatis 动态SQL之＜where＞标签-

简介

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

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

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

语法

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

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

网络案例

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

where标签-完整案例

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

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

publicList<Customer>findCustomerByIf(Customer customer);publicList<Customer>findCustomerByWhere(Customer customer);

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

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

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{@TestpublicvoidtestFindCustomerByIf(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
        customer.setJobs("teacher");CustomerMapper mapper = session.getMapper(CustomerMapper.class);List<Customer> customers = mapper.findCustomerByIf(customer);System.out.println("customers = "+ customers);// 关闭SqlSession
        session.close();}@TestpublicvoidtestFindCustomerByWhere(){// 通过工具类获取SqlSession对象SqlSession session =MybatisUtil.openSession();// 创建Customer对象，封装需要组合查询的条件Customer customer =newCustomer();
        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,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=?