MyBatis之DynamicSQL(动态SQL,if,choose,when,otherwise,trim,where,set,foreach,bind)

古城微笑少年丶 2022-01-22 10:11 278阅读 0赞

## 中文翻译 ##

1.**if**

通常在where语句后面需要做一些单一的`判断`，如果要判断传进来的值是否为空，只需要判断`object!=null`，如果是判断一个对象的某个值是否为空，需要判断`object!=null and object.attribute!=null`

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

2.**choose,when,otherwise**  
有时，我们不希望应用所有条件，而是希望在许多选项中只`选择一种情况`。类似Java中的`switch语句`或者`if{}else if{}else{}`类似，MyBatis提供了一个select的方式choose,when,otherwise。

如果title`不为空`，则`只搜索t`itle，如果author且author.name不为空则搜索author\_name。`如果两者都没有提供，则只返回默认条件`，也就是有特色的博客(可能是管理员战略性/选择性的列表，而不是返回大量无意义的随机博客列表)。

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

3.**trim, where, set**

关于trim的用法，官方文档不是很清晰，我们这里举其他栗子来说明。

首先就是Where的情况下，处理这些通用语句(通常都是各种判断然后追加AND/OR的)，那么`prefix`会`追加`一个"WHERE"的字符串进去，然后用`prefixOverrides`=“AND”`移除首字母`，也就是移除字符串中首个"AND"的字符串。

<trim prefix="WHERE" prefixOverrides="AND">
        <if test="state != null"> AND 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>
    </trim>

如果应用在INSERT上面，那么有时候会遇到需要`移除尾字母`，那么就用`suffixOverrides`="."

INSERT INTO BLOG
    <trim prefix="(" suffix=")" suffixOverrides=".">
        <if test="state != null"> state, </if>
        <if test="title != null"> title, </if>
        <if test="author != null and author.name != null"> author_name,</if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=".">
        <if test="state != null"> #{state}, </if>
        <if test="title != null"> #{title}, </if>
        <if test="author != null and author.name != null"> #{author.name},</if>
    </trim>
    ....

`SET`一般用于`update`中，set字段=值，并附带有`<trim prefix="" suffixOverrides=",">`的特效，也就是自动剔除末尾的多余逗号

假设一下如果update语句用IF条件判断，如果前面的if没有执行，则可能导致有多余逗号的错误。使用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>

4.**foreach**  
有时候，需要`批量`对某些id进行操作，进行选择或者删除更新等等，这个时候传进来的参数是一个`List`类型，那么可以用`foreach`来进行`遍历`，`collection`为参数名称，`index`为下标（通常可能没用到，除非选择并重新标记id），`item`为遍历的值，直接`#{item}`即可遍历到对应的值。

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

5.**bind**  
你可以很简单的通过`bind`方式，将`OGNL表达式`处理的值`绑定`到xml中，例如这里拼接一个like表达式。

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

## 英文原版 ##

