目录
动态sql简介
动态 SQL 是 MyBatis 的强大特性之一。根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
OGNL 表达式
官方文档http://commons.apache.org/proper/commons-ognl/language-guide.html
OGNL对象图导航语言,这是一种强大的表达式语言,通过它可以非常方便的来操作对象属性,类似于EL,SPEL
mybatis的动态sql也是借助于OGNL表达式
常用方法
访问对象属性:person.name
调用方法:person.getName()
调用静态属性/方法:@java.lang.Math@PI
@java.util.UUID@randomUUID()
调用构造方法:new com.edu.bean.Person('czs').name
运算符:+ - * / %
逻辑运算符: in,not in,>,>=…
if-判断
使用场景:判断参数有值就进行动态拼接
原生sql解决sql拼装问题
查询的时候如果某些条件没带可能sql拼装会有问题,使用where 1=1
,然后条件都放在if标签里面 通过and … 动态拼接
<select id="getEmpsByConditionIf" resultType="com.edu.mybatis.bean.Employee">
SELECT *
FROM tbl_employee
where 1=1
<if test="id!=null">
AND id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
AND last_name LIKE #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
AND email = #{email}
</if>
<if test="gender==0 or gender==1">
AND gender = #{gender}
</if>
</select>
where自动截取规则
使用where标签将所有的动态查询条件包括在内,mybatis会将多出来的and和where去掉,where只会去掉第一个多出来的and或者or,因此动态拼接的时候,需要把and等修饰sql放在前面
<select id="getEmpsByConditionIf" resultType="com.edu.mybatis.bean.Employee">
SELECT *
FROM tbl_employee
<where>
<if test="id!=null">
id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
AND last_name LIKE #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
AND email = #{email}
</if>
<if test="gender==0 or gender==1">
AND gender = #{gender}
</if>
</where>
</select>
自定义字符串截取规则
- prefix 前缀:trim标签体中是整个拼串后的结果 prefix给拼串后的整个字符串加一个前缀
- prefixOverrides 前缀覆盖: 去掉字符串前面多余的字符
- suffix 后缀
- suffixOverrides 后缀覆盖: 去掉字符串后面多余的字符
<select id="getEmpsByConditionIf" resultType="com.edu.mybatis.bean.Employee">
SELECT *
FROM tbl_employee
<trim prefix="where" prefixOverrides="AND" suffix="" suffixOverrides="AND">
<if test="id!=null">
id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
AND last_name LIKE #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
AND email = #{email}
</if>
<if test="gender==0 or gender==1">
AND gender = #{gender}
</if>
</trim>
</select>
set与if结合的动态更新
原生sql,mybatis会自己带上,
set自动检查,分割符,去除多余
<update id="updateEmp">
UPDATE tbl_employee
<set>
<if test="lastName!=null">
last_name = #{lastName},
</if>
<if test="email!=null">
email = #{email},
</if>
<if test="gender==0 or gender==1">
gender = #{gender}
</if>
</set>
WHERE
id = #{id}
</update>
trim更新拼串
<update id="updateEmp">
UPDATE tbl_employee
<trim prefix="set" suffixOverrides=",">
<if test="lastName!=null">
last_name = #{lastName}
</if>
<if test="email!=null">
email = #{email},
</if>
<if test="gender==0 or gender==1">
gender = #{gender}
</if>
</trim>
WHERE
id = #{id}
</update>
choose(when、otherwise)-分支选择
类似switch
例子:如果带了id用id查询,如果带了lastName用lastName查询,如果带了email用email查询,如果都没带就查gender为1的数据
进入的when只会有一个,如果id和lastName都带,只会进入id,也就是会先进入前面的when语句
<select id="getEmpsByConditionChoose" resultType="com.edu.mybatis.bean.Employee">
SELECT * FROM tbl_employee
<where>
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="lastName!=null">
last_name LIKE #{lastName}
</when>
<when test="email">
email = #{email}
</when>
<otherwise>
gender=1
</otherwise>
</choose>
</where>
</select>
foreach-遍历集合
遍历集合
collection: 指定要遍历的集合
item: 将当前遍历出的元素赋值给指定的变量
#{} 就能取出当前遍历的元素
separator: 每个元素之间的分隔符
open: 遍历出所有结果前拼接一个字符
close: 遍历出所有结果后拼接一个字符
index: 索引
-
遍历list的时候 index是索引 item是当前值
-
遍历map的时候 index表示的是map的key item是map的值
public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);
<select id="getEmpsByConditionForeach" resultType="com.edu.mybatis.bean.Employee">
SELECT * FROM tbl_employee WHERE id IN
<foreach collection="ids" item="item_id" separator="," open="(" close=")" index="">
#{item_id}
</foreach>
</select>
批量保存
方法一:,mysql支持values(),(),()保存
public void addEmps(@Param("emps") List<Employee> emps);
<insert id="addEmps">
INSERT INTO `mybatis`.`tbl_employee` (`last_name`, `gender`, `email`, `d_id`)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>
方式二:mysql以;结束表示执行一条sql,采用循环保存
allowMultiQueries
在一条语句中,允许使用;
来分割多条查询 默认为false
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
INSERT INTO `mybatis`.`tbl_employee` (`last_name`, `gender`, `email`, `d_id`)
VALUES
(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>
databaseId和parameter-内置参数
parameter
:代表整个参数
- 单个参数:
_parameter
就是这个参数 - 多个参数: 参数会被封装为一个map,
_parameter
就是代表这个map
_databaseId
:如果配置了databaseIdProvider标签,_databaseIdProvider
就是代表当前数据库的别名
<!--为不同的数据库厂商取别名-->
<databaseIdProvider type="DB_VENDOR">
<property name="MYSQL" value="mysql"/>
<property name="SQL Server" value="sqlserver"/>
<property name="DB2" value="db2"/>
<property name="Oracle" value="oracle"/>
</databaseIdProvider>
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
<if test="_parameter!=null">
select seq_users.nextval from dual
</if>
</if>
<if test="_databaseId == 'db2'">
<if test="_parameter.id!=null">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
bind-绑定
可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值
LIKE %#{pattern}%
会报错LIKE %${pattern}%
不安全,这个时候就可以用bind
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
抽取可重用的sql片段
使用sql标签定义可重用的片段
使用include标签引用可重用片段
<sql id="insertColumn">
`last_name`
, `gender`, `email`, `d_id`
</sql>
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
INSERT INTO `mybatis`.`tbl_employee` (<include refid="insertColumn"></include>)
VALUES
(#{emp.lastName},#{emp.gender},#{emp.email},#{emp.dept.id})
</foreach>
</insert>