如何从Java向Oracle存储过程传递JSON对象数组实现批量插入？

一、问题分析 #

你当前的实现只能处理单个Element对象，要支持数组格式的JSON，需要从存储过程批量处理能力和Java端数组参数传递两方面进行改造。核心是让存储过程能接收多个Element ID，并批量插入成员表，同时保证主表与成员表的关联ID正确。

二、存储过程改造 #

首先我们需要解决两个问题：接收多个Element ID、正确关联主表的ELEMENT_SET_ID。

1. 定义数组类型

先创建一个用于传递多个Element ID的自定义数组类型：

CREATE OR REPLACE TYPE VARCHAR2_ARRAY AS TABLE OF VARCHAR2(100);
/

2. 重写存储过程

修改后的存储过程会先插入主表PAY_ELEMENT_SETS，获取生成的主键ID，再用FORALL批量插入成员表（比循环插入效率高）：

CREATE OR REPLACE PROCEDURE INSERT_ELE_BATCH (
    ELEMENT_SET_NAME IN VARCHAR2,
    ELEMENT_SET_TYPE IN VARCHAR2,
    EFFECTIVE_START_DATE IN VARCHAR2,
    EFFECTIVE_END_DATE IN VARCHAR2,
    ELEMENT_TYPE_IDS IN VARCHAR2_ARRAY, -- 接收多个Element ID的数组
    OUT_SEQ OUT NUMBER
) AS
    v_element_set_id NUMBER;
BEGIN
    -- 1. 插入主表PAY_ELEMENT_SETS，获取主键ID
    -- 假设主表的ELEMENT_SET_ID由序列PAY_ELEMENT_SETS_SEQ生成，需确保序列存在
    INSERT INTO payroll_test.PAY_ELEMENT_SETS(
        ELEMENT_SET_NAME,
        ELEMENT_SET_TYPE,
        EFFECTIVE_START_DATE,
        EFFECTIVE_END_DATE,
        ELEMENT_SET_ID
    ) VALUES (
        ELEMENT_SET_NAME,
        ELEMENT_SET_TYPE,
        EFFECTIVE_START_DATE,
        EFFECTIVE_END_DATE,
        PAY_ELEMENT_SETS_SEQ.NEXTVAL
    ) RETURNING ELEMENT_SET_ID INTO v_element_set_id;

    -- 2. 批量插入成员表，使用FORALL提升性能
    FORALL i IN 1..ELEMENT_TYPE_IDS.COUNT
        INSERT INTO payroll_test.PAY_ELEMENT_SET_MEMBERS(
            ELEMENT_TYPE_ID,
            ELEMENT_SET_ID
        ) VALUES (
            ELEMENT_TYPE_IDS(i),
            v_element_set_id
        );

    COMMIT;
    OUT_SEQ := v_element_set_id; -- 返回主表的主键ID
END INSERT_ELE_BATCH;
/

注意：如果PAY_ELEMENT_SETS表的ELEMENT_SET_ID是由触发器自动生成的，可去掉INSERT语句里的ELEMENT_SET_ID赋值，直接用RETURNING获取即可。

三、Java代码改造 #

现在需要解析JSON中的Element数组，提取所有elementId，并将其转换为Oracle支持的数组参数传递给存储过程。

1. 解析JSON数组（以Jackson处理为例）

从请求JSON中提取Element数组并提取ID：

// 假设已将JSON解析到objectGroupFormBean，获取element数组
List<Object> elementList = objectGroupFormBean.getElement();
String[] elementIds = new String[elementList.size()];

for (int i = 0; i < elementList.size(); i++) {
    // 适配Map或自定义Bean两种情况
    if (elementList.get(i) instanceof Map) {
        Map<String, Object> elementMap = (Map<String, Object>) elementList.get(i);
        elementIds[i] = elementMap.get("elementId").toString();
    } else {
        ElementBean elementBean = (ElementBean) elementList.get(i);
        elementIds[i] = elementBean.getElementId();
    }
}

2. 调用改造后的存储过程

将字符串数组转换为Oracle的ARRAY类型，传递给存储过程：

String query = "{call INSERT_ELE_BATCH(?,?,?,?,?,?)}";
cstmt = connection.prepareCall(query);

// 设置主表参数
cstmt.setString(1, objectGroupFormBean.getElementSetName());
cstmt.setString(2, objectGroupFormBean.getElementSetType());
cstmt.setString(3, objectGroupFormBean.getEffectiveStartDate());
cstmt.setString(4, objectGroupFormBean.getEffectiveEndDate());

// 将Java数组转换为Oracle VARCHAR2_ARRAY类型
OracleConnection oracleConn = connection.unwrap(OracleConnection.class);
ARRAY elementIdArray = oracleConn.createARRAY("VARCHAR2_ARRAY", elementIds);
cstmt.setArray(5, elementIdArray);

// 注册输出参数并执行
cstmt.registerOutParameter(6, OracleTypes.NUMBER);
cstmt.executeUpdate();

// 获取返回的主表ID
int elementSetId = cstmt.getInt(6);
objectGroupFormBean.setElementSetId(elementSetId);
objectGroupFormBeanList.add(objectGroupFormBean);

注意：需确保项目引入Oracle JDBC驱动（ojdbc8+），且VARCHAR2_ARRAY的名称与数据库定义完全一致（大小写敏感，除非用双引号定义）。

四、可选优化：直接接收JSON字符串 #

如果不想定义数组类型，也可以让存储过程直接接收整个JSON字符串，在PL/SQL中解析：

CREATE OR REPLACE PROCEDURE INSERT_ELE_FROM_JSON (
    INPUT_JSON IN CLOB,
    OUT_SEQ OUT NUMBER
) AS
    v_element_set_name VARCHAR2(100);
    v_element_set_type VARCHAR2(100);
    v_eff_start_date VARCHAR2(20);
    v_eff_end_date VARCHAR2(20);
    v_element_set_id NUMBER;
BEGIN
    -- 解析JSON主表字段
    SELECT 
        json_value(INPUT_JSON, '$.elementSetName'),
        json_value(INPUT_JSON, '$.elementSetType'),
        json_value(INPUT_JSON, '$.effectiveStartDate'),
        json_value(INPUT_JSON, '$.effectiveEndDate')
    INTO v_element_set_name, v_element_set_type, v_eff_start_date, v_eff_end_date
    FROM dual;

    -- 插入主表
    INSERT INTO payroll_test.PAY_ELEMENT_SETS(...) VALUES (...) RETURNING ELEMENT_SET_ID INTO v_element_set_id;

    -- 解析JSON数组并批量插入成员表
    INSERT INTO payroll_test.PAY_ELEMENT_SET_MEMBERS(ELEMENT_TYPE_ID, ELEMENT_SET_ID)
    SELECT 
        json_value(value, '$.elementId'),
        v_element_set_id
    FROM json_table(INPUT_JSON, '$.element[*]' COLUMNS value VARCHAR2(4000) FOR JSON PATH);

    COMMIT;
    OUT_SEQ := v_element_set_id;
END INSERT_ELE_FROM_JSON;
/

这种方式Java端只需传递整个JSON字符串作为CLOB参数，无需处理数组转换，适合JSON结构复杂的场景。

内容的提问来源于stack exchange，提问作者Second View