Mybatis动态插入数据、更新数据

有时我们控制同一张表，但传入的参数不固定，操作表的字段不固定，就要用到mybatis动态插入。xml如下：

<insert id="insertMessage" parameterType="com.sf.ccsp.member.client.request.MessageReq">
       insert cx_customer_message
       (ID,MEMBERID,
         <if test='messageClassify != null and messageClassify != "" '>
             MESSAGEE_CLASSIFY,
         </if>
         <if test='messageCode != null and messageCode != "" '>
             MESSAGE_CODE,
         </if>
         <if test='messageContent != null and messageContent != "" '>
             MESSAGE_CONTENT
         </if>
        )
       values (
         #{id, jdbcType=VARCHAR},
         #{memberId, jdbcType=VARCHAR},
          <if test='messageClassify != null and messageClassify != "" '>
            #{messageClassify, jdbcType=VARCHAR},
          </if>
          <if test='messageCode != null and messageCode != "" '>
            #{messageCode, jdbcType=VARCHAR},
          </if>
          <if test='messageContent != null and messageContent != "" '>
            #{messageContent, jdbcType=VARCHAR}
          </if>
        )
</insert>

 问题来了，当messageClassify ！=null，但messageCode 和messageContent 为空时，会多出来逗号，以至于mybatis报错。正确的用法如下.xml：

<insert id="insertMessage" parameterType="com.sf.ccsp.member.client.request.MessageReq">
       insert cx_customer_message
        <trim prefix="(" suffix=")" suffixOverrides="," >
          ID,MEMBERID,
          <if test='messageClassify != null and messageClassify != "" '>
             MESSAGEE_CLASSIFY,
          </if>
          <if test='messageCode != null and messageCode != "" '>
             MESSAGE_CODE,
          </if>
         <if test='messageContent != null and messageContent != "" '>
             MESSAGE_CONTENT,
         </if>
        </trim>

        <trim prefix="values (" suffix=")" suffixOverrides="," >
          #{id, jdbcType=VARCHAR},
          #{memberId, jdbcType=VARCHAR},
          <if test='messageClassify != null and messageClassify != "" '>
            #{messageClassify, jdbcType=VARCHAR},
          </if>
          <if test='messageCode != null and messageCode != "" '>
            #{messageCode, jdbcType=VARCHAR},
          </if>
          <if test='messageContent != null and messageContent != "" '>
            #{messageContent, jdbcType=VARCHAR},
          </if>
        </trim>  
</insert>

trim标签的属性

prefix：前缀覆盖并增加其内容。也就是给中的sql语句加上前缀；
suffix：后缀覆盖并增加其内容。给包裹的sql语句加上后缀；
prefixOverrides：前缀判断的条件。取消指定的前缀，如where；
suffixOverrides：后缀判断的条件。取消指定的后缀，如and | or.，逗号等。

控制同一张表，但传入的参数不固定，操作表的字段不固定，就要用到mybatis动态更新，普通的<if test=" ">的方法会出现多余的字符。

方法一：使用set标签

<update id="updateMessage"  parameterType="com.sf.ccsp.member.client.request.MessageReq" >
     update cx_customer_message
     <set>
        <if test='messageClassify != null and messageClassify != "" '>
          MESSAGEE_CLASSIFY = #{messageClassify, jdbcType=VARCHAR},
        </if>
        <if test='messageCode != null and messageCode != "" '>
          MESSAGE_CODE = #{messageCode, jdbcType=VARCHAR},
        </if>
        <if test='messageContent != null and messageContent != "" '>
          MESSAGE_CONTENT = #{messageContent, jdbcType=VARCHAR},
        </if>
     </set>
     where ID = #{id, jdbcType=VARCHAR}
     and MEMBERID = #{memberId, jdbcType=VARCHAR} 
</update>

方法二：使用trim标签

<update id="updateMessage"  parameterType="com.sf.ccsp.member.client.request.MessageReq" >
     update cx_customer_message
     <trim prefix="set" suffixOverrides=",">  
        <if test='messageClassify != null and messageClassify != "" '>
          MESSAGEE_CLASSIFY = #{messageClassify, jdbcType=VARCHAR},
        </if>
        <if test='messageCode != null and messageCode != "" '>
          MESSAGE_CODE = #{messageCode, jdbcType=VARCHAR},
        </if>
        <if test='messageContent != null and messageContent != "" '>
          MESSAGE_CONTENT = #{messageContent, jdbcType=VARCHAR},
        </if>    
     </trim>  
     where ID = #{id, jdbcType=VARCHAR}
     and MEMBERID = #{memberId, jdbcType=VARCHAR} 
</update>