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>