Hibernate example source code file (query_sql.xml)

This example Hibernate source code file (query_sql.xml) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - Hibernate tags/keywords

as, as, cats, from, from, hibernate, id, java, java, name, select, sql, sql, where

The Hibernate query_sql.xml source code

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Hibernate, Relational Persistence for Idiomatic Java
  ~
  ~ Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as
  ~ indicated by the @author tags or express copyright attribution
  ~ statements applied by the authors.  All third-party contributions are
  ~ distributed under license by Red Hat Middleware LLC.
  ~
  ~ This copyrighted material is made available to anyone wishing to use, modify,
  ~ copy, or redistribute it subject to the terms and conditions of the GNU
  ~ Lesser General Public License, as published by the Free Software Foundation.
  ~
  ~ This program is distributed in the hope that it will be useful,
  ~ but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
  ~ or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
  ~ for more details.
  ~
  ~ You should have received a copy of the GNU Lesser General Public License
  ~ along with this distribution; if not, write to:
  ~ Free Software Foundation, Inc.
  ~ 51 Franklin Street, Fifth Floor
  ~ Boston, MA  02110-1301  USA
  -->
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN"
"http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd" [
<!ENTITY % BOOK_ENTITIES SYSTEM "../HIBERNATE_-_Relational_Persistence_for_Idiomatic_Java.ent">
%BOOK_ENTITIES;
]>
<chapter id="querysql" revision="2">
  <title>Native SQL

  <para>You can also express queries in the native SQL dialect of your
  database. This is useful if you want to utilize database-specific features
  such as query hints or the <literal>CONNECT keyword in Oracle. It
  also provides a clean migration path from a direct SQL/JDBC based
  application to Hibernate.</para>

  <para>Hibernate3 allows you to specify handwritten SQL, including stored
  procedures, for all create, update, delete, and load operations.</para>

  <section id="querysql-creating" revision="4">
    <title>Using a SQLQuery

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

    <section>
      <title>Scalar queries

      <para>The most basic SQL query is to get a list of scalars
      (values).</para>

      <programlisting role="JAVA">sess.createSQLQuery("SELECT * FROM CATS").list();
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();
</programlisting>

      <para>These will return a List of Object arrays (Object[]) with scalar
      values for each column in the CATS table. Hibernate will use
      ResultSetMetadata to deduce the actual order and types of the returned
      scalar values.</para>

      <para>To avoid the overhead of using
      <literal>ResultSetMetadata, or simply to be more explicit in
      what is returned, one can use <literal>addScalar():

      <programlisting role="JAVA">sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME", Hibernate.STRING)
 .addScalar("BIRTHDATE", Hibernate.DATE)
</programlisting>

      <para>This query specified:

      <itemizedlist>
        <listitem>
          <para>the SQL query string
        </listitem>

        <listitem>
          <para>the columns and types to return
        </listitem>
      </itemizedlist>

      <para>This will return Object arrays, but now it will not use
      <literal>ResultSetMetadata but will instead explicitly get the
      ID, NAME and BIRTHDATE column as respectively a Long, String and a Short
      from the underlying resultset. This also means that only these three
      columns will be returned, even though the query is using
      <literal>* and could return more than the three listed
      columns.</para>

      <para>It is possible to leave out the type information for all or some
      of the scalars.</para>

      <programlisting role="JAVA">sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME")
 .addScalar("BIRTHDATE")
</programlisting>

      <para>This is essentially the same query as before, but now
      <literal>ResultSetMetaData is used to determine the type of
      NAME and BIRTHDATE, where as the type of ID is explicitly
      specified.</para>

      <para>How the java.sql.Types returned from ResultSetMetaData is mapped
      to Hibernate types is controlled by the Dialect. If a specific type is
      not mapped, or does not result in the expected type, it is possible to
      customize it via calls to <literal>registerHibernateType in
      the Dialect.</para>
    </section>

    <section>
      <title>Entity queries

      <para>The above queries were all about returning scalar values,
      basically returning the "raw" values from the resultset. The following
      shows how to get entity objects from a native sql query via
      <literal>addEntity().

      <programlisting role="JAVA">sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);
