Hello

I have written a stored procedure which returns select from multiple tables. How can I return this resultset to java?

CREATE PROCEDURE [dbo].[spExtract2]
@param1 int,
@param2 varchar(255)
AS
begin
declare @t table(
expense_id VARCHAR(255),
type_name VARCHAR(255),
)

SELECT ex.expense_id, ext.type_name
FROM Expense ex, Expense_Type ext
WHERE ..............
return
end

---------------------------------------------------------------------------

<sql-query name="getExtract" callable="true">
<return class="Extract" alias="ex">
<return-property name="id" column="EXPENSE_ID"/>
<return-property name="type" column="type_name"/>
</return>
{call spExtract(?, :param1, :param2)}
</sql-query>

-------------------------------------------------------------------------------

Return class "Extract" has id and type mentioned. This is just a DTO to put all attributes and not a true entity that can be mapped to any table.

How can I make this query to return list of 'Extract' dtos which can not be really mapped to any table? This dto created just to return resultset.

This is just a sample and I have huge list of attributes in the resultset.

Thanks in advance

HI Gita,

I have been working on a very similar problem this past week and my posting never got a reply :(

I finally got things working this morning so I can share my solution, right or wrong.

I created my query as below with the return mapped to a bean with an associated hbm.xml mapping file. It had the id and property just as your does below. The column name here doesn't refer to any table but rather the named parameters returned in the cursor of your DB Function.


<sql-query name="fraudulentPanCheck_SP" callable="true">
<return alias="fraudulentPanCheck" class="com.fraud.fraudulentpan.FraudulentPanCheckImpl"/>
{? = call abedba.FRAUD_DETECTION_API.check_fraudulentpan(:p_ccnumber) }
</sql-query>

When I actuall invoke the query I recieve a list of the beans I declared.

fraudPanCheckResult = session.getNamedQuery("fraudulentPanCheck_SP")
.setParameter("p_ccnumber",pan)
.list();

if (fraudPanCheckResult.size() > 0) {
return (FraudulentPanCheck)fraudPanCheckResult.get(0);
}

Hope that helps!

-- Grant

Hello Grant

Thank you very much for your reply.

For me it gives error as unknown entity "Extract". Means I have to create it as a class "Extract" in hbm file but then i don't have equivalent table for the same. Hence this named query gives above mentioned error.

When you mentioned class FraudulentPanCheckImpl in your hbm, did you map that class to any table?

Thanks
gita

If I don't mention class in hbm then I get unknown entity error. If I mention class without mapped table as I don't have any then I get following error.

org.hibernate.MappingException: java.lang.StringIndexOutOfBoundsException: String index out of range: 0