hi,

iam newbie to hibernate and sql server 2005.
i want to know how do we execute sql server 2005 stored procedure
in hibernate.

Below is my database tables:

Database tables:

CREATE TABLE CL_USER_DETAILS(
USER_ID INT IDENTITY (1,1) PRIMARY KEY,
USER_NAME NVARCHAR(20) UNIQUE NOT NULL,
USER_SSN NVARCHAR(100) UNIQUE NOT NULL,
USER_SALUTATION NVARCHAR(5) NOT NULL,
USER_FIRST_NAME NVARCHAR(50) NOT NULL,
USER_LAST_NAME NVARCHAR(50) NOT NULL,
USER_PASSWORD NVARCHAR(50) NOT NULL,
USER_CREATED_TIMESTAMP DATETIME,
USER_MODIFIED_TIMESTAMP DATETIME,
USER_CREATED_BY INT,
USER_MODIFIED_BY INT,
USER_FIRM_ID INT,
USER_SUPERVISOR INT,
USER_STATUS NVARCHAR(20) NOT NULL REFERENCES CL_USER_STATUS(USER_STATUS),
FIRM_USER_INDEX INT
)

CREATE TABLE CL_FIRM_DETAILS(
FIRM_ID INT IDENTITY (1,1) PRIMARY KEY,
FIRM_CUSTOMER_CODE NVARCHAR(20) UNIQUE NOT NULL,
FIRM_REGISTERED_NAME NVARCHAR(100) UNIQUE NOT NULL,
FIRM_USER_COUNT INT NOT NULL,
FIRM_CONTRACT_START_DATE DATETIME NOT NULL,
FIRM_CONTRACT_END_DATE DATETIME NOT NULL,
FIRM_CREATED_TIMESTAMP DATETIME NOT NULL,
FIRM_MODIFIED_TIMESTAMP DATETIME NOT NULL,
FIRM_STATUS NVARCHAR(20) NOT NULL REFERENCES CL_FIRM_STATUS(FIRM_STATUS),
CREATED_BY INT REFERENCES CL_USER_DETAILS(USER_ID),
MODIFIED_BY INT REFERENCES CL_USER_DETAILS(USER_ID),
FIRM_DESCRIPTION NVARCHAR(200) NOT NULL
)

CREATE TABLE CL_CONTACT_DETAILS(
CONTACT_ID INT IDENTITY (1,1) PRIMARY KEY,
ADDRESS1 NVARCHAR(100) NOT NULL,
ADDRESS2 NVARCHAR(100) NOT NULL,
COUNTRY_CODE NVARCHAR(100) NOT NULL,
STATE_CODE NVARCHAR(100) NOT NULL,
CITY_CODE FLOAT NOT NULL,
PHONE_NUM NVARCHAR(100) NOT NULL,
MOBILE_NUM NVARCHAR(100) ,
MAIL_ID NVARCHAR(100) NOT NULL,
FAX NVARCHAR(30) NULL,
ZIPCODE NVARCHAR(30) NOT NULL,
FIRM_ID INT REFERENCES CL_FIRM_DETAILS(FIRM_ID),
USER_ID INT REFERENCES CL_USER_DETAILS(USER_ID),
FIRM_CONTACT_INDEX INT,
USER_CONTACT_INDEX INT
)



Here is my SQL server 2005 procedure:


use [master]
go
use [db]
go

CREATE PROC GetFirmsByPage

@PageSize int, @PageNumber int

AS

Declare @RowStart int
Declare @RowEnd int

if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;

With firms AS
(
SELECT
firm.FIRM_ID,
firm.FIRM_CUSTOMER_CODE,
firm.FIRM_REGISTERED_NAME,
firm.FIRM_USER_COUNT,
firm.FIRM_STATUS,
firm.FIRM_DESCRIPTION,
contact.CONTACT_ID as contactId,
contact.MAIL_ID as mailId
ROW_NUMBER() OVER (order by firm.FIRM_ID) as RowNumber

FROM
CL_FIRM_DETAILS firm,CL_CONTACT_DETAILS contact

WHERE
firm.FIRM_ID=contact.FIRM_ID AND
firm.FIRM_STATUS!= 'Deleted'

)

select *
from firms
WHERE RowNumber BETWEEN @RowStart AND @RowEnd

END


How do it execute the above stored procedure in hibernate.

How show it write <sql-query> for multiple tables and classess.
and access the same from my dao using
getHibernateTemplate().findByNamedQuery();


i wrote:

<sql-query name="firm_listing" callable="true">
<return alias="firm" class="com.centrilaw.lpo.firm.dto.FirmDTO">
<return-property name="firmId" column="FIRM_ID" />
<return-property name="firmCustomerCode" column="FIRM_CUSTOMER_CODE" />
<return-property name="firmRegisteredName" column="FIRM_REGISTERED_NAME" />
<return-property name="totalUserCount" column="FIRM_USER_COUNT" />
<return-property name="firmStatus" column="FIRM_STATUS" />
<return-property name="description" column="FIRM_DESCRIPTION" />
</return>

exec GetFirmsByPage 10, 2
</sql-query>

its giving erroring saying invalid column name.