使用Druid批量更新报错解决方案

最新推荐文章于 2024-04-19 15:17:17 发布

原创最新推荐文章于 2024-04-19 15:17:17 发布 · 3.6k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#Spring Boot #Druid

Spring Boot 专栏收录该内容

33 篇文章

订阅专栏

解决Druid连接池批量更新时因多语句不被允许导致的SQL注入异常问题，通过配置allowMultiQueries参数及WallConfig来允许执行多条语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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>