动态SQL1

2021-12-04 12:35:57

If标签

动态SQL可以说是MyBatis最强大之处了，这块的应用主要有四个方面if,choose,trim和foreach，接下来先说说if。

顾名思义，if是用来判断条件的，现在假设我们有个需求，原先我们查询员工是靠id查询的，现在提出新的要求，要求用户输入携带了哪个字段查询条件就带上这个字段的值，

  1. List<Employee> getEmpsByConditionIf(Employee e);  

那么这个sql语句该怎么写呢？

  1. <select id="EmployeeMapperDynamicTest" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee where id = #{id} and last_name like #{lastName}  
  3.     and email = #{email} and gender=#{gender}  
  4. </select> 

这种写法显然是有问题的，如果传入的Employee中lastName为空，这个select语句就查不出我们想要的结果，还有很多类似的情况，此时，就要使用到if，<if test=""></if>，test属性中采用的是OGNL表达式，与EL表达式和你像，大家可以去官网查找具体的内容[http://commons.apache.org/proper/commons-ognl/download_ognl.cgi]

在书写test属性中的OGNL时要注意遇到特殊符号应该写转移字符，比如&要写成&amp;;

具体的对应关系可以查找W3C的手册:

[https://www.w3cschool.cn/htmltags/ref-entities.html]

  1. <select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee  
  3.     <if test="id!=null">  
  4.         id = #{id}  
  5.     </if>  
  6.     <if test="lastName!=null and lastName!=""">  
  7.         and last_name like #{lastName}  
  8.     </if>  
  9.     <if test="email!=null and email.trim()!=""">  
  10.         and email = #{email}  
  11.     </if>  
  12.     <if test="gender==0 or gender==1">  
  13.         gender=#{gender}  
  14.     </if>  
  15. </select>

注意两点1.email判断时用到了email.trim() 2.gender原本是字符串，可此处将它当做是是数字,OGNL会进行字符串与数字的转化判断.

做一下测试：

  1. public static SqlSessionFactory getSqlSessionFactory() throws IOException {  
  2.     String resource = "mybatis-conf.xml";  
  3.     InputStream inputStream = Resources.getResourceAsStream(resource);  
  4.     
  5.     return new SqlSessionFactoryBuilder().build(inputStream);  
  6. }  
  7.     
  8. @Test  
  9. public void testGetEmpsByConditionIf() throws IOException {  
  10.     SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();  
  11.     SqlSession openSession = sqlSessionFactory.openSession();  
  12.     try {  
  13.         EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);  
  14.         Employee e = new Employee();  
  15.         e.setId(5);  
  16.         e.setLastName("%青%");  
  17.         List<Employee> employeeList = mapper.getEmpsByConditionIf(e);  
  18.         for (Employee employee:employeeList){  
  19.             System.out.println(employee);  
  20.         }  
  21.     }finally {  
  22.         openSession.close();  
  23.     }  
  24. }  

结果：Employee{id=5, lastName='吴青宁', gender='1', email='wq', department=null}

接着，再试试创建的Employee对象，只给lastName一个属性赋值的情况

  1. @Test  
  2. public void testGetEmpsByConditionIf() throws IOException {  
  3.     SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();  
  4.     SqlSession openSession = sqlSessionFactory.openSession();  
  5.     try {  
  6.         EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);  
  7.         Employee e = new Employee();  
  8.         e.setLastName("%青%");  
  9.         List<Employee> employeeList = mapper.getEmpsByConditionIf(e);  
  10.         for (Employee employee:employeeList){  
  11.             System.out.println(employee);  
  12.         }  
  13.     }finally {  
  14.         openSession.close();  
  15.     }  
  16. }  

结果：

报错！！！

分析一下，为什么？如果传入的Employee对象中没有id值，但是有lastName时，SQL语句会变成下面这个样子：

    select * from tb_employee and last_name like #{lastName}

显然是个有问题的查询语句，我们该如何解决这个问题？

我们可以将<select>查询中添加1=1，在之后的每个属性前都加上and XXXXXXX，就像下面这样：

  1. <select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee where 1=1  
  3.     
  4.     <if test="id!=null">  
  5.         and id = #{id}  
  6.     </if>  
  7.     <if test="lastName!=null and lastName!=""">  
  8.         and last_name like #{lastName}  
  9.     </if>  
  10.     <if test="email!=null and email.trim()!=""">  
  11.         and email = #{email}  
  12.     </if>  
  13.     <if test="gender==0 or gender==1">  
  14.         gender=#{gender}  
  15.     </if>  
  16.     
  17. </select>  

这显然没有问题，另外一种方法，MyBatis的<where>标签

where标签

