In-depth explanation of Mybatis series (9)---powerful dynamic SQL

The previous article "Mybatis series in simple terms (8)---mapper mapping file configuration select, resultMap" briefly introduced the query of mybatis. So far, CRUD has been explained. This article will introduce the powerful dynamic SQL of mybatis.

So, here comes the question: What is dynamic SQL? What is the role of dynamic SQL?

With the traditional method of using JDBC, I believe that when you combine complex SQL statements, you need to splice them together. If you don't pay attention, even missing a space will lead to errors. The dynamic SQL function of Mybatis is designed to solve this problem. It can be combined into very flexible SQL statements through if, choose, when, otherwise, trim, where, set, and foreach tags, thus improving the efficiency of developers. Let’s feel the charm of Mybatis dynamic SQL:

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

As a programmer, who doesn’t 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>

##The above example: If If the incoming id is not empty, then SQL will concatenate id = #{id}. I believe everyone can understand this just by looking at it, so I won’t go into detail.

Careful people will find a problem: "You are wrong! If the id you pass in is null, then your final SQL statement will become select * from user where and deleteFlag=0, There is something wrong with this statement! "

Yes, at this time, the where tag of mybatis will make its grand debut:

##2. where, with me, the SQL statement The splicing conditions are all in the clouds! Let’s transform the above example through where:

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

Some people will ask: “What are you talking about? What the hell! Compared with the above, isn’t it just an extra where tag? Will this one also show select * from user where and deleteFlag=0? "

Indeed, on the surface, It's just an extra where tag, but in essence, mybatis processes it. When it encounters AND or OR, it knows how to deal with it. In fact, we can customize this processing rule through the trim tag.

3. trim : My territory, I decide! The where tag above can actually be represented by trim as follows:

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

It means: When WHERE is followed by AND or then When ORing, remove AND or OR. In addition to WHERE, there is actually a more classic implementation, which is SET.

4. set: Trust me, you won’t make any 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 problem comes again: "If I only have name that is not null, then wouldn't this SQL become update set name = #{name}, where... .. ? The comma after your name will cause an error! "

Yes, at this time, we can use the set tag provided by mybatis. The following is modified through the set tag:

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

这个用trim 可表示为：

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

WHERE是使用的 prefixOverrides（前缀）， SET是使用的 suffixOverrides （后缀）， 看明白了吧！

5. foreach:  你有for, 我有foreach, 不要以为就你才屌！

java中有for, 可通过for循环， 同样， mybatis中有foreach, 可通过它实现循环，循环的对象当然主要是java容器和数组。

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

将一个 List 实例或者数组作为参数对象传给 MyBatis，当这么做的时候，MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键，而数组实例的键将是“array”。同样， 当循环的对象为map的时候，index其实就是map的key。

6. choose:  我选择了你，你选择了我！

Java中有switch,  mybatis有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>

以上例子中： 当title和author都不为null的时候， 那么选择二选一（前者优先）， 如果都为null, 那么就选择 otherwise中的， 如果tilte和author只有一个不为null, 那么就选择不为null的那个。

纵观mybatis的动态SQL， 强大而简单， 相信大家简单看一下就能使用了。

好啦，本次就写到这！下篇文章将结合mybatis的源码分析一次sql语句执行的整个过程。

 

 以上就是深入浅出Mybatis系列（九）---强大的动态SQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！