Mybatis动态SQL
☞ 博客导航,带你有序的阅读和学习!
文章目录
- 概念
- if 标签
- choose 标签
- trim 标签
- foreach标签
- bind 标签
概念
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