java - 从存储过程返回的 STRUCT 中读取 ARRAY

Question

数据库中有如下三种Oracle自定义类型(简体):

create or replace TYPE T_ENCLOSURE AS OBJECT(
  ENCLOSURE_ID      NUMBER(32,0),
  ENCLOSURE_NAME    VARCHAR2(255 BYTE),
  ANIMALS           T_ARRAY_ANIMALS,

  MEMBER FUNCTION   CHECK_IF_RED RETURN BOOLEAN
);


create or replace TYPE T_ARRAY_ANIMALS is TABLE OF T_ANIMAL;


create or replace TYPE T_ANIMAL AS OBJECT(
  ANIMAL_ID NUMBER(32,0),
  NUMBER_OF_HAIRS NUMBER(32,0)
);

和一个构建对象树的函数

FUNCTION GET_ENCLOSURE ( f_enclosure_id zoo_schema.ENCLOSURE_TABLE.ENCLOSURE_ID%TYPE ) RETURN T_ENCLOSURE
AS
    v_ENC T_ENCLOSURE;
    v_idx pls_integer;

BEGIN

    v_ENC := T_ENCLOSURE(
        f_enclosure_id,
        NULL,
        T_ARRAY_ANIMALS(T_ANIMAL(NULL,NULL))
    );

    SELECT ENCLOSURE_NAME
    INTO   v_ENC.ENCLOSURE_NAME
    FROM   ENCLOSURE_TABLE WHERE ENCLOSURE_ID = f_ENCLOSURE_ID;

    SELECT
        CAST(MULTISET(
            SELECT ANIMAL_ID, NUMBER_OF_HAIRS
            FROM   ANIMAL_TABLE
            WHERE  ENCLOSURE_ID = f_ENCLOSURE_ID
        ) AS T_ARRAY_ANIMALS
    )
    INTO v_ENC.ANIMALS
    FROM dual;

RETURN v_ENC;

END;

现在我想调用GET_ENCLOSURE函数并处理其结果 T_ENCLOSURE我的 Java 代码中的对象。

// prepare the call
Connection connection = MyConnectionFactory.getConnection(SOME_CONNECTION_CONFIG);
CallableStatement stmt = connection.prepareCall("{? = call zoo_schema.zoo_utils.GET_ENCLOSURE( ? )}");
stmt.registerOutParameter(1, OracleTypes.STRUCT, "zoo_schema.T_ENCLOSURE");
stmt.setInt(2, 6);  // fetch data for ENCLOSURE#6

// execute function
stmt.executeQuery();

// extract the result
Struct resultStruct = (Struct)stmt.getObject(1); // java.sql.Struct

我可以通过

访问 ID和 NAME

Integer id = ((BigInteger)resultStruct.getAttributes()[0]).intValue(); // works for me
String name = (String)resultStruct.getAttributes()[1]); // works for me

但是，我似乎无法获取动物的列表

resultStruct.getAttributes()[2].getClass().getCanonicalName(); // oracle.sql.ARRAY
ARRAY arrayAnimals = (ARRAY)jdbcStruct.getAttributes()[2];
arrayAnimals.getArray(); // throws a java.sql.SQLException("Internal Error: Unable to resolve name")

我在这里进行了一些尝试和错误，包括

OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
STRUCT resultOracleStruct = (STRUCT) stmt.getObject(1); // oracle.sql.STRUCT
oracleConnection.createARRAY("zoo_schema.T_ARRAY_ANIMALS", resultOracleStruct.getAttributes()[2]) // throws an SQLException("Fail to convert to internal representation: oracle.sql.ARRAY@8de7cfc4")

但也不走运。

如何将动物列表放入 List<TAnimal> 中？

Answer 1

创建实现 java.sql.SQLData 的对象.在这种情况下，创建 TEnclosure和 TAnimal类，它们都实现了 SQLData .

仅供引用，在较新的 Oracle JDBC 版本中，类型如 oracle.sql.ARRAY已弃用 java.sql类型。虽然我不确定如何仅使用 java.sql 来编写数组(如下所述) API。

当您实现 readSQL() 时您按顺序阅读字段。您获得一个 java.sql.Array与 sqlInput.readArray() .所以TEnclosure.readSQL()看起来像这样。

@Override
public void readSQL(SQLInput sqlInput, String s) throws SQLException {
    id = sqlInput.readBigDecimal();
    name = sqlInput.readString();
    Array animals = sqlInput.readArray();
    // what to do here...
}

