MyBatis series (ix)---powerful dynamic SQL (reprint)

Original source: http://www.cnblogs.com/dongying/p/4092662.html

In the article "Simple MyBatis Series (eight)---Mapper mapping file configuration of Select, Resultmap" simply introduced the MyBatis query, so far, Crud has been finished. This article describes MyBatis's powerful dynamic SQL.

So, here's the question: What is dynamic SQL? How does dynamic SQL work?

The traditional method of using JDBC, I believe that in the combination of complex SQL statements, we need to go to the stitching, a little attention to even less space, will lead to errors. MyBatis's dynamic SQL capabilities are designed to solve this problem by combining the IF, choose, when, otherwise, trim, where, set, and foreach tags into very flexible SQL statements to improve developer productivity. Let's feel the charm of mybatis dynamic sql:

1. If: You can judge, I can judge!

As a program ape, who does not understand if! You can also use if in MyBatis:

<select id= "Finduserbyid" resulttype= "user" >           select * from user where            <if test= "id! = NULL" >               ID =#{id}           </if> and            deleteflag=0;</select>

Above example: if the incoming ID is not empty, then the SQL will be stitching id = #{id}. This believes that we can see the same, do not say much.

Careful people will find a problem: "You this is not right ah!" If your incoming ID is null, then your final SQL statement will not be a select * from user where and deleteflag=0, this statement has a problem! ”

Yes, at this time, MyBatis's where label should be a grand debut:

2. Where, with me, SQL statement stitching conditions God horse is a cloud!

Let's transform the above example through where:

<select id= "Finduserbyid" resulttype= "User" >           
           <where>               <if test= "id! = NULL" >                   id=#{id}               </if> and               deleteflag=0;           </where> </select>

Some people are going to ask: "What are you all this stuff!" Compared with the above, is not a more where label it! Will this still happen? SELECT * from user where and deleteflag=0? ”

Indeed, on the surface, there is a more where tag, but in essence, MyBatis is dealing with it, and when it encounters and or OR, it knows how to handle it. In fact, we can customize this processing rule through the trim tag.

3. Trim: My turf, I'm the Boss!

The where label above, in fact with trim can be expressed as follows:

<trim prefix= "WHERE" prefixoverrides= "and |or" >  ... </trim>

It means: When the where is followed by and or or, remove and OR OR. In addition to where, there is actually a more classic implementation, that is set.

4. Set: Believe me, no mistakes!

<update id= "UpdateUser" parametertype= "Com.dy.entity.User" >           update User set            <if test= "Name! = NULL" >               name = #{name},           </if>            <if test= "Password! = null" >               password = #{password},           </ if>            <if test= "Age! = null" > Age               = #{age}           </if>            <where>               <if test= "id! = Null ">                   id = #{id}               </if> and               deleteflag = 0;           </where></update>

The question comes again: "If I only have name is not NULL, then this SQL does not become the update set name = #{name}, where ...?" The comma behind your name will cause an error! ”

Yes, at this time, you can use MyBatis to provide us with the set tag. The following is modified by the set label:

<update id= "UpdateUser" parametertype= "Com.dy.entity.User" >           update User
        <set>          <if test= "Name! = NULL" >name = #{name},</if>              <if test= "Password! = null" > Password = #{password},</if>              <if test= "Age! = null" >age = #{age},</if>         </set>           <where>               <if test= "id! = NULL" >                   id = #{id}               </if> and               deleteflag = 0;           </where></update>

This trim can be expressed as:

<trim prefix= "SET" suffixoverrides= "," >  ...</trim>

Where is the prefixoverrides (prefix) used, set is the use of the suffixoverrides (suffix), see it!

5. Foreach: You have for, I have foreach, don't think just you dick!

There is a for in Java, and with a For loop, as well, there is a foreach in the mybatis that can be used to loop through it, and the object of the loop is, of course, the Java container and the array.

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

Pass a List instance or array as a parameter object to MyBatis, and when you do, MyBatis automatically wraps 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". Similarly, when the loop object is a map, index is actually the key of the map.

6. Choose: I chose you, you chose me!

There is a switch in Java, MyBatis has choose.

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

In the above example: when the title and author are not NULL, then choose Two to select one (the former first), if all is null, then select otherwise, if Tilte and author only one is not NULL, then select the one that is not NULL.

Throughout the MyBatis dynamic SQL, powerful and simple, I believe that we can use a simple look.

Well, this is the time to write this! The next article will analyze the entire process of SQL statement execution in conjunction with MyBatis's source code.

MyBatis series (ix)---powerful dynamic SQL (reprint)