31. Legacy Hibernate Native Queries

31.1. Legacy named SQL queries

Named SQL queries can also be defined during mapping and called in exactly the same way as a named HQL query. In this case, you do not need to call addEntity() anymore.

Example 700. Named sql query using the <sql-query> mapping element

<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>

Example 701. Execution of a named query

List people = session
    .getNamedQuery( "persons" )
    .setParameter( "namePattern", namePattern )
    .setMaxResults( 50 )
    .list();

The <return-join> element is use to join associations and the <load-collection> element is used to define queries which initialize collections.

Example 702. Named sql query with association

<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:

Example 703. Named query returning a scalar

<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 information in a <resultset> element which will allow you to either reuse them across several named queries or through the setResultSetMapping() API.

Example 704. mapping used to externalize mapping information

<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.

Example 705. Programmatically specifying the result mapping information

List cats = session
    .createSQLQuery( "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id" )
    .setResultSetMapping( "catAndKitten" )
    .list();

31.2. Legacy return-property to explicitly specify column/alias names

You can explicitly tell Hibernate what column aliases to use with <return-property>, instead of using the {} syntax to let Hibernate inject its own aliases. For example:

<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 cannot 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>

In this example <return-property> was used in combination with the {} syntax for injection. This allows 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.

31.3. Legacy stored procedures for querying

Hibernate 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.

31.4. Legacy 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.doWork().

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 a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF 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.

31.5. Legacy custom SQL for create, update and delete

Hibernate can use custom SQL for create, update, and delete operations. The SQL can be overridden at the statement level or individual column level. This section describes statement overrides. For columns, see Column transformers: read and write expressions. The following example shows how to define custom SQL operations using annotations.

Example 706. Custom CRUD XML

<class name = "Person">
    <id name = "id">
        <generator class = "increment"/>
    </id>
    <property name = "name" not-null = "true"/>
    <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
    <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
    <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

To check that the execution happens correctly, Hibernate allows you to define one of those three strategies:

• none: no check is performed; the store procedure is expected to fail upon issues

• count: use of rowcount to check that the update is successful

• param: like COUNT but using an output parameter rather that the standard mechanism

To define the result check style, use the check parameter which is again available in annotations as well as in xml.

Last but not least, stored procedures are in most cases required to return the number of rows inserted, updated and deleted. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:

Example 707. Stored procedures and their return value

CREATE OR REPLACE FUNCTION updatePerson (uid IN NUMBER, uname IN VARCHAR2)
    RETURN NUMBER IS
BEGIN
    update PERSON
    set
        NAME = uname,
    where
        ID = uid;
    return SQL%ROWCOUNT;
END updatePerson;

31.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:

<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>