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>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和文件夹
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 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=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.*;@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 功能测试
在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=?
