16.2.2. Using stored procedures for querying
Hibernate3 provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. 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>
Stored procedures currently only return scalars and entities.
<return-join>
and <load-collection>
are not supported.
16.2.2.1. Rules/limitations for using stored procedures
You cannot use stored procedures with Hibernate unless you follow some procedure/function 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 cannot be paged with
setFirstResult()/setMaxResults()
.
The recommended call form is standard SQL92:
{ ? = call functionName(<parameters>) }
or { ? = call procedureName(<parameters>}
. Native call syntax is not supported.
For Oracle the following rules apply:
- A function must return a result set. The first parameter of a procedure must be an
OUT
that returns a result set. This is done by using aSYS_REFCURSOR
type in Oracle 9 or 10. In Oracle you need to define aREF CURSOR
type. See Oracle literature for further information.
For Sybase or MS SQL server the following rules apply:
- The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded.
- If you can enable
SET NOCOUNT ON
in your procedure it will probably be more efficient, but this is not a requirement.