MyBatis(7)--spring boot集成MyBatis，集成Druid批量更新报错multi-statement not allow

来源：互联网发布：云熙软件编辑：程序博客网时间：2024/05/29 15:46

MyBatis集成Druid时，执行批量更新，也就是多个Update语句，报错如下:

java.sql.SQLException: sql injection violation, multi-statement not allow :  UPDATE t_user_finance_cashflow SET tradeBeforeAmount = ?,  tradeAfterAmount = ?, updateTime = sysdate()  WHERE userOid = ? AND accountCode = ?  AND tradeFlowCode = ? ; UPDATE t_user_finance_cashflow SET tradeBeforeAmount = ?,  tradeAfterAmount = ?, updateTime = sysdate()  WHERE userOid = ? AND accountCode = ?  AND tradeFlowCode = ? ;

从错误中可以看出，不允许批量执行，其本质错误是Druid的防火墙配置(WallConfig)中变量

multiStatementAllow默认为false，在源码中当多条语句时，判断了是否开启了批量执行,

否则报错不允许执行多条语句。

源码:

所以，我们要解决这个问题只需把WallConfig中的multiStatementAllow设置为true即可。

spring boot集成MyBatis，集成Druid时关于DruidDataSource简化版配置如下:

package com.baofeng.banking.base.dal.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.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.context.annotation.DependsOn;import org.springframework.context.annotation.Primary;import javax.sql.DataSource;import java.util.ArrayList;import java.util.List;@Configurationpublic class MybatisConfig {    private  final Logger logger = LoggerFactory.getLogger(MybatisConfig.class);    @Autowired    WallFilter wallFilter;    @Bean(name = "dataSource", destroyMethod = "close")    //声明其为Bean实例    @Primary  //在同样的DataSource中，首先使用被标注的DataSource    @ConfigurationProperties(prefix = "spring.datasource")    public DataSource dataSource(){        DruidDataSource datasource = new DruidDataSource();        // filter        List<Filter> filters = new ArrayList<>();        filters.add(wallFilter);        datasource.setProxyFilters(filters);        return datasource;    }    @Bean(name = "wallFilter")    @DependsOn("wallConfig")    public WallFilter wallFilter(WallConfig wallConfig){        WallFilter wallFilter = new WallFilter();        wallFilter.setConfig(wallConfig);        return wallFilter;    }    @Bean(name = "wallConfig")    public WallConfig wallConfig(){        WallConfig wallConfig = new WallConfig();        wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句        wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句        return wallConfig;    }}

别忘记了，在你数据库连接后面需要加上allowMultiQueries=true,上面解决的是Druid的拦截，

而在数据库上的配置解决的是数据库服务层面的拦截。

eg:

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test_account?characterEncoding=utf-8&useSSL=false&allowMultiQueries=true

到此，批量执行sql应该可以了！

阅读全文

0 0