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

—

No best answer available. Please pick the good answer available or submit your answer.

September 16, 2008 02:17:07

subhani_syed

Member Since: June 2007 Contribution: 7

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: 7

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

Go To Top