• Post Reply Bookmark Topic Watch Topic
  • New Topic

How Hibernate invokes Stored Procedures

 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Reference texts and guides are available, from Hibernate and on this forum
[ August 13, 2007: Message edited by: Edvins Reisons ]
 
Nina Anderson
Ranch Hand
Posts: 148
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!