MyBatis动态sql语句(OGNL语法)
1、if
<select id="select"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="name!= null">
AND name like #{title}
</if>
</select>
2、where
像上面的那种情况,如果where后面没有条件,然后需要直接写if判断(开头如果是 and / or 的话,会去除掉)
<select id="select"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="title != null">
AND title like #{title}
</if>
<if test="name!= null">
AND name like #{title}
</if>
<where>
</select>
3、choose(when、otherwise)
choose 相当于 java 里面的 switch 语句。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>
4、trim
prefix:前缀prefixoverride:去掉第一个and或者是or
select * from test
<trim prefix="WHERE" prefixoverride="AND丨OR">
<if test="a!=null and a!=' '">AND a=#{a}<if>
<if test="b!=null and b!=' '">AND a=#{a}<if>
</trim>
5、set
set 元素主要是用在更新操作的时候,如果包含的语句是以逗号结束的话将会把该逗号忽略,如果set包含的内容为空的话则会出错。
<update id="dynamicSetTest" parameterType="Blog">
update t_blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="content != null">
content = #{content},
</if>
<if test="owner != null">
owner = #{owner}
</if>
</set>
where id = #{id}
</update>
6、foreach
foreach主要用在构建in条件中
<select id="dynamicForeachTest" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
open separator close
相当于是in (?,?,?)
如果是个map怎么办
<select id="dynamicForeach3Test" resultType="Blog">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
collection对应map的键,像这样
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(6);
ids.add(7);
ids.add(9);
Map<String, Object> params = new HashMap<String, Object>();
params.put("ids", ids);