一、动态SQL简介
MyBatis的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。有些时候,SQL语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息。使用Oracle的序列、mySQL的函数生成Id。这时我们可以使用动态SQL。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。 MyBatis中用于实现动态SQL的元素主要有:
1、if和where
2、choose(when,otherwise)
3、trim
4、set
5、foreach
下面将逐一进行介绍。
1、if和where
if就是简单的条件判断,利用if语句我们可以实现某些简单的条件选择。动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。 使用示例: 查询语句块:
(where可以自动去掉条件中的第一个and,include 引用sql片段)
<select id="findUserList" parameterType="com.kang.pojo.UserQueryVo"
resultType="com.kang.pojo.UserCustom">
SELECT * FROM USER
<where>
<include refid="query_user_where"></include>
</where>
</select>
2、choose
choose元素的作用就相当于JAVA中的switch语句,基本上跟JSTL中的choose的作用和用法是一样的,通常都是与when和otherwise搭配的。 使用示例:
<select id="findUserList" parameterType="com.kang.pojo.UserQueryVo"
resultType="com.kang.pojo.UserCustom">
SELECT * FROM USER
<choose>
<when test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex = #{userCustom.sex}
</when>
<when test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</when>
<otherwise>
and user.id = 1
</otherwise>
</choose>
</select>
3、trim
trim元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是prefix和suffix;可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是prefixOverrides和suffixOverrides;正因为trim有这样的功能,所以我们也可以非常简单的利用trim来代替where元素的功能。 使用示例:
<select id="findUserList" parameterType="com.kang.pojo.UserQueryVo"
resultType="com.kang.pojo.UserCustom">
SELECT * FROM USER
<trim prefix="where" prefixOverrides="and |or">
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex = #{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</if>
</if>
</trim>
</select>
trim属性介绍:
prefix:前缀
prefixoverride:去掉第一个and或者是or
suffix:后缀
suffixoverride:去掉最后一个逗号(也可以是其他的标记,就像是上面前缀中的and一样)
4、set
set元素主要是用在更新操作的时候,它的主要功能和where元素其实是差不多的,主要是在set标签包含的语句前输出一个set。如果包含的语句是以逗号结束的话将会把该逗号忽略。如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段,而不用每次更新全部字段。 使用示例:
<update id="updateUser" parameterType="com.kang.pojo.User">
update user
<set>
<if test="username != null">
username=#{username},
</if>
<if test="birthday != null">
birthday=#{birthday},
</if>
<if test="sex != null">
sex=#{sex},
</if>
<if test="address != null">
address=#{address}
</if>
</set>
where id = #{id}
</update>
5、foreach
示例:
<select id="getImages" resultType="java.util.HashMap">
select * from test_table
<where>
<if test="list != null">
and opt_user IN
<foreach collection="list" item = "item" separator="," open="(" close=")">
#{item}
</foreach>
</if>
<if test="bTime!=null and bTime != ''">
and opt_time <![CDATA[ >=]]>#{bTime}
</if>
<if test="eTime!=null and eTime != ''">
and opt_time <![CDATA[ <=]]>#{eTime}
</if>
</where>
</select>
各个属性的含义:
collection:指定要遍历的集合:
List类型的参数会特殊处理封装在map中,map的key就叫list ;
item:将当前遍历出的元素赋值给指定的变量 ;
separator:每个元素之间的分隔符 ;
open:遍历出所有结果拼接一个开始的字符 ;
close:遍历出所有结果拼接一个结束的字符 ;
index:索引。遍历list的时候是index就是索引,item就是当前值 ;
遍历map的时候index表示的就是map的key,item就是map的值;
6、动态SQL: SQL 片段
有时候可能某个 sql 语句我们用的特别多,为了增加代码的重用性,简化代码,我们需要将这些代码抽取出来,然后使用时直接调用。
比如:假如我们需要经常根据用户名和性别来进行联合查询,那么我们就把这个代码抽取出来,如下:
123456789
AND username = #{username} AND sex = #{sex}
引用 sql 片段
12345678 select * from user (引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace ) )在这里还可以引用其他的 sql 片段)
注意:①、最好基于 单表来定义 sql 片段,提高片段的可重用性
②、在 sql 片段中不要包括 where