★ FEATURED ARTICLE 简介这份PDF资料面向使用Mybatis与Oracle的Java后端开发者聚焦于如何将List类型参数传入Oracle存储过程以实现批量插入数据解决Mybatis批处理难以实时返回主键、foreach循环集合长度受限、事务回滚不便等痛点。资源包共1个PDF文件大小约61KB内容围绕数据库表与Oracle对象类型、数组类型的建立、存储过程编写、Mybatis的parameterMap与TypeHandler配置以及Server端调用方式展开并给出ListHandler将Java List转换为Oracle ARRAY的具体实现思路。目前已有1961人学习适合需要突破Mybatis批量插入限制、希望在存储过程中灵活控制事务的中高级开发者参考可帮助读者掌握参数封装与调用链路的关键细节快速应用到实际项目中。1. MyBatis 传 List 参数调用 Oracle 存储过程一个被低估的批量写入方案批量写入场景里很多人第一反应是拼INSERT ALL或者用foreach拼多值VALUES但 SQL 文本长度、绑定变量上限、解析开销三座大山压下来几千条数据就开始翻车。Oracle 存储过程配合数组入参TABLE OF类型是一条被低估的路子把 List 一次性交给存储过程由数据库侧完成批量插入或业务校验网络往返从 N 次降到 1 次。这个方案适合有 Oracle 环境、需要批量落库或批量校验、且团队能接受在数据库侧维护一段 PL/SQL 的团队。下面把我实际落地时踩过的类型映射、参数传递、事务边界问题拆开讲清楚。2. 为什么选存储过程 数组入参而不是 foreach 拼 SQL2.1 三种批量写入方式的真实差距在 Oracle 上做批量写入常见有三条路。第一条是 MyBatis 的foreach拼多值INSERT INTO t (a,b) VALUES (?,?),(?,?)...写法最直观但 Oracle 对单条 SQL 的绑定变量数量有硬限制超过 65535 个绑定变量直接报ORA-01745或解析异常按每行 5 个字段算一万行就顶到天花板。第二条是INSERT ALL语法上能绕开一部分限制但 SQL 文本会随行数线性膨胀硬解析成本高而且拼接过程本身在 Java 侧就消耗大量内存。第三条就是本文要讲的定义一个 Oracle 集合类型把 List 作为数组参数传给存储过程过程内部用FORALL批量执行。它的优势在于 SQL 文本固定只有一份解析计划绑定变量只有数组一个网络往返一次。代价是要在数据库侧建类型、写过程跨团队协作时沟通成本高一些。方式网络往返SQL 解析绑定变量上限适用量级foreach 多值1 次每次文本不同硬解析受 65535 限制千级以内INSERT ALL1 次文本膨胀解析成本高无硬限制但内存吃紧千级存储过程 数组1 次固定文本软解析无万级以上2.2 数组入参的类型设计Oracle 侧需要两层类型一层是行对象OBJECT一层是行对象的集合TABLE OF。行对象的字段要和 Java 侧传入的 List 元素字段一一对应。这里有个容易忽略的点TABLE OF必须是TABLE OF 行对象类型不能直接TABLE OF VARCHAR2再靠下标拆后者在字段多的时候维护起来是灾难。-- 1. 定义行对象类型字段与 Java 侧 DTO 对齐 CREATE OR REPLACE TYPE T_EMP_ROW AS OBJECT ( EMP_ID NUMBER, EMP_NAME VARCHAR2(100), DEPT_NO VARCHAR2(20), SALARY NUMBER(12,2) ); / -- 2. 定义行对象集合类型供存储过程入参使用 CREATE OR REPLACE TYPE T_EMP_TAB AS TABLE OF T_EMP_ROW; /类型建好后存储过程签名里直接写P_DATA IN T_EMP_TAB。注意T_EMP_TAB是 schema 级类型不是包内类型这样 JDBC 侧才能通过StructDescriptor找到它。如果定义在包内Java 侧拿不到类型描述符调用时会报ORA-06550或类型不匹配。2.3 存储过程主体的 FORALL 写法过程内部用FORALL而不是逐行INSERT这是批量性能的关键。FORALL会把整个集合一次性交给 SQL 引擎减少 PL/SQL 与 SQL 引擎之间的上下文切换。CREATE OR REPLACE PROCEDURE P_BATCH_INSERT_EMP ( P_DATA IN T_EMP_TAB ) AS BEGIN -- FORALL 一次性提交整个集合避免逐行循环 FORALL i IN 1 .. P_DATA.COUNT INSERT INTO EMP_TAB (EMP_ID, EMP_NAME, DEPT_NO, SALARY) VALUES ( P_DATA(i).EMP_ID, P_DATA(i).EMP_NAME, P_DATA(i).DEPT_NO, P_DATA(i).SALARY ); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; /FORALL的索引必须从 1 开始连续如果集合是稀疏的比如中间有删除要用INDICES OF变体。异常处理里显式ROLLBACK再RAISE是为了保证批量操作要么全成要么全败避免部分写入后调用方拿到异常却不知道落了多少条。3. MyBatis 侧怎么把 List 映射成 Oracle 数组3.1 Mapper 接口与 XML 的参数声明MyBatis 本身不认识 Oracle 的TABLE OF类型需要借助TypeHandler把 Java List 转成java.sql.Array。Mapper 接口方法签名里List 参数用Param命名XML 里用modeOUT之外的jdbcTypeARRAY声明。public interface EmpMapper { // 传入 List由自定义 TypeHandler 负责转成 Oracle 数组 void batchInsertByProcedure(Param(empList) ListEmpDTO empList); }XML 里调用存储过程注意statementTypeCALLABLE参数用jdbcTypeARRAY并指定typeHandler。insert idbatchInsertByProcedure statementTypeCALLABLE {call P_BATCH_INSERT_EMP( #{empList, jdbcTypeARRAY, modeIN, typeHandlercom.example.handler.EmpArrayTypeHandler} )} /insert这里modeIN是默认值写出来是为了可读性。typeHandler的全限定名必须和实际类路径一致写错会报ClassNotFoundException或TypeException。3.2 自定义 TypeHandler 的核心实现TypeHandler 要做三件事把 List 转成Struct[]用StructDescriptor描述行结构再用ArrayDescriptor创建数组。连接对象从BaseTypeHandler的setNonNullParameter里拿。MappedTypes(List.class) public class EmpArrayTypeHandler extends BaseTypeHandlerListEmpDTO { // Oracle 侧定义的类型名必须大写 private static final String TYPE_NAME T_EMP_TAB; private static final String ROW_TYPE_NAME T_EMP_ROW; Override public void setNonNullParameter(PreparedStatement ps, int i, ListEmpDTO parameter, JdbcType jdbcType) throws SQLException { Connection conn ps.getConnection(); try { // 1. 描述行对象结构字段顺序必须与 T_EMP_ROW 一致 StructDescriptor rowDesc StructDescriptor.createDescriptor( ROW_TYPE_NAME, conn); Object[] structs new Object[parameter.size()]; for (int idx 0; idx parameter.size(); idx) { EmpDTO dto parameter.get(idx); Object[] row new Object[] { dto.getEmpId(), dto.getEmpName(), dto.getDeptNo(), dto.getSalary() }; structs[idx] new STRUCT(rowDesc, conn, row); } // 2. 描述集合类型并创建数组 ArrayDescriptor arrDesc ArrayDescriptor.createDescriptor( TYPE_NAME, conn); ARRAY array new ARRAY(arrDesc, conn, structs); ps.setArray(i, array); } catch (SQLException e) { throw new SQLException(List 转 Oracle 数组失败, e); } } Override public ListEmpDTO getNullableResult(ResultSet rs, String columnName) throws SQLException { return null; // 入参场景不需要读取 } // 其余 getNullableResult 重载省略返回 null 即可 }StructDescriptor.createDescriptor的第一个参数是类型名Oracle 默认大写如果建类型时用了小写或引号这里要对应调整。STRUCT构造函数的Object[]顺序必须和T_EMP_ROW字段声明顺序完全一致错一位就会把姓名写进工资字段而且不报错这是最阴的坑。3.3 连接池与类型缓存的注意事项StructDescriptor和ArrayDescriptor每次调用都创建会有性能开销但缓存它们又涉及连接生命周期。稳妥做法是用Connection级别的缓存或者干脆不缓存——批量场景本身调用频率不高描述符创建的开销相对FORALL的收益可以忽略。如果确实要缓存注意连接池切换连接后描述符可能失效需要按连接维度存。另外Druid 或 HikariCP 这类连接池在setArray后如果发生连接归还数组对象可能被提前释放。确保存储过程调用和数组设置在同一连接、同一事务内完成不要在中间做连接切换。4. 避坑类型映射与事务边界的五个血泪教训4.1 现象调用报 ORA-06550提示类型不存在原因通常是类型建在了包内或者建类型时用了引号导致大小写敏感。Java 侧StructDescriptor.createDescriptor(T_EMP_TAB, conn)传的是大写数据库里实际存的是小写匹配不上。解决建类型时不要加引号让 Oracle 默认转大写如果已经建了小写要么重建要么在 Java 侧传带引号的类型名。更稳的做法是统一用 schema 级类型避免包内类型。4.2 现象数据写进去了但字段错位原因就是STRUCT的Object[]顺序和T_EMP_ROW字段声明顺序不一致。Oracle 不会校验字段名只按位置匹配错位后数据静默写错。解决把行对象字段顺序和 Java DTO 字段顺序写成注释对照表改任何一边都同步改另一边。有条件的话写个单元测试插入一条已知数据再查出来比对。4.3 现象批量插入到一半报错前面数据已提交原因是存储过程里FORALL之后直接COMMIT异常处理里虽然ROLLBACK但如果调用方在 MyBatis 层又包了一层事务ROLLBACK可能只回滚了过程内的部分。解决存储过程内不要COMMIT把事务控制权交给调用方。MyBatis 的SqlSession开启事务过程执行完由 Java 侧统一提交或回滚。这样批量操作的原子性由外层事务保证过程内只负责执行。4.4 现象List 为空时调用报 ORA-06533 或数组下标越界原因是FORALL i IN 1 .. P_DATA.COUNT在集合为空时COUNT为 01 .. 0在某些 PL/SQL 版本下行为不一致。解决过程开头加空集合判断IF P_DATA IS NULL OR P_DATA.COUNT 0 THEN RETURN; END IF;。Java 侧也做一次空 List 拦截避免无意义的数据库调用。4.5 现象大批量时内存溢出或响应超时原因是 List 太大STRUCT[]在 Java 侧占用大量堆内存或者FORALL一次性提交的数据量超过 undo 表空间。解决在 Java 侧做分批比如每 2000 条调用一次存储过程外层循环控制批次。批次大小根据单行字段数和 undo 表空间调整字段多就调小。分批后每批独立事务失败只影响当前批重试成本也低。5. 进阶用批量错误日志和批次调优把方案跑稳5.1 用 FORALL 的 SAVE EXCEPTIONS 定位坏数据FORALL默认遇到第一条错误就中断批量场景下更希望知道哪些行有问题。加SAVE EXCEPTIONS后所有行都会尝试执行错误收集在SQL%BULK_EXCEPTIONS里。CREATE OR REPLACE PROCEDURE P_BATCH_INSERT_EMP ( P_DATA IN T_EMP_TAB ) AS -- 定义批量异常收集类型 e_bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381); BEGIN IF P_DATA IS NULL OR P_DATA.COUNT 0 THEN RETURN; END IF; FORALL i IN 1 .. P_DATA.COUNT SAVE EXCEPTIONS INSERT INTO EMP_TAB (EMP_ID, EMP_NAME, DEPT_NO, SALARY) VALUES (P_DATA(i).EMP_ID, P_DATA(i).EMP_NAME, P_DATA(i).DEPT_NO, P_DATA(i).SALARY); EXCEPTION WHEN e_bulk_errors THEN -- 遍历错误集合记录出错行号和错误码 FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP DBMS_OUTPUT.PUT_LINE(行号: || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 错误码: || SQL%BULK_EXCEPTIONS(j).ERROR_CODE); END LOOP; RAISE; END; /ERROR_INDEX对应集合下标ERROR_CODE是 Oracle 错误码。生产环境可以把这些信息写进日志表调用方根据日志决定重试哪些行。注意SAVE EXCEPTIONS下FORALL仍然是一个整体异常抛出后已成功的行是否提交取决于外层事务所以事务控制还是要放在 Java 侧。5.2 批次大小的经验取值批次大小没有万能值我一般按这个顺序试先看单行字段数字段少于 10 个批次从 2000 起字段 10 到 30 个批次 1000超过 30 个字段批次 500。然后观察两个指标单批执行时间和 undo 表空间增长。单批超过 2 秒就调小undo 增长过快也调小。调优时用固定数据集跑三遍取平均避免单次波动误导。单行字段数建议批次观察指标 102000单批耗时、undo 增长10 ~ 301000单批耗时、错误日志量 30500单批耗时、Java 堆占用5.3 验证方案是否跑通的最小闭环写完代码别急着上生产先跑一个最小闭环建一张测试表造 10 条数据调一次存储过程查出来比对字段。再故意造一条坏数据比如主键重复确认SAVE EXCEPTIONS能定位到行号。最后把批次调到 5000 条压一次看耗时和内存。这三步走完方案基本就稳了。我自己在这个方案上翻过两次车一次是字段错位没测出来上线后对账才发现一次是过程内COMMIT导致外层回滚失效。后来养成的习惯是任何存储过程入参先在 PL/SQL 里手工构造一个集合调通再写 Java 侧映射最后才接 MyBatis。顺序反了排查成本翻倍。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
