关于mybatis批量更新操作

最新推荐文章于 2021-10-12 17:27:47 发布

原创最新推荐文章于 2021-10-12 17:27:47 发布 · 389 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #批量更新

Mybatis 专栏收录该内容

2 篇文章

订阅专栏

本文介绍使用MyBatis进行批量更新的两种方法，并强调在MySQL数据库中启用多查询支持的重要性，以避免语法错误。

第一种写法：

<update id="batchUpdate" parameterType="com.entity.TestMode">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update user set c_username = #{item.userName} where c_id = #{item.id}
        </foreach>
</update>

当使用这种做法进行批量更新时，一定要在连接数据库的url后面加上&allowMultiQueries=true，否则，汇报如下错误：

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 user set c_username = 'zjpt8' where c_id = '8'' at line 3
### The error may involve com.mapper.SettlementRecordMapper.batchUpdate-Inline
### The error occurred while setting parameters
### SQL: update user set c_username = ? where c_id = ?          ;              update user set c_username = ? where c_id = ?
### 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 user set c_username = 'zjpt8' where c_id = '8'' at line 3
; 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 user set c_username = 'zjpt8' where c_id = '8'' at line 3
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy116.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy118.batchUpdate(Unknown Source)

第二种写法：

<update id="batchUpdate" parameterType="com.model.TestMode">
        update user
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="c_username =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    when c_id = #{item.id} then #{item.userName}
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" item="item" index="index" separator="or">
            c_id = #{item.id}
        </foreach>
</update>

后续补充两种方法的比较