Execution of native SQL queries is controlled via the SQLQuery interface, which is obtained by calling Session.createSQLQuery(). The following sections describe how to use this API for querying.

sess.createSQLQuery("SELECT * FROM CATS").list();

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

sess.createSQLQuery("SELECT * FROM CATS")

 .addScalar("ID", Hibernate.LONG)

 .addScalar("NAME", Hibernate.STRING)

 .addScalar("BIRTHDATE", Hibernate.DATE)

sess.createSQLQuery("SELECT * FROM CATS")

 .addScalar("ID", Hibernate.LONG)

 .addScalar("NAME")

 .addScalar("BIRTHDATE")

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);

sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME FROM CATS c, DOGS d WHERE c.DOG_ID = d.D_ID")

 .addEntity("cat", Cat.class)

 .addJoin("cat.dog");

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID FROM CATS c, DOGS d WHERE c.ID = d.CAT_ID")

 .addEntity("cat", Cat.class)

 .addJoin("cat.dogs");

sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")

 .addEntity("cat", Cat.class)

 .addEntity("mother", Cat.class)

sess.createSQLQuery("SELECT {cat.*}, {mother.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")

 .addEntity("cat", Cat.class)

 .addEntity("mother", Cat.class)

String sql = "SELECT ID as {c.id}, NAME as {c.name}, " +

         "BIRTHDATE as {c.birthDate}, MOTHER_ID as {c.mother}, {mother.*} " +

         "FROM CAT_LOG c, CAT_LOG m WHERE {c.mother} = c.ID";


List loggedCats = sess.createSQLQuery(sql)

        .addEntity("cat", Cat.class)

        .addEntity("mother", Cat.class).list()

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")

        .setResultTransformer(Transformers.aliasToBean(CatDTO.class))

Query query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class);

List pusList = query.setString(0, "Pus%").list();

     

query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").addEntity(Cat.class);

List pusList = query.setString("name", "Pus%").list();          

Named SQL queries can be defined in the mapping document and called in exactly the same way as a named HQL query. In this case, you 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> element is use to join associations and the <load-collection> element is used to define queries which initialize collections,

<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

>

Eine benannte SQL-Anfrage kann einen Skalarwert wiedergeben. Sie müssen unter Verwendung des <return-scalar>-Elements den Spalten-Alias und den Hibernate-Typ deklarieren:

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

<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();

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

<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

>

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;

<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

>

Hibernate3 can use custom SQL for create, update, and delete operations. The SQL can be overridden at the statement level or inidividual column level. This section describes statement overrides. For columns, see Abschnitt 5.7, „Column read and write expressions“.

The class and collection persisters in Hibernate already contain a set of configuration time generated strings (insertsql, deletesql, updatesql etc.). The mapping tags <sql-insert>, <sql-delete>, and <sql-update> override these strings:

<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

>

The SQL is directly executed in your database, so you can use any dialect you like. This will reduce the portability of your mapping if you use database specific SQL.

Gespeicherte Prozeduren werden unterstützt, wenn das callable-Attribut wie folgt eingestellt ist:

<class name="Person">

    <id name="id">

        <generator class="increment"/>

    </id>

    <property name="name" not-null="true"/>

    <sql-insert callable="true"

>{call createPerson (?, ?)}</sql-insert>

    <sql-delete callable="true"

>{? = call deletePerson (?)}</sql-delete>

    <sql-update callable="true"

>{? = call updatePerson (?, ?)}</sql-update>

</class

>

The order of the positional parameters is vital, as they must be in the same sequence as Hibernate expects them.

You can view the expected order by enabling debug logging for the org.hibernate.persister.entity level. With this level enabled, Hibernate will print out the static SQL that is used to create, update, delete etc. entities. To view the expected sequence, do not include your custom SQL in the mapping files, as this will override the Hibernate generated static SQL.

The stored procedures are in most cases required to return the number of rows inserted, updated and deleted, as Hibernate has some runtime checks for the success of the statement. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:

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;

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 Abschnitt 5.7, „Column 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

>

Das funktioniert sogar mit gespeicherten Prozeduren.

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

>