"Database _mysql" Mysql Dynamic statement if set choose where foreach Trim

MyBatis's dynamic SQL is based on the OGNL expression, which helps us to implement some logic in SQL statements conveniently.

The elements used to implement dynamic SQL in MyBatis are:

• If
• Choose (when,otherwise)
• Trim
• where
• Set
• Foreach

ifis simple conditional judgment, we can implement some simple condition selection by using the IF statement. Let's take a look at the following example:

 <select id= "dynamiciftest" parametertype= "blog" resulttype= "blog" > select /span>* from t_blog where one = 1 <if  test= "Title! = NULL" > and title  = #{title}  </if  > <if  test=" Content! = NULL ">   and content  = #{content}  </if  > <if  test= "Owner! = null" > and owner  = #{owner}  </if  > </select> 

The meaning of this statement is very simple, if you provide the title parameter, then must satisfy Title=#{title}, also if you provide content and owner, they also need to meet the appropriate conditions, and then return to meet these conditions of all the blog, This is a very useful feature, in the past we use other types of framework or direct use of JDBC, if we want to achieve the same selection effect, we need to spell the SQL statement, which is extremely troublesome, compared to the above dynamic SQL is much simpler. ChooseThe function of the element is equivalent to the switch statement in Java, which is basically the same as the function and usage of choose in Jstl, usually paired with when and otherwise. Look at one of the following examples:

 <select id= "dynamicchoosetest" parametertype= "blog" resulttype= "blog" > Select  * from T_blog where one = 1 <choose> <when test= "Title! = NULL" > and title  = #{title}  </when> & Lt;when test= "Content! = null" > and content  = #{content}  </when> <otherwise> and own Er  = "owner1" </otherwise> </choose> </select> 

When the element indicates that when the conditions in the time when the content of the output, and the switch effect in Java is similar to the order of the conditions, when there is a condition to meet the time, will jump out of choose, that is, all the when and otherwise conditions, Only one will output, and when all of my conditions are not satisfied, I output the contents of otherwise. So the meaning of the above statement is very simple, when the title!=null output and Titlte = #{title}, no longer judge the condition, when the title is empty and content!=null output and content = #{content}, Outputs the contents of the otherwise when all conditions are not met. whereThe main purpose of the statement is to simplify the judgment of the conditions in the Where in the SQL statement, and first look at an example and then explain the benefits of where.

 <select id= "dynamicwheretest" parametertype= "blog" resulttype= "blog" > Select  * from T_blog  <where> <if  test=" Title! = NULL "> title  = #{title}  </if  > <if
     test= "Content! = null" > and content  = #{content}  </if  > <if   test= "Owner! = null" > and owner  = #{owner}  </if  > </where> </select> 

The purpose of the where element is to output a where element where it is written, and another benefit is that you do not need to consider what the conditional output inside the where element looks like, and MyBatis will intelligently handle it for you. If all the conditions are not met then MyBatis will find all the records, if the output is and start, MyBatis will be the first and ignore, of course, if the or start, MyBatis will ignore it, in addition, in the WHERE element you do not need to consider the question of spaces, MyBatis will be smart to help you add. As in the example above, if title=null, and content! = NULL, then the entire statement of the output would be a select * from t_blog where content = #{content} instead of select * from T_blog w Here and content = #{content}, because MyBatis will intelligently ignore the first and or OR. TrimThe main function of the element is that you can add some prefixes to the content that you include, or you can add some suffixes to it, and the corresponding properties are prefix and suffix; you can overwrite some content of the header containing the content, that is, ignore it, or you can overwrite some content of the trailer. The corresponding properties are prefixoverrides and suffixoverrides; Because trim has such functionality, we can also use trim to replace the where element's functionality very simply, as shown in the following example code:

<select id= "dynamictrimtest" parametertype= "blog" resulttype= "blog" >Select*From T_blog<trim prefix= "where" prefixoverrides= "and |or" > <iftest= "Title! = NULL" >title=#{title}</if> <iftest= "Content! = NULL" >and content=#{content}</if> <iftest= "Owner! = NULL" >or owner=#{owner}</if> </trim> </select>