[http://www.mybatis.org/mybatis-3/dynamic-sql.html][http_www.mybatis.org_mybatis-3_dynamic-sql.html]

## Dynamic SQL ##

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

*  if
 *  choose (when, otherwise)
 *  trim (where, set)
 *  foreach
 *  ### if ###
    
    The most common thing to do in dynamic SQL is conditionally include a part of a where clause. For example:
    
        <select id="findActiveBlogWithTitleLike"
             resultType="Blog">
          SELECT * FROM BLOG
          WHERE state = ‘ACTIVE’
          <if test="title != null">
            AND title like #{title}
          </if>
        </select>
    
    This statement would provide an optional text search type of functionality. If you passed in no title, then all active Blogs would be returned. But if you do pass in a title, it will look for a title like that (for the keen eyed, yes in this case your parameter value would need to include any masking or wildcard characters).
    
    What if we wanted to optionally search by title and author? First, I’d change the name of the statement to make more sense. Then simply add another condition.
    
        <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 ###
    
    Sometimes we don’t want all of the conditionals to apply, instead we want to choose only one case among many options. Similar to a switch statement in Java, MyBatis offers a choose element.
    
    Let’s use the example above, but now let’s search only on title if one is provided, then only by author if one is provided. If neither is provided, let’s only return featured blogs (perhaps a strategically list selected by administrators, instead of returning a huge meaningless list of random blogs).
    
        <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 ###
    
    The previous examples have been conveniently dancing around a notorious dynamic SQL challenge. Consider what would happen if we return to our "if" example, but this time we make "ACTIVE = 1" a dynamic condition as well.
    
        <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>
        </select>
    
    What happens if none of the conditions are met? You would end up with SQL that looked like this:
    
        SELECT * FROM BLOG
        WHERE
    
    This would fail. What if only the second condition was met? You would end up with SQL that looked like this:
    
        SELECT * FROM BLOG
        WHERE
        AND title like ‘someTitle’
    
    This would also fail. This problem is not easily solved with conditionals, and if you’ve ever had to write it, then you likely never want to do so again.
    
    MyBatis has a simple answer that will likely work in 90% of the cases. And in cases where it doesn’t, you can customize it so that it does. With one simple change, everything works fine:
    
        <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 knows to only insert "WHERE" if there is any content returned by the containing tags. Furthermore, if that content begins with "AND" or "OR", it knows to strip it off.
    
    If the *where* element does not behave exactly as you like, you can customize it by defining your own trim element. For example, the trim equivalent to the *where* element is:
    
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
          ...
        </trim>
    
    The *prefixOverrides* attribute takes a pipe delimited list of text to override, where whitespace is relevant. The result is the removal of anything specified in the *prefixOverrides* attribute, and the insertion of anything in the *prefix* attribute.
    
    There is a similar solution for dynamic update statements called *set*. The *set* element can be used to dynamically include columns to update, and leave out others. For example:
    
        <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>
    
    Here, the *set* element will dynamically prepend the SET keyword, and also eliminate any extraneous commas that might trail the value assignments after the conditions are applied.
    
    If you’re curious about what the equivalent *trim* element would look like, here it is:
    
        <trim prefix="SET" suffixOverrides=",">
          ...
        </trim>
    
    Notice that in this case we’re overriding a suffix, while we’re still appending a prefix.
    
    ### foreach ###
    
    Another common necessity for dynamic SQL is the need to iterate over a collection, often to build an IN condition. For example:
    
        <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>
    
    The *foreach* element is very powerful, and allows you to specify a collection, declare item and index variables that can be used inside the body of the element. It also allows you to specify opening and closing strings, and add a separator to place in between iterations. The element is smart in that it won’t accidentally append extra separators.
    
    NOTE You can pass any Iterable object (for example List, Set, etc.), as well as any Map or Array object to foreach as collection parameter. When using an Iterable or Array, index will be the number of current iteration and value item will be the element retrieved in this iteration. When using a Map (or Collection of Map.Entry objects), index will be the key object and item will be the value object.
    
    This wraps up the discussion regarding the XML configuration file and XML mapping files. The next section will discuss the Java API in detail, so that you can get the most out of the mappings that you’ve created.
    
    ### bind ###
    
    The bind element lets you create a variable out of an OGNL expression and bind 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-db vendor support ###
    
    If a databaseIdProvider was configured a "\_databaseId" variable is available for dynamic code, so you can build different statements depending on database vendor. Have a look at 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>
    
    ### Pluggable Scripting Languages For Dynamic SQL ###
    
    Starting from version 3.2 MyBatis supports pluggable scripting languages, so you can plug a language driver and use that language to write your dynamic SQL queries.
    
    You can plug a language by implementing the following interface:
    
        public interface LanguageDriver {
              
          ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
          SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
          SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
        }
    
    Once you have your custom language driver you can set it to be the default by configuring it in the mybatis-config.xml file:
    
        <typeAliases>
          <typeAlias type="org.sample.MyLanguageDriver" alias="myLanguage"/>
        </typeAliases>
        <settings>
          <setting name="defaultScriptingLanguage" value="myLanguage"/>
        </settings>
    
    Instead of changing the default, you can specify the language for an specific statement by adding the lang attribute as follows:
    
        <select id="selectBlog" lang="myLanguage">
          SELECT * FROM BLOG
        </select>
    
    Or, in the case you are using mappers, using the @Lang annotation:
    
        public interface Mapper {
              
          @Lang(MyLanguageDriver.class)
          @Select("SELECT * FROM BLOG")
          List<Blog> selectBlog();
        }
    
    NOTE You can use Apache Velocity as your dynamic language. Have a look at the MyBatis-Velocity project for the details.
    
    All the xml tags you have seen in the previous sections are provided by the default MyBatis language that is provided by the driver org.apache.ibatis.scripting.xmltags.XmlLanguageDriver which is aliased as xml.

[http_www.mybatis.org_mybatis-3_dynamic-sql.html]: http://www.mybatis.org/mybatis-3/dynamic-sql.html