1. 问题概述

我们在使用mybatis的<if>、<choose>标签动态生成sql的时候，由于参数的不同，有可能出现生成的错误sql的问题，最常见的有以下两种：

where后面的and/or问题
update中多余的逗号问题

错误一：

 <select id="selectByCriteria" resultMap="studentMap">
     select id,name,age 
     from student
     where
     <if test="name!=null and name!=''">
         name like concat('%',#{name},'%')
     </if>
     <if test="age!=null">
         and age=#{age}
     </if>
 </select>

如果name不是null或者空字符串，那么生成的sql是正常的，没有问题；如果name为null或者空字符串，那么生成的sql为：

select id,name,age from student  where and logo=?

这个sql肯定会报错，需要把“and”去掉。

错误二：

<update id="updateByPrimaryKey" parameterType="com.test.student">
    update student
    set
    <if test="name!=null">name = #{name},</if>
    <if test="age!=null">age = #{age},</if>
    <if test="address!=null">address = #{address}</if>
where id = #{id,jdbcType=BIGINT}

如果上面三个值都不为null，那么，生成的sql语句没有任何问题，如果address为null，那么生成的语句为：

update student set name=?,age=?, where id = ?

问题很明显，where前面多了个逗号。

2. where问题的解决方法

错误一的解决方法有三种，一种是我们在很早以前（通过java拼装sql）就一直在用的方法，就是在where后加上1=1；另一种方法就是使用mybatis的<where>标签；第三种是使用mybatis的<trim>标签。

2.1 where后加1=1

这种方法比较简单了，还是上面那个例子，可以将上面的例子改成如下方式：

<select id="selectByCriteria" resultMap="studentMap">
     select id,name,age 
     from student
     where 1=1            ##新添加
     <if test="name!=null and name!=''">
         and name like concat('%',#{name},'%') ##添加and
     </if>
     <if test="age!=null">
         and age=#{age}
     </if>
</select>

我们直接在where后添加1=1，然后后面的每个if中都是用and或者or开头即可，这样，无论哪个为null，生成的sql语句都没有问题。如果全部为null，那么生成的sql为：

select id,name,age from student where 1=1

2.2 使用where标签

<where>标签的作用是只有在子元素返回内容的情况下才会插入where子句（也就是说如果where标签没有任何子元素，那么where将不会被拼接到sql中），除此之外，它还有一个功能就是如果字句以and或者or开头，将会被自动除去。上面的例子使用where标签修改为：

 <select id="selectByCriteria" resultMap="studentMap">
     select id,name,age 
     from student
     <where>
         <if test="name!=null and name!=''">
             name like concat('%',#{name},'%')
         </if>
         <if test="age!=null">
             and age=#{age}
         </if>
      </where>
 </select>

如果name为null，age不为null，将会生成where子句，但是age中是以and开头的，and将会被去掉。sql为：

select id,name,age from student where age=？

如果name和age都为null，将不会生成where子句，sql为：

select id,name,age from student

2.3 使用trim标签

<trim>标签的作用主要是在它所包含的内容上添加前缀、后缀，删除它所包含内容的前缀、后缀。主要包含以下几个属性：

prefix：在所包含的内容上添加指定的前缀。
suffix：在所包含的内容上添加指定的后缀。
prefixOverrides：删除所包含内容指定的前缀。
suffixOverrides：删除所包含元素指定的后缀。

现在使用trim标签来改造错误一中的问题，如下：

<select id="selectByCriteria" resultMap="studentMap">
     select id,name,age 
     from student
     <trim prefix="where" prefixOverrides="and">  ##添加trim标签
         <if test="name!=null and name!=''">
             name like concat('%',#{name},'%')
         </if>
         <if test="age!=null">
             and age=#{age}
         </if>
    </trim>
 </select>

在上面的配置中，如果name为null，而age不为null，如果没有trim，生成的sql为：

select id,name,age from student  and age=?

由于trim中prefix="where"的作用，将会添加where前缀，也就是：

select id,name,age from student where  and age=?

由于trim中prefixOverrides="and"的作用，将会去除开头的and，也就是：

select id,name,age from student where   age=?

如果说name和age都是null，会出现什么情况？<trim>标签和<where>标签一样，如果不包含任何内容，那么，prefix和suffix都不会插入任何东西。

还有一点需要注意，那就是如果prefixOverrides或者suffixOverrides如果包含多个元素，用“|”分隔，例如：

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

并且AND和OR后面的空格是必须的。

3. update问题的解决方法

错误二的解决方法有两种，一种是通过<set>标签来解决；另一种也是通过<trim>标签来解决。

3.1 使用set标签

set标签的作用和where标签的作用类似，主要有两个：

在包含的内容前面插入set标签。
删除掉额外的逗号。

我们使用set标签改造错误二的配置，如下：

<update id="updateByPrimaryKey" parameterType="com.test.student">
    update student
    <set>
        <if test="name!=null">name = #{name},</if>
        <if test="age!=null">age = #{age},</if>
        <if test="address!=null">address = #{address}</if>
    </set>
    
where id = #{id,jdbcType=BIGINT}

如果address为null而其他不为null，在没有set标签的情况下，sql语句为：

update student set name=?,age=?, where id = ?

在使用set标签的情况下，会自动去除语句中多余的逗号，就生成了正确的sql：

update student set name=?,age=? where id = ?

3.2 使用trim标签

和问题一类似，问题二也可以使用trim标签来解决，改造完如下：

<update id="updateByPrimaryKey" parameterType="com.test.student">
    update student
    <trim prefix="set" suffixOverrides=",">
        <if test="name!=null">name = #{name},</if>
        <if test="age!=null">age = #{age},</if>
        <if test="address!=null">address = #{address}</if>
    </trim>
where id = #{id,jdbcType=BIGINT}

prefix="set"将在包含内容不为空的情况下添加“set”，suffixOverrides=","将删除尾部的“,”，从而生成了正确的sql语句。

4. 总结

<where>标签：

在<where>标签包含内容不为空的情况下，在内容的开头添加where子句。
如果返回的内容是以and或者or开头，将自动删除掉。

<set>标签：

在<set>标签包含内容不为空的情况下，在内容的开头添加set子句。
自动删除掉包含内容结尾的逗号。

<trim>标签：

prefix：在所包含的内容上添加指定的前缀。
suffix：在所包含的内容上添加指定的后缀。
prefixOverrides：删除所包含内容指定的前缀。
suffixOverrides：删除所包含元素指定的后缀。

玖叶教程网

前端编程开发入门

解决mybatis动态生成sql错误的问题