</programlisting>

      <para>This query specified:

      <itemizedlist>
        <listitem>
          <para>the SQL query string
        </listitem>

        <listitem>
          <para>the entity returned by the query
        </listitem>
      </itemizedlist>

      <para>Assuming that Cat is mapped as a class with the columns ID, NAME
      and BIRTHDATE the above queries will both return a List where each
      element is a Cat entity.</para>

      <para>If the entity is mapped with a many-to-one to
      another entity it is required to also return this when performing the
      native query, otherwise a database specific "column not found" error
      will occur. The additional columns will automatically be returned when
      using the * notation, but we prefer to be explicit as in the following
      example for a <literal>many-to-one to a
      <literal>Dog:

      <programlisting role="JAVA">sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);
</programlisting>

      <para>This will allow cat.getDog() to function properly.
    </section>

    <section>
      <title>Handling associations and collections

      <para>It is possible to eagerly join in the Dog to
      avoid the possible extra roundtrip for initializing the proxy. This is
      done via the <literal>addJoin() method, which allows you to
      join in an association or collection.</para>

      <programlisting role="JAVA">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");
</programlisting>

      <para>In this example, the returned Cat's will have
      their <literal>dog property fully initialized without any
      extra roundtrip to the database. Notice that you added an alias name
      ("cat") to be able to specify the target property path of the join. It
      is possible to do the same eager joining for collections, e.g. if the
      <literal>Cat had a one-to-many to Dog
      instead.</para>

      <programlisting role="JAVA">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");
</programlisting>

      <para>At this stage you are reaching the limits of what is possible with
      native queries, without starting to enhance the sql queries to make them
      usable in Hibernate. Problems can arise when returning multiple entities
      of the same type or when the default alias/column names are not
      enough.</para>
    </section>

    <section>
      <title>Returning multiple entities

      <para>Until now, the result set column names are assumed to be the same
      as the column names specified in the mapping document. This can be
      problematic for SQL queries that join multiple tables, since the same
      column names can appear in more than one table.</para>

      <para>Column alias injection is needed in the following query (which
      most likely will fail):</para>

      <programlisting role="JAVA">sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)
</programlisting>

      <para>The query was intended to return two Cat instances per row: a cat
      and its mother. The query will, however, fail because there is a
      conflict of names; the instances are mapped to the same column names.
      Also, on some databases the returned column aliases will most likely be
      on the form "c.ID", "c.NAME", etc. which are not equal to the columns
      specified in the mappings ("ID" and "NAME").</para>

      <para>The following form is not vulnerable to column name
      duplication:</para>

      <programlisting role="JAVA">sess.createSQLQuery("SELECT {cat.*}, {m.*}  FROM CATS c, CATS m WHERE c.MOTHER_ID = m.ID")
 .addEntity("cat", Cat.class)
 .addEntity("mother", Cat.class)
</programlisting>

      <para>This query specified:

      <itemizedlist>
        <listitem>
          <para>the SQL query string, with placeholders for Hibernate to
          inject column aliases</para>
        </listitem>

        <listitem>
          <para>the entities returned by the query
        </listitem>
      </itemizedlist>

      <para>The {cat.*} and {mother.*} notation used above is a shorthand for
      "all properties". Alternatively, you can list the columns explicitly,
      but even in this case Hibernate injects the SQL column aliases for each
      property. The placeholder for a column alias is just the property name
      qualified by the table alias. In the following example, you retrieve
      Cats and their mothers from a different table (cat_log) to the one
      declared in the mapping metadata. You can even use the property aliases
      in the where clause.</para>

      <programlisting role="JAVA">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()
