MyBatis

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

一动态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的小尾巴.

留言

您的电子邮箱地址不会被公开。 必填项已用*标注