MyBatisのDynamicSQL(ダイナミックSQL,if,chose,when,otherswise,trim,where,set,foreach,bind)

中国語の翻訳
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.chose、when、otherswiseは、すべての条件を適用したくないです.多くの選択肢の中で だけを望んでいます.Javaのswitch またはif{}else if{}else{}と同様に、MyBatisはselectの方式chose、when、otherswiseを提供しています.
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フィールド=値にの特効が付されています.つまり、自動的に最後の余剰カンマを削除します.
もしudate文が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を下付きとしてもいいです.直接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は、簡単に#{item}によって、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
Dynamic SQL
One of the most powerful feature s of MyBatis has always been its Dynamic SQL capability.If You have any experience with JDBC or any simillar frame ork,you unded how painful it is to conditinally thetens Station Stensmaking sure not to forget spaces or to omit a commat the end of a list of columns.Dynamic SQL can be downright painful to deal with.
While working with Dynamic SQL will never be party、MyBatis certainly improves the situation with a powerful Dynamic SQL lagge that can be used within mapped SQL statement.
The Dynamic SQL elemens shoud be familliar to anyone who has used JSTL or any simiar XML based text processors.In previous of MyBatis,there were a lot of elemens to know and unders.MyBations 3and now there are less than half of those elements to work with.MyBatis employs powerful ONL based expressitions to eliminate most of the other elements:

if

chose(when、otherswise)

trim

foreach

if
The most common thing to do in dynamic SQL is conditionlyinclude a part of a where clause.For example:

 id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
   test="title != null">
    AND title like #{title}
  

This statement would provide an optional text search type of functionity.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 liketer
What if we wanted to optitionaly search by title and author?First,I’d change the name of the statement to make more sense.The n simply add another condition.

 id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
   test="title != null">
    AND title like #{title}
  
   test="author != null and author.name != null">
    AND author_name like #{author.name}
  

chose,when,otherswise
Sometimes we don’t want all of the conditions to appy,instead we to chose only one case among maning.Simiar to a switch statement in Java,MyBatis ofers a chose 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 feature d bloges.

 id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  
     test="title != null">
      AND title like #{title}
    
     test="author != null and author.name != null">
      AND author_name like #{author.name}
    
    
      AND featured = 1
    
  

trim,where,set
The previous examples have been convently dancing around a notonius dynamic SQL challing.Consinder what would happen if we return to our“if”example,but this time we make“ACT IVE=1”a.infont.infont.infont.

 id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE
   test="state != null">
    state = #{state}
  
   test="title != null">
    AND title like #{title}
  
   test="author != null and author.name != null">
    AND author_name like #{author.name}
  

What happens if none of the conditions arement?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 easury soved with conditional ls,and if you’ve had to write it,then you likely never to 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 sothat.With one simple change,rything works fine:

 id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  
     test="state != null">
         state = #{state}
    
     test="title != null">
        AND title like #{title}
    
     test="author != null and author.name != null">
        AND author_name like #{author.name}
    
  

The where element knows to only insert“WHERE”if there is any content returned by the containing tags.Frerthere more,if that content begins with“AND”or“OR”,it knows to strit off.
If the where element does behave exactly as you like,you can customize it by defining your own trim element.For example,the trim equivent to the where elemens:

 prefix="WHERE" prefixOverrides="AND |OR ">
  ...

The prefixOverrides atribute tars a pipe delimited list of text to override、where whites pace is relevant.The reult is the removal of anything specified in the prefixOverrides atribute、and the the the insent the the firtion of
The re is a simiar solution for dynamic udate statemens caled set.The set element can be used to dynamically include columns to udate,and leave out others.For example:

 id="updateAuthorIfNecessary">
  update Author
    
       test="username != null">username=#{username},
       test="password != null">password=#{password},
       test="email != null">email=#{email},
       test="bio != null">bio=#{bio}
    
  where id=#{id}

Here、the set element will dynamically prepend the SET keyword、and also eliminate any extraneous comas that might trol the value assignment after the conditions ares.
If you’re curious about what the equivalent trim element would look like,here it is:

 prefix="SET" suffixOverrides=",">
  ...

Notice that in this case we’re overriding a suffix,while we’re still apending a prefix.
foreach
Another common necessity for dynamic SQL is the need to iterate over collection,oten to build an IN condition.For example:

 id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
   item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  

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 allows you to specify opening and cliningstring stringand add a separator to placce in between iteration.The element is smart in that it won’t accidentally apped extra separators.
NO TE You can pass any Iterable oject(for example List、Set、etc.)、as well asany Map or Aray Oray ooject to foreach collection parameter.When using an Iterablor Aray、index will be thenumbebeber the ininininininininbebebebebebebebebebeber of of of proproprorererererererererererererererererererererererererererererererevevevevevevevevevestststststststststststststststststststrererererererererererere. Entry object)index will be the key object and item will be the value oject.
This wraps up the discussion regarding the XML configration file and XML mapping files.The next section will discuss the Java API in detail,so that you can get the mappines'crted.
ビッド
The bind element lets you create a variable out of an ONL expression and bind it to the context.For example:

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

Multi-dbベンダーsupport
If a databaseIdProvider was configred a「au databaseId」variable is available for dynamic code、so you can build different statemens depending on database ventr.Have a look at the follexample:

 id="insert">
   keyProperty="id" resultType="int" order="BEFORE">
     test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    
     test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    
  
  insert into users values (#{id}, #{name})

Pluggable Script ing Languags For Dynamic SQL
Starting from version 3.2 MyBatis supports pluggable scripting langgge、so you can plugge a langage driver and use that langage to write your dynamic SQL queries.
You can plug a langage by implement 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 lagage driver you can set it to be the default by configling it in the mybatis-config.xml file:

   type="org.sample.MyLanguageDriver" alias="myLanguage"/>


   name="defaultScriptingLanguage" value="myLanguage"/>

Instead of change the default,you can specify the langage for an specific statement by adding the lang atribute as follows:

 id="selectBlog" lang="myLanguage">
  SELECT * FROM BLOG

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 langage.Have a look at the MyBatis-Velocity project for the detail.
All the xml tags You have seen in the previous sections are provided by the default MyBatis lagge that is provided by the driver org.apaache.ibatis.scripting.xmltags.XmlLanguage Driver whiseass.