Menu
▾
▴
[Hibernate-commits] Hibernate Code SVN: r9638 - trunk/Hibernate3/doc/reference/en/modules
|
From: <hib...@li...> - 2006-03-16 16:21:02
|
Author: max...@jb...
Date: 2006-03-16 11:20:52 -0500 (Thu, 16 Mar 2006)
New Revision: 9638
Modified:
trunk/Hibernate3/doc/reference/en/modules/query_sql.xml
Log:
more complete native sql documentation
Modified: trunk/Hibernate3/doc/reference/en/modules/query_sql.xml
===================================================================
--- trunk/Hibernate3/doc/reference/en/modules/query_sql.xml 2006-03-16 14:26:38 UTC (rev 9637)
+++ trunk/Hibernate3/doc/reference/en/modules/query_sql.xml 2006-03-16 16:20:52 UTC (rev 9638)
@@ -11,191 +11,385 @@
<para>Hibernate3 allows you to specify handwritten SQL (including stored
procedures) for all create, update, delete, and load operations.</para>
- <sect1 id="querysql-creating" revision="3">
+ <sect1 id="querysql-creating" revision="4">
<title>Using a <literal>SQLQuery</literal></title>
<para>Execution of native SQL queries is controlled via the
<literal>SQLQuery</literal> interface, which is obtained by calling
- <literal>Session.createSQLQuery()</literal>. In extremely simple cases, we
- can use the following form:</para>
+ <literal>Session.createSQLQuery()</literal>. The following describes how
+ to use this API for querying.</para>
- <programlisting><![CDATA[List cats = sess.createSQLQuery("select * from cats")
- .addEntity(Cat.class)
- .list();]]></programlisting>
+ <sect2>
+ <title>Scalar queries</title>
- <para>This query specified:</para>
+ <para>The most basic SQL query is to get a list of scalars
+ (values).</para>
- <itemizedlist>
- <listitem>
- <para>the SQL query string</para>
- </listitem>
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT * FROM CATS").list();
+sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();
+]]></programlisting>
- <listitem>
- <para>the entity returned by the query</para>
- </listitem>
- </itemizedlist>
+ <para>These will both 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>Here, 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 which join multiple tables, since the same column names
- may appear in more than one table. The following form is not vulnerable to
- column name duplication:</para>
+ <para>To avoid the overhead of using
+ <literal>ResultSetMetadata</literal> or simply to be more explicit in
+ what is returned one can use <literal>addScalar()</literal>.</para>
- <programlisting><![CDATA[List cats = sess.createSQLQuery("select {cat.*} from cats cat")
- .addEntity("cat", Cat.class)
- .list();]]></programlisting>
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT * FROM CATS")
+ .addScalar("ID", Hibernate.LONG)
+ .addScalar("NAME", Hibernate.STRING)
+ .addScalar("BIRTHDATE", Hibernate.DATE)
+]]></programlisting>
- <para>This query specified:</para>
+ <para>This query specified:</para>
- <itemizedlist>
- <listitem>
- <para>the SQL query string, with a placeholder for Hibernate to inject
- the column aliases</para>
- </listitem>
+ <itemizedlist>
+ <listitem>
+ <para>the SQL query string</para>
+ </listitem>
- <listitem>
- <para>the entity returned by the query, and its SQL table alias</para>
- </listitem>
- </itemizedlist>
+ <listitem>
+ <para>the columns and types to return</para>
+ </listitem>
+ </itemizedlist>
- <para>The <literal>addEntity()</literal> method associates the SQL table
- alias with the returned entity class, and determines the shape of the
- query result set.</para>
+ <para>This will still return Object arrays, but now it will not use
+ <literal>ResultSetMetdata</literal> 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>*</literal> and could return more than the three listed
+ columns.</para>
- <para>The <literal>addJoin()</literal> method may be used to load
- associations to other entities and collections.</para>
+ <para>It is possible to leave out the type information for all or some
+ of the scalars.</para>
- <programlisting><![CDATA[List cats = sess.createSQLQuery(
- "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
- )
- .addEntity("cat", Cat.class)
- .addJoin("kitten", "cat.kittens")
- .list();]]></programlisting>
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT * FROM CATS")
+ .addScalar("ID", Hibernate.LONG)
+ .addScalar("NAME")
+ .addScalar("BIRTHDATE")
+]]></programlisting>
- <para>A native SQL query might return a simple scalar value or a
- combination of scalars and entities.</para>
+ <para>This is essentially the same query as before, but now
+ <literal>ResultSetMetaData</literal> is used to decide the type of NAME
+ and BIRTHDATE where as the type of ID is explicitly specified.</para>
- <programlisting><![CDATA[Double max = (Double) sess.createSQLQuery("select max(cat.weight) as maxWeight from cats cat")
- .addScalar("maxWeight", Hibernate.DOUBLE);
- .uniqueResult();]]></programlisting>
+ <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</literal> in
+ the Dialect.</para>
+ </sect2>
- <para>You can alternatively describe the resultset mapping informations in
- your hbm files and use them for your queries</para>
+ <sect2>
+ <title>Entity queries</title>
- <programlisting><![CDATA[List cats = sess.createSQLQuery(
- "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
- )
- .setResultSetMapping("catAndKitten")
- .list();]]></programlisting>
- </sect1>
+ <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()</literal>.</para>
- <sect1 id="querysql-aliasreferences">
- <title>Alias and property references</title>
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);
+sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);
+]]></programlisting>
- <para>The <literal>{cat.*}</literal> notation used above is a shorthand
- for "all properties". Alternatively, you may list the columns explicity,
- but even this case we let Hibernate inject 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, we retrieve
- <literal>Cat</literal>s from a different table
- (<literal>cat_log</literal>) to the one declared in the mapping metadata.
- Notice that we may even use the property aliases in the where clause if we
- like.</para>
+ <para>This query specified:</para>
- <para>The <literal>{}</literal>-syntax is <emphasis>not</emphasis>
- required for named queries. See <xref
- linkend="querysql-namedqueries" /></para>
+ <itemizedlist>
+ <listitem>
+ <para>the SQL query string</para>
+ </listitem>
- <programlisting><![CDATA[String sql = "select cat.originalId as {cat.id}, " +
- "cat.mateid as {cat.mate}, cat.sex as {cat.sex}, " +
- "cat.weight*10 as {cat.weight}, cat.name as {cat.name} " +
- "from cat_log cat where {cat.mate} = :catId"
+ <listitem>
+ <para>the entity returned by the query</para>
+ </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 <literal>many-to-one</literal> 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</literal> to a
+ <literal>Dog</literal>:</para>
+
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);
+]]></programlisting>
+
+ <para>This will allow cat.getDog() to function properly.</para>
+ </sect2>
+
+ <sect2>
+ <title>Handling associations and collections</title>
+
+ <para>It is possible to eagerly join in the <literal>Dog</literal> to
+ avoid the possible extra roundtrip for initializing the proxy. This is
+ done via the <literal>addJoin()</literal> method, which allows you to
+ join in an association or collection.</para>
+
+ <programlisting><![CDATA[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 <literal>Cat</literal>'s will have
+ their <literal>dog</literal> property fully initialized without any
+ extra roundtrip to the database. Notice that we added a 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</literal> had a one-to-many to <literal>Dog</literal>
+ instead.</para>
+
+ <programlisting><![CDATA[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>
+
+ <p>At this stage we are reaching the limits of what is possible with
+ native queries without starting to enhance the sql queries to make them
+ usable in Hibernate; the problems starts to arise when returning
+ multiple entities of the same type or when the default alias/column
+ names are not enough.</p>
+ </sect2>
+
+ <sect2>
+ <title>Returning multiple entities</title>
+
+ <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 which join multiple tables, since the same
+ column names may appear in more than one table.</para>
+
+ <para>Column alias injection is needed in the following query (which
+ most likely will fail):</para>
+
+ <programlisting><![CDATA[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 intention for this query is to return two Cat instances per
+ row, a cat and its mother. This will fail since there is a conflict of
+ names since they are mapped to the same column names and 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 specificed in
+ the mappings ("ID" and "NAME").</para>
+
+ <para>The following form is not vulnerable to column name
+ duplication:</para>
+
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT {cat.*}, {mother.*} FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
+ .addEntity("cat", Cat.class)
+ .addEntity("mother", Cat.class)
+]]></programlisting>
+
+ <para>This query specified:</para>
+
+ <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</para>
+ </listitem>
+ </itemizedlist>
+
+ <para>The {cat.*} and {mother.*} notation used above is a shorthand for
+ "all properties". Alternatively, you may list the columns explicity, but
+ even in this case we let Hibernate inject 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, we retrieve
+ Cats and their mothers from a different table (cat_log) to the one
+ declared in the mapping metadata. Notice that we may even use the
+ property aliases in the where clause if we like.</para>
+
+ <programlisting><![CDATA[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)
- .setLong("catId", catId)
- .list();]]></programlisting>
+ .addEntity("cat", Cat.class)
+ .addEntity("mother", Cat.class).list()
+]]></programlisting>
- <para><emphasis>Note:</emphasis> if you list each property explicitly, you
- must include all properties of the class <emphasis>and its
- subclasses</emphasis>!</para>
+ <sect3 id="querysql-aliasreferences" revision="2">
+ <title>Alias and property references</title>
- <para>The following table shows the different possibilities of using the
- alias injection. Note: the alias names in the result are examples, each
- alias will have a unique and probably different name when used.</para>
+ <para>For most cases the above alias injection is needed, but for
+ queries relating to more complex mappings like composite properties,
+ inheritance discriminators, collections etc. there are some specific
+ aliases to use to allow Hibernate to inject the proper aliases.</para>
- <table frame="topbot" id="aliasinjection-summary">
- <title>Alias injection names</title>
+ <para>The following table shows the different possibilities of using
+ the alias injection. Note: the alias names in the result are examples,
+ each alias will have a unique and probably different name when
+ used.</para>
- <tgroup cols="4">
- <colspec colwidth="1*" />
+ <table frame="topbot" id="aliasinjection-summary">
+ <title>Alias injection names</title>
- <colspec colwidth="1*" />
+ <tgroup cols="3">
+ <colspec colwidth="1*" />
- <colspec colwidth="2.5*" />
+ <colspec colwidth="1*" />
- <thead>
- <row>
- <entry>Description</entry>
- <entry>Syntax</entry>
- <entry>Example</entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry>A simple property</entry>
- <entry><literal>{[aliasname].[propertyname]</literal></entry>
- <entry><literal>A_NAME as {item.name}</literal></entry>
- </row>
- <row>
- <entry>A composite property</entry>
- <entry><literal>{[aliasname].[componentname].[propertyname]}</literal></entry>
- <entry><literal>CURRENCY as {item.amount.currency}, VALUE as {item.amount.value}</literal></entry>
- </row>
- <row>
- <entry>Discriminator of an entity</entry>
- <entry><literal>{[aliasname].class}</literal></entry>
- <entry><literal>DISC as {item.class}</literal></entry>
- </row>
- <row>
- <entry>All properties of an entity</entry>
- <entry><literal>{[aliasname].*}</literal></entry>
- <entry><literal>{item.*}</literal></entry>
- </row>
- <row>
- <entry>A collection key</entry>
- <entry><literal>{[aliasname].key}</literal></entry>
- <entry><literal>ORGID as {coll.key}</literal></entry>
- </row>
- <row>
- <entry>The id of an collection</entry>
- <entry><literal>{[aliasname].id}</literal></entry>
- <entry><literal>EMPID as {coll.id}</literal></entry>
- </row>
- <row>
- <entry>The element of an collection</entry>
- <entry><literal>{[aliasname].element}</literal></entry>
- <entry><literal>XID as {coll.element}</literal></entry>
- <entry></entry>
- </row>
- <row>
- <entry>Property of the element in the collection</entry>
- <entry><literal>{[aliasname].element.[propertyname]}</literal></entry>
- <entry><literal>NAME as {coll.element.name}</literal></entry>
- </row>
- <row>
- <entry>All properties of the element in the collection</entry>
- <entry><literal>{[aliasname].element.*}</literal></entry>
- <entry><literal>{coll.element.*}</literal></entry>
- </row>
- <row>
- <entry>All properties of the the collection</entry>
- <entry><literal>{[aliasname].*}</literal></entry>
- <entry><literal>{coll.*}</literal></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
+ <colspec colwidth="2.5*" />
+
+ <thead>
+ <row>
+ <entry>Description</entry>
+
+ <entry>Syntax</entry>
+
+ <entry>Example</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>A simple property</entry>
+
+ <entry><literal>{[aliasname].[propertyname]</literal></entry>
+
+ <entry><literal>A_NAME as {item.name}</literal></entry>
+ </row>
+
+ <row>
+ <entry>A composite property</entry>
+
+ <entry><literal>{[aliasname].[componentname].[propertyname]}</literal></entry>
+
+ <entry><literal>CURRENCY as {item.amount.currency}, VALUE as
+ {item.amount.value}</literal></entry>
+ </row>
+
+ <row>
+ <entry>Discriminator of an entity</entry>
+
+ <entry><literal>{[aliasname].class}</literal></entry>
+
+ <entry><literal>DISC as {item.class}</literal></entry>
+ </row>
+
+ <row>
+ <entry>All properties of an entity</entry>
+
+ <entry><literal>{[aliasname].*}</literal></entry>
+
+ <entry><literal>{item.*}</literal></entry>
+ </row>
+
+ <row>
+ <entry>A collection key</entry>
+
+ <entry><literal>{[aliasname].key}</literal></entry>
+
+ <entry><literal>ORGID as {coll.key}</literal></entry>
+ </row>
+
+ <row>
+ <entry>The id of an collection</entry>
+
+ <entry><literal>{[aliasname].id}</literal></entry>
+
+ <entry><literal>EMPID as {coll.id}</literal></entry>
+ </row>
+
+ <row>
+ <entry>The element of an collection</entry>
+
+ <entry><literal>{[aliasname].element}</literal></entry>
+
+ <entry><literal>XID as {coll.element}</literal></entry>
+ </row>
+
+ <row>
+ <entry>roperty of the element in the collection</entry>
+
+ <entry><literal>{[aliasname].element.[propertyname]}</literal></entry>
+
+ <entry><literal>NAME as {coll.element.name}</literal></entry>
+ </row>
+
+ <row>
+ <entry>All properties of the element in the collection</entry>
+
+ <entry><literal>{[aliasname].element.*}</literal></entry>
+
+ <entry><literal>{coll.element.*}</literal></entry>
+ </row>
+
+ <row>
+ <entry>All properties of the the collection</entry>
+
+ <entry><literal>{[aliasname].*}</literal></entry>
+
+ <entry><literal>{coll.*}</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect3>
+ </sect2>
+
+ <sect2>
+ <title>Returning non-managed entities</title>
+
+ <para>It is possible to apply a ResultTransformer to native sql queries. Allowing it to e.g. return non-managed entities.</para>
+
+ <programlisting><![CDATA[sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
+ .setResultTransformer(Transformers.aliasToBean(CatDTO.class))]]></programlisting>
+
+ <para>This query specified:</para>
+
+ <itemizedlist>
+ <listitem>
+ <para>the SQL query string</para>
+ </listitem>
+
+ <listitem>
+ <para>a result transformer</para>
+ </listitem>
+ </itemizedlist>
+
+ <para>
+ The above query will return a list of <literal>CatDTO</literal> which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding
+ properties or fields.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Handling inheritance</title>
+
+ <para>Native sql queries which query for entities that is mapped as part
+ of an inheritance must include all properties for the baseclass and all
+ it subclasses.</para>
+ </sect2>
+
+ <sect2>
+ <title>Parameters</title>
+
+ <para>Native sql queries support positional as well as named
+ parameters:</para>
+
+ <programlisting><![CDATA[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>
+ </sect2>
+
+
+
</sect1>
<sect1 id="querysql-namedqueries" revision="3">
@@ -277,6 +471,15 @@
WHERE person.NAME LIKE :namePattern
</sql-query>]]></programlisting>
+ <para>You can alternatively use the resultset mapping information in your
+ hbm files directly in java code.</para>
+
+ <programlisting><![CDATA[List cats = sess.createSQLQuery(
+ "select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id"
+ )
+ .setResultSetMapping("catAndKitten")
+ .list();]]></programlisting>
+
<sect2 id="propertyresults">
<title>Using return-property to explicitly specify column/alias
names</title>
@@ -325,22 +528,19 @@
<literal>{}</literal>-syntax for injection. Allowing 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></literal> to specify the
- discriminator column.
- </para>
-
+ <para>If your mapping has a discriminator you must use
+ <literal><return-discriminator></literal> to specify the
+ discriminator column.</para>
</sect2>
<sect2 id="sp_query" revision="1">
<title>Using stored procedures for querying</title>
<para>Hibernate 3 introduces 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>
+ 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><![CDATA[CREATE OR REPLACE FUNCTION selectAllEmployments
RETURN SYS_REFCURSOR
@@ -381,11 +581,11 @@
<sect3 id="querysql-limits-storedprocedures" revision="1">
<title>Rules/limitations for using stored procedures</title>
- <para>To use stored procedures with Hibernate the procedures/functions have to
- follow some 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()</literal>. The
- rules are different for each database, since database vendors have
+ <para>To use stored procedures with Hibernate the procedures/functions
+ have to follow some 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()</literal>.
+ The rules are different for each database, since database vendors have
different stored procedure semantics/syntax.</para>
<para>Stored procedure queries can't be paged with
@@ -393,18 +593,19 @@
<para>Recommended call form is standard SQL92: <literal>{ ? = call
functionName(<parameters>) }</literal> or <literal>{ ? = call
- procedureName(<parameters>}</literal>. Native call syntax is
- not supported.</para>
+ procedureName(<parameters>}</literal>. Native call syntax is not
+ supported.</para>
<para>For Oracle the following rules apply:</para>
<itemizedlist spacing="compact">
<listitem>
<para>A function must return a result set. The first parameter of
- a procedure must be an <literal>OUT</literal> that returns a result
- set. This is done by using a <literal>SYS_REFCURSOR</literal> type
- in Oracle 9 or 10. In Oracle you need to define a
- <literal>REF CURSOR</literal> type, see Oracle literature.</para>
+ a procedure must be an <literal>OUT</literal> that returns a
+ result set. This is done by using a
+ <literal>SYS_REFCURSOR</literal> type in Oracle 9 or 10. In Oracle
+ you need to define a <literal>REF CURSOR</literal> type, see
+ Oracle literature.</para>
</listitem>
</itemizedlist>
@@ -554,5 +755,4 @@
WHERE ID=?
</sql-query>]]></programlisting>
</sect1>
-
</chapter>
\ No newline at end of file
|
