Red Hat Training
A Red Hat training course is available for Red Hat JBoss Web Server
17.4. Custom SQL for loading
You can 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 can 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 can 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 can also 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>