Mybatis批量更新报错com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

批量更新数据，非常简单的一段代码，硬是报错，插入的数据也能显示出来

List<User> userlist = new ArrayList<User>();
userlist.add(new User(1, "qq", "123132", "eee"));
userlist.add(new User(2, "3333", "123132", "rrrr"));

int i = usermapper.updateData(userlist);
<!-- 批量更新 -->
<update id="updateData" parameterType="list">
<foreach collection="list" item="item" separator=";">
update useres
<set>
uname=#{item.uname},pwd=#{item.pwd},realname=#{item.realname}
</set>
where
id=#{item.id}
</foreach>
</update>
[main] 2018/09/01 15:55:59  DEBUG (BaseJdbcLogger.java:132) - ooo Using Connection 
[main] 2018/09/01 15:55:59  DEBUG (BaseJdbcLogger.java:132) - ==>  Preparing: update useres SET uname=?,pwd=?,realname=? where id=? ; update useres SET uname=?,pwd=?,realname=? where id=? 
[main] 2018/09/01 15:55:59  DEBUG (BaseJdbcLogger.java:132) - ==> Parameters: qq(String), 123132(String), eee(String), 
### The error may involve com.bjsxt.user.entity.UserMapper.updateData-Inline
### The error occurred while setting parameters
### SQL: update useres     SET uname=?,pwd=?,realname=?     where    id=?    ;     update useres     SET uname=?,pwd=?,realname=?     where    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 useres
             SET uname='3333',pwd='123132',realname='rrrr' 
            where
            id=' at line 6
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:49)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
    at com.sun.proxy.$Proxy0.updateData(Unknown Source)
    at com.bjsxt.user.test.TestMapper.main(TestMapper.java:41)
Caused by: 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 useres
             SET uname='3333',pwd='123132',realname='rrrr' 
            where
            id=' at line 6
   

最终，解决了问题如果连续执行多条sql语句 需要在url的后面 补充一个allowMultiQueries=true 即可
 

<property name="url" value="jdbc:mysql://localhost:3306/test?allowMultiQueries=true" />
[main] 2018/09/01 16:04:23  DEBUG (BaseJdbcLogger.java:132) - ooo Using Connection [com.mysql.jdbc.JDBC4Connection@5ce81285]
[main] 2018/09/01 16:04:23  DEBUG (BaseJdbcLogger.java:132) - ==>  Preparing: update useres SET uname=?,pwd=?,realname=? where id=? ; update useres SET uname=?,pwd=?,realname=? where id=? 
[main] 2018/09/01 16:04:23  DEBUG (BaseJdbcLogger.java:132) - ==> Parameters: qq(String), 123132(String), eee(String), 1(Integer), 3333(String), 123132(String), rrrr(String), 2(Integer)
[main] 2018/09/01 16:04:23  DEBUG (BaseJdbcLogger.java:132) - <==    Updates: 1
1
[main] 2018/09/01 16:04:23  DEBUG (JdbcTransaction.java:66) - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5ce81285]
[main] 2018/09/01 16:04:23  DEBUG (JdbcTransaction.java:117) - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5ce81285]
[main] 2018/09/01 16:04:23  DEBUG (JdbcTransaction.java:85) - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5ce81285]
[main] 2018/09/01 16:04:23  DEBUG (PooledDataSource.java:332) - Returned connection 1558712965 to pool.
---------------------
作者：风泊月
来源：CSDN
原文：https://blog.csdn.net/hello_word2/article/details/82287895
版权声明：本文为博主原创文章，转载请附上博文链接！