Druid批量更新报错
Caused by: java.sql.SQLException: sql injection violation, multi-statement not
allowcom.alibaba.druid.wall.WallFilter.check(WallFilter.java:714)
atcom.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:240)
atcom.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
atcom.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:928)
atcom.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
atcom.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:448)
atcom.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:342)
atcom.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:318)
解决方案:
1、配置数据库连接,添加allowMultiQueries=true
jdbc.url=jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
完整配置
jdbc.url=jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
jdbc.username=root
jdbc.password=root
druid.initialSize=10
druid.minIdle=10
druid.maxActive=50
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.validationQuery=SELECT 'x'
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.poolPreparedStatements=true
druid.maxPoolPreparedStatementPerConnectionSize=20
2.如果需要开启wall监控,同时允许multiStatementAllow,就不要在application.yml中配置filter。
### 注解方式
/使用连接池dataSource
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource dataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
List filterList=new ArrayList<>();
filterList.add(wallFilter());
druidDataSource.setProxyFilters(filterList);
return 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;
}
- xml配置方式
<!-- 数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${druid.initialSize}"/>
<property name="minIdle" value="${druid.minIdle}"/>
<property name="maxActive" value="${druid.maxActive}"/>
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${druid.maxWait}"/>
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${druid.validationQuery}" />
<property name="testWhileIdle" value="${druid.testWhileIdle}" />
<property name="testOnBorrow" value="${druid.testOnBorrow}" />
<property name="testOnReturn" value="${druid.testOnReturn}" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。-->
<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
<!-- 配置监控统计拦截的filters -->
<!-- 关键部分 start-->
<property name="proxyFilters">
<list>
<ref bean="stat-filter"/>
<ref bean="wall-filter"/>
</list>
</property>
</bean>
<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter"/>
<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">
<property name="config" ref="wall-config"/>
</bean>
<bean id="wall-config" class="com.alibaba.druid.wall.WallConfig">
<property name="multiStatementAllow" value="true"/>
<property name="noneBaseStatementAllow" value="true"/>
</bean>
<!-- 关键部分 end-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:sqlMapConfig.xml"></property>
<property name="dataSource" ref="dataSource" />
</bean>
如果未解决按照以下配置,尝试下:
<bean id="mergeStat-filter" class="com.alibaba.druid.filter.stat.MergeStatFilter"></bean>
<!--配置log4j2的Log4j2Filter-->
<bean id="log4j2-filter" class="com.alibaba.druid.filter.logging.Log4j2Filter">
<property name="connectionLogEnabled" value="false"/>
<property name="statementLogEnabled" value="false"/>
<property name="resultSetLogEnabled" value="true"/>
<property name="statementExecutableSqlLogEnable" value="true"/>
</bean>
<bean id="myWallConfig" class="com.alibaba.druid.wall.WallConfig">
<property name="multiStatementAllow" value="true"/>
<property name="noneBaseStatementAllow" value="true"/>
</bean>
<bean id="wall-filter" class="com.alibaba.druid.wall.WallFilter">
<property name="config" ref="myWallConfig"/>
</bean>
<!-- dataSource -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="url" value="${db.master.url}" />
<property name="username" value="${db.master.user}" />
<property name="password" value="${db.master.password}" />
<!-- 配置监控统计拦截的filters -->
<!--<property name="filters" value="mergeStat,wall,log4j2" />-->
<property name="initialSize" value="5" />
<property name="maxActive" value="100" />
<property name="minIdle" value="10" />
<property name="maxWait" value="60000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="true" />
<property name="testWhileIdle" value="true" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="1800" />
<property name="logAbandoned" value="true" />
<property name="proxyFilters">
<list>
<ref bean="wall-filter"/>
<ref bean="mergeStat-filter"/>
<ref bean="log4j2-filter"/>
</list>
</property>
</bean>