This is the 18th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

One of the great features of MyBatis is its dynamic SQL. If you have experience with JDBC or other similar frameworks, you know the pain of concatenating SQL statements based on different conditions. For example, when concatenating, make sure you don’t forget to add necessary Spaces, and take care to remove the comma from the last column name of the list. You can take advantage of dynamic SQL to get rid of this pain. While using dynamic SQL has not been easy in the past, MyBatis has improved the situation by providing a powerful dynamic SQL language that can be used in any SQL mapping statement. Dynamic SQL elements are similar to JSTL or xmL-like text processors. In previous versions of MyBatis, there were many elements that took time to understand. MyBatis 3 has drastically reduced the number of elements, and now only needs to learn half of the original elements. MyBatis uses powerful OGNL based expressions to eliminate most of the other elements.

IF statement

<! Select * from blog where name = #{name} and password = #{password} --> select * from blog where name = #{password} -->
<select id="selectUserByNameAndPassword" parameterType="map" resultType="pojo.User">
    select *
    from user
    where
    <if test="name! =null">
        name = #{name}
    </if>
    <if test="password! =null">
        and password = #{password}
    </if>
</select>
Copy the code

Select * from user where password = #{password} select * from user where password = #{password

Where clause

<! Select * from blog where name = #{name} and password = #{password} --> select * from blog where name = #{password} -->
<select id="selectUserByNameAndPassword" parameterType="map" resultType="pojo.User">
    select *
    from user
    <where>
        <if test="name ! = null">
            name = #{name}
        </if>
        <if test="password ! = null">
            and password = #{password}
        </if>
    </where>
</select>
Copy the code

“Where” tag: It inserts a “where” tag if it contains a return value. In addition, if the tag returns something that starts with AND OR OR, it strips it out.

The Set statement

<! -- update user set name=#{name},password=#{password} where id=#{id} -->
<update id="updateUserById" parameterType="pojo.User">
    update user
    <set>
        <if test="name! =null">
            name=#{name},
        </if>
        <if test="password! =null">
            password = #{password}
        </if>
    </set>
    where id = #{id}
</update>
Copy the code

Choose the statement

Similar to Java switch statements, only one of the query conditions is satisfied.


<select id="selectUserByNameAndPassword" parameterType="map" resultType="pojo.User">
    select *
    from user
    <where>
        <choose>
            <when test="name ! = null">
                name = #{name}
            </when>
            <when test="password ! = null">
                and password = #{password}
            </when>
            <otherwise>
                and id=#{id}
            </otherwise>
        </choose>
    </where>
</select>
Copy the code

foreach

The foreach element is very powerful. It allows you to specify a collection and declare collection items and index variables that can be used in the body of the element. It also allows you to specify open and close matching strings and to place separators in the middle of iterations. This element is smart enough that it does not accidentally append extra delimiters.

Another common requirement for dynamic SQL is the need to iterate over a collection, usually when building IN conditional statements. Such as:

<! --select * from user where id in (1,2,3)-->
<select id="selectUser" resultType="pojo.User">
    select *
    from user
    where id in
    <! Array instance's key is "array" -->
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>
Copy the code
  • Collection: Specifies the collection properties in the input object
  • Item: The object generated by each iteration
  • Open: concatenation string to start traversal
  • Close: concatenated string at the end
  • Separator: Iterates over the string to be concatenated between objects

Note: You can pass a List instance or array as a parameter object to MyBatis, and when you do, MyBatis will automatically wrap it in a Map with the name as the key. List instances will have “List” as the key, and array instances will have “array” as the key.

SQL fragment

Sometimes we may use a certain SQL statement too much. In order to increase the reuse of the code and simplify the code, we need to extract the code and call it directly when using it.

  • Extract the repeated SQL fragment:

    <sql id="if-name-password">
    <if test="name ! = null">
        name = #{name}
    </if>
    <if test="password ! = null">
        and password = #{password}
    </if>
</sql>
Copy the code

  • Reference SQL fragment:

    <select id="selectUserByNameAndPassword" parameterType="map" resultType="pojo.User">
  select * from user
   <where>
       <! Select * from SQL where refid does not specify a namespace.
       <include refid="if-name-password"></include>
       <! Other SQL fragments can also be referenced here.
   </where>
</select>
Copy the code

Note:

  1. It is best to define SQL fragments based on a single table to improve the reusability of the fragments
  2. Do not include WHERE in SQL fragments