数据库批量提交失败 sql injection violation, multi-statement not allow

问题

批量更新数据库失败，代码如下：
@Update("<script>" +
        "<foreach collection = 'edgeCourses' item = 'item'  separator =';'>" +
        " update edge_course set record_state = ${item.recordState} " +
        " where id = ${item.id} and record_state = ${oldState}" +
        "</foreach>" +
        "</script>")
 执行后报错如下：

     Cause: java.sql.SQLException: sql injection violation, multi-statement not allow :
     update edge_course set record_state = 2  where id = 2 and record_state = 1 ;  update edge_course set record_state = 2  where id = 3 and record_state = 1### The error may exist in com/qudian/qpark/aiot/edge/dao/mapper/EdgeCourseMapper.java (best guess)
  ### The error may involve com.qudian.qpark.aiot.edge.dao.mapper.EdgeCourseMapper.batchUpdateCourseRecordState
  ### The error occurred while executing an update
  ### SQL: update edge_course set record_state = 2  where id = 2 and record_state = 1 ;  update edge_course set record_state = 2  where id = 3 and record_state = 1
  ### Cause: java.sql.SQLException: sql injection violation, multi-statement not allow : update edge_course set record_state = 2  where id = 2 and record_state = 1 ;  update edge_course set record_state = 2  where id = 3 and record_state = 1

排查

看控制台提到WallFilter 点进去找到对应的源码，陆续追踪可以找到报错处 即multiStatementAllow没有设置为true

解决

在数据库配置上的url追加multiStatementAllow=true即可解决此问题,如下： spring.datasource.druid.url = jdbc:mysql://localhost:3306/demo?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&failOverReadOnly=false&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&multiStatementAllow=true

另一种解决方法，不要配置过滤器spring.shardingsphere.datasource.slave0.filters，url体现allowMultiQueries=true也可以。