目录
前言
MyBatis提供了10种动态SQL标签:trim、where、set、foreach、if、choose、when、otherwise、bind、include;其执行原理为,使用OGNL从SQL参数对象中计算表达式的值,根据表达式的值动态拼接SQL,以此来完成动态SQL的功能。
动态标签用法
一、if标签
通过test属性中的表达式去判断内容是否有效,有效则将if中的sql片段拼接执行。
例如:在人员信息表,根据姓名,年龄,地址,性别等条件查询人员信息。
dao层:
User sqlUse(@Param("name") String name, @Param("age") int age,
@Param("address") String address, @Param("sex") String sex);
xml(注释已在代码):
<select id="sqlUse" resultType="com.example.animalhome.web.entity.User">
select * from user where
<!-- 判断name不为空 -->
<if test="name != '' and name != null">
name = #{name}
</if>
<!-- 比较address的写法:equals或者== -->
<!-- 若是单个字符串,建议使用此写法(原因在sex使用时会有说明!!!): -->
<!-- <if test="address.equals('北'.toString()) or address == '上'.toString()"> -->
<if test="address.equals('北京') or address == '上海'">
and address = #{address}
</if>
<!-- 比较age是否大于0且等于19(注意:字段age为int类型)-->
<if test="age > 0 and age == 19">
and age = #{age}
</if>
<!-- sex使用<if test="flag=='1'"></if>这种写法时有风险,-->
<!-- 原因:Mybatis是用OGNL表达式来解析的,在OGNL的表达式中,-->
<!-- '1'会被解析成字符,java是强类型的,char和一个string会导致不等,-->
<!-- 所以if标签中的sql不会被解析,故推荐使用toString()解析成字符串来比较。-->
<if test="sex == '1'.toString()">
and sex = #{sex}
</if>
</select>
操作日志:
问题:若是上述条件都不满足会出现select * from where...的情况,亦或name条件不满足则会出现select * from where and...的语法错误,导致sql报错无法进行,那么该如何修改sql?
<select id="sqlUse" resultType="com.example.animalhome.web.entity.User">
select * from user where 1=1
<if test="name != '' and name != null">
and name = #{name}
</if>
<if test="age > 0 and age == 19">
and age = #{age}
</if>
<if test="sex == '1'.toString()">
and sex = #{sex}
</if>
</select>
解释:只需要在where语句后加上1=1的恒成立条件,并且在每个if的sql前面加上and即可;但是接下来介绍的<where>标签是最优解。
二、where标签
where标签特性:
- 只有if标签有内容的情况下才会拼接where字句;
- 若子句的开通为"and"或"or",where标签会将它替换去除。
需要注意的是:where标签只会智能的去除首个满足条件语句的前缀,所以建议在使用where标签时,每个语句都最好写上 and 前缀或者 or 前缀。否则会出现以下问题:
<select id="sqlUse" resultType="com.example.animalhome.web.entity.User">
select * from user
<where>
<if test="name != '' and name != null">
name = #{name}
</if>
<if test="age > 0 and age == 19">
age = #{age}
</if>
<if test="sex == '1'.toString()">
sex = #{sex}
</if>
</where>
</select>
生成的sql语句如下:
select * from user WHERE name = ? age = ? sex = ?
很明显,sql语法是有问题的,所以在使用where标签时,建议将所有条件都添加上and或or;正确的使用方法如下:
例如:在人员信息表,根据姓名,年龄,地址,性别等条件查询人员信息。
<select id="sqlUse" resultType="com.example.animalhome.web.entity.User">
select * from user
<where>
<if test="name != '' and name != null">
and name = #{name}
</if>
<if test="age > 0 and age == 19">
and age = #{age}
</if>
<if test="sex == '1'.toString()">
and sex = #{sex}
</if>
</where>
</select>
三、trim标签
trim标签参数说明:
- prefix:给trim标签内sql语句加上前缀
- suffix:给trim标签内sql语句加上后缀
- prefixOverrides:去除多余的前缀内容,如:prefixOverrides=“OR”,去除trim标签内sql语句多余的前缀"OR"
- suffixOverrides:去除多余的后缀内容,如:suffixOverrides=",",去除trim标签内sql语句多余的后缀","
例如:在人员信息表,根据姓名,年龄,地址,性别等条件查询人员信息。
使用prefix增加前缀(where),prefixOverrides去除多余前缀(and | or)实现查询功能
<select id="sqlUse" resultType="com.example.animalhome.web.entity.User">
select * from user
<trim prefix="where" prefixOverrides="and | or">
<if test="name != '' and name != null">
and name = #{name}
</if>
<if test="age > 0 and age == 19">
and age = #{age}
</if>
<if test="sex == '1'.toString()">
and sex = #{sex}
</if>
</trim>
</select>
例如:根据人员信息表主键id,更新人员姓名、年龄、地址、性别等信息。
使用prefix增加前缀(set),suffixOverrides去除多余后缀(,),suffix增加后缀(where id = #{id})实现更新用户信息功能
<update id="updateUser" parameterType="com.example.animalhome.web.entity.User">
update user
<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
<if test="name != null and name != ''">name= #{name},</if>
<if test="age != null and age != ''">age= #{age},</if>
<if test="sex != null and sex != ''">sex= #{sex},</if>
<if test="address != null and address != ''">address= #{address},</if>
</trim>
</update>
例如:人员信息表插入数据。
使用prefix增加前缀((),suffixOverrides去除多余后缀(,),suffix增加后缀()),实现插入用户信息功能。
<insert id="insertUser" parameterType="com.example.animalhome.web.entity.User">
INSERT INTO user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="null != id and '' != id">
id,
</if>
<if test="null != name and '' != name">
name,
</if>
<if test="null != age and '' != age">
age,
</if>
<if test="null != sex and '' != sex">
sex,
</if>
<if test="null != address and '' != address">
address,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="null != id and '' != id">
#{id},
</if>
<if test="null != name and '' != name">
#{name},
</if>
<if test="null != age and '' != age">
#{age},
</if>
<if test="null != sex and '' != sex">
#{sex},
</if>
<if test="null != address and '' != address">
#{address},
</if>
</trim>
</insert>
四、set标签
使用set标签可以动态配置SET关键字,和剔除追加到条件末尾的任何不相关的逗号。
例如:根据人员信息表主键id,更新人员姓名、年龄、地址、性别等信息。
<update id="updateUser" parameterType="com.example.animalhome.web.entity.User">
update user
<set>
<if test="name != null and name != ''">name= #{name},</if>
<if test="age != null and age != ''">age= #{age},</if>
<if test="sex != null and sex != ''">sex= #{sex},</if>
<if test="address != null and address != ''">address= #{address},</if>
</set>
where id = #{id}
</update>
通过控制台日志可以发现address后面的逗号被去除!
五、foreach标签
foreach 标签属性主要有 item,index,open,separator,close,collection。
- item:集合中元素迭代时的别名,该参数为必选。
- index:在 list 和数组中,index 是元素序号;在 map 中,index 是元素 key。该参数可选。
- open:foreach 代码的开始符号,一般是 ”(“,和 close=“)” 合用。常用在 in(),values() 时。该参数可选。
- separator:元素之间的分隔符,例如在 in() 的时候,separator=“,” 会自动在元素中间用 “,“ 隔开,避免手动输入逗号导致 SQL 错误,如 in(1, 2,) 这样。该参数可选。
- close:foreach 代码的关闭符号,一般是 ”)“,和 open=“(” 合用。常用在 in(),values()时。该参数可选。
- collection:要被 foreach 标签循环解析的对象。
foreach 标签的 collection 属性在接受参数名时,有两种情况:
匿名参数
当在 java 方法中没有通过 @Param 注解指定参数名时,列表类型默认参数名为 ”list“,数组类型默认参数名为 ”array“,Map 对象没有默认值。
具名参数
java 方法中使用了 @Param 注解指定了参数名称,则 foreach 中的 collection 属性必须为参数名。
例如:根据主键Id集合查询所有符合的人员信息。
dao层:
//如下两种入参均可用selectAllUser语句
List<User> selectAllUser(@Param("ids") List<String> ids);
List<User> selectAllUser(@Param("ids") String[] ids);
xml:
<select id="selectAllUser" resultType="com.example.animalhome.web.entity.User">
select * from user where id in
<foreach collection="ids" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</select>
在使用foreach批量更新、插入数据时,mybatis 会根据XML文件配置,动态生成多条 SQL。而要让 mybatis 成功执行多条语句,须开启允许批量查询设置,即在 jdbc-url 连接信息中添加 &allowMultiQueries=true,否则无法批量执行sql语句!!!如下所示。
例如:批量更新人员信息。
dao层:
int updateAllUser(List<User> users);
xml:
<update id="updateAllUser">
<foreach collection="list" separator=";" item="user">
update user
<set>
<if test="user.name != null and user.name != ''">name= #{user.name},</if>
<if test="user.age != null and user.age != ''">age= #{user.age},</if>
<if test="user.sex != null and user.sex != ''">sex= #{user.sex},</if>
<if test="user.address != null and user.address != ''">address= #{user.address},</if>
</set>
where id = #{user.id}
</foreach>
</update>
例如:批量插入人员信息。
dao层:
int insertAllUser(List<User> users);
xml:
<insert id="insertAllUser">
INSERT INTO user (id, name, age, address, sex) VALUES
<foreach collection="list" separator="," item="user">
(#{user.id}, #{user.name}, #{user.age}, #{user.address}, #{user.sex})
</foreach>
</insert>
上述传参类型都是List、数组类型,如果入参类型是Map,那么foreach又该如何处理呢?
Map集合在foreach由于没有默认键可用,故需要使用 @Param 注解手动指定一个标识,后面将在foreach中将其作为键使用。该标识任意指定即可,这里使用"maps"
//dao层方法
User selectAllUserByMap(@Param("maps") Map<String,Object> maps);
传入参数为集合时,映射文件的sql标签parameterType属性可省略。由于是Map集合,index、item属性分别表示为该Map集合中的key、value,故可以分别用${k},#{v}来获取该Map集合中的key、value。
Map<String,Object> maps = new HashMap<>();
maps.put("name","萨摩耶");
maps.put("sex","1");
User user = animalInformationDao.selectAllUserByMap(maps);
//xml层方法
<select id="selectAllUserByMap" resultType="com.example.animalhome.web.entity.User">
select * from user
<where>
<foreach collection="maps" index="k" item="v">
AND ${k} = #{v}
</foreach>
</where>
</select>
操作日志如下:
六、choose、when、otherwise标签
MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签,如果想要达到<if>...<else>...</else> </if> 的效果,可以借助 <choose>、<when>、<otherwise> 来实现。
//dao层
List<User> selectUserInfo(User user);
//xml
<select id="selectUserInfo"
parameterType="com.example.animalhome.web.entity.User"
resultType="com.example.animalhome.web.entity.User">
SELECT * FROM user
<where>
<choose>
<when test="name != '' and name != null">
name=#{name}
</when>
<when test="age != '' and age != null">
age = #{age}
</when>
<when test="sex != '' and sex != null">
sex = #{sex}
</when>
//otherwise条件的值必须不为空,否则sql报错!!!
<otherwise>
address = #{address}
</otherwise>
</choose>
</where>
</select>
七、bind标签
bind标签可以从OGNL(对象图导航语言)表达式中创建一个变量并将其绑定到上下文
Mybatis中使用Mysql的模糊查询字符串拼接(like) 中也涉及到bind的使用。
//dao层
List<User> findUser(@Param("name") String name);
//xml
<select id="findUser" resultType="com.example.animalhome.web.entity.User">
SELECT * FROM user
<where>
<if test="name != null and name != ''">
<bind name="name" value="'%'+name+'%'"/>
name like #{name}
</if>
</where>
</select>
八、include标签
include标签引用,可以复用SQL片段,sql标签中id属性对应include标签中的refid属性,通过include标签将sql片段和原sql片段进行拼接成一个完整的sql语句进行执行。
<sql id="field">
id,name,age,address,sex
</sql>
<sql id="table">
user
</sql>
<select id="selectUser" resultType="com.example.animalhome.web.entity.User">
select
<include refid="field"/>
from
<include refid="table"/>
</select>
等同于:
<select>
select id,name,age,address,sex from user
</select>