Mybatis3详解(七)----动态SQL
1、动态SQL介绍
在使用传统的JDBC来编写代码时,很多时候需要去拼接SQL,这是一件很麻烦的事情,因为有些查询需要许多的条件,比如在查询用户时,需要根据用户名,年龄,性别或地址等信息进行查询,当不需要用户名查询时却依然使用用户名作为条件查询就不合适了,而如果使用大量的Java进行判断,那么代码的可读性比较差,又或者在拼接的时候,不注意哪里少了或多了个空格、符号,都会导致错误。而Mybatis提供了对SQL语句动态拼接的能力,可以让我们在 xml 映射文件内,以标签的形式编写动态 SQL,完成逻辑判断和动态拼接 SQL的功能。大量的判断都可以在Mybatis的映射xml文件里面配置,以达到许多需要大量代码才能实现的功能,从而大大减少了代码量。
Mybatis动态SQL语句是基于OGNL表达式的,主要有以下几类:
- if 标签:简单的条件判断。
- where 标签:相当于where关键字,并且能智能的处理and or ,不必担心多余导致语法错误。
- set 标签:和where标签差不多,主要用于更新。
- trim 标签:插入包含prefix前缀、suffix后缀的内容,并且prefixOverrides去除第一个前缀内容,suffixOverrides去除结尾后缀内容。
- choose、when、otherwize 标签:相当于java 语言中的 switch 语法,与 jstl 中的choose 很类似。
- foreach 标签:用来对一个集合进行遍历,在使用 in 语句查询时特别有用。
- bind 标签:允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文。
本章我们以 User 表为例来说明:
2、if 标签
if 标签用来实现根据条件拼接sql语句,如果判断参数不为null,则拼接sql,否则不拼接。判断条件内容写在if标签的 test 属性中。示例如下:
<mapper namespace="com.thr.mapper.UserMapper">
<resultMap id="userMap" type="com.thr.pojo.User">
<id property="userId" column="id"/>
<result property="userName" column="username"/>
<result property="userAge" column="age"/>
<result property="userBirthday" column="birthday"/>
<result property="userSex" column="sex"/>
<result property="userAddress" column="address"/>
</resultMap>
<!--根据用户名和地址查询用户信息-->
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
select * from t_user where
<if test="userName!=null and userName!=''">
username = #{userName}
</if>
<if test="userAddress!=null and userAddress!=''">
and address = #{userAddress}
</if>
</select>
</mapper>
上述代码当参数userName和userAddress都不为 null 时,拼接出的SQL语句为:select * from t_user where username = ? and address = ? 。但是如果上面的SQL语句中传入的参数 userName 为null,则拼接出的sql语句为:select * from t_user where and address = ? ,可以明显看到 where and 是错误的语法,导致报错,又或者是传入的两个参数都为null,那么拼接出的sql语句为:select * from t_user where ,这明显也是错误的语法,要解决这个问题,需要用到where标签。
3、where 标签
<where>标签相当于SQL语句中的where关键字,而且where标签还有特殊的作用。作用如下:
- 自动向sql语句中添加where关键字
- 去掉第一个条件的and 或 or 关键字
上面的示例用where标签改写后示例如下:
<!--根据用户名和地址查询用户信息-->
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
select * from t_user
<where>
<if test="userName!=null and userName!=''" >
and username = #{userName}
</if>
<if test="userAddress!=null and userAddress!=''">
and address = #{userAddress}
</if>
</where>
</select>
SQL语句等价于:select * from t_user where username = ? and address = ?
4、set 标签
set标签的功能和 where 标签差不多,只是set 标签是用在更新操作的时候,作用如下:
- 自动向修改sql语句中添加set关键字
- 去掉最后一个条件结尾的逗号
使用set标签示例代码如下:
<!--修改用户名、年龄和地址-->
<update id="updateUser" parameterType="user">
update t_user
<set>
<if test="userName!=null and userName!=''">
username = #{userName},
</if>
<if test="userAge!=null and userAge!=''">
age = #{userAge},
</if>
<if test="userAddress!=null and userAddress!=''">
address = #{userAddress},
</if>
</set>
where id = #{userId}
</update>
可以发现最后一个修改条件多了一个逗号(,),但set标签帮我们去掉了,SQL语句等价于:update t_user SET username = ?, age = ?, address = ? where id = ?
5、trim 标签(了解)
trim 元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是 prefix 和 suffix;可以把包含内容的首部某些内容去除,也可以把尾部的某些内容去除,对应的属性是 prefixOverrides 和 suffixOverrides;正因为 trim 有这样的功能,它可以用来实现 where 和 set 一样的效果。
trim标签的属性:
- prefix:表示在trim标签内sql语句加上前缀
- suffix:表示在trim标签内sql语句加上后缀
- prefixOverrides:表示去除第一个前缀
- suffixOverrides:表示去除最后一个后缀
将前面where 标签示例用trim 标签代替:
<!--根据用户名和地址查询用户信息-->
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
select * from t_user
<!--<where>
<if test="userName!=null and userName!=''" >
and username = #{userName}
</if>
<if test="userAddress!=null and userAddress!=''">
and address = #{userAddress}
</if>
</where>-->
<!-- 插入prefix属性中指定的内容,并且移除首部所有指定在prefixOverrides属性中的内容-->
<trim prefix="where" prefixOverrides="and | or">
<if test="userName!=null and userName!=''" >
and username = #{userName}
</if>
<if test="userAddress!=null and userAddress!=''">
and address = #{userAddress}
</if>
</trim>
</select>
将前面set 标签示例用trim 标签代替:
<!--修改用户名、年龄和地址-->
<update id="updateUser" parameterType="user">
update t_user
<!--<set>
<if test="userName!=null and userName!=''">
username = #{userName},
</if>
<if test="userAge!=null and userAge!=''">
age = #{userAge},
</if>
<if test="userAddress!=null and userAddress!=''">
address = #{userAddress},
</if>
</set>-->
<!-- 插入prefix属性中指定的内容,并且移除尾部所有指定在suffixOverrides属性中的内容-->
<trim prefix="set" suffixOverrides=",">
<if test="userName!=null and userName!=''">
username = #{userName},
</if>
<if test="userAge!=null and userAge!=''">
age = #{userAge},
</if>
<if test="userAddress!=null and userAddress!=''">
address = #{userAddress},
</if>
</trim>
where id = #{userId}
</update>
6、choose、when、otherwise 标签
choose、when、otherwise标签是按顺序判断其内部 when 标签中的 test 条件出否成立,如果有一个成立,则choose结束,执行条件成立的SQL。当 choose 中所有 when 的条件都不满足时,则执行 otherwise 中的SQL,类似于Java中的switch…case…default语句。
示例代码如下:
<select id="selectUserByChoose" resultType="user" parameterMap="userMap">
select * from t_user
<where>
<choose>
<when test="userName!= null and userName!=''">
username=#{userName}
</when>
<when test="userAddress!= null and userAddress!=''">
and address=#{userAddress}
</when>
<otherwise>
and age=#{userAge}
</otherwise>
</choose>
</where>
</select>
- 如果username不为空,则只用username作为条件查询。SQL语句等价于:select * from t_user where username = ?
- 当username为空,而address不为空,则用address作为条件进行查询。SQL语句等价于:select * from t_user where address= ?
- 当username和address都为空时,则要求以age作为条件查询。SQL语句等价于:select * from t_user where age= ?
虽然这种场景有点不切实际,但是我们这里主要集中如何使用这三个标签来实现即可
7、foreach 标签(重要)
foreach 标签主要用于遍历集合。通常是用来构建 IN 条件语句,也可用于其他情况下动态拼接sql语句。
foreach标签有以下几个属性:
- collection:表示要遍历的集合元素,注意不要写#{}。
- item:表示每次遍历时生成的对象名(注:当传入Map对象或Map.Entry对象的集合时,index 是键,item是值)。
- index:表示在迭代过程中,每次迭代到的位置。
- open:表示开始遍历时要拼接的字符串。
- close:表示结束遍历时要拼接的字符串。
- sperator:表示在每次遍历时两个对象直接的连接字符串。
示例:如果现在有这样的需求:我们需要查询 t_user 表中 id 分别为1,2,4,5的用户。所对应的sql语句有这两条:select * from t_user where id=1 or id=2 or id=4 or id=5;和 select * from t_user where id in (1,2,4,5);。下面我们使用foreach标签来改写。
①、创建一个UserVo类,里面封装一个 List<Integer> ids 的属性,代码如下:
public class UserVo {
//封装多个id
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
②、foreach 来改写 select * from t_user where id=1 or id=2 or id=4 or id=5;代码如下:
<select id="selectUserByListId" parameterType="userVo" resultMap="userMap">
select * from t_user
<where>
<!--加个括号
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id=#{id}
</foreach>-->
<foreach collection="ids" item="id" separator="or">
id=#{id}
</foreach>
</where>
</select>
测试代码如下:
@Test
public void testSelectUserByListId(){
UserVo userVo = new UserVo();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(4);
ids.add(5);
userVo.setIds(ids);
List<User> userList = mapper.selectUserByListId(userVo);
for (User user : userList) {
System.out.println(user);
}
}
运行结果:
③、foreach 来改写 select * from t_user where id in (1,2,4,5);将上面的映射文件稍加修改:
<select id="selectUserByListId" parameterType="userVo" resultMap="userMap">
select * from t_user
<where>
<foreach collection="ids" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
运行结果:
8、bind 标签(了解)
bind 标签允许你在 OGNL 表达式以外创建一个变量,并将其绑定到当前的上下文(可定义多个)。示例代码如下:
<!-- 模糊查询,根据username字段查询用户-->
<select id="selectUserByName" parameterType="string" resultMap="userMap">
<bind name="pattern" value="'%'+_parameter+'%'"/>
select * from t_user where username like #{pattern}
</select>
这里的”_parameter”代表的是传递进来的参数,它和通配符(%)连接后赋给了pattern,SQL语句等价于:select * from t_user where username like ?。这种方式无论是Mysql还是Oracle都可以使用这样的语句,提高了代码的可移植性。如果传递了多个参数,则可以定义多个bind 标签。
<select id="selectUserByNameAndAddress" parameterType="user" resultMap="userMap">
<bind name="pattern_username" value="'%'+userName+'%'"/>
<bind name="pattern_address" value="'%'+userAddress+'%'"/>
select * from t_user where username like #{pattern_username} and address like #{pattern_address}
</select>