if标签 和 where标签的使用
<select id="findUserByCondition" resultType="domain.User" parameterType="domain.User">
select * from user
<where>
<if test="username != null" >
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
</where>
</select>
foreach标签
<foreach collection="ids" open="and id in (" closs = ")" item = "id" separator=",">
#{id}
</foreach>
<!--
collection:要遍历的集合元素
open:开始的片段
closs:结束的片段
item:遍历集合的变量
separator:分割符
-->
choose、when、otherwise
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形。若两者都没有传入,就返回标记为 featured 的 BLOG。
trim、where、set
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
或者,你可以通过使用trim元素来达到同样的效果:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
抽取sql片段(sql标签)
<!-- 抽取 -->
<sql id="selectUser">select * from testMyBatis.user</sql>
<!-- 使用 -->
<include refid="selectUser"></include>