从存储过程返回的STRUCT中读取数组 [英] Read an ARRAY from a STRUCT returned by a stored procedure
问题描述
数据库中有以下三种Oracle定制类型(简化):
In the database are three Oracle custom types (simplified) as follows:
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)
);
和一个用于构建对象树的函数
and a function, that build the object tree
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
函数,并在Java代码中使用其结果T_ENCLOSURE
对象.
Now I want to call the GET_ENCLOSURE
function and work with its result T_ENCLOSURE
object in my Java code.
// 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
我可以通过
Integer id = ((BigInteger)resultStruct.getAttributes()[0]).intValue(); // works for me
String name = (String)resultStruct.getAttributes()[1]); // works for me
但是,我似乎无法获得动物名单
However, I cannot seem to get the list of animals
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")
我在这里有一些尝试和错误,
I had a bit of trial and error here including
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>
?
推荐答案
只要特定于Oracle的解决方案就足够了,关键就在DTO内.它们都必须实现ORAData
和ORADataFactory
As long as a Oracle specific solution is sufficient, the key lies within the DTOs. All of them have to implement ORAData
and ORADataFactory
public class TAnimal implements ORAData, ORADataFactory {
Integer animal_id, number_of_hairs;
public TAnimal() { }
// [ Getter and Setter omitted here ]
@Override
public Datum toDatum(Connection connection) throws SQLException {
OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
StructDescriptor structDescriptor = StructDescriptor.createDescriptor("zoo_schema.T_ANIMAL", oracleConnection);
Object[] attributes = {
this.animal_id,
this.number_of_hairs
};
return new STRUCT(structDescriptor, oracleConnection, attributes);
}
@Override
public TAnimal create(Datum datum, int sqlTypeCode) throws SQLException {
if (datum == null) {
return null;
}
Datum[] attributes = ((STRUCT) datum).getOracleAttributes();
TAnimal result = new TAnimal();
result.animal_id = asInteger(attributes[0]); // see TEnclosure#asInteger(Datum)
result.number_of_hairs = asInteger(attributes[1]); // see TEnclosure#asInteger(Datum)
return result;
}
}
和
public class TEnclosure implements ORAData, ORADataFactory {
Integer enclosureId;
String enclosureName;
List<Animal> animals;
public TEnclosure() {
this.animals = new ArrayList<>();
}
// [ Getter and Setter omitted here ]
@Override
public Datum toDatum(Connection connection) throws SQLException {
OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
StructDescriptor structDescriptor = StructDescriptor.createDescriptor("zoo_schema.T_ENCLOSURE", oracleConnection);
Object[] attributes = {
this.enclosureId,
this.enclosureName,
null // TODO: solve this; however, retrieving data works without this
};
return new STRUCT(structDescriptor, oracleConnection, attributes);
}
@Override
public TEnclosure create(Datum datum, int sqlTypeCode) throws SQLException {
if (datum == null) {
return null;
}
Datum[] attributes = ((STRUCT) datum).getOracleAttributes();
TEnclosure result = new TEnclosure();
result.enclosureId = asInteger(attributes[0]);
result.enclosureName = asString(attributes[1]);
result.animals = asListOfAnimals(attributes[2]);
return result;
}
// Utility methods
Integer asInteger(Datum datum) throws SQLException {
if (datum == null)
return null;
else
return ((NUMBER) datum).intValue(); // oracle.sql.NUMBER
}
String asString(Datum datum) throws SQLException {
if (datum = null)
return null;
else
return ((CHAR) datum).getString(); // oracle.sql.CHAR
}
List<TAnimal> asListOfAnimals(Datum datum) throws SQLException {
if (datum == null)
return null;
else {
TAnimal factory = new TAnimal();
List<TAnimal> result = new ArrayList<>();
ARRAY array = (ARRAY) datum; // oracle.sql.ARRAY
Datum[] elements = array.getOracleArray();
for (int i = 0; i < elements.length; i++) {
result.add(factory.create(elements[i], 0));
}
return result;
}
}
}
然后获取数据的方式如下:
then fetching the data works like so:
TEnclosure factory = new TEnclosure();
Connection connection = null;
OracleConnection oracleConnection = null;
OracleCallableStatement oracleCallableStatement = null;
try {
connection = MyConnectionFactory.getConnection(SOME_CONNECTION_CONFIG);
oracleConnection = connection.unwrap(OracleConnection.class);
oracleCallableStatement = (OracleCallableStatement) oracleConnection.prepareCall("{? = call zoo_schema.zoo_utils.GET_ENCLOSURE( ? )}");
oracleCallableStatement.registerOutParameter(1, OracleTypes.STRUCT, "zoo_schema.T_ENCLOSURE");
oracleCallableStatement.setInt(2, 6); // fetch data for ENCLOSURE#6
// Execute query
oracleCallableStatement.executeQuery();
// Check result
Object oraData = oracleCallableStatement.getORAData(1, factory);
LOGGER.info("oraData is a {}", oraData.getClass().getName()); // acme.zoo.TEnclosure
} finally {
ResourceUtils.closeQuietly(oracleCallableStatement);
ResourceUtils.closeQuietly(oracleConnection);
ResourceUtils.closeQuietly(connection); // probably not necessary...
}
这篇关于从存储过程返回的STRUCT中读取数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!