I'm trying to make Hibernate play with a storedproceedure that returns a REFCURSOR to a result-set that also contains a nested CURSOR for each of the rows in the first result set. But I'm not sure how to do the mapping for this. Would love some suggestions.
I've simplified the example as much as possible.
thanks for any help.
Hibernate version: 3.0.5
Mapping documents:
Code:
<hibernate-mapping package="com.foo">
<class name="Event" table="foo_event">
<id name="id" column="id" type="long">
<generator class="native"/>
</id>
<property name="post_date" type="string"/>
</class>
</hibernate-mapping>
<hibernate-mapping package="com.foo">
<class name="Message" table="foo_message">
<id name="id" column="id" type="long">
<generator class="native"/>
</id>
<property name="text" type="string"/>
</class>
<sql-query name="selectMessageEventWithRef_SP" callable="true">
<return alias="mesg3" class="Message">
<return-property name="id" column="ID"/>
<return-property name="text" column="TEXT"/>
</return>
{ ? = call selectMessageWhereId(?) }
</sql-query>
</hibernate-mapping>
Code:
session = HibernateUtil.currentSession();
q = session.getNamedQuery("selectMessageEventWithRef_SP");
results = q.list();
for (Iterator i = results.iterator(); i.hasNext();) {
mesg = (Message) i.next();
/would like to be able to get the list of Events here.
}
Name and version of the database you are using: Oracle 10g
SQL Stored Proceedure:Code:
CREATE TABLE foo_message (
id number NOT NULL,
text varchar(45) NOT NULL,
CONSTRAINT foo_message_id_pk PRIMARY KEY (id)
USING INDEX TABLESPACE USERS
);
CREATE TABLE foo_event (
foo_event_id number NOT NULL,
post_date varchar(45) NOT NULL,
foo_message_id number NOT NULL,
FOREIGN KEY (foo_message_id) REFERENCES foo_message(id),
CONSTRAINT foo_event_id_pk PRIMARY KEY (foo_event_id)
USING INDEX TABLESPACE USERS
);
-- the stored proc that returns the nested cursors
CREATE OR REPLACE FUNCTION getMessageEventWithRef
RETURN SYS_REFCURSOR AS r_ref SYS_REFCURSOR;
p_id NUMBER := 1;
begin
OPEN r_ref FOR
SELECT id, text, CURSOR (SELECT foo_event_id,
post_date FROM foo_event)
FROM foo_message
WHERE id = p_id;
RETURN r_ref;
end;
/
Login
FAQ
Search