Hibernate and Stored Procedure.

Calling Stored procedure from Hibernate is really easy if it is adhered to certain rules.

Pre Condition: Function or Procedure should always return Refcursor. The return type of Ref cursor is also depends on the Database and drivers the application is using.
a) It supports IN and OUT.
b) If you use SP wiht Oracle own driver , Hibernate requires the first parameter of the SP is OUT.
c) In Oracle 9 or newer version , the type of the OUT parameter has to be SYS_REFCURSOR
d) In Older version of Oracle it can be declared as REF CURSOR.
e) For different database like MySQL and MS SQL the syntax differs.


2) Create the SP/Function
REATE OR REPLACE PROCEDURE GET_EMPLOYEE_DETAIL(OUT_RESULT OUT SYS_REFCURSOR) AS
BEGIN
//Write application business logic and return the SYS_REFCURSOR here
//OR

open OUT_RESULT for SELECT * FROM EMPLOYEE WHERE DEPT_ID =100
END;
/


3) Create the hibernate mapping file for SP/Function. Usually it will be StoredProcedure-hbm.xml

a) This mapping is not same as class and table mapping. But we need to use SQL-named Query mapping for this.
b) This SQL Query mapping we need add callable="true" attribute. This enable the handling of SP from Hibernate and output of the procedure.


hibernate-mapping
< name="GetEmployeeDetail_SP" callable="true">
< alias="employee" class="com.test.employee">
< / return >

{ call PIP.GET_EMPLOYEE_DETAIL(?) }
< / sql-query >
< / hibernate-mapping >


4) Execute the Procedure.
List l = HibernateUtil.getSession().getNamedQuery("GetEmployeeDetail_SP").list();

a) After execution we will received it as a list which contains the hydrated object of employee class.