MyBatis的动态SQL本质上采用了OGNL的语法,也就是说:你会OGNL,那么MyBatis的动态SQL就不会有任何问题。下面我列出一张表格,会列举出一系列标签以及用法:
标签名 |
描述 |
用法 |
if |
判断是否加入where语句的条件 |
3.5.1 |
choose、when、otherwise |
类似于java的switch |
3.5.2 |
where |
where元素知道如果由被包含的标记返回任意内容,就仅仅插入“WHERE”。而且,如 果以“AND”或“OR”开头的内容,那么就会跳过 WHERE 不插入 |
3.5.3 |
set |
set元素可以被用于动态包含更新的列,而不包 含不需更新的 |
3.5.3 |
trim |
当where或者set满足不了你的要求的时候,可以用trim来自己定义 |
3.5.3 |
foreach |
迭代一个集合,构建IN条件。允许你指定一个集合,声明集合项和索引变量。它也允许你指定开放和关闭的字符串,在迭代之间放置分隔符。这个元素 是很智能的,它不会偶然地附加多余的分隔符 |
3.5.4 |
bind |
定义一个变量 |
3.5.5 |
3.5.1 if
<selectid="findActiveBlogWithTitleLike"
parameterType="Blog"resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<iftest="title!= null">
AND title like #{title}
</if>
</select>
3.5.2 choose、when、otherwiswe
<select id="findActiveBlogLike"
parameterType="Blog" 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>
3.5.3 trim、where、set
<selectid="findActiveBlogLike"
parameterType="Blog"resultType="Blog">
SELECT * FROM BLOG
<where>
<iftest="state!= null">
state = #{state}
</if>
<iftest="title!= null">
AND title like #{title}
</if>
<iftest="author!= null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
<trimprefix="WHERE"prefixOverrides="AND|OR ">
...
</trim>
<updateid="updateAuthorIfNecessary"
parameterType="domain.blog.Author">
update Author
<set>
<iftest="username!= null">username=#{username},</if>
<iftest="password!= null">password=#{password},</if>
<iftest="email!= null">email=#{email},</if>
<iftest="bio!= null">bio=#{bio}</if>
</set>
where id=#{id}
</select>
<trimprefix="SET"suffixOverrides=",">
...
</trim>
3.5.4 foreach
<selectid="selectPostIn"resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreachitem="item"index="index"collection="list"
open="("separator=","close=")">
#{item}
</foreach>
</select>
3.5.5 bind
<selectid="selectBlogsLike"parameterType="Blog"resultType="Blog">
<bindname="pattern"value="'%'+ _parameter.getTitle() + '%'"/>
SELECT * FROM BLOG
WHERE title LIKE #{pattern}
</select>