使用如下Mybatis map xml文件
出错信息:
### The error may involve com.hirain.testmanagement.mapper.TestcaseNodeMapper.updateTestcaseNodeBatch-Inline
### The error occurred while setting parameters
### SQL: UPDATE testcase_node SET name=?, version=?, description=?, last_modify_user=?, last_modify_time=? WHERE object_id=? AND root_id=? ; UPDATE testcase_node SET name=?, version=?, description=?, last_modify_user=?, last_modify_time=? WHERE object_id=? AND root_id=?
### Cause: 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 testcase_node
SET name='Türstatus',
version=4,
' at line 8
; bad SQL grammar []; nested exception is 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 testcase_node
SET name='Türstatus',
version=4,
' at line 8
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
仔细检查 map文件 和数据库表字段没有错误
最终结果是因为 配置的 MySQL jdbc 链接字符串 默认不支持一次性执行多个sql 语句;
但是在我们的 update map中需要执行多个 update语句。
最后加上参数 "allowMultiQueries" 设置为true 如下:
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true" />
问题解决!
但是这样其实是一条记录update一次,性能比较差,容易造成阻塞。
MySQL没有提供直接的方法来实现批量更新,可以考虑使用case when语法来实现这个功能。
UPDATE course
SET name = CASE id
WHEN 1 THEN 'name1'
WHEN 2 THEN 'name2'
WHEN 3 THEN 'name3'
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
这条sql的意思是,如果id为1,则name的值为name1,title的值为New Title1;依此类推。
在Mybatis中的配置则如下:
<update id="updateBatch" parameterType="list">
update course
<trim prefix="set" suffixOverrides=",">
<trim prefix="peopleId =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.peopleId!=null">
when id=#{i.id} then #{i.peopleId}
</if>
</foreach>
</trim>
<trim prefix=" roadgridid =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.roadgridid!=null">
when id=#{i.id} then #{i.roadgridid}
</if>
</foreach>
</trim>
<trim prefix="type =case" suffix="end," >
<foreach collection="list" item="i" index="index">
<if test="i.type!=null">
when id=#{i.id} then #{i.type}
</if>
</foreach>
</trim>
<trim prefix="unitsid =case" suffix="end," >
<foreach collection="list" item="i" index="index">
<if test="i.unitsid!=null">
when id=#{i.id} then #{i.unitsid}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index" >
id=#{i.id}
</foreach>
</update>