</programlisting>

      <section id="querysql-aliasreferences" revision="2">
        <title>Alias and property references

        <para>In most cases the above alias injection is needed. For queries
        relating to more complex mappings, like composite properties,
        inheritance discriminators, collections etc., you can use specific
        aliases that allow Hibernate to inject the proper aliases.</para>

        <para>The following table shows the different ways you can use the
        alias injection. Please note that the alias names in the result are
        simply examples; each alias will have a unique and probably different
        name when used.</para>

        <table frame="topbot" id="aliasinjection-summary">
          <title>Alias injection names

          <tgroup cols="3">
            <colspec colwidth="1*" />

            <colspec colwidth="1*" />

            <colspec colwidth="2.5*" />

            <thead>
              <row>
                <entry>Description

                <entry>Syntax

                <entry>Example
              </row>
            </thead>

            <tbody>
              <row>
                <entry>A simple property

                <entry>{[aliasname].[propertyname]

                <entry>A_NAME as {item.name}
              </row>

              <row>
                <entry>A composite property

                <entry>{[aliasname].[componentname].[propertyname]}

                <entry>CURRENCY as {item.amount.currency}, VALUE as
                {item.amount.value}</literal>
              </row>

              <row>
                <entry>Discriminator of an entity

                <entry>{[aliasname].class}

                <entry>DISC as {item.class}
              </row>

              <row>
                <entry>All properties of an entity

                <entry>{[aliasname].*}

                <entry>{item.*}
              </row>

              <row>
                <entry>A collection key

                <entry>{[aliasname].key}

                <entry>ORGID as {coll.key}
              </row>

              <row>
                <entry>The id of an collection

                <entry>{[aliasname].id}

                <entry>EMPID as {coll.id}
              </row>

              <row>
                <entry>The element of an collection

                <entry>{[aliasname].element}

                <entry>XID as {coll.element}
              </row>

              <row>
                <entry>property of the element in the collection

                <entry>{[aliasname].element.[propertyname]}

                <entry>NAME as {coll.element.name}
              </row>

              <row>
                <entry>All properties of the element in the collection

                <entry>{[aliasname].element.*}

                <entry>{coll.element.*}
              </row>

              <row>
                <entry>All properties of the collection

                <entry>{[aliasname].*}

                <entry>{coll.*}
              </row>
            </tbody>
          </tgroup>
        </table>
      </section>
    </section>

    <section>
      <title>Returning non-managed entities

      <para>It is possible to apply a ResultTransformer to native SQL queries,
      allowing it to return non-managed entities.</para>

      <programlisting role="JAVA">sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
        .setResultTransformer(Transformers.aliasToBean(CatDTO.class))</programlisting>

      <para>This query specified:

      <itemizedlist>
        <listitem>
          <para>the SQL query string
        </listitem>

        <listitem>
          <para>a result transformer
        </listitem>
      </itemizedlist>

      <para>The above query will return a list of CatDTO
      which has been instantiated and injected the values of NAME and
      BIRTHNAME into its corresponding properties or fields.</para>
    </section>

    <section>
      <title>Handling inheritance

      <para>Native SQL queries which query for entities that are mapped as
      part of an inheritance must include all properties for the baseclass and
      all its subclasses.</para>
    </section>

    <section>
      <title>Parameters

      <para>Native SQL queries support positional as well as named
      parameters:</para>

      <programlisting role="JAVA">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();          </programlisting>
    </section>
  </section>

  <section id="querysql-namedqueries" revision="3">
    <title>Named SQL queries

    <para>Named SQL queries can also be defined in the mapping document and
    called in exactly the same way as a named HQL query (see <xref
    linkend="objectstate-querying-executing-named" />). In this case, you do
    <emphasis>not need to call
    <literal>addEntity().

    <example>
      <title>Named sql query using the <sql-query> maping
      element</title>

      <programlisting role="XML"><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></programlisting>
    </example>

    <example>
      <title>Execution of a named query

      <programlisting role="JAVA">List people = sess.getNamedQuery("persons")
    .setString("namePattern", namePattern)
    .setMaxResults(50)
    .list();</programlisting>
    </example>

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

    <example>
      <title>Named sql query with association

      <programlisting role="XML"><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></programlisting>
    </example>

    <para>A named SQL query may return a scalar value. You must declare the
    column alias and Hibernate type using the
    <literal><return-scalar> element:

    <example>
      <title>Named query returning a scalar

      <programlisting role="XML"><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></programlisting>
    </example>

    <para>You can externalize the resultset mapping information in a
    <literal><resultset> element which will allow you to
    either reuse them across several named queries or through the
    <literal>setResultSetMapping() API.

    <example>
      <title><resultset> mapping used to externalize mapping
      information</title>

      <programlisting role="XML"><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></programlisting>
    </example>

    <para>You can, alternatively, use the resultset mapping information in
    your hbm files directly in java code.</para>

    <example>
      <title>Programmatically specifying the result mapping information
      </title>

      <programlisting role="JAVA">List cats = sess.createSQLQuery(
        "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
    )
    .setResultSetMapping("catAndKitten")
    .list();</programlisting>
    </example>

    <para>So far we have only looked at externalizing SQL queries using
    Hibernate mapping files. The same concept is also available with
    anntations and is called named native queries. You can use
    <classname>@NamedNativeQuery
    (<classname>@NamedNativeQueries) in conjunction with
    <literal>@SqlResultSetMapping
    (<literal>@SqlResultSetMappings). Like
    <literal>@NamedQuery, @NamedNativeQuery
    and <literal>@SqlResultSetMapping can be defined at class level,
    but their scope is global to the application. Lets look at a view
    examples.</para>

    <para> parameter is defined in
    <literal>@NamedNativeQuery. It represents the name of a defined
    <literal>@SqlResultSetMapping. The resultset mapping declares
    the entities retrieved by this native query. Each field of the entity is
    bound to an SQL alias (or column name). All fields of the entity including
    the ones of subclasses and the foreign key columns of related entities
    have to be present in the SQL query. Field definitions are optional
    provided that they map to the same column name as the one declared on the
    class property. In the example 2 entities, <literal>Night and
    <literal>Area, are returned and each property is declared and
    associated to a column name, actually the column name retrieved by the
    query. </para>

    <para>In  the result
    set mapping is implicit. We only describe the entity class of the result
    set mapping. The property / column mappings is done using the entity
    mapping values. In this case the model property is bound to the model_txt
    column. </para>

    <para>Finally, if the association to a related entity involve a composite
    primary key, a <literal>@FieldResult element should be used for
    each foreign key column. The <literal>@FieldResult name is
    composed of the property name for the relationship, followed by a dot
    ("."), followed by the name or the field or property of the primary key.
    This can be seen in <xref
    linkend="example-field-result-annotation-with-associations" />.</para>

    <example id="example-named-native-query-annotation-with-result-set-mapping">
      <title>Named SQL query using @NamedNativeQuery
      together with <classname>@SqlResultSetMapping

      <programlisting language="JAVA" role="JAVA">@NamedNativeQuery(name="night&area", query="select night.id nid, night.night_duration, "
    + " night.night_date, area.id aid, night.area_id, area.name "
    + "from Night night, Area area where night.area_id = area.id", 
                  resultSetMapping="joinMapping")
@SqlResultSetMapping(name="joinMapping", entities={
    @EntityResult(entityClass=Night.class, fields = {
        @FieldResult(name="id", column="nid"),
        @FieldResult(name="duration", column="night_duration"),
        @FieldResult(name="date", column="night_date"),
        @FieldResult(name="area", column="area_id"),
        discriminatorColumn="disc"
    }),
    @EntityResult(entityClass=org.hibernate.test.annotations.query.Area.class, fields = {
        @FieldResult(name="id", column="aid"),
        @FieldResult(name="name", column="name")
    })
    }
)</programlisting>
    </example>

    <example id="example-implicit-result-set-mapping">
      <title>Implicit result set mapping

      <programlisting language="JAVA" role="JAVA">@Entity
@SqlResultSetMapping(name="implicit",
                     entities=@EntityResult(entityClass=SpaceShip.class))
@NamedNativeQuery(name="implicitSample", 
                  query="select * from SpaceShip", 
                  resultSetMapping="implicit")
public class SpaceShip {
    private String name;
    private String model;
    private double speed;

    @Id
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Column(name="model_txt")
    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public double getSpeed() {
        return speed;
    }

    public void setSpeed(double speed) {
        this.speed = speed;
    }
}</programlisting>
    </example>

    <example id="example-field-result-annotation-with-associations">
      <title>Using dot notation in @FieldResult for specifying associations
      </title>

      <programlisting language="JAVA" role="JAVA">@Entity
@SqlResultSetMapping(name="compositekey",
        entities=@EntityResult(entityClass=SpaceShip.class,
            fields = {
                    @FieldResult(name="name", column = "name"),
                    @FieldResult(name="model", column = "model"),
                    @FieldResult(name="speed", column = "speed"),
                    @FieldResult(name="captain.firstname", column = "firstn"),
                    @FieldResult(name="captain.lastname", column = "lastn"),
                    @FieldResult(name="dimensions.length", column = "length"),
                    @FieldResult(name="dimensions.width", column = "width")
                    }),
        columns = { @ColumnResult(name = "surface"),
                    @ColumnResult(name = "volume") } )

@NamedNativeQuery(name="compositekey",
    query="select name, model, speed, lname as lastn, fname as firstn, length, width, length * width as surface from SpaceShip", 
    resultSetMapping="compositekey")
} )
public class SpaceShip {
    private String name;
    private String model;
    private double speed;
    private Captain captain;
    private Dimensions dimensions;

