How Hibernate invokes Stored Procedures

[Nina Anderson]

Hi Guys,

I have a Sql Server Stored Procedure that I need Hibernate to invoke. However, I have NO CLUE how hibernate invokes a stored procedures. I feel helpless because there are not a lot of step-by-step examples on this.

I'm using Spring + Hibernate. I cannot create a HQL for this query, because Hibernate does not recognize SQL Server's "FREETEXT" keyword.

These are the questions I have:
1) How do I create a hibernate-mapping for this?
2) How do I configure this in Spring's application-context?
3) How does java DAO invoke this procedure?

Here's my stored procedure:

CREATE PROCEDURE PB_Main_Search
@cityName varchar(100),
@state char(2),
@businessName varchar(100),
@serviceCategory varchar(100),
@serviceSubCategory varchar(100),
@serviceName varchar(100)
AS
SELECT *
FROM Busines B
LEFT OUTER JOIN Business_Service as BS ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC ON B.Business_Id = BSSC.Business_Id
where B.Business_Id IN (
Select B.Business_Id from Busines B
JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id
JOIN Address as A ON A.Address_Id = BA.Address_Id
WHERE
A.City_Name = @cityName
and A.State = @state)
AND (
FREETEXT(B.Business_Name, @businessName )

.....
)



Here's my .hbm.xml so far:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
<sql-query name="findByBusinesStoredProc" callable="true">
<return class="com.PBCoreApp.datamodel.BusinessCbo" >
</return>
exec PB_Main_Search
</sql-query>
</hibernate-mapping>


Please Please help...!!!

[Edvins Reisons]

Reference texts and guides are available, from Hibernate and on this forum
[ August 13, 2007: Message edited by: Edvins Reisons ]

[Nina Anderson]

My Stored Procedure does a SELECT *.... Do I need the <return-property> tag in my hibernate-mapping even if I just need the entire table object result?

[Edvins Reisons]

My approach here would be to first establish a mapping/code that works (if any - there are restrictions on stored procedures that can be used with Hibernate) and then look into ways to optimize it.