30.6. Legacy custom SQL for loading
You can also declare your own SQL (or HQL) queries for entity loading. As with inserts, updates, and deletes, this can be done at the individual column level as described in For columns, see Column transformers: read and write expressions or at the statement level. Here is an example of a statement level override:
</div>
`<sql-query name = "person">
<return alias = "pers" class = "Person" lock-mod e= "upgrade"/>
SELECT NAME AS {pers.name}, ID AS {pers.id}
FROM PERSON
WHERE ID=?
FOR UPDATE
</sql-query>`
</div>
</div>
This is just a named query declaration, as discussed earlier. You can reference this named query in a class mapping:
</div>
`<class name = "Person"> <id name = "id"> <generator class = "increment"/> </id> <property name = "name" not-null = "true"/> <loader query-ref = "person"/> </class>`</div> </div>
This even works with stored procedures.
</div>
You can even define a query for collection loading:
</div>
`<set name = "employments" inverse = "true"> <key/> <one-to-many class = "Employment"/> <loader query-ref = "employments"/> </set>`</div> </div>
`<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>`
</div>
</div>
You can also define an entity loader that loads a collection by join fetching:
</div>
`<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>
</div> </div> </div> </div> </div>