今天在做mysql批量更新时,总是报错:
DEBUG - Translating SQLException with SQL state '42000', error code '1064', message [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
看到这个错误 首先想到的是我的sql语句可能用到了保留关键字,但是我仔细检查后没有什么问题。
解决方法:
附件:
oracle:
<
update
id=
"batchUpdate"
parameterType=
"java.util.List"
>
<foreach collection=
"list"
item=
"item"
index
=
"index"
open
=
"begin"
close
=
"end;"
separator=
";"
>
update
test
<
set
>
test=${item.test}+1
</
set
>
where
id = ${item.id}
</foreach>
</
update
>
mysql:
<
update
id="
updateOrderBatch
" parameterType="
list
">
<foreach collection=" list " item=" order " open="" close="" separator=" ; ">
update t_order
< set >
Orderid = #{order.orderid,jdbcType=VARCHAR},RealGet = #{order.realGet,jdbcType=REAL}
</ set >
where ID = #{order.ID,jdbcType=INTEGER}
</foreach>
</ update >
<foreach collection=" list " item=" order " open="" close="" separator=" ; ">
update t_order
< set >
Orderid = #{order.orderid,jdbcType=VARCHAR},RealGet = #{order.realGet,jdbcType=REAL}
</ set >
where ID = #{order.ID,jdbcType=INTEGER}
</foreach>
</ update >
特别提醒:
mysql数据库采用一下写法即可执行,但是数据库连接必须配置:&allowMultiQueries=true
例如:jdbc:mysql://192.168.1.236:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true