mybatis 传参list批量update时出错

Xu_Yang.

已于 2022-06-30 09:21:32 修改

阅读量1.6k

点赞数 3

分类专栏： mysql java 文章标签： mybatis

于 2020-11-30 17:46:30 首次发布

本文链接：https://blog.csdn.net/StrugglingXuYang/article/details/110393587

版权

java 同时被 2 个专栏收录

13 篇文章 2 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

业务需求中需要批量修改数据数量在60条左右。
报错是语法错误
原sql，update传参使用list

<update id="updateList" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            UPDATE febs_base.xfjc_setting_probability
            <set>
                `number`=#{item.number}
            </set>
            where corpid = #{item.corpid} and `code` = #{item.code}
        </foreach>
    </update>

以下报错信息

UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 1;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 2;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 3;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 4;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 5;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 6;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 7;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 8;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 9;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 10;UPDATE febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND `code` = 11;UPDATE febs_base.xfjc_setting_probability SET `number` = 86 WHERE corpid = 2 AND `code` = 12;UPDATE febs_base.xfjc_setting_probability SET `number` = 78 WHERE corpid = 2 AND `code` = 13;UPDATE febs_base.xfjc_setting_probability SET `number` = 66 WHERE corpid = 2 AND `code` = 14;UPDATE febs_base.xfjc_setting_probability SET `number` = 87 WHERE corpid = 2 AND `code` = 15;UPDATE febs_base.xfjc_setting_probability SET `number` = 91 WHERE corpid = 2 AND `code` = 16;UPDATE febs_base.xfjc_setting_probability SET `number` = 95 WHERE corpid = 2 AND `code` = 17;UPDATE febs_base.xfjc_setting_probability SET `number` = 68 WHERE corpid = 2 AND `code` = 18;UPDATE febs_base.xfjc_setting_probability SET `number` = 77 WHERE corpid = 2 AND `code` = 19;UPDATE febs_base.xfjc_setting_probability SET `number` = 88 WHERE corpid = 2 AND `code` = 20;UPDATE febs_base.xfjc_setting_probability SET `number` = 56 WHERE corpid = 2 AND `code` = 21;UPDATE febs_base.xfjc_setting_probability SET `number` = 65 WHERE corpid = 2 AND `code` = 22;UPDATE febs_base.xfjc_setting_probability SET `number` = 85 WHERE corpid = 2 AND `code` = 23;UPDATE febs_base.xfjc_setting_probability SET `number` = 86 WHERE corpid = 2 AND `code` = 24;UPDATE febs_base.xfjc_setting_probability SET `number` = 88 WHERE corpid = 2 AND `code` = 25;UPDATE febs_base.xfjc_setting_probability SET `number` = 83 WHERE corpid = 2 AND `code` = 26;UPDATE febs_base.xfjc_setting_probability SET `number` = 85 WHERE corpid = 2 AND `code` = 27;UPDATE febs_base.xfjc_setting_probability SET `number` = 76 WHERE corpid = 2 AND `code` = 28;UPDATE febs_base.xfjc_setting_probability SET `number` = 72 WHERE corpid = 2 AND `code` = 29;UPDATE febs_base.xfjc_setting_probability SET `number` = 56 WHERE corpid = 2 AND `code` = 30;UPDATE febs_base.xfjc_setting_probability SET `number` = 78 WHERE corpid = 2 AND `code` = 31;UPDATE febs_base.xfjc_setting_probability SET `number` = 72 WHERE corpid = 2 AND `code` = 32;UPDATE febs_base.xfjc_setting_probability SET `number` = 71 WHERE corpid = 2 AND `code` = 33;UPDATE febs_base.xfjc_setting_probability SET `number` = 73 WHERE corpid = 2 AND `code` = 34;UPDATE febs_base.xfjc_setting_probability SET `number` = 68 WHERE corpid = 2 AND `code` = 35;UPDATE febs_base.xfjc_setting_probability SET `number` = 63 WHERE corpid = 2 AND `code` = 36;UPDATE febs_base.xfjc_setting_probability SET `number` = 58 WHERE corpid = 2 AND `code` = 37;UPDATE febs_base.xfjc_setting_probability SET `number` = 63 WHERE corpid = 2 AND `code` = 38;UPDATE febs_base.xfjc_setting_probability SET `number` = 57 WHERE corpid = 2 AND `code` = 39;UPDATE febs_base.xfjc_setting_probability SET `number` = 58 WHERE corpid = 2 AND `code` = 40;UPDATE febs_base.xfjc_setting_probability SET `number` = 57 WHERE corpid = 2 AND `code` = 41;UPDATE febs_base.xfjc_setting_probability SET `number` = 47 WHERE corpid = 2 AND `code` = 42;UPDATE febs_base.xfjc_setting_probability SET `number` = 38 WHERE corpid = 2 AND `code` = 43;UPDATE febs_base.xfjc_setting_probability SET `number` = 43 WHERE corpid = 2 AND `code` = 44;UPDATE febs_base.xfjc_setting_probability SET `number` = 65 WHERE corpid = 2 AND `code` = 50;UPDATE febs_base.xfjc_setting_probability SET `number` = 68 WHERE corpid = 2 AND `code` = 51;UPDATE febs_base.xfjc_setting_probability SET `number` = 39 WHERE corpid = 2 AND `code` = 53;UPDATE febs_base.xfjc_setting_probability SET `number` = 48 WHERE corpid = 2 AND `code` = 54;UPDATE febs_base.xfjc_setting_probability SET `number` = 58 WHERE corpid = 2 AND `code` = 55;UPDATE febs_base.xfjc_setting_probability SET `number` = 67 WHERE corpid = 2 AND `code` = 56;UPDATE febs_base.xfjc_setting_probability SET `number` = 76 WHERE corpid = 2 AND `code` = 57;UPDATE febs_base.xfjc_setting_probability SET `number` = 75 WHERE corpid = 2 AND `code` = 58;UPDATE febs_base.xfjc_setting_probability SET `number` = 75 WHERE corpid = 2 AND `code` = 59;UPDATE febs_base.xfjc_setting_probability SET `number` = 77 WHERE corpid = 2 AND `code` = 60;UPDATE febs_base.xfjc_setting_probability SET `number` = 68 WHERE corpid = 2 AND `code` = 61;UPDATE febs_base.xfjc_setting_probability SET `number` = 67 WHERE corpid = 2 AND `code` = 62;UPDATE febs_base.xfjc_setting_probability SET `number` = 65 WHERE corpid = 2 AND `code` = 63;
2020-11-30 17:31:49 ERROR http-nio-9001-exec-2 cc.mrbird.febs.common.aspect.ControllerEndpointAspect 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1
### The error may involve cc.mrbird.febs.xfjc.mapper.XfjcSettingProbabilityMapper.updateList-Inline
### The error occurred while setting parameters
### SQL: UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?
### Cause: java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1
org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1
### The error may involve cc.mrbird.febs.xfjc.mapper.XfjcSettingProbabilityMapper.updateList-Inline
### The error occurred while setting parameters
### SQL: UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?;UPDATE febs_base.xfjc_setting_probability SET `number` = ? WHERE corpid = ? AND `code` = ?
### Cause: java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 febs_base.xfjc_setting_probability SET `number` = 50 WHERE corpid = 2 AND' at line 1

在navicat中运行没问题，在这里插入图片描述
原因是数据库连接中缺少
&allowMultiQueries=true
原链接
jdbc:mysql://192.168.1.4:3306/febs_base?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8
添加后链接
jdbc:mysql://192.168.1.4:3306/febs_base?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8&allowMultiQueries=true