Re: Mapping Oracle collection in Java/Hibernate
I see that mentioned in "JDBC Developer's Guide,"
"16 Working with Oracle Collections" under
"Using a Type Map to Map Array Elements":
<http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.h...>
Great! Thanks again, John.
It seemed strange that no official documentation was provided for such
a thing...I tried to search for docs using different key words
(limitation of non-ontological search engines :P).
Hi there,
only to post working code that solved my question.
Perhaps, there is room to improve the code either the design solution.
<code>
public List<SampledValue> getResults() {
//int progress = 0;
List<SampledValue> svList = new ArrayList<SampledValue>();
try {
CallableStatement cs = conn.prepareCall(CALL_SP);
cs.setBigDecimal(1, this.virtualId);
cs.setTimestamp(2, new Timestamp(dateFrom.getTime()));
cs.setTimestamp(3, new Timestamp(dateTo.getTime()));
cs.setString(4, this.dateTimeStep);
cs.setString(5, this.variables);
//the third parameter is the SQL_TYPE name of the NESTED
TABLE
//as declared in Oracle:
//create or replace TYPE SAMPLED_VALUES_ARRAY AS TABLE OF
SAMPLED_VALUE;
cs.registerOutParameter(6, Types.ARRAY,
"SAMPLED_VALUES_ARRAY");
cs.execute();
ARRAY a = (ARRAY) cs.getObject(6);
//from the ARRAY object, you need to get a ResultSet...
ResultSet rs = a.getResultSet();
while (rs.next()) {
//the first "column" is the row number while the
second is the real Oracle object
//Oracle objects are mapped as java.sql.STRUCT
STRUCT object = (STRUCT) rs.getObject(2);
//BigDecimal row = (BigDecimal) rs.getObject(1);
//get attributes from the Oracle object
Object[] attrs = object.getAttributes();
//you need how to map Oracle object into your java
object
//
// create or replace TYPE SAMPLED_VALUE AS OBJECT
// ( data_timestamp date,
// data_value number(8,2)
// );
SampledValue sv = new SampledValue();
sv.setDateTimeUtc((Timestamp) attrs[0]);
sv.setDataValue((BigDecimal) attrs[1]);
svList.add(sv);
}
cs.close();
} catch (Exception exc) {
svList = null;
exc.printStackTrace();
}
return svList;
}
</code>