前言
今天我们来聊聊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完成的查询:
- 当name不为空,math为空时,根据name去查询
- 当math不为空,name为空时,根据math去查询
- 当name和math都不为空,同时根据name和math去查询
- 当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
注意:
- < where > 标签会判断如果它包含的标签中有返回值的话,它就插入一个 where
- 如果标签返回的内容是以AND 或OR开头的,它会自动剔除掉。比如:当 name 为空并且 math 不为空时,此时标签返回的内容是以 and 开头的,这是 where 标签会自动把 and 剔除掉。
- 如果使用了 < 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
注意:
- where后加 1=1 恒等式是为了避免当查询条件全都为空时报错。
- 最好基于单表来定义SQL片段,提高片段的可重用性。
- 在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中写了三个查询条件,但是只能选择一个作为查询条件
- 如果name不为空,查询语句就是根据name条件进行查询
- 如果name为空,那么看math是否为空,如果不为空,那么查询语句就是根据math条件去进行查询
- 如果name为空,math也为空,那么查询条件为根据id去进行查询
- 如果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语句的中间