Mybatis集成Druid批量更新时经常会出现Error updating database. Cause: java.sql.SQLException: sql injection violation, multi-statement not allow 异常。导致该异常出现是因为Druid的multiStatementAllow默认是false,所以需要开启,设置成true。
原因
mysql默认不支持批量更新
Druid的multiStatementAllow默认是false
解决方案
方法一
修改application配置
spring.datasource.url 后面加上 &allowMultiQueries=true
如果配置了druid 注释掉 filters: wall,加上
filter:
wall:
config:
multi-statement-allow: true
none-base-statement-allow: true
参考如下
spring:
#mysql
datasource:
url: jdbc:xxx&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
username: xxx
password: xxx
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
#druid
druid:
# filters: wall
filter:
wall:
config:
multi-statement-allow: true
none-base-statement-allow: true
方法二
添加一个DruidConfig类
package com.song.config;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
/**
* @author anatre
* @date 2020.06.04
*/
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties (prefix="spring.datasource")
public DataSource druidPrimary(){
DruidDataSource druidDataSource = new DruidDataSource();
List<Filter> filterList = new ArrayList<> ();
filterList.add(wallFilter());
druidDataSource.setProxyFilters(filterList);
return new DruidDataSource();
}
@Bean
public WallFilter wallFilter(){
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig());
return wallFilter;
}
@Bean
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
//允许一次执行多条语句
config.setMultiStatementAllow(true);
config.setNoneBaseStatementAllow(true);
return config;
}
}