Mybatis (四) Mybatis动态SQL
Mybatis动态SQL
概念
摘自官网：https://mybatis.org/mybatis-3/zh/dynamic-sql.html
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
动态sql语句标签主要包括下面这几种：
- if
- choose...when...otherwise
- trim
- foreach
- bind
if 标签
当我们需要做一些判断的时候，可以使用到if标签，如下：
<select id="findUsersByUsernamePassword" resultType="user" >
select * from tb_user
<where>
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</where>
</select>
所对应的Mapper接口方法：
/*根据username和password查询*/
List<User> findUsersByUsernamePassword(User user);
我们可以直接使用对象中的属性来取值。如果此时我们加了@Param("user")
/*根据username和password查询*/
List<User> findUsersByUsernamePassword(@Param("user") User user);
对应的取值xml：
<select id="findUsersByUsernamePassword" resultType="user" >
select * from tb_user
<where>
<if test="user.username != null">
username = #{user.username}
</if>
<if test="user.password != null">
and password = #{user.password}
</if>
</where>
</select>
解释：上述示例中，sql拼接结果如下：
- username 为null，password 不为null
select * from tb_user where password = ?
- username 不为null，password为null
select * from tb_user where username = ?
- username 不为null，password不为null
select * from tb_user where username = ? and password = ?
- username 为null，password为null
select * from tb_user
注意：
<if test="id != null and userId != '' and username =='2'">
refusalreason=#{remark,jdbcType=VARCHAR}
</if>
mybatis是用OGNL表达式来解析的，在OGNL的表达式中，'2'会被解析成字符，java是强类型的，char 和一个string 会导致不等，所以if标签中的sql不会被解析。
- if标签里，判断相等或不相等
单个字符需要加.toString()，如：auditidentified =='2'.toString()
- if标签里，判断相等或不相等
多个字符不需要加toString()，如：auditidentified !='2155'
choose 标签
有时我们不希望用到所有的条件，比如用户有记录有id，user_id, username这几个属性，但是查询的时候希望有优先级，比如id存在的时候，只按id查询，如果id不存在，但是user_id存在，则按照user_id查询，如果前面两者都不存在，则按照username查询。
语句模板：
<choose>
<when test = "a">A</when>
<when test = "b">B</when>
<otherwise>C</otherwise>
</choose>
语句解释：
解释：（只会执行一个条件,也必定会有一个条件执行）
这个语句相当Java中的 switch...case...default
switch ==> choose:定义语句范围
case ==> when :满足当前条件 test 时执行
default ==> otherwise :当前面的所有when都不满足的时候，使用otherwise的语句
即：当表达式a计算结果为true时，则执行语句A, choose结束
当表达式b计算结果为true时，则执行语句B, choose结束
当前面的when都不满足时，执行C。choose结束。
示例语句如下：
<select id="findUsersByIdOrUserIdOrUsername" resultType="user">
select * from tb_user
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="userId != null">
user_id = #{userId}
</when>
<otherwise>
username = #{username}
</otherwise>
</choose>
</where>
</select>
对应的Mapper接口方法：
/*查询，如果有id不为空，则直接按id查询，如果id为空，则按userId查询，否则按username查询*/
List<User> findUsersByIdOrUserIdOrUsername(
@Param("id") Integer id,
@Param("userId") String userId,
@Param("username") String username);
注意的同学可以发现，这里与之前的传参方法不同。后面将会有一篇专门介绍参数传递问题。这里先不细说。
trim 标签
与trim相关的标签还有set和where标签，这是用来我们解决sql拼接的问题，之前自己手动凭借SQL的时候，需要非常注意 and，or和逗号问题，对应and|or，我们会在sql之前使用where 1 = 1,如下：
select * from tb_user where 1 = 1 and ...
我们看一下下面这个sql:
<select id="findUsersByUsernamePassword" resultType="user" >
select * from tb_user where
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</select>
当我username为空，password不为空时，处理之后就会生成如下sql：
select * from tb_user where and password = ?
上面这个sql就是一个错误的sql，但是mybatis中的动态sql帮我们提供了方法，下面我们看一下trim标签：
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""></trim>
<!-- 解释：
prefix: 前缀,即在当前使用trim标签的最开头处添加指定元素。
suffix: 后缀,即在当前使用trim标签的最结束处添加指定元素。
prefixOverrides: 在最前面将元素指定的数据移除掉。
suffixOverrides: 在最后面将元素指定的数据移除掉。
-->
所以我们使用trim标签来解决上面标签产生的问题，如下：
<select id="findUsersByUsernamePassword" resultType="user" >
select * from tb_user
<trim prefix="where" prefixOverrides="and |or">
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</trim>
</select>
注意：and后面的空格是必要添加的(官网介绍)。但是我测试的空格没有影响，为了规避问题，建议加上空格。
mybatis鉴于上述使用环境非常常见，将其封装为where标签，以及set标签，如下：
<select id="findUsersByUsernamePassword" resultType="user" >
select * from tb_user
<where>
<if test="username != null">
username = #{username}
</if>
<if test="password != null">
and password = #{password}
</if>
</where>
</select>
<update id="updateUserByUser" >
update tb_user
<set>
<if test="userId != null">
user_id = #{userId},
</if>
<if test="username != null">
username = #{username},
</if>
<if test="password != null">
password = #{password},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="gender != null">
gender = #{gender},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="status != null">
status = #{status},
</if>
<if test="createTime != null">
create_time = #{createTime},
</if>
<if test="createUser != null">
create_user = #{createUser},
</if>
<if test="modifyUser != null">
modify_user = #{modifyUser},
</if>
<if test="modifyTime != null">
modify_time = #{modifyTime},
</if>
</set>
where id = #{id}
</update>
foreach标签
这个标签对我们来说从名字上，其实就知道是用来干啥的了。对，foreach标签就是用来循环迭代一些可迭代的元素，如，list，set，map，数组等。
试想以下，当我们需要根据多个id批量查询的时候，我们使用java代码需要如何操作：
public void test() {
String sql = " select * from tb_user where id in ";
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(4);
String idSql = "(";
for (int i = 0; i < ids.size(); i++) {
if (i == ids.size() - 1) {
idSql += ids.get(i) +")";
}else{
idSql += ids.get(i) + ",";
}
}
sql += idSql;
System.out.println(sql);
}
//select * from tb_user where id in (1,2,3,4);
可以看出，拼接这个sql非常繁琐，但是Mybatis提供的foreach可以很简单的实现,在此之前，我们看一下foreach标签：
<foreach collection="" index="" item="" open="" separator="" close="" ></foreach>
注意：
你可以将任何可迭代对象 如 List、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时。
-
collection 属性的书写方式，List -> list/collection,Set->collection,[]数组 -> array,Map -> map.
-
index 是当前迭代的次数，item 的值是本次迭代获取的元素。
当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。
-
open表示拼接最前的元素
-
close表示拼接在最后的元素
-
separator表示遍历的各个元素之间的分隔符。
使用foreach实现：
<select id="findUsersByIds" resultType="user">
select * from tb_user
<where>
id in
<foreach collection="list" item="ite" open="(" separator="," close=")">
#{ite}
</foreach>
</where>
</select>
对应的mapper接口的方法：
List<User> findUsersByIds(List<Integer> id);
bind 标签
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文：
<select id="findUserById" resultType="user">
<bind name="id" value=" id + 1"/>
select * from tb_user where id = #{id}
</select>
<!--
==> Preparing: select * from tb_user where username like ?
==> Parameters: %xxx%(String)
-->
<select id="findUsersByLikeUsername" resultType="user">
<bind name="name" value=" '%' +username+ '%' "/>
select * from tb_user where username like #{name}
</select>
<!-- 模糊查询还可以这样 -->
<select id="findUserByQueryVo" parameterType="QueryVo"
resultType="User">
select * from tb_user where username like concat('%',#{user.username},'%')
</select>
bind 标签比较简单，如上所示，可以将字符串进行拼接，也可以对传递进来的参数进行处理。
源码地址：
https://gitee.com/ooyhao/JavaRepo_Public/tree/master/Mybatis
最后
如果觉得不错的话，那就关注一下小编哦！一起交流，一起学习
