Mybatis的动态SQL是基于OGNL【对象导航图语言(Object Graph Navigation Language)】表达式的,Mybatis中用于实现动态SQL的元素主要包括 if、choose(when, otherwise)、trim、where、set、foreach等。
<if>元素
<if>用于条件判断,可实现简单的条件选择。
<select id="findUserInfoByName" parameterType="UserInfo" resultType="UserInfo">
select * from sys_user
<if test="userName != null and userName != '' ">
where userName LIKE CONCAT(CONCAT('%',#{userName}),'%')
</if>
</select>
<if>会对userName进行判空,如果条件成立(即userName非空),则将where语句拼接在select中,否则忽略where子句。
有参数输出日志:
2020-02-02 16:54:38 DEBUG findUserInfoById:143 - ==> Preparing: select * from sys_user where userName LIKE CONCAT(CONCAT('%',?),'%')
2020-02-02 16:54:38 DEBUG findUserInfoById:143 - ==> Parameters: d(String)
2020-02-02 16:54:38 DEBUG findUserInfoById:143 - <== Total: 2
无参数输出日志:
2020-02-02 16:56:40 DEBUG findUserInfoById:143 - ==> Preparing: select * from sys_user
2020-02-02 16:56:40 DEBUG findUserInfoById:143 - ==> Parameters:
2020-02-02 16:56:40 DEBUG findUserInfoById:143 - <== Total: 4
<where>、<if>元素
当<if>元素较多时,可能会拼接出 where and 或者 where or 的错误语句。使用<where>时,只有符合<where>内的条件时,才会在语句上加上where 关键字。如果出现where and 或者 where or 时,<where>会自动将多余的and 或者 or 移除。
<select id="findUserInfoByName" parameterType="UserInfo" resultType="UserInfo">
select * from sys_user
<where>
<if test="userName != null and userName != '' ">
username LIKE CONCAT(CONCAT('%',#{userName}),'%')
</if>
<if test="id > 0">
and id = #{id}
</if>
</where>
</select>
有参输出日志:
2020-02-02 17:16:58 DEBUG findUserInfoByName:143 - ==> Preparing: select * from sys_user WHERE username LIKE CONCAT(CONCAT('%',?),'%') and id = ?
2020-02-02 17:16:58 DEBUG findUserInfoByName:143 - ==> Parameters: d(String), 3(Integer)
2020-02-02 17:16:58 DEBUG findUserInfoByName:143 - <== Total: 1
无参输出日志:
1.
2020-02-02 17:20:24 DEBUG findUserInfoByName:143 - ==> Preparing: select * from sys_user WHERE id = ?
2020-02-02 17:20:24 DEBUG findUserInfoByName:143 - ==> Parameters: 3(Integer)
2020-02-02 17:20:24 DEBUG findUserInfoByName:143 - <== Total: 1
2
2020-02-02 17:22:55 DEBUG findUserInfoByName:143 - ==> Preparing: select * from sys_user WHERE username LIKE CONCAT(CONCAT('%',?),'%')
2020-02-02 17:22:55 DEBUG findUserInfoByName:143 - ==> Parameters: d(String)
2020-02-02 17:22:55 DEBUG findUserInfoByName:143 - <== Total: 2
3
2020-02-02 17:22:10 DEBUG findUserInfoByName:143 - ==> Preparing: select * from sys_user
2020-02-02 17:22:10 DEBUG findUserInfoByName:143 - ==> Parameters:
2020-02-02 17:22:10 DEBUG findUserInfoByName:143 - <== Total: 4
<set>、<if>元素
<set>和<if>用来组装update语句,只有当<set>内的条件成立时,才会在sql语句上加上set关键字。<set>内包含<if>子元素,<if>包含的sql语句通常最后面有一个逗号,<set>元素会自动移除多余的逗号。
<update id="updateUser" parameterType="UserInfo">
update sys_user
<set>
<if test="userName != null and userName != '' ">
username = #{userName},
</if>
<if test="password != null and password != '' ">
password = #{password}
</if>
where id = #{id}
</set>
</update>
有参输出日志:
2020-02-02 17:35:33 DEBUG updateUser:143 - ==> Preparing: update sys_user SET username = ?, password = ? where id = ?
2020-02-02 17:35:33 DEBUG updateUser:143 - ==> Parameters: qwert(String), 666666(String), 4(Integer)
2020-02-02 17:35:33 DEBUG updateUser:143 - <== Updates: 1
无参输出日志:
2020-02-02 17:57:30 DEBUG updateUser:143 - ==> Preparing: update sys_user SET username = ? where id = ?
2020-02-02 17:57:30 DEBUG updateUser:143 - ==> Parameters: aaaaaa(String), 4(Integer)
2020-02-02 17:57:30 DEBUG updateUser:143 - <== Updates: 1
<trim>元素
<trim>元素的属性有,prefix、prefixOverrides、suffix、suffixOverrides。
属性 | 作用 |
---|---|
prefix | 在拼装的SQL语句片段前加上前缀 |
suffix | 在拼装的SQL语句片段前加上后缀 |
prefixOverrides | 把要拼装的SQL语句片段首部的某些内容覆盖 |
suffixOverrides | 把要拼装的SQL语句片段尾部的某些内容覆盖 |
<trim>可用来替代<where>和<set>实现同样的功能。
- 改写<where>元素的sql:
<select id="findUserInfoByName" parameterType="UserInfo" resultType="UserInfo">
select * from sys_user
<trim prefix="where" prefixOverrides="and|or">
<if test="userName != null and userName != '' ">
username LIKE CONCAT(CONCAT('%',#{userName}),'%')
</if>
<if test="id > 0">
and id = #{id}
</if>
</trim>
</select>
- 改写<set>元素的sql:
<update id="updateUser" parameterType="UserInfo">
update sys_user
<trim prefix="set" suffixOverrides=",">
<if test="userName != null and userName != '' ">
username = #{userName},
</if>
<if test="password != null and password != '' ">
password = #{password},
</if>
</trim>
where id = #{id}
</update>
<choose>、<when>、<otherwise>元素
<choose>、<when>、<otherwise>元素类似于if - else if - else 语法。<choose>元素会按顺序判断<when>元素中的条件是否成立,如果有一个成立,则不再判断后面的<when>,<choose>元素执行结束;如果所有<when>都不成立,则执行<otherwise>元素中的sql语句。
<select id="findUserInfoByName" parameterType="UserInfo" resultType="UserInfo">
select * from sys_user
<where>
<choose>
<when test="userName != null and userName != '' ">
username LIKE CONCAT(CONCAT('%',#{userName}),'%')
</when>
<when test="id > 0">
and id = #{id}
</when>
<otherwise></otherwise>
</choose>
</where>
</select>
<foreach>元素
<foreach>元素主要是迭代一个集合,在SQL语句中通常在in 关键字后面。
<foreach>元素可以向SQL语句传递数组,List<E>等实例。List<E>实例使用list作为键,数组使用array作为键。
<foreach>实例的主要属性:
属性 | 作用 |
---|---|
item | 表示集合中每个元素迭代时的别名 |
index | 指定一个变量名称,表示每次迭代到的位置 |
open | 表示该语句的开始符号 |
separator | 表示每次迭代之间的分隔符号 |
close | 表示该语句的结束符号 |
例:查询id为1 和 3 的用户
- 使用List<E>实例
<select id="findUser" resultType="UserInfo">
select * from sys_user where id in
<foreach collection="list" item="ids" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>
结果日志:
2020-02-02 18:35:52 DEBUG findUser:143 - ==> Preparing: select * from sys_user where id in ( ? , ? )
2020-02-02 18:35:52 DEBUG findUser:143 - ==> Parameters: 1(Integer), 3(Integer)
2020-02-02 18:35:52 DEBUG findUser:143 - <== Total: 2
- 使用数组实例:
<select id="findUser" resultType="UserInfo">
select * from sys_user where id in
<foreach collection="array" item="ids" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>
结果日志:
2020-02-02 18:44:05 DEBUG findUser:143 - ==> Preparing: select * from sys_user where id in ( ? , ? )
2020-02-02 18:44:05 DEBUG findUser:143 - ==> Parameters: 1(Integer), 3(Integer)
2020-02-02 18:44:05 DEBUG findUser:143 - <== Total: 2