MyBatis - dynamic SQL

MyBatis dynamic SQL

(as learning notes, please refer to: MyBatis reference)
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>
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
<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>
  • The WHERE element inserts the WHERE clause only if the condition of at least one child element returns an SQL clause. Also, if the statement begins with AND OR, the WHERE element removes them.
  • Custom trim element:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>
  • 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>

Equivalent to:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>
foreach
  • foreach allows you to specify a collection, use set item and index variables, and allow you to specify the beginning and end strings and place separators between iteration results.
  • Can iterate list,set,map. When it's map, index is the key and item is the value.
<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
  • The bind element creates a variable from an OGNL expression and binds it to the context. For example:
<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>
Multi database support
  • A databaseIdProvider with the variable "databaseId" configured can be used in dynamic code, so that specific statements can be built according to different database vendors. For example:
<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.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>

Tags: Mybatis SQL Database Oracle

Posted on Sat, 08 Feb 2020 10:15:46 -0500 by phpstuck