使用方法很简单，将之前的<if>标签都包含在<where></where>之中即可

  1. <select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee  
  3.     <where>  
  4.         <if test="id!=null">  
  5.             and id = #{id}  
  6.         </if>  
  7.         <if test="lastName!=null and lastName!=""">  
  8.             and last_name like #{lastName}  
  9.         </if>  
  10.         <if test="email!=null and email.trim()!=""">  
  11.             and email = #{email}  
  12.         </if>  
  13.         <if test="gender==0 or gender==1">  
  14.             gender=#{gender}  
  15.         </if>  
  16.     </where>  
  17. </select> 
DEBUG [main] - ==>  Preparing: select * from tb_employee WHERE last_name like ?

可以发现<where>能将多余的and或者or去掉，当然where也有不好使的时候，有些人习惯将and放在后面，如下:

  1. <select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee  
  3.     <where>  
  4.         <if test="id!=null">  
  5.             and id = #{id} and  
  6.         </if>  
  7.         <if test="lastName!=null and lastName!=""">  
  8.             last_name like #{lastName} and  
  9.         </if>  
  10.         <if test="email!=null and email.trim()!=""">  
  11.             email = #{email} and  
  12.         </if>  
  13.         <if test="gender==0 or gender==1">  
  14.             gender=#{gender}  
  15.         </if>  
  16.     </where>  
  17. </select>  

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ? and

SQL语句明显出错，可见<where>只能去掉头部的and或者or

trim标签

如果我就想把and 放在后面，要怎么办？这是我们该用<trim>标签了，<trim>可以自定义截取标签

<trim>标签包含4个属性:

  1. 前缀：prefix
  2. 前缀覆盖：prefixOverrides
  3. 后缀：suffix
  4. 后缀覆盖：suffixOverrides

trim标签体中是整个字符串拼串后的结果，prefix给拼串后的整个字符串加一个前缀，比如我们可以不在SQL语句中写where而选择在<trim>中写：<trim prefix="where">……

前缀覆盖指的是去掉拼串前面多余的字符

后缀和后缀覆盖同理，那么我们在后面添加and就有可能出现后面多出and的情况，因此我们只要指定后缀覆盖的属性值为and，就可以处理上述情况：

  1. <select id="getEmpsByConditionTrim" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee  
  3.     <trim prefix="where" suffixOverrides="and">  
  4.         <if test="id!=null">  
  5.              id = #{id} and  
  6.         </if>  
  7.         <if test="lastName!=null and lastName!=""">  
  8.              last_name like #{lastName} and  
  9.         </if>  
  10.         <if test="email!=null and email.trim()!=""">  
  11.              email = #{email} and  
  12.         </if>  
  13.         <if test="gender==0 or gender==1">  
  14.              gender=#{gender}  
  15.         </if>  
  16.     </trim>  
  17. </select>  

DEBUG [main] - ==> Preparing: select * from tb_employee where last_name like ?

虽然看似功能强大的<trim>其实并不常用，大家作为了解即可

choose标签

这是一个分支选择标签，类似于Java中的switch-case，之前我们在<if>标签中是逐一判断哪个条件是合法存在的，然后使用该标签，接下来我们换一种要求，已知传入的Employee对象只会有一种属性是存在值的，那么我们就可以使用choose，当然也可以用if

    List<Employee> getEmpsByConditionChoose(Employee e);
  1. <select id="getEmpsByConditionChoose" resultType="com.figsprite.bean.Employee">  
  2.     select * from tb_employee  
  3.     <where>  
  4.         <choose>  
  5.             <when test="id!=null">  
  6.                 id=#{id}  
  7.             </when>  
  8.             <when test="lastName!=null and lastName!=""">  
  9.                 last_name like #{lastName}  
  10.             </when>  
  11.             <when test="email!=null and email.trim()!=""">  
  12.                 email = #{email}  
  13.             </when>  
  14.             <otherwise>  
  15.                 1=1  
  16.             </otherwise>  
  17.         </choose>  
  18.     </where>  
  19. </select>  

(其中test属性中的""要改成&quot;)

<otherwise>标签，就是当前面所有的when条件都没中的时候，拼接上这个标签里的语句

  1. @Test  
  2. public void testGetEmpsByConditionIf() throws IOException {  
  3.     SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();  
  4.     SqlSession openSession = sqlSessionFactory.openSession();  
  5.     try {  
  6.         EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);  
  7.         Employee e = new Employee();  
  8.         e.setLastName("%青%");  
  9.         List<Employee> employeeList = mapper.getEmpsByConditionChoose(e);  
  10.         for (Employee employee:employeeList){  
  11.             System.out.println(employee);  
  12.         }  
  13.     }finally {  
  14.         openSession.close();  
  15.     }  
  16. } 

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ?

如果传入的对象同时包含id和lastName的话会查谁？显然是只会用id作为条件

上一篇：在Nginx上部署ThinkPHP,解决Pathinfo问题


下一篇：雷林鹏分享：jQuery EasyUI 表单 - 创建异步提交表单