java mysql druid mybatis-plus里使用多表删除出错的一种处理方式

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: java mysql druid mybatis-plus里使用多表删除出错的一种处理方式

今天在出来多表删除的时候在mapper.xml用了下面的多个delete语句

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nbcio.modules.flowable.mapper.FlowTaskMapper">
  <select id="queryActivityInstance" resultType="org.flowable.engine.impl.persistence.entity.ActivityInstanceEntityImpl">
        select t.* from
        act_ru_actinst t
       <where>
           <if test="processInstanceId !=null and processInstanceId != ''" >
              t.PROC_INST_ID_=#{processInstanceId} and ACT_TYPE_ = 'userTask' and  END_TIME_ is not null 
           </if>
           
       </where>
         order by t.END_TIME_ ASC
    </select>
    
    <delete id="deleteRunActinstsByIds" parameterType="java.util.List">
        delete from act_ru_actinst where ID_ in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>
    <delete id="deleteHisActinstsByIds" parameterType="java.util.List">
        delete from act_hi_actinst where ID_ in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>
    
    <delete id="deleteAllHisAndRun" parameterType="String">
      delete  from  act_ru_actinst  where proc_inst_id_ = #{processInstanceId}; 
      delete  from  act_ru_identitylink  where proc_inst_id_ = #{processInstanceId};
      delete  from  act_ru_task  where proc_inst_id_ = #{processInstanceId};
      delete  from  act_ru_variable  where proc_inst_id_ = #{processInstanceId};
      delete  from  act_ru_execution  where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_actinst where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_comment where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_identitylink  where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_procinst where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_taskinst where proc_inst_id_ = #{processInstanceId};
      delete  from  act_hi_varinst  where proc_inst_id_ = #{processInstanceId};
    </delete>
</mapper>

   结果执行的时候出现下面错误

2023-08-19 10:32:22.408 [http-nio-8080-exec-6] [34mINFO [0;39m [36mo.j.c.util.security.AbstractQueryBlackListHandler:56[0;39m - 
--获取sql信息--
2023-08-19 10:32:32.175 [http-nio-8080-exec-4] [1;31mERROR[0;39m [36mo.jeecg.common.exception.JeecgBootExceptionHandler:69[0;39m - 
### Error updating database.  Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : 
DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
### The error may exist in file [F:\codestudy\nbcio-oa\develop\nbcio-boot\nbcio-boot-module-flowable\target\classes\com\nbcio\modules\flowable\mapper\xml\FlowTaskMapper.xml]
### The error may involve com.nbcio.modules.flowable.mapper.FlowTaskMapper.deleteAllHisAndRun
### The error occurred while executing an update
### SQL: DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
### Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?; nested exception is java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
### The error may exist in file [F:\codestudy\nbcio-oa\develop\nbcio-boot\nbcio-boot-module-flowable\target\classes\com\nbcio\modules\flowable\mapper\xml\FlowTaskMapper.xml]
### The error may involve com.nbcio.modules.flowable.mapper.FlowTaskMapper.deleteAllHisAndRun
### The error occurred while executing an update
### SQL: DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
### Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
; uncategorized SQLException; SQL state [null]; error code [0]; sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?; nested exception is java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.15, multi-statement not allow : DELETE FROM act_ru_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_ru_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_ru_task WHERE proc_inst_id_ = ?;DELETE FROM act_ru_variable WHERE proc_inst_id_ = ?;DELETE FROM act_ru_execution WHERE proc_inst_id_ = ?;DELETE FROM act_hi_actinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_comment WHERE proc_inst_id_ = ?;DELETE FROM act_hi_identitylink WHERE proc_inst_id_ = ?;DELETE FROM act_hi_procinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_taskinst WHERE proc_inst_id_ = ?;DELETE FROM act_hi_varinst WHERE proc_inst_id_ = ?
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)

主要是说druid不支持多表删除的操作,先相关资料实际上是可以配置druid进行多表操作

配置如下,sql连接增加&allowMultiQueries=true

url: jdbc:mysql://192.168.199.151:3306/nbcio-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai&allowMultiQueries=true

同时druid里filters: stat,slf4j  这里去掉防火墙wall

     但这里也会引起一次多表操作的风险,不安全的时候风险也比较大,所以还是想办法考虑其它方式来处理为好。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
27天前
|
算法 Java 数据库连接
Spring+MySQL+数据结构+集合,Alibaba珍藏版mybatis手写文档
Spring+MySQL+数据结构+集合,Alibaba珍藏版mybatis手写文档
|
2天前
|
SQL Java 关系型数据库
Java中的ORM框架——myBatis
Java中的ORM框架——myBatis
|
7天前
|
XML Java 数据库连接
MyBatis第二课,灰度发布,@Results注解,使用xml书写mysql
MyBatis第二课,灰度发布,@Results注解,使用xml书写mysql
|
8天前
|
关系型数据库 MySQL 数据库
mysql 中文问号,mybatis-plus insert中文数据库显示问号
mysql 中文问号,mybatis-plus insert中文数据库显示问号
|
13天前
|
Java 关系型数据库 MySQL
Java语言与MySQL数据库连接的技术性探讨
Java语言与MySQL数据库连接的技术性探讨
|
20天前
|
SQL Java 关系型数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
JavaWeb(JDBC编程)看这一篇就够了 —— 如何使用Java操作mysql数据库
15 0
|
20天前
|
SQL 缓存 Java
Java核心-MyBatis
Java核心-MyBatis
9 2
|
24天前
|
监控 NoSQL Java
java云MES 系统源码Java+ springboot+ mysql 一款基于云计算技术的企业级生产管理系统
MES系统是生产企业对制造执行系统实施的重点在智能制造执行管理领域,而MES系统特点中的可伸缩、信息精确、开放、承接、安全等也传递出:MES在此管理领域中无可替代的“王者之尊”。MES制造执行系统特点集可伸缩性、精确性、开放性、承接性、经济性与安全性于一体,帮助企业解决生产中遇到的实际问题,降低运营成本,快速适应企业不断的制造执行管理需求,使得企业已有基础设施与一切可用资源实现高度集成,提升企业投资的有效性。
64 5
|
27天前
|
存储 关系型数据库 MySQL
《MySQL 入门教程》第 05 篇 账户和权限,Java高并发编程详解深入理解pdf
《MySQL 入门教程》第 05 篇 账户和权限,Java高并发编程详解深入理解pdf
|
27天前
|
存储 数据可视化 前端开发
Echarts+vue+java+mysql实现数据可视化
Echarts+vue+java+mysql实现数据可视化