Hi there,
I am trying to execute the stored procedure in hibernate. I am using spring,hibernate,and sql server 2005.
My mapping is like this

and in dao, I can call either

or

The problem is it returns list of Object, instead of my ReturnObjectFromSP. So I modify the map to specify the return class,return-property. But it throws exception "Caused by: org.hibernate.HibernateException: Errors in named queries: TestProc".Is it because ReturnObjectFromSP is not mapped to any table? But the result from my stored procedure is combination of different tables.
So I have another try by removing the return class,return-property in the map, and add

with this way, it throws another error like it cannot find the setter for the column into my class.
Can anyone give me an advice?
TIA.

Hi,
I think you are close. We did NOT use a Stored Procedure calls from Hibernate in our project but we were returning Table data (from a temp table that the Stored Procedure created and populated) to a third party Report. So the columns names were the aliases and case sensitive.

Can you post your properties in the Class ReturnObjectFromSP? Also can you show how you are returning the data from the stored procedure?

-latha.

Thanks for your reply.
This is my ReturnObjectFromSP

and my stored procedure looks like that

It worked fine for me. The only thing i found is that when the data types don't match an error stating IllegalArgumentException for the given property.Worked fine with the String data types thou.

Stored Procedure on SQL Server 2005
Table activity has the column ID of type numeric in the database.
Table ele_fld has the column fld_char as varchar(255)

The Numeric columns had to be Generic with Number datatype and not Long or Integer at java end. Hibernate threw exception in those cases.

drop PROCEDURE [dbo].[RunTestProc]
CREATE PROCEDURE [dbo].[RunTestProc]
AS
select
activity.id element,
meta.fld_char metadata
from ele_fld meta
join activity activity on meta.id=activity.id
where meta.id=1002 and meta.type=1

Class:-
public class TestSP {
private Number element;

public Number getElement() {
return element;
}
public void setElement(Number element) {
this.element = element;
}
private String metadata;

public String getMetadata() {
return metadata;
}
public void setMetadata(String metadata) {
this.metadata = metadata;
}
}

Junit test
public void testStoreProcedure(){
Session session=getSession();
List<TestSP> testSPs=session.createSQLQuery("{call RunTestProc}").setResultTransformer(Transformers.aliasToBean(TestSP.class)).list();
for(TestSP testSP:testSPs){
System.out.println(testSP.getElement()+" "+testSP.getMetadata());

}

}

Hope this helps,
latha.

Thanks latha1119,
it's working fine for me if I have the setter has exact name with the column such as if column is id, I have to have setid(int id); setId(int id) wont work. I dont know why?
Again, thanks for your reply.

I guess I found out what the issue is..
I ran into the same issue as you did.. For some reason Hibernate treats differently if the Query is:-
Select table1.column1 mycolumn from table table1

Differently than(the keyword 'AS')
Select table1.column1 AS mycolumn from table table1

Hope this helps,
latha.