批量更新部门层级 报错

Question

老师，我测试批量更新部门层级时出错。我想测试下把id为2的后端开发的parent_id设为6时，看看后端开发的level变为0.6，python和java的leve更新0.6.2

Caused by: java.sql.SQLException: sql injection violation, multi-statement not allow : UPDATE sys_dept
SET level = ?
WHERE id = ?
;
UPDATE sys_dept
SET level = ?
WHERE id = ?
at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:808)
at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:259)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:350)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:85)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
… 54 common frames omitted

dao接口

void batchUpdateLevel(@Param("sysDeptList") List<SysDept> sysDeptList);

mapper.xml

  <update id="batchUpdateLevel" parameterType="map">
    <foreach collection="sysDeptList" item="sysDept" separator=";">
      UPDATE sys_dept
      SET level = #{sysDept.level}
      WHERE id = #{sysDept.id}
    </foreach>
  </update>

Answer 1

Jimin 2020-10-18 11:12:08

你好，这个可能与db版本低有关，默认不允许批量更新，可以这样做：

1 配置数据库连接，添加allowMultiQueries=true
2 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall’用于防火墙，此处去除防火墙spring.datasource.druid.filters=config,stat,slf4j
配置过滤器wall的参数
spring.datasource.druid.filter.wall.config.multi-statement-allow=true