if:
拼接SQL的基本字段。
<select id="findActiveBlogWithTitleLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> </select>
choose, when, otherwise:
同JAVA
<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>
where:
动态插入where语句。剔除以 and 或 or开头的语句。<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> </where> </select>
trim:
与where类似。但可以自定义我们需要剔除的元素。
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
set:
用于更新语句 自动动态添加SET关键字。但剔除无效的更新逗号。
<update id="updateAuthorIfNecessary"> 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} </update>
foreach:
同JAVA类似 关键字有
Collection:需要迭代的类似 List = list 数组 = array
Item:迭代的变量
index:迭代的下标
separator:分隔符 ,
open:开始符(
close:结束符)
<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>
bind:
自定义变量。
<select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select>
原文:https://mybatis.github.io/mybatis-3/zh/dynamic-sql.html