    @Id
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @ManyToOne(fetch= FetchType.LAZY)
    @JoinColumns( {
            @JoinColumn(name="fname", referencedColumnName = "firstname"),
            @JoinColumn(name="lname", referencedColumnName = "lastname")
            } )
    public Captain getCaptain() {
        return captain;
    }

    public void setCaptain(Captain captain) {
        this.captain = captain;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public double getSpeed() {
        return speed;
    }

    public void setSpeed(double speed) {
        this.speed = speed;
    }

    public Dimensions getDimensions() {
        return dimensions;
    }

    public void setDimensions(Dimensions dimensions) {
        this.dimensions = dimensions;
    }
}

@Entity
@IdClass(Identity.class)
public class Captain implements Serializable {
    private String firstname;
    private String lastname;

    @Id
    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    @Id
    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }
}
</programlisting>
    </example>

    <tip>
      <para>If you retrieve a single entity using the default mapping, you can
      specify the <literal>resultClass attribute instead of
      <literal>resultSetMapping:

      <programlisting language="JAVA" role="JAVA">@NamedNativeQuery(name="implicitSample", query="select * from SpaceShip", resultClass=SpaceShip.class)
public class SpaceShip {</programlisting>
    </tip>

    <para>In some of your native queries, you'll have to return scalar values,
    for example when building report queries. You can map them in the
    <literal>@SqlResultsetMapping through
    <literal>@ColumnResult. You actually can even mix, entities and
    scalar returns in the same native query (this is probably not that common
    though).</para>

