Mybatis批量更新数据问题

参照博主：http://www.cnblogs.com/haimishasha/p/5697740.html

踩坑描述：

问题背景：批量更新数据时，mysql抛出各种异常，如：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UPDATE portal_website SET update_time = NOW(), organization_id = 147, website_n’ at line 3等等。

解决办法：修改数据库连接配置：&allowMultiQueries=true，如：jdbc:mysql://192.168.0.201:3306/zhsw_portal_app?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true

总结：

一、第一种场景：修改的字段值是一样的，条件是多个的情况：（执行的是一条SQL）

<sql id="UPDATE_SET_CLAUSE">
    <set>
      update_time = now(),
      <if test="portalTitle != null and portalTitle != ''"><![CDATA[portal_title = #{portalTitle},]]></if>
      <if test="portalContent != null and portalContent != ''"><![CDATA[portal_content = #{portalContent},]]></if>
      <if test="indexImgPath != null and indexImgPath != ''"><![CDATA[index_img_path = #{indexImgPath},]]></if>
      <if test="readCount != null"><![CDATA[read_count = #{readCount},]]></if>
      <if test="noticeEndTime != null"><![CDATA[notice_end_time = #{noticeEndTime},]]></if>
      <if test="noticeStatus != null"><![CDATA[notice_status = #{noticeStatus},]]></if>
      <if test="updateUserId != null"><![CDATA[update_user_id = #{updateUserId},]]></if>
      <if test="deleteFlag != null"><![CDATA[delete_flag = #{deleteFlag},]]></if>
    </set>
  </sql>

<delete id="deleteByCondition" parameterMap="portalNews">
    <![CDATA[UPDATE portal_news]]>
    <include refid="UPDATE_SET_CLAUSE"/>
    <where>
      <if test="id != null"><![CDATA[ AND id = #{id}]]></if>
      <if test="ids != null and ids.size > 0 ">
        <![CDATA[AND id IN ]]>
        <foreach item="id" index="index" collection="ids"
                 open="(" separator="," close=")">
          #{id}
        </foreach>
      </if>
    </where>
  </delete>

二、第二种场景，修改的字段值和条件都不一样：（执行的是多条SQL）
1、修改mysql数据库连接的配置文件：&allowMultiQueries=true
如：jdbc.type=mysql
spring.datasource.url=jdbc:mysql://192.168.0.201:3306/zhsw_portal_app?useUnicode=true&characterEncoding=utf- 8&useSSL=false&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=Water@2017
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
2、mybatis代码示例：

<sql id="UPDATE_SET_CLAUSE_LIST">
    <set>
      update_time = now(),
      organization_id = #{t.organizationId},
      <if test="t.websiteName != null and t.websiteName != ''"><![CDATA[website_name = #{t.websiteName},]]></if>
      <if test="t.updateUserId != null"><![CDATA[update_user_id = #{t.updateUserId},]]></if>
      <if test="t.deleteFlag != null"><![CDATA[delete_flag = #{t.deleteFlag},]]></if>
    </set>
  </sql>

  <update id="updateList" parameterType="java.util.List">
    <foreach collection="list" item="t" index="index" open="" close="" separator=";">
      <![CDATA[UPDATE portal_website]]>
      <include refid="UPDATE_SET_CLAUSE_LIST"/>
      <![CDATA[WHERE id = #{t.id}]]>
    </foreach>
  </update>