mybatis choose when 多条件_Mybatis—动态SQL详解

前言

今天我们来聊聊Mybatis的动态SQL的使用，动态SQL可以说是mybatis的核心，可以对SQL语句进行灵活操作，通过表达式进行判断，对SQL进行灵活拼接，组装。在实际项目开发中，我们还可以将在业务层处理的逻辑转移到SQL中进行处理，因为SQL语句通常会比程序代码执行的要快，毕竟数据库是专门做数据处理和计算的嘛。

我们以 student_score 表为例来说明：

if+where 标签

SQL语句中 < if > 标签和代码中的 if 语句作用是一样的，都是条件判断。加上 if 标签的SQL语句会自动的去判断传入的条件是否为空。

1、< if >标签的使用

	//会自动去判断传入的 name 和 math 是否为空，	//如果name为空则SQL语句中不会拼接 name=？查询语句	//如果math为空则SQL语句中不会拼接 math=?查询语句             SELECT         sc.id,sc.name         FROM         student_score sc         WHERE 1=1                    sc.name=#{name}                            sc.math=#{math}                 ORDER BY         sc.math DESC     

上述SQL完成的查询：

1. 当name不为空，math为空时，根据name去查询
2. 当math不为空，name为空时，根据math去查询
3. 当name和math都不为空，同时根据name和math去查询
4. 当name和math都为空，无条件查询表中数据。where后加 1=1 恒等式是为了避免当查询条件全都为空时报错。

2、< if > + < where >标签的使用

             SELECT         sc.id,sc.name         FROM         student_score sc                                    sc.name=#{name}                                       and sc.math=#{math}                             ORDER BY         sc.math DESC     

注意：

1. < where > 标签会判断如果它包含的标签中有返回值的话，它就插入一个  where
2. 如果标签返回的内容是以AND 或OR开头的，它会自动剔除掉。比如：当 name 为空并且 math 不为空时，此时标签返回的内容是以 and 开头的，这是 where 标签会自动把 and 剔除掉。
3. 如果使用了 < where > 标签，当条件都为空时，此时SQL语句的作用是没有条件查询表中数据。(此时不加1=1恒等式也不会报错)

SQL 片段

在Mapper文件中可以定义SQL片段，有时候某个SQL语句(或某些查询字段)可能用的很多，就可以把这块SQL语句抽离出来写成SQL片段。定义后的SQL语句可以引用该SQL片段，简化代码，从而使代码得到复用。

1、重复使用的字段

     (id,`name`,math,english)           insert into        student_score                values                    (#{list.id},#{list.name},#{list.math},#{list.english})         

2、重复使用的SQL语句

                   and math=#{math}                        and name=#{name}           	           SELECT        *        FROM        student_score sc        WHERE 1=1                ORDER BY        sc.math DESC    

注意：

1. where后加 1=1 恒等式是为了避免当查询条件全都为空时报错。
2. 最好基于单表来定义SQL片段，提高片段的可重用性。
3. 在SQL片段中最好不要包括where

foreach 标签

我们可以使用 foreach 标签 来做循环操作，比如批量插入，批量更新等。当向 SQL 传递数组，List 或 Map 时可以使用 foreach 解析。

先来看下foreach标签中的几个属性：

collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串

1、foreach 标签的使用

需求：根据list集合中的id和English去更新表中name和math字段的值，实现批量更新。Dao层代码：

Boolean updateByEntity(@Param("studentEntities") List studentEntities);

Mapper：

   //对list集合做批量更新操作   //根据list中的id和English去更新表中name和math字段                      UPDATE student_score sc            SET sc.NAME = #{entity.name},            sc.math = #{entity.math}            WHERE            sc.id = #{entity.id} and sc.english=#{entity.english}            

注意：数据库链接URL地址要加上 &allowMultiQueries=true ，默认不支持同时执行多条语句。打印出来的SQL语句：

2、foreach标签结合 case when的使用

需求：根据list集合中的id去更新表中name和math字段的值，实现批量更新。Dao层代码：

Boolean updateByEntity(@Param("studentEntities") List studentEntities);

Mapper：

             update student_score sc         set name=                      when #{entity.id} then #{entity.name}                  math=                      when #{entity.id} then #{entity.math}                  where id in                      #{entity.id}              

此时数据库链接URL地址不需要加 &allowMultiQueries=true 同时执行多条语句的配置，因为这种写法是在一条SQL中完成的更新操作。

打印出来的SQL语句：

choose(when ,otherwise)标签

有时候我们不想用到所有的查询条件，只想选择其中一个，查询条件有一个满足即可，使用choose标签可以解决这种情况，类似java的switch语句。

           SELECT        *        FROM        student_score sc                                                        sc.name=#{name}                                                    and sc.math=#{math}                                                    and id=#{id}                                            ORDER BY        sc.math DESC    

虽然SQL中写了三个查询条件，但是只能选择一个作为查询条件

1. 如果name不为空，查询语句就是根据name条件进行查询
2. 如果name为空，那么看math是否为空，如果不为空，那么查询语句就是根据math条件去进行查询
3. 如果name为空，math也为空，那么查询条件为根据id去进行查询
4. 如果name为空，math不为空，id也不为空，此时查询语句是根据math去进行查询， id 的查询条件并不会一起查询。只能选择一个作为查询条件。

case when + forEach 实现多条件多值批量更新

1、单个条件

           update mydata_table                                                                                   when id=#{item.id} then #{item.status}                                                                                      where id in                    #{item.id,jdbcType=BIGINT}            

2、多个条件

     update demo_table            status=                    when field2=#{item.field2} and company_id=#{item.field3} then #{item.status}                create_time =                  when field2=#{item.field2} and company_id=#{item.field3} then                                    #{item.createTime}                        now()                          WHERE          device_num=#{item.field2} and company_id=#{item.field3}      

注意：foreach标签如果放在一条SQL外边的执行要比在一条SQL中写foreach然后根据条件循环更新的效率要低，数据量大的时候特别明显，建议foreach标签的使用写在一条SQL语句的中间