备注:readInt()也存在，但 Oracle JDBC 似乎总是提供 BigDecimal对于 NUMBER

您会注意到一些 API，例如 java.sql.Array有采用类型映射的方法 Map<String, Class<?>>这是 Oracle 类型名称与其对应的 Java 类实现 SQLData 的映射。 (ORAData 也可以吗？)。

如果你调用Array.getArray() , 你会得到 Struct对象，除非 JDBC 驱动程序通过 Connection.setTypeMap(typeMap) 知道您的类型映射.但是，在连接上设置 typeMap 对我不起作用，所以我使用 getArray(typeMap)

创建您的 Map<String, Class<?>> typeMap某处并为您的类型添加条目:

typeMap.put("T_ENCLOSURE", TEnclosure.class);
typeMap.put("T_ANIMAL", TAnimal.class);

在 SQLData.readSQL() 内实现，调用sqlInput.readArray().getArray(typeMap) ，返回 Object[] Object在哪里条目或类型 TAnimal .

当然代码要转换成List<TAnimal>变得乏味，所以只需使用这个实用函数并根据您的需要调整它，直到 null 与空列表策略:

/**
 * Constructs a list from the given SQL Array
 * Note: this needs to be static because it's called from SQLData classes.
 *
 * @param <T> SQLData implementing class
 * @param array Array containing objects of type T
 * @param typeClass Class reference used to cast T type
 * @return List<T> (empty if array=null)
 * @throws SQLException
 */
public static <T> List<T> listFromArray(Array array, Class<T> typeClass) throws SQLException {
    if (array == null) {
        return Collections.emptyList();
    }
    // Java does not allow casting Object[] to T[]
    final Object[] objectArray = (Object[]) array.getArray(getTypeMap());
    List<T> list = new ArrayList<>(objectArray.length);
    for (Object o : objectArray) {
        list.add(typeClass.cast(o));
    }
    return list;
}

写入数组

弄清楚如何编写一个数组令人沮丧，Oracle API 需要一个连接来创建一个数组，但在 writeSQL(SQLOutput sqlOutput) 的上下文中没有明显的连接.还好，this blog有一个技巧/技巧来获得 OracleConnection ，我在这里使用过。

当您使用 createOracleArray() 创建数组时您为类型名称指定列表类型 ( T_ARRAY_ANIMALS )，而不是单个对象类型。

这是一个用于编写数组的通用函数。在你的情况下，listType将是 "T_ARRAY_ANIMALS"你会传入 List<TAnimal>

/**
 * Write the list out as an Array
 *
 * @param sqlOutput SQLOutput to write array to
 * @param listType array type name (table of type)
 * @param list List of objects to write as an array
 * @param <T> Class implementing SQLData that corresponds to the type listType is a list of.
 * @throws SQLException
 * @throws ClassCastException if SQLOutput is not an OracleSQLOutput
 */
public static <T> void writeArrayFromList(SQLOutput sqlOutput, String listType, @Nullable List<T> list) throws SQLException {
    final OracleSQLOutput out = (OracleSQLOutput) sqlOutput;
    OracleConnection conn = (OracleConnection) out.getSTRUCT().getJavaSqlConnection();
    conn.setTypeMap(getTypeMap());  // not needed?
    if (list == null) {
        list = Collections.emptyList();
    }
    final Array array = conn.createOracleArray(listType, list.toArray());
    out.writeArray(array);
}

注意事项:

• 有一次我想setTypeMap是必需的，但现在当我删除该行时，我的代码仍然有效，所以我不确定是否有必要。
• 我不确定您应该写 null 还是空数组，但我假设空数组更正确。

Oracle 类型提示

• Oracle 将所有内容都大写，因此所有类型名称都应大写。
• 您可能需要指定 SCHEMA.TYPE_NAME如果该类型不在您的默认架构中。
• 记得调用grant execute如果您正在连接的用户不是所有者，则在类型上。
  如果您对包执行，但不是类型，getArray()当它试图寻找类型元数据时会抛出异常。

Spring

对于使用 Spring 的开发人员，您可能需要查看 Spring Data JDBC Extensions , 它提供了 SqlArrayValue和 SqlReturnArray , 这对于创建 SimpleJdbcCall 很有用对于将数组作为参数或返回数组的过程。

章节7.2.1 Setting ARRAY values using SqlArrayValue for an IN parameter解释如何使用数组参数调用过程。