MyBatis动态SQL示例

if

<select id="list" parameterType="map" resultMap="BlogResult">
	select *
	from t_blog
	<where>
		<if test="title != null and title != ''">
			and title like #{title}
		</if>
		<if test="blogTypeId != null">
			and blogTypeId = #{blogTypeId}
		</if>
		<if test="isMenuBlog != null">
			and isMenuBlog = #{isMenuBlog}
		</if>
		<if test="isMenuBlogKey != null">
			and isMenuBlog != 1
		</if>
		<if test="releaseDateStr != null and releaseDateStr != ''">
			and date_format(releaseDate, '%Y年%m月') = #{releaseDateStr}
		</if>
	</where>
	<if test="sortByReleaseDate == null">
		order by releaseDate desc
	</if>
	<if test="start != null and size != null">
		limit #{start},#{size}
	</if>
</select>

choose、when、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>

trim、where、set

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</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>
  </where>
</select>

<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

<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>

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);

bind

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>