参照博主: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>