Spring Boot调用含嵌套自定义对象的Oracle PL/SQL存储过程报错解决

错误原因 #

你遇到的InvalidDefinitionException是因为SimpleJdbcCall返回的结果中包含Oracle原生的oracle.sql.STRUCT/oracle.sql.ARRAY对象，这些对象内部持有数据库连接的引用。当你尝试将返回的Map序列化（比如返回给前端）时，Jackson会递归遍历对象属性，遇到连接对象的包装器时形成循环引用，导致序列化失败。

解决方案 #

核心思路是将Oracle自定义对象/集合转换为对应的Java实体类，而不是保留原生Oracle类型。需要自定义类型处理器，解析Oracle的STRUCT和ARRAY为Java对象。

步骤1：创建对应Oracle自定义类型的Java实体类 #

根据Oracle的自定义类型，创建匹配的Java类（属性名和类型需与数据库类型对应）：

1.1 对应CTRY_INT_VOLUMES_OBJ的实体类

public class CtryIntVolumesObj {
    private String countryCode;
    private List<RmsVolObj> rmsVolumePoints; // 对应ds_rms_vol_obj_tab，需提前定义RmsVolObj类

    // 构造器、getter、setter方法
}

1.2 对应DS_INT_RMS_VOLUME_OBJ的实体类

public class DsIntRmsVolumeObj {
    private String orderMonth;
    private BigDecimal dsPpv;
    private BigDecimal dsDlv;
    // ... 其他基本类型属性，NUMBER用BigDecimal，DATE用LocalDate等
    private List<RmsVolObj> rmsVolumes; // 对应ds_rms_vol_obj_tab
    private List<CtryIntVolumesObj> countryVolumes; // 对应CTRY_INT_VOLTYPE_TAB
    private BigDecimal cdlv;

    // 构造器、getter、setter方法
}

步骤2：自定义类型处理器 #

2.1 自定义SqlReturnType解析单个STRUCT对象

public class CustomStructReturnType implements SqlReturnType {
    private final Class<?> targetClass;

    public CustomStructReturnType(Class<?> targetClass) {
        this.targetClass = targetClass;
    }

    @Override
    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
        STRUCT struct = (STRUCT) cs.getObject(paramIndex);
        if (struct == null) {
            return null;
        }
        Object[] attributes = struct.getAttributes();
        
        if (targetClass == DsIntRmsVolumeObj.class) {
            DsIntRmsVolumeObj obj = new DsIntRmsVolumeObj();
            obj.setOrderMonth((String) attributes[0]);
            obj.setDsPpv((BigDecimal) attributes[1]);
            obj.setDsDlv((BigDecimal) attributes[2]);
            // ... 映射其他基本属性
            // 解析嵌套集合rmsVolumes
            ARRAY rmsVolArray = (ARRAY) attributes[21];
            obj.setRmsVolumes(parseArray(rmsVolArray, RmsVolObj.class));
            // 解析嵌套集合countryVolumes
            ARRAY countryVolArray = (ARRAY) attributes[22];
            obj.setCountryVolumes(parseArray(countryVolArray, CtryIntVolumesObj.class));
            return obj;
        } else if (targetClass == CtryIntVolumesObj.class) {
            CtryIntVolumesObj obj = new CtryIntVolumesObj();
            obj.setCountryCode((String) attributes[0]);
            ARRAY rmsArray = (ARRAY) attributes[1];
            obj.setRmsVolumePoints(parseArray(rmsArray, RmsVolObj.class));
            return obj;
        }
        return null;
    }

    // 通用解析ARRAY为Java列表的方法
    private <T> List<T> parseArray(ARRAY array, Class<T> clazz) throws SQLException {
        if (array == null) {
            return Collections.emptyList();
        }
        Object[] arrayAttrs = (Object[]) array.getArray();
        List<T> list = new ArrayList<>(arrayAttrs.length);
        for (Object attr : arrayAttrs) {
            if (attr instanceof STRUCT) {
                CustomStructReturnType handler = new CustomStructReturnType(clazz);
                T obj = (T) handler.getTypeValue(null, 0, 0, ((STRUCT) attr).getSQLTypeName());
                list.add(obj);
            }
        }
        return list;
    }
}

2.2 自定义SqlReturnArray解析ARRAY集合

public class CustomArrayReturnType implements SqlReturnArray {
    private final Class<?> elementType;

    public CustomArrayReturnType(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public Object getTypeValue(CallableStatement cs, int paramIndex, int sqlType, String typeName) throws SQLException {
        ARRAY array = (ARRAY) cs.getObject(paramIndex);
        if (array == null) {
            return Collections.emptyList();
        }
        Object[] structs = (Object[]) array.getArray();
        List<Object> resultList = new ArrayList<>(structs.length);
        CustomStructReturnType structHandler = new CustomStructReturnType(elementType);
        for (Object struct : structs) {
            if (struct instanceof STRUCT) {
                resultList.add(structHandler.getTypeValue(null, 0, 0, ((STRUCT) struct).getSQLTypeName()));
            }
        }
        return resultList;
    }
}

步骤3：修改SimpleJdbcCall代码 #

使用自定义类型处理器替换默认的SqlReturnArray：

public Map<String, Object> getVolumesInfo() {
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("get_rms_volumes_info")
            .withCatalogName("DS_INT_COMMON_PKG")
            .withoutProcedureColumnMetaDataAccess()
            .declareParameters(
                    new SqlOutParameter("out_chr_err_code", Types.VARCHAR),
                    new SqlOutParameter("out_chr_err_msg", Types.VARCHAR),
                    new SqlOutParameter("out_chr_ds_id", Types.VARCHAR),
                    // 使用自定义数组处理器，指定元素类型为DsIntRmsVolumeObj
                    new SqlOutParameter("out_ds_volume_tab", Types.ARRAY, "DS_INT_RMS_VOLUME_TBL", 
                            new CustomArrayReturnType(DsIntRmsVolumeObj.class)),
                    // ... 其他OUT参数保持原定义
                    new SqlParameter("in_chr_debug", Types.VARCHAR),
                    new SqlParameter("in_chr_service_consumer", Types.VARCHAR),
                    new SqlParameter("in_chr_ds_id", Types.VARCHAR),
                    new SqlParameter("in_chr_from_month", Types.VARCHAR),
                    new SqlParameter("in_chr_to_month", Types.VARCHAR),
                    new SqlParameter("include_Order_purpose", Types.VARCHAR),
                    new SqlParameter("inventory_order_month", Types.VARCHAR)
            );

    Map<String, String> inParams = new HashMap<>();
    inParams.put("in_chr_debug", "N");
    inParams.put("in_chr_service_consumer", "MYHL");
    inParams.put("in_chr_ds_id", "STAFF");
    inParams.put("in_chr_from_month", "202208");
    inParams.put("in_chr_to_month", "202208");
    inParams.put("include_Order_purpose", "Y");
    inParams.put("inventory_order_month", "Y");

    return simpleJdbcCall.execute(inParams);
}

额外注意事项 #

• 确保Oracle类型名（如DS_INT_RMS_VOLUME_TBL）与数据库中定义的完全一致（Oracle默认大写）。
• 需根据实际Oracle类型定义补充RmsVolObj类的属性和映射逻辑。
• 如果需要将结果序列化返回，Java实体类需避免循环引用，必要时可添加@JsonIgnore忽略无关属性。

内容的提问来源于stack exchange，提问作者Aishwarya B