    <example>
      <title>Scalar values via @ColumnResult

      <programlisting language="JAVA" role="JAVA">@SqlResultSetMapping(name="scalar", columns=@ColumnResult(name="dimension"))
@NamedNativeQuery(name="scalar", query="select length*width as dimension from SpaceShip", resultSetMapping="scalar")</programlisting>
    </example>

    <para>An other query hint specific to native queries has been introduced:
    <literal>org.hibernate.callable which can be true or false
    depending on whether the query is a stored procedure or not.</para>

    <section id="propertyresults">
      <title>Using return-property to explicitly specify column/alias
      names</title>

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

      <programlisting role="XML"><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>
</programlisting>

      <para><return-property> also works with
      multiple columns. This solves a limitation with the
      <literal>{}-syntax which cannot allow fine grained control of
      multi-column properties.</para>

      <programlisting role="XML"><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></programlisting>

      <para>In this example <return-property> was
      used in combination with the <literal>{}-syntax for injection.
      This allows users to choose how they want to refer column and
      properties.</para>

      <para>If your mapping has a discriminator you must use
      <literal><return-discriminator> to specify the
      discriminator column.</para>
    </section>

    <section id="sp_query" revision="1">
      <title>Using stored procedures for querying

      <para>Hibernate3 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:</para>

