In-depth introduction to the Mybatis series (9)-powerful dynamic SQL statements and a simple introduction to mybatis

In-depth introduction to the Mybatis series (9)-powerful dynamic SQL statements and a simple introduction to mybatis

In the previous article, "go deep into Mybatis series (8) --- select and resultMap for mapper ing file configuration" briefly introduces the query of mybatis. Now, CRUD has been completed. This article describes the powerful dynamic SQL statements of mybatis.

So the question is: what is dynamic SQL? What is the role of dynamic SQL?

In the traditional method of using JDBC, we believe that splicing is required when you combine complex SQL statements. If you do not pay attention to it, even if a space is missing, errors will occur. To solve this problem, Mybatis uses the if, choose, when, otherwise, trim, where, set, and foreach labels to form flexible SQL statements, this improves the efficiency of developers. Let's take a look at the charm of Mybatis dynamic SQL:

 

1. if: You can judge, and I can also judge!

As a programmer, no one knows 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>

In the preceding example, if the input id is not empty, the SQL statement is used to concatenate id =#{ id }. I believe that everyone can understand the same, not to mention.

Careful people will find a problem: "You are wrong! If the input id is null, your final SQL statement will not become select * from user where and deleteFlag = 0. This statement has a problem !"

Yes. At this time, the where tag of mybatis should be unveiled:

 

2. where, with me, the SQL statement splicing conditions are cloudization!

Let's use where to transform the above example:

<select id="findUserById" resultType="user">           select * from user where            <where>               <if test="id != null">                   id=#{id}               </if>               and deleteFlag=0;           </where> </select>

Some may ask, "what are you doing! Compared with the above, there is more than a where tag! Will this still show select * from user where and deleteFlag = 0 ?"

Indeed, on the surface, a where label is added, but in essence, mybatis processes it. When it encounters and or, it knows how to handle it. In fact, we can use trim tags to customize such processing rules.

 

3. trim: My website. I am the master!

The above where tag can be represented as follows using trim:

<trim prefix="WHERE" prefixOverrides="AND |OR ">  ... </trim>

It means to remove and or when the WHERE clause is followed by and or. In addition to WHERE, there is actually a classic implementation, that is, SET.

 

4. set: Trust me, no error!

<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 problem arises again: "If I only have a name that is not null, then this SQL statement will not become update set name = # {name}, where ........? The comma following your name will cause an error !"

Yes. At this time, we can use the set tag mybatis provides for us. The following is the transformation through the set Tag:

<update id="updateUser" parameterType="com.dy.entity.User">           update user set            <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>

Trim can be used to indicate:

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

WHERE is the prefixOverrides used, and SET is the suffixOverrides used (suffix). See it!

 

5. foreach: you have for, I have foreach, don't think it's just you!

In java, for can be implemented through a for loop. Similarly, in mybatis, foreach can be used to implement a loop. Of course, the circular objects are mainly java containers and arrays.

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

Send a List instance or array as a parameter object to MyBatis. In this case, MyBatis automatically wraps it in a Map and uses the name as the key. A List instance uses "list" as the key, while an array instance uses "array" as the key ". Similarly, when the cyclic object is map, the index is actually the key of the map.

 

6. choose: I chose you. You chose me!

Switch exists in Java, and choose exists in mybatis.

<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 neither title nor author is null, select either of them (the former takes precedence). If both are null, select otherwise, if tilte and author have only one non-null value, select the non-null value.

Looking at the dynamic SQL of mybatis, it is powerful and simple. I believe you will be able to use it after a brief look.

Okay, this is what we wrote this time! In the next article, we will analyze the entire process of executing an SQL statement based on the source code of mybatis.