使用Mybatis遇到需要拼接/复用sql问题？快来试试Mybatis的动态sql吧

一、前言

---

我们在使用mybatis的时候，很多时候我们需要自定义sql。通常我们会在mapper文件上写上如下操作。

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE state = ‘SHOW’
</select>

假设现在增加一个条件，当业务有传时间参数的时候，我们就在后面加上and createTime >= #{time}，没有参数就不加。

我们有两种方法：

第一种是在加一个$开头的类型占位符，在后端java中动态补充sql语句。这种方法也可行，但耦合性太强。

第二种是使用mybatis中的动态sql，在末尾加上这句话即可。

<if test="time != null">
    AND createTime >= #{time}
</if>

 

二、使用Mybatis的动态sql

---

mybatis的动态sql有以下几种：

1. if

2. choose (when, otherwise)

3. trim (where, set)

4. foreach

5. sql片段

（1）if 标签

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE state = ‘SHOW’
    <if test="time != null">
        AND createTime >= #{time}
    </if>
</select>

通过上述例子，我们可以看出其含义：

当time不为空（也就是time这个参数传值过来），就添加<if test="time != null">AND createTime >= #{time}</if>这条动态sql进去；如果为空，这段动态sql就相当于没有一样。

 

（2）choose (when, otherwise)标签

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE state = ‘SHOW’
    <choose>
    <when test="type != null">
      AND type = #{type}
    </when>
    <when test="sex != null">
      AND sex like #{sex}
    </when>
    <otherwise>
      AND status = 1
    </otherwise>
  </choose>
</select>

这种结构更像是if-else的类型。

当A满足条件时，增加A中的动态sql；当B满足条件时，增加B中的动态sql；当A和B条件都不符合时，增加C中的动态sql。

 

（3）trim (where)标签

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE 
    <if test="status != null">
        status = #{status}
    </if>
    <if test="time != null">
        AND createTime >= #{time}
    </if>
    <if test="sex != null">
        AND sex = #{sex}
    </if>
</select>

我们看上述动态sql，第一眼看上去没什么问题。可是细细观察会发现，当某些条件不符合的时候会变成以下一些异常的sql

  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE 


或者是

  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE 
        AND createTime >= #{time}
        AND sex = #{sex}

这都是我们不希望看到的，我们希望他能执行以下操作：当条件都不满足时，自动隐藏where；或者某一些条件满足时，而且动态sql拼接前是where，自动删掉and、or。

这时候，我们就可以用到mybaits中的where标签或者trim标签了。

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  <where>
        <if test="status != null">
            status = #{status}
        </if>
        <if test="time != null">
            AND createTime >= #{time}
        </if>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
    </where>
</select>

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

（4）foreach标签

动态sql中还有一个经常用到的，就是对集合进行遍历。尤其是传进的参数是数组，而我们的sql需要使用in的时候。

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  WHERE ID in
  <foreach item="ids" index="index" collection="list" open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符

（5）sql片段

有时候，对于某段代码，我们复用率特别高，而且我们希望能封装起来，即使修改的时候，我们也只需要改这个封装起来的sql即可。

首先，我们可以对重复性高的代码使用如下标签去封装它。

<sql id="base_status">
     status in (0,5,6,9,75)
</sql>

当我们使用的时候，我们只需要引入即可

<select id="findSuncat" resultType="Suncat">
  SELECT * FROM table
   left table1 on table.id = table.tq_id
  <where>
        <!-- refid，sql片段的标识 -->
        <include refid="base_status"></include>
  </where>
</select>

这样就可以重复利用写好的sql，省了不少代码。