      <programlisting role="XML">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;</programlisting>

      <para>To use this query in Hibernate you need to map it via a named
      query.</para>

      <programlisting role="XML"><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></programlisting>

      <para>Stored procedures currently only return scalars and entities.
      <literal><return-join> and
      <literal><load-collection> are not supported.

      <section id="querysql-limits-storedprocedures" revision="1">
        <title>Rules/limitations for using stored procedures

        <para>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
        <literal>session.connection(). The rules are different for
        each database, since database vendors have different stored procedure
        semantics/syntax.</para>

        <para>Stored procedure queries cannot be paged with
        <literal>setFirstResult()/setMaxResults().

        <para>The recommended call form is standard SQL92: { ? = call
        functionName(<parameters>) }</literal> or { ? = call
        procedureName(<parameters>}</literal>. Native call syntax is not
        supported.</para>

        <para>For Oracle the following rules apply:

        <itemizedlist spacing="compact">
          <listitem>
            <para>A function must return a result set. The first parameter of
            a procedure must be an <literal>OUT that returns a
            result set. This is done by using a
            <literal>SYS_REFCURSOR type in Oracle 9 or 10. In Oracle
            you need to define a <literal>REF CURSOR type. See
            Oracle literature for further information.</para>
          </listitem>
        </itemizedlist>

        <para>For Sybase or MS SQL server the following rules apply:

        <itemizedlist spacing="compact">
          <listitem>
            <para>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.</para>
          </listitem>

          <listitem>
            <para>If you can enable SET NOCOUNT ON in your
            procedure it will probably be more efficient, but this is not a
            requirement.</para>
          </listitem>
        </itemizedlist>
      </section>
    </section>
  </section>

  <section id="querysql-cud">
    <title>Custom SQL for create, update and delete

    <para>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 <xref linkend="mapping-column-read-and-write" />. 

      <programlisting language="JAVA" role="JAVA">@Entity
@Table(name="CHAOS")
@SQLInsert( sql="INSERT INTO CHAOS(size, name, nickname, id) VALUES(?,upper(?),?,?)")
@SQLUpdate( sql="UPDATE CHAOS SET size = ?, name = upper(?), nickname = ? WHERE id = ?")
@SQLDelete( sql="DELETE CHAOS WHERE id = ?")
@SQLDeleteAll( sql="DELETE CHAOS")
@Loader(namedQuery = "chaos")
@NamedNativeQuery(name="chaos", query="select id, size, name, lower( nickname ) as nickname from CHAOS where id= ?", resultClass = Chaos.class)
public class Chaos {
    @Id
    private Long id;
    private Long size;
    private String name;
    private String nickname;</programlisting>
    </example>

    <para>@SQLInsert, @SQLUpdate,
    <literal>@SQLDelete, @SQLDeleteAll
    respectively override the INSERT, UPDATE, DELETE, and DELETE all
    statement. The same can be achieved using Hibernate mapping files and the
    <literal><sql-insert>,
    <literal><sql-update> and
    <literal><sql-delete> nodes. This can be seen in 

    <example id="example-custom-crdu-via-xml">
      <title>Custom CRUD XML

      <programlisting role="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></programlisting>
    </example>

    <para>If you expect to call a store procedure, be sure to set the
    <literal>callable attribute to true. In
    annotations as well as in xml. </para>

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

    <itemizedlist>
      <listitem>
        <para>none: no check is performed: the store procedure is expected to
        fail upon issues</para>
      </listitem>

      <listitem>
        <para>count: use of rowcount to check that the update is
        successful</para>
      </listitem>

      <listitem>
        <para>param: like COUNT but using an output parameter rather that the
        standard mechanism</para>
      </listitem>
    </itemizedlist>

    <para>To define the result check style, use the check
    parameter which is again available in annoations as well as in xml.</para>

    <para>You can use the exact same set of annotations respectively xml nodes
    to override the collection related statements -see <xref
    linkend="example-overriding-sql-collections-annotations" />.</para>

    <example id="example-overriding-sql-collections-annotations">
      <title>Overriding SQL statements for collections using
      annotations</title>

      <programlisting language="JAVA" role="JAVA">@OneToMany
@JoinColumn(name="chaos_fk")
@SQLInsert( sql="UPDATE CASIMIR_PARTICULE SET chaos_fk = ? where id = ?")
@SQLDelete( sql="UPDATE CASIMIR_PARTICULE SET chaos_fk = null where id = ?")
private Set<CasimirParticle> particles = new HashSet<CasimirParticle>();</programlisting>
    </example>

    <tip>
      <para>The parameter order is important and is defined by the order
      Hibernate handles properties. You can see the expected order by enabling
      debug logging for the <literal>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 see the
      expected sequence, remember to not include your custom SQL through
      annotations or mapping files as that will override the Hibernate
      generated static sql)</para>
    </tip>

    <para>Overriding SQL statements for secondary tables is also possible
    using <literal>@org.hibernate.annotations.Table and either (or
    all) attributes <literal>sqlInsert,
    <literal>sqlUpdate, sqlDelete:

    <example>
      <title>Overriding SQL statements for secondary tables

      <programlisting language="JAVA" role="JAVA">@Entity
@SecondaryTables({
    @SecondaryTable(name = "`Cat nbr1`"),
    @SecondaryTable(name = "Cat2"})
@org.hibernate.annotations.Tables( {
    @Table(appliesTo = "Cat", comment = "My cat table" ),
    @Table(appliesTo = "Cat2", foreignKey = @ForeignKey(name="FK_CAT2_CAT"), fetch = FetchMode.SELECT,
        sqlInsert=@SQLInsert(sql="insert into Cat2(storyPart2, id) values(upper(?), ?)") )
} )
public class Cat implements Serializable {</programlisting>
    </example>

    <para>The previous example also shows that you can give a comment to a
    given table (primary or secondary): This comment will be used for DDL
    generation.</para>

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

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

    <example>
      <title>Stored procedures and their return value

      <programlisting>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;</programlisting>
    </example>
  </section>

  <section id="querysql-load">
    <title>Custom SQL for loading

    <para>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 <xref
    linkend="mapping-column-read-and-write" /> or at the statement level. Here
    is an example of a statement level override:</para>

    <programlisting role="XML"><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></programlisting>

    <para>This is just a named query declaration, as discussed earlier. You
    can reference this named query in a class mapping:</para>

    <programlisting role="XML"><class name="Person">
    <id name="id">
        <generator class="increment"/>
    </id>
    <property name="name" not-null="true"/>
    <loader query-ref="person"/>
</class></programlisting>

    <para>This even works with stored procedures.

    <para>You can even define a query for collection loading:

    <programlisting role="XML"><set name="employments" inverse="true">
    <key/>
    <one-to-many class="Employment"/>
    <loader query-ref="employments"/>
</set></programlisting>

    <programlisting role="XML"><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></programlisting>

    <para>You can also define an entity loader that loads a collection by join
    fetching:</para>

    <programlisting role="XML"><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></programlisting>

    <para>The annotation equivalent <loader> is the
    @Loader annotation as seen in <xref
    linkend="example-custom-crdu-via-annotations" />.</para>
  </section>
</chapter>