【Oracle】Mybatis 批量插入数据

最新推荐文章于 2024-07-30 03:54:38 发布

master336

最新推荐文章于 2024-07-30 03:54:38 发布

阅读量5.4k

点赞数 3

分类专栏：解决方案及技巧数据库文章标签： Mybatis Oracle 批量插入

本文链接：https://blog.csdn.net/master336/article/details/124046980

版权

解决方案及技巧同时被 2 个专栏收录

53 篇文章 1 订阅

订阅专栏

数据库

10 篇文章 0 订阅

订阅专栏

这篇博客探讨了在Mybatis中进行批量插入的两种不同方式：使用begin-end和insertallinto。文章指出，begin-end方式在执行后返回-1，无法获取影响行数，而insertallinto方式的返回值由最后的select决定。内容还包含了这两种方法的XML映射文件和对应的SQL示例。

摘要由CSDN通过智能技术生成

循环调用单次插入

常规方式通过循环调用单条insert语句完成，演示（略）

方式一 begin end

Mapper.xml

    <insert id="batchInsert">
      begin
        <foreach collection="taskRelationList" item="relation" separator=" ">
            insert into t_ds_process_task_relation_log (name, process_definition_version, project_code, process_definition_code,
            pre_task_code, pre_task_version, post_task_code, post_task_version, condition_type, condition_params, operator, operate_time,
            create_time, update_time)
            values
            (#{relation.name},#{relation.processDefinitionVersion},#{relation.projectCode},#{relation.processDefinitionCode},
            #{relation.preTaskCode},#{relation.preTaskVersion},#{relation.postTaskCode},#{relation.postTaskVersion},
            #{relation.conditionType},#{relation.conditionParams},#{relation.operator},#{relation.operateTime},
            #{relation.createTime},#{relation.updateTime});
        </foreach>
      end;
    </insert>

sql

begin
  insert into t_ds_process_task_relation
    (name,
     process_definition_version,
     project_code,
     process_definition_code,
     pre_task_code,
     pre_task_version,
     post_task_code,
     post_task_version,
     condition_type,
     condition_params,
     create_time,
     update_time)
  values
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
  insert into t_ds_process_task_relation
    (name,
     process_definition_version,
     project_code,
     process_definition_code,
     pre_task_code,
     pre_task_version,
     post_task_code,
     post_task_version,
     condition_type,
     condition_params,
     create_time,
     update_time)
  values
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
  insert into t_ds_process_task_relation
    (name,
     process_definition_version,
     project_code,
     process_definition_code,
     pre_task_code,
     pre_task_version,
     post_task_code,
     post_task_version,
     condition_type,
     condition_params,
     create_time,
     update_time)
  values
    (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
end;

注意：
begin … end 此种方式返回 -1（没有返回值）导致无法获取影响行

方式二 insert all into …

Mapper.xml

    <insert id="batchInsert">
        insert all
        <foreach collection="taskRelationList" item="relation" separator=" ">
            into t_ds_process_task_relation_log (name, process_definition_version, project_code, process_definition_code,
            pre_task_code, pre_task_version, post_task_code, post_task_version, condition_type, condition_params, operator, operate_time,
            create_time, update_time)
            values
            (#{relation.name},#{relation.processDefinitionVersion},#{relation.projectCode},#{relation.processDefinitionCode},
            #{relation.preTaskCode},#{relation.preTaskVersion},#{relation.postTaskCode},#{relation.postTaskVersion},
            #{relation.conditionType},#{relation.conditionParams},#{relation.operator},#{relation.operateTime},
            #{relation.createTime},#{relation.updateTime})
        </foreach>
        select ${taskRelationList.size} from dual
    </insert>

sql

insert all into t_ds_process_task_relation
  (name,
   process_definition_version,
   project_code,
   process_definition_code,
   pre_task_code,
   pre_task_version,
   post_task_code,
   post_task_version,
   condition_type,
   condition_params,
   create_time,
   update_time)
values
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) into t_ds_process_task_relation
  (name,
   process_definition_version,
   project_code,
   process_definition_code,
   pre_task_code,
   pre_task_version,
   post_task_code,
   post_task_version,
   condition_type,
   condition_params,
   create_time,
   update_time)
values
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  select 2 from dual

注意：
insert all into 的方式返回值由最后的select 决定

其他方式（未使用Mybatis验证）

-- 创建测试表
create table t_demo
(
  tno   NUMBER(3),
  tname VARCHAR2(30),
  flag   VARCHAR2(20)
);
-- 插入数据
insert into t_demo(tno,tname,flag)
    select * from (
    select 11 tno,'张三1' tname,'1' flag from dual
    union
    select 12 tno,'张三2' tname,'1' flag from dual
    union
    select 13 tno,'张三3' tname,'1' flag from dual
)
-- 验证
select * from t_demo;