The most commonly used dynamic SQL statement grooming-Share to the small partners who use MyBatis!

Company projects have been using MyBatis as a persistent layer framework, natural, dynamic SQL is also more written, the most common is to use the IF tag in query statements to dynamically change the filter conditions. One of the great features of MyBatis is its dynamic SQL, which eliminates the hassle of splicing SQL, which is common and uncommon in the development of your project, and you can now summarize this piece.

• 　If
• Choose (when,otherwise)
• Trim (Where,set)
• Foreach

If

 <select id= "getcategory" parametertype= "Eshopshopcategory" resultMap= " Eshopcategory "> select * from CATEGORY t WHERE ( 1=1)  <if  test=" Shopcategoryid!=null ">  and t.category_id  =#{shopcategoryid} </if  > <if  test= "    Shopcategoryname!=null "> and t.category_name like  '%${shopcategoryname}% ' </if  > <if  test= "Shopid==null"  > and t.shop_id  =0 </if  > ORDER by sequence_no  </select>  

　　This is typically used for multi-conditional combination queries.

<insert id= "Addcategory" parametertype= "Eshopcategory" >INSERT INTO CATEGORY (<ifTest= "Shopcategoryname!=null and shopcategoryname!=" >Shop_category_name,</if> <ifTest= "Shopid!=null and shopid!=" >shop_id,</if>add_time) VALUES (<ifTest= "Shopcategoryname!=null and shopcategoryname!=" >#{shopcategoryname,jdbctype=VARCHAR},</if> <ifTest= "Shopid!=null and shopid!=" >#{shopid,jdbctype=NUMERIC},</if>current_timestamp)
</insert> 

This applies when the database has a default value, so you can not insert null values.

<update id= "Updateproductcategory" parametertype= "eshopshopcategory" >      update CATEGORY t set       <if test= "Shopcategoryname!=null" >          T.category_name=#{shopcategoryname,jdbctype=varchar},      </if>      <if test= "Updateuser!=null" >          T.update_user=#{updateuser,jdbctype=varchar},      </if>      T.update_time=current_timestamp      where t.shop_category_id=#{shopcategoryid,jdbctype=numeric}</ Update>

This dynamic modification of the statement is used very much, because many times when we do the modification operation is not sure which fields to modify (which properties), there may be a need to save the original value of the same time, you can do dynamic SQL, you create a new object will need to modify the field to enclose the new value, A property that does not have to be modified is null on this object and can be partially modified by invoking this dynamic SQL.

Choose,when,otherwise

Scenario: We don't want to use all the conditional statements, we just want to choose one or two. In this case, MyBatis provides the Choose element, which is a bit like a switch statement in Java. (I feel it's kind of like providing a variety of conditional rules that can be combined when they're written together.)

resulttype= "blog" >  * 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>      = 1    </otherwise>  </choose></select>

So far, I have not used the choose, and then pay more attention.

Trim,where,set

In order to avoid when the if dynamic condition is not established, or if the first condition is not established when the second condition is established, such as "select * from TableA where" or "select * from TableA and where" morbid SQL, we can use trim, Where,set label to resolve.

 <select id= "findactivebloglike" resulttype  = "Blog" > select  * from BLOG  <WHERE>  <if  test= "state! = null" > State  = #{state}  </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> 

In practical applications, I usually do not write where tags, but after the WHERE keyword plus 1=1 conditions. That is, you can always get the complete sql:select * from a where 1=1, regardless of whether there is a dynamic condition ...

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

Foreach

foreach can sometimes be encountered in a project, and more than once, it needs to be a bit of a brain. Typically used to filter out a set of multiple values or exclude multiple values of the scene, it is clear, that is, we used to write SQL in and not when: (The collection is dynamically indeterminate, need to pass the value from the foreground)

<select id= "Selectnuminorder" resulttype= "String" >     Select count (0) from order a LEFT join item B on A.O rder_id = b.order_id     where a.status in (' 1 ', ' 2 ', ' 3 ', ' 5 ')      <if test= "List.size () > 0 "> and          b.phone_num           in<foreach item=" numberlist "collection=" list "open=" (" Separator= "," close= ")" >              #{numberlist.num}          </foreach>     </if></ Select>

<select id= "Selectpostin" resulttype= "Domain.blog.Post" >  * from  Post P  WHERE ID in   <foreach item= "item" index= "index" collection= "list"      open= "(" separator= "," close= ")" >         #{item}  </foreach></select>

The functionality of the Foreach element is very powerful, allowing you to specify a collection that declares the collection items and index variables that can be used within the body of the element. It also allows you to specify the opening and closing of a matching string and place the delimiter in the middle of the iteration. This element is very intelligent, so it does not accidentally append extra separators.

Note that you can pass a List instance or array as a parameter object to MyBatis, and when you do this, MyBatis will automatically wrap it in a Map with the name key. The list instance will be "list" as the key, and the array instance key will be "array".

The above is a combination of http://mybatis.github.io/mybatis-3/zh/getting-started.html and their own development of the more common summary out, today to comb, share to everyone!

The most commonly used dynamic SQL statement grooming-Share to the small partners who use MyBatis!