1.if
<select id="findActive" resultType="Blong"> 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
多个条件中选择一个使用,类似switch case
<select id="findActive" resultType="Blog"> <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.trime、where、set
<select id="findActive" 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>
where元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where元素的功能。比如,和 where元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR"> ... </trim>
(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
<update id="updateAuthor"> 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> </update>
<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>
可以将任何可迭代对象(list,set,Map,数组等)作为集合参数传递给foreach,index是当前迭代的序号,item是本次迭代获取到的元素。当使用Map对象(或者 Map.Entry 对象的集合)时,index是键,item是值。
5.script
在带注解的映射器接口类中使用动态 SQL,可以使用 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.多数据库支持
如果配置了 databaseIdProvider,你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。
<insert id="insert"> <selectKey keyProperty="id" resultType="int" order="BEFORE"> <if test="_databaseId == 'oracle'"> select seq_users.nextval from dual </if> <if test="_databaseId == 'db2'"> select nextval for seq_users from sysibm.sysdummy </if> </selectKey> insert into users values (#{id}, #{name}) </insert>