The set element is mainly used in the update operation, its main function and the where element is actually similar, mainly in the containing statement before the output of a set, and then if the included statement is terminated with a comma, the comma will be ignored, If the set contains empty content, an error occurs. With the set element, we can dynamically update those fields that have been modified. Here is a sample code:

 <update id= "dynamicsettest" parametertype= "Blog" > update t_blog  &              lt;set> <if  test= "Title! = NULL" > Title  = #{title},  </if  &          Gt <if  test= "Content! = null" > content 
    = #{content},  </if  > & Lt if  test= "Owner! = null" > owner  = #{owner}  </if  > </set>
    
      where ID  =
      #{id}  </update> 
    

In the example code above, if a condition in set is not satisfied, the content contained in the set is empty and an error is given. foreachis used primarily in the build in condition, which can iterate a collection in an SQL statement. The properties of the Foreach element are mainly item,index,collection,open,separator,close. The item represents the alias of each element in the collection when it iterates, and index specifies a name that represents the position at which each iteration occurs during the iteration, and open indicates what the statement begins with, and separator indicates what symbol is used as the delimiter between each iteration. Close means the end, the most critical and error-prone when using foreach is the collection property, which must be specified, but in different cases the value of the property is not the same, there are 3 main cases:

1. If a single parameter is passed in and the parameter type is a list, the collection property value is List
2. If a single parameter is passed in and the parameter type is an array, the value of the collection property is array
3. If the parameters passed in are multiple, we need to encapsulate them into a map, of course, the single parameter can also be encapsulated asa map, in fact, if you pass in the parameter, in the MyBatis will also wrap it into a map, the map key is the parameter name, So this time the collection property value is the key to the incoming list or array object in its own encapsulated map

Here are the sample code for each of the three cases: 1. The type of the single parameter list:

<select id= "Dynamicforeachtest" resulttype= "Blog" >      * from      t_blog where IDin < foreach collection= "list" index= "index" item= "Item" open= "(" separator= "," close= ")" >          #{item}       </foreach>  

The value of the above collection is list, the corresponding Mapper is this

 Public List<blog> dynamicforeachtest (list<integer> IDs);  

Test code:

  @Test  public  void   Dynamicforeachtest () {sqlsession session  = UTIL.G      Etsqlsessionfactory (). Opensession (); Blogmapper blogmapper  = session.getmapper (Blogmapper.      Class   <Integer> ids = new  arraylist<integer> ();      Ids.add ( 1 3 6 <Blog> Blogs = blogmapper.dynamicforeachtest (IDs);  for   (Blog blog:blogs) System.out.printl      n (blog);  Session.close (); } 

2. Type of single-parameter array arrays:

<select id= "Dynamicforeach2test" resulttype= "Blog" >      * from      t_blog where IDin < foreach collection= "array" index= "index" item= "Item" open= "(" separator= "," close= ")" >          #{item}       </foreach>  </select>  

The above collection is an array, corresponding to the mapper code:

 Public List<blog> dynamicforeach2test (int[] IDs);  

The corresponding test code:

@Test    Public void dynamicforeach2test () {      = util.getsqlsessionfactory (). Opensession ();       = Session.getmapper (blogmapper.  Class);       int New int [] {1,3,6,9};      List<Blog> blogs = blogmapper.dynamicforeach2test (IDs);        for (Blog blog:blogs)          SYSTEM.OUT.PRINTLN (blog);      Session.close ();  }  

3. Encapsulate the parameters as a map type

<select id= "Dynamicforeach3test" resulttype= "Blog" >      * from T_blog where title like "%" #{title} "%" and ID       in <foreach collection= "IDs" index= "index" item= "Item" open= "(" separator= "," close= ")" >          #{item}      </foreach>  </select>

The value of the above collection is IDs, which is the key of the incoming parameter map, corresponding to the mapper code:

 Public List<blog> dynamicforeach3test (map<string, object> params);  

Corresponding Test code:

@Test Public voiddynamicforeach3test () {sqlsession session=util.getsqlsessionfactory (). Opensession (); Blogmapper Blogmapper= Session.getmapper (blogmapper.class); Finallist<integer> ids =NewArraylist<integer>(); Ids.add (1); Ids.add (2); Ids.add (3); Ids.add (6); Ids.add (7); Ids.add (9); Map<string, object> params =NewHashmap<string, object>(); Params.put ("IDs", IDS); Params.put ("title", "China"); List<Blog> Blogs =blogmapper.dynamicforeach3test (params);  for(blog blog:blogs) System.out.println (blog);  Session.close (); }  

"Database _mysql" Mysql Dynamic statement if set choose where foreach Trim