使⽤Druid批量更新报错解决⽅案
Druid批量更新报错
Caused by: java.sql.SQLException: sql injection violation, multi-statement not
allowcom.alibaba.druid.wall.WallFilter.check(WallFilter.java:714)
atcom.alibaba.druid.tion_prepareStatement(WallFilter.java:240)
atcom.alibaba.druid.tion_prepareStatement(FilterChainImpl.java:448)
atcom.alibaba.druid.tion_prepareStatement(FilterAdapter.java:928)
atcom.alibaba.druid.tion_prepareStatement(FilterEventAdapter.java:122)
atcom.alibaba.druid.tion_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.poolPreparedStatements=true
druid.maxPoolPreparedStatementPerConnectionSize=20
2、如果需要开启wall监控，同时允许multiStatementAllow，就不要在l中配置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="${stWhileIdle}" />
<property name="testOnBorrow"value="${stOnBorrow}" />
<property name="testOnReturn"value="${stOnReturn}" />
批量更新sql语句<!-- 打开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="batis.spring.SqlSessionFactoryBean">
<property name="configLocation"value="l"></property>
<property name="dataSource"ref="dataSource" />
</bean>
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论