Named SQL queries may be defined in the mapping document and called
in exactly the same way as a named HQL query. In this case, we do
not need to call
addEntity()
.
<sql-query name="persons"> <return alias="person" class="eg.Person"/> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex} FROM PERSON person WHERE person.NAME LIKE :namePattern </sql-query>
List people = sess.getNamedQuery("persons") .setString("namePattern", namePattern) .setMaxResults(50) .list();
The <return-join>
and
<load-collection>
elements are used to join
associations and define queries which initialize collections,
respectively.
<sql-query name="personsWith"> <return alias="person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex}, address.STREET AS {address.street}, address.CITY AS {address.city}, address.STATE AS {address.state}, address.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS address ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>
A named SQL query may return a scalar value. You must declare the
column alias and Hibernate type using the
<return-scalar>
element:
<sql-query name="mySqlQuery"> <return-scalar column="name" type="string"/> <return-scalar column="age" type="long"/> SELECT p.NAME AS name, p.AGE AS age, FROM PERSON p WHERE p.NAME LIKE 'Hiber%' </sql-query>
You can externalize the resultset mapping informations in a
<resultset>
element to either reuse them across
several named queries or through the
setResultSetMapping()
API.
<resultset name="personAddress"> <return alias="person" class="eg.Person"/> <return-join alias="address" property="person.mailingAddress"/> </resultset> <sql-query name="personsWith" resultset-ref="personAddress"> SELECT person.NAME AS {person.name}, person.AGE AS {person.age}, person.SEX AS {person.sex}, address.STREET AS {address.street}, address.CITY AS {address.city}, address.STATE AS {address.state}, address.ZIP AS {address.zip} FROM PERSON person JOIN ADDRESS address ON person.ID = address.PERSON_ID AND address.TYPE='MAILING' WHERE person.NAME LIKE :namePattern </sql-query>
You can alternatively use the resultset mapping information in your hbm files directly in java code.
List cats = sess.createSQLQuery( "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id" ) .setResultSetMapping("catAndKitten") .list();
With <return-property>
you can explicitly
tell Hibernate what column aliases to use, instead of using the
{}
-syntax to let Hibernate inject its own
aliases.
<sql-query name="mySqlQuery"> <return alias="person" class="eg.Person"> <return-property name="name" column="myName"/> <return-property name="age" column="myAge"/> <return-property name="sex" column="mySex"/> </return> SELECT person.NAME AS myName, person.AGE AS myAge, person.SEX AS mySex, FROM PERSON person WHERE person.NAME LIKE :name </sql-query>
<return-property>
also works with
multiple columns. This solves a limitation with the
{}
-syntax which can not allow fine grained control of
multi-column properties.
<sql-query name="organizationCurrentEmployments"> <return alias="emp" class="Employment"> <return-property name="salary"> <return-column name="VALUE"/> <return-column name="CURRENCY"/> </return-property> <return-property name="endDate" column="myEndDate"/> </return> SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer}, STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate}, REGIONCODE as {emp.regionCode}, EID AS {emp.id}, VALUE, CURRENCY FROM EMPLOYMENT WHERE EMPLOYER = :id AND ENDDATE IS NULL ORDER BY STARTDATE ASC </sql-query>
Notice that in this example we used
<return-property>
in combination with the
{}
-syntax for injection. Allowing users to choose how
they want to refer column and properties.
If your mapping has a discriminator you must use
<return-discriminator>
to specify the
discriminator column.
Hibernate 3 introduces 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>
Notice stored procedures currently only return scalars and
entities. <return-join>
and
<load-collection>
are not supported.
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.
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 a
SYS_REFCURSOR
type in Oracle 9 or 10. In Oracle
you need to define a REF CURSOR
type, see
Oracle literature.
For Sybase or MS SQL server the following rules apply:
The procedure must return a result set. Note that since these servers can/will 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.