Mybatics动态sql
if语句
只有满足在test中的条件的情况下,才会在查询条件中加入if标签内的内容
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
test中需要包含两个条件的情况下(直接使用and)
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
choose、when、otherwise
想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,类似Java 中的 switch 语句:
<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>
这里的when相当于case语句,otherwise就相当于switch中的default语句。
trim
<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>
</select>
这种情况下一旦遇到第一个条件不满足,下面某个条件满足就会出现查询条件第一个子条件出现and前缀:如:
SELECT * FROM BLOG
WHERE
AND title like ‘someTitle’
这样的语句很明显会造成程序执行错误。
这时就可以使用trim标签,将上述代码改为:
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE
**<trim prefix="WHERE" prefixoverride="AND |OR">**
<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>
**</trim>**
</select>
prefix:前缀
prefixoverride:去掉第一个and或者是or
有了这个标签,就很容易解决了子条件出现前缀的问题
foreach
<delete id="deleteBatch">
delete from user where id in
<foreach collection="array" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
假如 ids = {1,2,3,4,5}
这里的sql就相当于:
delete form user where id in (1,2,3,4,5)