Hibernate example source code file (query_native.xml)

This example Hibernate source code file (query_native.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

entityresult, fieldresult, fieldresult, hibernate, java, java, lesser, public, query, sql, sql, sqlresultsetmapping, this, this

The Hibernate query_native.xml source code

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ~ Hibernate, Relational Persistence for Idiomatic Java
  ~
  ~ Copyright (c) 2008, Red Hat Inc 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 Inc.
  ~
  ~ 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">
<chapter id="query_native">
  <title>Native query

  <para>You may 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 CONNECT BY option in Oracle. It also provides a
  clean migration path from a direct SQL/JDBC based application to Hibernate.
  Note that Hibernate allows you to specify handwritten SQL (including stored
  procedures) for all create, update, delete, and load operations (please
  refer to the reference guide for more information.)</para>

  <sect1>
    <title>Expressing the resultset

    <para>To use a SQL query, you need to describe the SQL resultset, this
    description will help the <literal>EntityManager to map your
    columns onto entity properties. This is done using the
    <literal>@SqlResultSetMapping annotation. Each
    <literal>@SqlResultSetMapping has a name which is used when
    creating a SQL query on <literal>EntityManager.

    <programlisting role="JAVA" language="JAVA">@SqlResultSetMapping(name="GetNightAndArea", entities={
    @EntityResult(name="org.hibernate.test.annotations.query.Night", fields = {
        @FieldResult(name="id", column="nid"),
        @FieldResult(name="duration", column="night_duration"),
        @FieldResult(name="date", column="night_date"),
        @FieldResult(name="area", column="area_id")
    }),
    @EntityResult(name="org.hibernate.test.annotations.query.Area", fields = {
        @FieldResult(name="id", column="aid"),
        @FieldResult(name="name", column="name")
    })
    }
)

//or
@SqlResultSetMapping(name="defaultSpaceShip", entities=@EntityResult(name="org.hibernate.test.annotations.query.SpaceShip"))</programlisting>

    <para>You can also define scalar results and even mix entity results and
    scalar results</para>

    <programlisting role="JAVA" language="JAVA">@SqlResultSetMapping(name="ScalarAndEntities",
    entities={
        @EntityResult(name="org.hibernate.test.annotations.query.Night", fields = {
            @FieldResult(name="id", column="nid"),
            @FieldResult(name="duration", column="night_duration"),
            @FieldResult(name="date", column="night_date"),
            @FieldResult(name="area", column="area_id")
        }),
        @EntityResult(name="org.hibernate.test.annotations.query.Area", fields = {
            @FieldResult(name="id", column="aid"),
            @FieldResult(name="name", column="name")
        })
    },
    columns={
        @ColumnResult(name="durationInSec")
    }
)</programlisting>

    <para>The SQL query will then have to return a column alias
    <literal>durationInSec.

    <para>Please refer to the Hibernate Annotations reference guide for more
    information about <literal>@SqlResultSetMapping.
  </sect1>

  <sect1>
    <title>Using native SQL Queries

    <para>TODO: This sounds like a dupe...

    <para>Now that the result set is described, we are capable of executing
    the native SQL query. <literal>EntityManager provides all the
    needed APIs. The first method is to use a SQL resultset name to do the
    binding, the second one uses the entity default mapping (the column
    returned has to have the same names as the one used in the mapping). A
    third one (not yet supported by Hibernate entity manager), returns pure
    scalar results.</para>

    <programlisting role="JAVA" language="JAVA">String sqlQuery = "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 "
    + "and night.night_duration >= ?";
Query q = entityManager.createNativeQuery(sqlQuery, "GetNightAndArea");
q.setParameter( 1, expectedDuration );
q.getResultList();</programlisting>

    <para>This native query returns nights and area based on the
    <literal>GetNightAndArea result set.

    <programlisting role="JAVA" language="JAVA">String sqlQuery = "select * from tbl_spaceship where owner = ?";
Query q = entityManager.createNativeQuery(sqlQuery, SpaceShip.class);
q.setParameter( 1, "Han" );
q.getResultList();</programlisting>

    <para>The second version is useful when your SQL query returns one entity
    reusing the same columns as the ones mapped in metadata.</para>
  </sect1>

  <sect1>
    <title>Named queries

    <para>Native named queries share the same calling API than JP-QL named
    queries. Your code doesn't need to know the difference between the two.
    This is very useful for migration from SQL to JP-QL:</para>

    <programlisting role="JAVA" language="JAVA">Query q = entityManager.createNamedQuery("getSeasonByNativeQuery");
q.setParameter( 1, name );
Season season = (Season) q.getSingleResult();</programlisting>
  </sect1>
</chapter>