Hibernate - stored procedure in hibernate

GeekInterview.com > Interview Questions > J2EE > Hibernate

Go To First | Previous Question | Next Question

Hibernate | Question 32 of 33

stored procedure in hibernate

How to call stored procedure in mysql through hibernate?

Total Answers and Comments: 3

Last Update: November 17, 2008 Asked by: bhupeshb

Sponsored Links

Best Rated Answer

—

Submitted by: krish_1911

In order to call a stored procedure using the hibernate , define a named query for a persistent class mapping document.
Then call the named query from your java application .Example is given in the Hibernate Reference documentation as follows:-

First create a stored procedure in your database as,

CREATE OR REPLACE FUNCTION selectAllEmployments
RETURN SYS_REFCURSOR
AS
st_cursor SYS_REFCURSOR;
BEGIN
OPEN st_cursor FOR
SELECT EMPLOYEE, EMPLOYER,
STARTDATE, ENDDATE,
REGIONCODE, EID, VALUE, CURRENCY
FROM EMPLOYMENT;
RETURN st_cursor;
END;

Then in the mapping document create a named query as follows:-

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="Employment">
<return-property name="employee" column="EMPLOYEE"/>
<return-property name="employer" column="EMPLOYER"/>
<return-property name="startDate" column="STARTDATE"/>
<return-property name="endDate" column="ENDDATE"/>
<reeturn-property name="regionCode" column="REGIONCODE"/>
<return-property name="id" column="EID"/>
<return-property name="salary">
<return-column name="VALUE"/>
<return-column name="CURRENCY"/>
</return-property>
</return>
{ ? = call selectAllEmployments() }
</sql-query>

Then, in your java code just call the named query as follows:-

List people = sess.getNamedQuery("selectAllEmployees_SP")
.setString("namePattern", namePattern)
.setMaxResults(50)
.list();

Above answer was rated as good by the following members:
mani_id

September 16, 2008 02:17:07

subhani_syed

Member Since: June 2007 Contribution: 9

RE: stored procedure in hibernate

As per sql-->

create a procedure in your sql by using the following code.

create or replace procedure first-procedure(x in number y out number)

begin

y: x*x;

end;

write your sql connection code in hibernate configuration file. In your hibernate client application get the session obj. On the session object call the connection() method and get the connection.

ex: Connection con session.connection();

CallableStatement cst con.prepareCall("{call first-procedure(? ?)}");

cst.registerOutParameter(2 Types.Integer);

cst.setInt(1 20);

cst.execute();

int res cst.getInt(2);

s.o.p(res);

ses.close();

Is this answer useful? Yes | No

0 1

Overall Rating: -1

September 16, 2008 02:22:47

subhani_syed

Member Since: June 2007 Contribution: 9

RE: stored procedure in hibernate

As per sql-->

create a procedure in your sql by using the following code.

create or replace procedure first-procedure(x in number y out number)

begin

y: x*x;

end;

write your sql connection code in hibernate configuration file.In your hibernate client application get the session obj.On the session object call the connection() method and get the connection.

ex: Connection con session.connection();

CallableStatement cst con.prepareCall("{call first-procedure(? ?)}");

cst.registerOutParameter(2 Types.Integer);

cst.setInt(1 20);

cst.execute();

int res cst.getInt(2);

s.o.p(res);

ses.close();

Is this answer useful? Yes | No

0 1

Overall Rating: -1

November 17, 2008 06:53:23

krish_1911

Member Since: November 2008 Contribution: 1

RE: stored procedure in hibernate

In order to call a stored procedure using the hibernate define a named query for a persistent class mapping document.
Then call the named query from your java application .Example is given in the Hibernate Reference documentation as follows:-

First create a stored procedure in your database as

CREATE OR REPLACE FUNCTION selectAllEmployments

RETURN SYS_REFCURSOR

AS

st_cursor SYS_REFCURSOR;

BEGIN

OPEN st_cursor FOR

SELECT EMPLOYEE EMPLOYER

STARTDATE ENDDATE

REGIONCODE EID VALUE CURRENCY

FROM EMPLOYMENT;

RETURN st_cursor;

END;

Then in the mapping document create a named query as follows:-

<sql-query name selectAllEmployees_SP callable true >
 <return alias emp class Employment >
 <return-property name employee column EMPLOYEE />
 <return-property name employer column EMPLOYER />
 <return-property name startDate column STARTDATE />
 <return-property name endDate column ENDDATE />
 <reeturn-property name regionCode column REGIONCODE />
 <return-property name id column EID />
 <return-property name salary >
 <return-column name VALUE />
 <return-column name CURRENCY />
 </return-property>
 </return>
 { ? call selectAllEmployments() }
 </sql-query>

Then in your java code just call the named query as follows:-

List people sess.getNamedQuery( selectAllEmployees_SP )
 .setString( namePattern namePattern)
 .setMaxResults(50)
 .list();

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank