动态sql
- 动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
- 使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
- 如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
sql片段
这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。 参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值。
比如：我们将经常查询的列，设置成sql片段， 通过include标签引用他就行了
<sql id="base_column">
id, username, email, addr, cre_time
</sql>
<select id="findById" parameterType="Integer" resultType="User">
select <include refid="base_column"/> from t_user where id = #{id}
</select>
有时候，当我们做连接查询，一般都会给各个表取个别名，防止等下列名冲突，那我们sql片段就可以这样设置：通过inclue标签的元素property，为变量alias赋值
<sql id="base_column">
${alias}.id, ${alias}.username, ${alias}.email, ${alias}.addr, ${alias}.mobile, ${alias}.status
</sql>
<select id="findById" parameterType="Integer" resultType="User">
select
<include refid="base_column">
<property name="alias" value="t"></property>
</include>
from t_user t where t.id = #{id}
</select>
查询sql: select t.id, t.username, t.email, t.addr, t.mobile, t.status from t_user t where t.id = ?
最后, 也可以在 include 元素的 refid 属性或内部语句中使用属性值，例如：
<sql id="sometable">
${prefix}Table
</sql>
<sql id="someinclude">
from
<include refid="${include_target}"/>
</sql>
<select id="select" resultType="map">
select
field1, field2, field3
<include refid="someinclude">
<property name="prefix" value="Some"/>
<property name="include_target" value="sometable"/>
</include>
</select>
if
if语句跟java代码中的if类似，主要用于对参数进行判断，当满足test表达式时，才加入查询条件，一般会配合where进行使用
/**
* 根据用户名和手机号模糊查询
*
* @param username 用户名
* @param mobile
* @return list
*/
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile);
<select id="getUserList" resultType="User">
select * from t_user t
where 1 = 1
<if test="username != null and username != ''">
and instr(t.username, #{username}) > 0
</if>
<if test="mobile != null and mobile != ''">
and instr(t.mobile, #{mobile}) > 0
</if>
</select>
where
上面的if，我们在where后面加了1=1， 是为了防止语句错误，当if条件都不满足时，或者只匹配第二个if， sql是这样的:
select * from t_user t where,select * from t_user t where and instr(t.mobile, #{mobile}) > 0
where元素就是为了解决这样的问题的: where元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
/**
* 根据用户名和手机号模糊查询
*
* @param username 用户名
* @param mobile
* @return list
*/
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile);
<select id="getUserList" resultType="User">
select * from t_user t
<where>
<if test="username != null and username != ''">
instr(t.username, #{username}) > 0
</if>
<if test="mobile != null and mobile != ''">
and instr(t.mobile, #{mobile}) > 0
</if>
</where>
</select>
但是注意一点，删除语句尽量不要使用,比如下面例子: 当条件不满足时，直接删表了
<delete id="deleteById" parameterType="Integer">
delete from t_user
<where>
<if test="id != null">
and id = #{id}
</if>
</where>
</delete>
正常语句就行了: 当id为空时， delete from t_user where id = null， 一般我们数据库也不会让主键为空的
<delete id="deleteById" parameterType="Integer">
delete from t_user where id = #{id}
</delete
trim
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
- prefix: 指定插入的内容
- prefixOverrides 属性会忽略通过管道符分隔的文本序列，比如前缀多余的and或者or
- suffixOverrides属性会过滤多余的后缀， 比如更新语句时多余的逗号
//等价于where元素
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
//等价于set元素
<trim prefix="SET" suffixOverrides=",">
...
</trim>
<select id="getUserList" resultType="User">
select * from t_user t
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="username != null and username != ''">
instr(t.username, #{username}) > 0
</if>
<if test="mobile != null and mobile != ''">
and instr(t.mobile, #{mobile}) > 0
</if>
</trim>
</select>
<update id="updateById" parameterType="User">
UPDATE t_user
<trim prefix="SET" suffixOverrides=",">
<if test="username != null">
username = #{username},
</if>
<if test="mobile != null">
mobile = #{mobile},
</if>
</trim>
WHERE id = #{id}
</update>
choose、when、otherwise
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
/**
* 获取用户集合
*
* @param username 用户名
* @param mobile
* @param userType 用户类型
* @return list
*/
List<User> getUserList(@Param("username") String username, @Param("mobile") String mobile,
@Param("userType") String userType);
<select id="getUserList" resultType="User">
select * from t_user t
<where>
<if test="username != null and username != ''">
instr(t.username, #{username}) > 0
</if>
<if test="mobile != null and mobile != ''">
and instr(t.mobile, #{mobile}) > 0
</if>
<choose>
<when test="userType != null and userType == 'superAdmin'">
and t.status = 1
</when>
<when test="userType != null and userType == 'admin'">
and t.status = 2
</when>
<otherwise>
and t.status = 3
</otherwise>
</choose>
</where>
</select>
set
用于更新语句， 主要是用于去除后缀的逗号
int updateById(User user);
<update id="updateById">
update t_user
<set>
<if test="username != null">username=#{username},</if>
<if test="email != null">email=#{email},</if>
<if test="addr != null">addr=#{addr},</if>
<if test="mobile != null">mobile=#{mobile}</if>
</set>
where id = #{id}
</update>
foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候), 可以支持数组、List、Set接口。
- collection: 参数名称，这里注意一下，要用
@Param定义一下，不然会报错：Parameter 'ids' not found. Available parameters are [collection, list] - item: 循环中当前的元素
- index: 当前元素在集合的位置下标。
- open和 close配置的是以什么符号将这些集合元素包装起来
- separator是各个元素的间隔符。
批量查询
/**
* 根据主键批量查询
* @param ids 主键集合
* @return list
*/
List<User> getUserListByIds(@Param("ids") List<Integer> ids);
<sql id="base_column">
id, username, email, addr, mobile, status
</sql>
<select id="getUserListByIds" resultType="User">
select <include refid="base_column" /> from t_user
where id in
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</select>
对应的查询sql：
select id, username, email, addr, mobile, status from t_user where id in ( ? , ? )
批量插入
数据库id设置自增了
/**
* 批量插入
* @param users 集合
* @return 影响条数
*/
int batchInsert(@Param("users") List<User> users);
<insert id="batchInsert">
insert into t_user(username, email, addr, mobile, status)
values
<foreach collection="users" item="user" separator=",">
(#{user.username}, #{user.email}, #{user.addr}, #{user.mobile}, #{user.status})
</foreach>
</insert>
对应sql: 这里插入2条数据
insert into t_user(username, email, addr, mobile, status) values (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?)
这里推荐一篇文章：MyBatis批量插入几千条数据慎用foreach
script
简单了解就行，要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:
@Update({"<script>",
"update Author",
" <set>",
" <if test='username != null'>username=#{username},</if>",
" <if test='password != null'>password=#{password},</if>",
" <if test='email != null'>email=#{email},</if>",
" <if test='bio != null'>bio=#{bio}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);
bind
简单了解就行，bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：
<select id="selectBlogsLike" resultType="Blog">
<bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>
多数据库支持
简单了解就行，如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如下面的例子：
<insert id="insert">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
<if test="_databaseId == 'oracle'">
select seq_users.nextval from dual
</if>
<if test="_databaseId == 'db2'">
select nextval for seq_users from sysibm.sysdummy1"
</if>
</selectKey>
insert into users values (#{id}, #{name})
</insert>
参考链接
https://mybatis.net.cn/dynamic-sql.html
扫一扫
7360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
