16.4. Custom SQL for loading
You may also declare your own SQL (or HQL) queries for entity loading:
<sql-query name="person">
<return alias="pers" class="Person" lock-mode="upgrade"/>
SELECT NAME AS {pers.name}, ID AS {pers.id}
FROM PERSON
WHERE ID=?
FOR UPDATE
</sql-query>
This is just a named query declaration, as discussed earlier. You may reference this named query in a class mapping:
<class name="Person">
<id name="id">
<generator class="increment"/>
</id>
<property name="name" not-null="true"/>
<loader query-ref="person"/>
</class>
This even works with stored procedures.
You may even define a query for collection loading:
<set name="employments" inverse="true">
<key/>
<one-to-many class="Employment"/>
<loader query-ref="employments"/>
</set>
<sql-query name="employments">
<load-collection alias="emp" role="Person.employments"/>
SELECT {emp.*}
FROM EMPLOYMENT emp
WHERE EMPLOYER = :id
ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>
You could even define an entity loader that loads a collection by join fetching:
<sql-query name="person">
<return alias="pers" class="Person"/>
<return-join alias="emp" property="pers.employments"/>
SELECT NAME AS {pers.*}, {emp.*}
FROM PERSON pers
LEFT OUTER JOIN EMPLOYMENT emp
ON pers.ID = emp.PERSON_ID
WHERE ID=?
</sql-query>
