简介
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>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><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.util
src/main/resources/下创建文件夹
com/biem/mapper
src/test/java下创建package
com.biem.test
5 框架配置文件
5.1 mybatis核心配置文件mybatis-config.xml
<!DOCTYPE configurationPUBLIC "-//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=root5.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.*;/** * ClassName: Customer * Package: com.biem.pojo * Description: * * @Create 2023/4/5 22:17 * @Version 1.0 */@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;/** * ClassName: CustomerMapper * Package: com.biem.mapper * Description: * * @Create 2023/4/5 22:19 * @Version 1.0 */public interface CustomerMapper {}6.3 mybatis用户配置文件
<!DOCTYPE mapperPUBLIC "-//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;/** * 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;//利用静态块在初始化类时实例化sqlSessionFactorystatic {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 语句中判断条件为 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中添加
<!-- public List 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 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 功能测试
在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;/** * ClassName: TestCustomer * Package: com.biem.test * Description: * * @Create 2023/4/5 22:32 * @Version 1.0 */public class TestCustomer {@Testpublic 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);// 关闭SqlSessionsession.close();}@Testpublic 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);// 关闭SqlSessionsession.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=?