Mybatis批量更新出现BadSqlGrammarException异常解决

1.xml写法 批量更新sql如下

   <update id="updateDemo">
        <foreach collection="demos" item="demo" open="" separator=";" close="" index="index">
            update demo
            <set>
                <if test="null != demo.name">name = #{demo.name},</if>
                <if test="null != demo.age">age= #{demo.age}</if>
            </set>
            <where>id=#{demo.id} </where>
        </foreach>
    </update>

2.执行出现异常

  JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@1500edf3] will be managed by Spring
==>  Preparing: update demo SET name = ?, age= ? WHERE id=? ; update demo SET name = ?, age= ? WHERE id=? 
==> Parameters: 2(Integer), 2(Integer), 1(Integer), 3(Integer), 3(Integer), 2(Integer)
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11d474a]
Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11d474a]
Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11d474a]
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 demo
             SET name = 3,
                age' at line 8
### The error may exist in file [D:\Demo-server\target\classes\mapper\DemoMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update demo              SET name = ?,                 age= ?               WHERE id=?                               ;              update demo              SET name = ?,                 age= ?               WHERE 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 app_user
             SET name = 3,
                age' at line 8
; 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 demo
             SET name = 3,
                age' at line 8

3.配置连接数据库的url中后缀中添加以下参数

allowMultiQueries=true //开启批量更新

4.配置结果

jdbc:mysql://xxx:3306/xxx?useSSL=false&characterEncoding=utf8&allowMultiQueries=true
上一篇:Spring Data JPA : 批量增删


下一篇:Spring_data_JPA教程(Gradle构建)