1.MyBatis dynamic SQL

One of the powerful features of MyBatis is its dynamic SQL, that is, splicing SQL strings. If you have experience using JDBC or other similar frameworks, you can realize how painful it is to splice SQL statements according to different conditions. When splicing, make sure you don't forget the necessary spaces, and pay attention to omitting the comma at the end of the column name list. Using the feature of dynamic SQL can completely get rid of this pain.

Usually, using dynamic SQL cannot be an independent part. Of course, MyBatis uses a powerful dynamic SQL language to improve this situation. This language can be used in any SQL mapping statement.

Dynamic SQL elements are similar to using JSTL or other similar XML based text processors. In previous versions of MyBatis, there were many elements to understand. MyBatis 3 has greatly improved them. Now it can use less than half of the original elements. MyBatis uses powerful OGNL based expressions to eliminate other elements.

2. Dynamic SQL Tags: if, choose (when, otherwise), trim (where, set), foreach

1. if tag: ` insert code slice here

<select id="queryByIdAndTitle"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 1=1 
  <if test="id!= null and title!=null">
    AND id=#{id} and title=#{title}
  </if>
</select>

Note: the if tag is generally used for non empty verification. For example, if the id is empty, the code in the if tag will not be executed; otherwise, it will be executed.

1. choose(when,otherwise) label:

<select id="queryBy"
     resultType="Blog">
  SELECT * FROM BLOG WHERE 1=1
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <otherwise>
      AND id= 1
    </otherwise>
  </choose>
</select>

Note: the choose(when,otherwise) tag is equivalent to switch(case,default). As in the above example, if the title is empty, the code in the when tag will not be executed, and the code in the otherwise tag will be executed by default.

1. trim(where,set) tag:

<select id="queryBy" resultType="com.scme.pojo.User" parameterType="com.scme.pojo.User">
                 select * from user 
                 <where>
                         <if test="username!=null and password!=null">
                             and username=#{username} and password=#{password}
                         </if>
                 </where>
</select>

Note: assuming that the username and password passed in in the above example are not empty, the code can run successfully! But my friends may have questions. What are the sql statements actually executed?
In fact, the sql is: select * from user where username=? and password=? Do you find that the where tag replaces the where keyword in sql, but the and in if is missing. In fact, the where tag can automatically remove the "and" OR "keyword in sql that starts with" and "OR".

If the where element does not play according to the normal routine, we can still customize sql by customizing the trim element to achieve the effect of where tag. The code is as follows:

<select id="queryBy" resultType="com.scme.pojo.User" parameterType="com.scme.pojo.User">
                 select * from user 
                 <trim prefix="WHERE" prefixOverrides="AND |OR ">
                          <if test="username!=null and password!=null">
                             and username=#{username} and password=#{password}
                         </if>
                </trim>
                 <!-- The effect is the same as above　　　　　　　　　　<where>
                         <if test="username!=null and password!=null">
                             and username=#{username} and password=#{password}
                         </if>
                 </where> -->
         </select>

set tag, the code is as follows:

<update id="updateUser" parameterType="com.scme.pojo.User">
                 update user 
                 <set>
                     <if test="username!=null">
                             username=#{username}
                     </if>
                 </set>
                 <where> 
                     <if test="id!=null">
                             id=#{id}
                     </if>
                 </where>
         </update>

Note: the function of the set tag is similar to that of the where tag. The set tag replaces the set keyword in sql. The set tag can automatically remove the redundant "," in sql
Similarly, trim tag can also realize the function of set tag

<update id="updateUser" parameterType="com.scme.pojo.User">
                 update user 
 　　　　　　　　<trim prefix="set" prefixOverrides=",">
　　　　　　　　　　　　　　<if test="username!=null"> username=#{username} </if> 　　　　　　　　</trim>　　 　　　　　　　　<where> 　　　　　　　　　　　　　　　　<if test="id!=null"> id=#{id} </if> 　　　　　　　　　　　　　　　　　　</where> </update>

1. Foreach tag: the foreach tag enables batch deletion

<delete id="batchDelete" parameterType="java.lang.String">
  delete from user
  where id in
  <foreach item="id" index="index" collection="list"
      open="(" separator="," close=")">
        #{id}
  </foreach>
</delete >

Note: the foreach tag can iterate over any object (such as list, set, etc.) and any dictionary or array object. It is passed to foreach as a set parameter. When an iteratable object or array is used, index is the number of current iterations, and the value of item is the element obtained in this iteration. When using a dictionary (or a collection of Map.Entry objects), index is the key and item is the value. The collection tab can be filled in ('List ',' array ',' map ').

The attributes of foreach elements mainly include item, index, collection, open, separator and close.

item represents the alias of each element in the collection when iterating;

index specifies a name to indicate the location of each iteration in the iteration process;

open indicates what the statement starts with,

Separator indicates what symbol is used as the separator between each iteration;

close indicates what to end with.

1. bind:

The bind element can create a variable from an OGNL expression and bind it to a context. For example:

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

1. Multi-db vendor support

One configured "_ The databaseIdProvider of the "databaseId" variable is available for dynamic code, so that specific statements can be built according to different database vendors. For example, the following 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>