Using stored procedures with Hibernate

Introdunction

A shortcoming in earlier releases of Hibernate was the lack of support

for stored procedures. Hibernate 3 introduces support for queries via stored procedures and functions. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

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;

To use this query in Hibernate you need to map it via a named query.

<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"/>

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

· Executing the stored procedure is the same as using a named HQL

query:

Query query = session.getNamedQuery("selectAllEmployees_SP");

List results = query.list();

· If your stored procedures take parameters, you can set them using the

Query.setParameter(int, Object) method. Your stored procedures

must return a result set to be usable by Hibernate.

· Notice stored procedures currently only return scalars and entities. <return-join> and

<load-collection> are not supported.

Rules/limitations for using stored procedures with Hibernate

To use stored procedures with Hibernate the procedures/functions have to follow some rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.connection(). The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

· Stored procedure queries can't be paged with setFirstResult()/setMaxResults().

· Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

There are rules to calling Stored Procedures. They are

1. Only one return value allowed
2. It must be the first out value.
3. The out value must be a reference cursor (This out value returns a result set,in Oracle 9 or 10 this is done by using a SYS_REFCURSOR and you need to define a REF CURSOR type).

Example on Mapping Stored Procedure:

1- SELECT_USER_BY_RANK procedure

<database-object>

<create>

create or replace procedure SELECT_USERS_BY_RANK

(

OUT_RESULT out SYS_REFCURSOR,

IN_RANK in int

) as

begin

open OUT_RESULT for

select

u.user_id as user_id,

u.name as name,

u.rank as rank

from

AppUser u

where

u.rank >= IN_RANK;

end;

</create>

<drop>

drop procedure SELECT_USERS_BY_RANK

</drop>

</database-object>

2- Mapping Configuration

<sql-query name="loadUserByRank" callable="true">

<return class="core.AppUser">

<return-property name="id" column="USER_ID"/>

<return-property name="name" column="NAME"/>

<return-property name="rank" column="RANK"/>

</return>

{ call SELECT_USERS_BY_RANK(?, :rank) }

</sql-query>

3- Stored Procedure Mapping Example

public static void main(String[] args) {

Configuration config = new Configuration();

SessionFactory factory = config.configure().buildSessionFactory();

Session session = factory.openSession();

Transaction tx = session.beginTransaction();

Query q = session.getNamedQuery("loadUserByRank");

q.setParameter("rank", new Integer(2));

List result = q.list();

System.out.println("Result=" + result.size());

for(int i=0; i<result.size(); i++) {

AppUser user = (AppUser)result.get(i);

System.out.println(user.toString());

}

tx.commit();

session.close();

}