Support the use of Stored Procedures which return multiple result sets.

Description

When a stored procedure (SP) returns multiple result set, Hibernate only use the first result set. It would be very useful to manage multiple result sets.

For example, this SP returns two result sets:

CREATE PROCEDURE SELECT_NAME_ADRESS()
dynamic result sets 2
language sql
begin
--define and open result cursor
begin
declare v_cur1 cursor with return to caller
for select * from INVENTORY.NAME;
open v_cur1;
end;
--define and open result cursor
begin
declare v_cur2 cursor with return to caller
for select * from INVENTORY.ADDRESS;
open v_cur2;
end;
end;

The mapping's configuration could be something like this:
<sql-query name="SELECT_PERSON_ADDRESS_SP" callable="true">
<!-- First result set -->
<return alias="per" class="com.something.persistence.Person">
<return-property name="id" column="ID" />
<return-property name="name" column="NAME" />
<return-property name="father" column="Father" />
</return>
<!-- Second result set -->
<return alias="add" class="com.something.persistence.Address">
<return-property name="id" column="ID" />
<return-property name="address" column="ADDRESS" />
<return-property name="zip" column="ZIP" />
<return-property name="phone" column="PHONE" />
</return>
{ call SELECT_PERSON_ADDRESS() }
</sql-query>

Any comments?

Environment

Hibernate 3.1 rc3 and DB2

Activity

Show:
Max Rydahl Andersen
December 8, 2005, 6:38 PM

that mapping syntax is what is used to read two entities per row from single resultset.
e.g. its similar to what you get from a HQL such as "select per, add from Person, Address"

Francois Jean
December 8, 2005, 9:41 PM

I'm not an expert in DTD, but I'm only proposing a way of managing multiple result sets.

Since it seems that it cannot be handle with the actual syntax of the configuration file, I'm exploring a way of representing multiple result sets.

First proposition:

<!-- Defining the Stored Procedure which return two result set -->
<sql-query name="SELECT_PERSON_ADDRESS_SP" callable="true">
<!-- Defining the first result set -->
<result-set name="rs1" >
<return alias="per" class="com.something.persistence.Person">
<return-property name="id" column="ID" />
<return-property name="name" column="NAME" />
<return-property name="father" column="Father" />
</return>
</result-set>

<!-- Defining the Stored Procedure which return two result set -->
<result-set name = "rs2">
<return alias="add" class="com.something.persistence.Address">
<return-property name="id" column="ID" />
<return-property name="address" column="ADDRESS" />
<return-property name="zip" column="ZIP" />
<return-property name="phone" column="PHONE" />
</return>
</result-set>
{ call SELECT_PERSON_ADDRESS() }
</sql-query>

Second proposition:
<!-- Defining the first result set -->
<result-set name="rs1" >
<return alias="per" class="com.something.persistence.Person">
<return-property name="id" column="ID" />
<return-property name="name" column="NAME" />
<return-property name="father" column="Father" />
</return>
</result-set>

<!-- Defining the second result set -->
<result-set name = "rs2">
<return alias="add" class="com.something.persistence.Address">
<return-property name="id" column="ID" />
<return-property name="address" column="ADDRESS" />
<return-property name="zip" column="ZIP" />
<return-property name="phone" column="PHONE" />
</return>
</result-set>

<!-- Defining the Stored Procedure which return two result set -->
<sql-query name="SELECT_PERSON_ADDRESS_SP" callable="true">
<return order="1" result-set="rs1" />
<return order="2" result-set="rs2"/>
{ call SELECT_PERSON_ADDRESS() }
</sql-query>

Assignee

Unassigned

Reporter

Francois Jean

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure