MyBatis

Mybatis上手教程–动态SQL的if用法示例

2021年10月17日 /

一动态SQL的说明

有些时候，我们希望传入的参数如果不为空的时候，就把该参数放到where条件中去过滤数据。如：

select * from tbl_employee where id>? and email like ?

即，如果参数中没有传入email的话，则SQL变成下述：

select * from tbl_employee where id>?

二动态SQL使用if示例

1创建新的Mapper接口EmployeeMapperDynamicSQL.java
package com.knockatdatabase.dao;
​
import com.knockatdatabase.Employee;
​
import java.util.List;
​
/**
 * @Author:asher
 * @Date:2021/10/17 15:07
 * @Description:com.knockatdatabase.dao
 * @Version:1.0
 */
public interface EmployeeMapperDynamicSQL {
​
    List<Employee> getEmployeeByConditionIf(Employee employee);
}

其中，接口中定义了一个新的方法，参数传递一个Employee，这个示例的重点不是参数，而是条件是动态的。

2创建对应的SQLmapper文件EmployeeMapperDynamicSQL.xml
<?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.knockatdatabase.dao.EmployeeMapperDynamicSQL">
        <select id="getEmployeeByConditionIf" resultType="com.knockatdatabase.Employee">
               select * from tbl_employee where
               <if test="id !=null">
                   id >#{id}
               </if>
               <if test="mail !=null">
                   and email like #{mail}
               </if>
        </select>
</mapper>
​

说明：

  • namespace写对应的mapper 接口的全类名：com.knockatdatabase.dao.EmployeeMapperDynamicSQL
  • select id就写与mapper文件中对应的方法名：getEmployeeByConditionIf
  • resultType写返回集合中元素的类型：com.knockatdatabase.Employee
  • if标签中，写test表达式，其实Mybatis使用的OGNL表达式，具体用法可以参看其Apache官方网站，这里的意思是如果参数中的mail不为空的话，那么SQL条件中，就会拼接and email like #{mail}，同时拼接上id字段的过滤条件
  • 表达式中<id！=NULL>以及<mail != null>，这里的id和mail是实体类中的属性，也就是传入的条件；而下述的id>#{id}和and email like #{mail},这里的id和email是对应的数据库表中的字段名；
3Mybatis配置文件中，引入mapper文件
<mappers>
    <mapper resource="EmployeeMapperDynamicSQL.xml"></mapper>
</mappers>

Mybatis的主配置文件，mybatis-config.xml中的mapper标签处，引入该mapper文件。

4 测试类Java
package com.knockatdatabase;
​
import com.knockatdatabase.dao.EmployeeMapperDynamicSQL;
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 org.junit.Test;
​
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
​
/**
 * @Author:asher
 * @Date:2021/10/17 15:09
 * @Description:com.knockatdatabase
 * @Version:1.0
 */
public class EmployeeDynamicTest {
​
    @Test
    public void testEmployeeByIdDynamicSQl() {
        String resource = "mybatis-config.xml";
        SqlSession sqlSession = null;
        try{
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            sqlSession = sqlSessionFactory.openSession();
            EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> employeeList = mapper.getEmployeeByConditionIf(new Employee(4, "huangwei", "男", null, null));
            System.out.println(employeeList);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
}
//结果：
DEBUG [main] - ==>  Preparing: select * from tbl_employee where id>?
DEBUG [main] - ==> Parameters: 4(Integer)
DEBUG [main] - <==      Total: 6
[Employee{id=5, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=6, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=7, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=8, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=9, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=10, lastName='Huang', gender='男', mail='null', department=null}]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@52e6fdee]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@52e6fdee]
DEBUG [main] - Returned connection 1390869998 to pool.

可以看到，此时真正执行的SQL是：select * from tbl_employee where id>? ，因为Java代码中，mail属性传递的是NULL。当然，如果我们的Java代码中，传入的Employee对象，带有mail属性，即mail不为空的话，则：

List<Employee> employeeList = mapper.getEmployeeByConditionIf(new Employee(7, "huangwei", "男","%163%", null));
​
//结果：
DEBUG [main] - ==>  Preparing: select * from tbl_employee where id>? and email like ?
DEBUG [main] - ==> Parameters: 7(Integer), %163%(String)
DEBUG [main] - <==      Total: 3
[Employee{id=8, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=9, lastName='Huang', gender='男', mail='null', department=null}, Employee{id=10, lastName='Huang', gender='男', mail='null', department=null}]
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@42e99e4a]
DEBUG [main] - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@42e99e4a]
DEBUG [main] - Returned connection 1122606666 to pool.

当然，这里的SQL执行结果中，由于没有指定列别名，或者没有在SQL mapper文件中，通过resultMap映射，所以看到mail和department都为NULL。不过，这些不是我们这里的动态SQL要说的内容。

5 存在问题及解决方案

如果我们传入的参数中，只带有mail作为过滤条件，而id为空的话，则会报错。如：

List<Employee> employeeList = mapper.getEmployeeByConditionIf(new Employee(null, "huangwei", "男","%163%", null));
​
//结果
DEBUG [main] - ==>  Preparing: select * from tbl_employee where and email like ?
DEBUG [main] - ==> Parameters: %163%(String)
rg.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 email like '%163%'' at line 4
### The error may exist in EmployeeMapperDynamicSQL.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select * from tbl_employee where                     and email like ?

很明显，这里出现了错误的SQL语句。解决方法有两种：

方法1：SQL mapper文件中，人为添加where 1=1的条件，并且在每个动态拼接的从句部分，加上and 条件；

<?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.knockatdatabase.dao.EmployeeMapperDynamicSQL">
<select id="getEmployeeByConditionIf" resultType="com.knockatdatabase.Employee">
    select * from tbl_employee where 1=1
    <if test="id !=null">
        and id >#{id}
    </if>
    <if test="mail !=null">
        and email like #{mail}
    </if>
</select>
</mapper>

方法2：SQL mapper文件中，将动态拼接的SQL从句部分，放到where标签中：

<?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.knockatdatabase.dao.EmployeeMapperDynamicSQL">
<select id="getEmployeeByConditionIf" resultType="com.knockatdatabase.Employee">
    select * from tbl_employee
    <where>
    <if test="id !=null">
        id >#{id}
    </if>
    <if test="mail !=null">
        and email like #{mail}
    </if>
    </where>
​
</select>
</mapper>
​

三小结

快速上手并动手实践了动态SQL通过if标签来拼接的用法，同时也给出了2种规避SQL出错的方案。

学习了Mybatis的这个小功能之后，明白了作为DBA的我，多年前帮开发团队执行SQL performance tuning的时候，为什么几乎所有的SQL语句中都带了了where 1=1的小尾巴.

Post Views: 5,075

留言