1.if
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <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>
2.choose,when,otherwise
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>
3.trim,where,set
where元素只会在至少有一个子元素的条件返回SQL子句的情况下才会插入"where"子句。
而且,若语句的开头为"AND"或"OR",where元素也会将它们去除。如果where元素没有按照正常套路出牌,我们可以通过自定义trim元素来定制where元素的功能。例如:
where元素等价的自定义trim元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
prefixOverrides 属性移除所有指定在 prefixOverrides 属性中的内容,并且插入 prefix 属性中指定的内容。
set:可以用于动态包含需要更新的列,而舍去其他的。
hset元素等价:会删掉无关的逗号。
<trim prefix="SET" suffixOverrides=","> ... </trim>
4.foreach
<select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>
5.script
@Update({"<script>", "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}", "</script>"}) void updateAuthorValues(Author author);
6.bind
可以从 OGNL 表达式中创建一个变量并将其绑定到上下文
<select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select>
7.多数据库支持
8.动态sql中的可插拔脚本语言(很少用到,就不写了)