一、IF语句
<select id="queryBlogIF" parameterType="map" resultType="blog"> select * from mybatis.blog where 1=1 <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </select>
二、常用标签
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>
trim(where\set)
解决插入之后where语句错误的问题
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG <where> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </where> </select>
————————————————————————————————————————————————————————————————————————————————————
<!--实例-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
set 解决updata应用
<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>
et 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
如果 where 、set元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 、set元素的功能。
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim> --------------------------------------------------------------- <trim prefix="SET" suffixOverrides=","> ... </trim>
三、FOREACH
<sql id=""/>
<include refid=""/>
实现代码块的复用
遍历一个集合(sql语句中的in)foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。