Re: Mapping Oracle collection in Java/Hibernate

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>