<?xml version='1.0' encoding="UTF-8"?>

<!--
  ~ Hibernate, Relational Persistence for Idiomatic Java
  ~
  ~ License: GNU Lesser General Public License (LGPL), version 2.1 or later.
  ~ See the lgpl.txt file in the root directory or <http://www.gnu.org/licenses/lgpl-2.1.html>.
  -->
<chapter xml:id="queryhql" revision="1" xmlns="http://docbook.org/ns/docbook">
    <title>HQL: The Hibernate Query Language</title>

    <para>
        Hibernate uses a powerful query language (HQL) that is similar in appearance to
        SQL. Compared with SQL, however, HQL is fully object-oriented
        and understands notions like inheritance, polymorphism and association.
    </para>

    <section xml:id="queryhql-casesensitivity">
        <title>Case Sensitivity</title>

        <para>
            With the exception of names of Java classes and properties, queries are case-insensitive.
            So <literal>SeLeCT</literal> is the same as
            <literal>sELEct</literal> is the same as
            <literal>SELECT</literal>, but
            <literal>org.hibernate.eg.FOO</literal> is not
            <literal>org.hibernate.eg.Foo</literal>, and
            <literal>foo.barSet</literal> is not
            <literal>foo.BARSET</literal>.
        </para>

        <para>
            This manual uses lowercase HQL keywords. Some users find queries with uppercase keywords
            more readable, but this convention is unsuitable for queries embedded in Java code.
        </para>

    </section>

    <section xml:id="queryhql-from">
        <title>The from clause</title>

        <para>
            The simplest possible Hibernate query is of the form:
        </para>

        <programlisting><![CDATA[from eg.Cat]]></programlisting>

        <para>
            This returns all instances of the class <literal>eg.Cat</literal>.
            You do not usually need to qualify the class name, since <literal>auto-import</literal>
            is the default. For example:
        </para>

        <programlisting><![CDATA[from Cat]]></programlisting>

        <para>
            
            In order to refer to the <literal>Cat</literal> in other parts of the
            query, you will need to assign an <emphasis>alias</emphasis>. For example:
        </para>

        <programlisting><![CDATA[from Cat as cat]]></programlisting>

        <para>
            This query assigns the alias <literal>cat</literal> to <literal>Cat</literal>
            instances, so you can use that alias later in the query. The <literal>as</literal>
            keyword is optional. You could also write:
        </para>

        <programlisting><![CDATA[from Cat cat]]></programlisting>

        <para>
            Multiple classes can appear, resulting in a cartesian product or "cross" join.
        </para>

        <programlisting><![CDATA[from Formula, Parameter]]></programlisting>
        <programlisting><![CDATA[from Formula as form, Parameter as param]]></programlisting>

        <para>
            It is good practice to name query aliases using an initial lowercase as this is
            consistent with Java naming standards for local variables
            (e.g. <literal>domesticCat</literal>).
        </para>

    </section>

    <section xml:id="queryhql-joins" revision="3">
        <title>Associations and joins</title>

        <para>
            You can also assign aliases to associated entities or to elements of a
            collection of values using a <literal>join</literal>. For example:
        </para>

        <programlisting><![CDATA[from Cat as cat
    inner join cat.mate as mate
    left outer join cat.kittens as kitten]]></programlisting>

        <programlisting><![CDATA[from Cat as cat left join cat.mate.kittens as kittens]]></programlisting>

        <programlisting><![CDATA[from Formula form full join form.parameter param]]></programlisting>

        <para>
            The supported join types are borrowed from ANSI SQL:
        </para>

        <itemizedlist spacing="compact">
            <listitem>
                <para>
                    <literal>inner join</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>left outer join</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>right outer join</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>full join</literal> (not usually useful)
                </para>
            </listitem>
        </itemizedlist>

        <para>
            The <literal>inner join</literal>, <literal>left outer join</literal> and
            <literal>right outer join</literal> constructs may be abbreviated.
        </para>

        <programlisting><![CDATA[from Cat as cat
    join cat.mate as mate
    left join cat.kittens as kitten]]></programlisting>

        <para>
            You may supply extra join conditions using the HQL <literal>with</literal>
            keyword.
        </para>

        <programlisting><![CDATA[from Cat as cat
    left join cat.kittens as kitten
        with kitten.bodyWeight > 10.0]]></programlisting>

        <para>
            A "fetch" join allows associations or collections of values to be
            initialized along with their parent objects using a single select. This is particularly
            useful in the case of a collection. It effectively overrides the outer join and
            lazy declarations of the mapping file for associations and collections. See
            <xref linkend="performance-fetching"/> for more information.
        </para>

        <programlisting><![CDATA[from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens]]></programlisting>

        <para>
            A fetch join does not usually need to assign an alias, because the associated objects
            should not be used in the <literal>where</literal> clause (or any other clause). 
            The associated objects are also not returned directly in the query results. Instead, they may
            be accessed via the parent object. The only reason you might need an alias is if you are
            recursively join fetching a further collection:
        </para>

        <programlisting><![CDATA[from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens child
    left join fetch child.kittens]]></programlisting>

        <para>
            The <literal>fetch</literal> construct cannot be used in queries called using
            <literal>iterate()</literal> (though <literal>scroll()</literal> can be used). 
            <literal>Fetch</literal> should not be used together with <literal>setMaxResults()</literal> or
            <literal>setFirstResult()</literal>, as these operations are based on the result rows which
            usually contain duplicates for eager collection fetching, hence, the number of rows is not what
            you would expect.
            <literal>Fetch</literal> should also not be used together with impromptu <literal>with</literal> condition.
            It is possible to create a cartesian product by join fetching more than one collection in a
            query, so take care in this case. Join fetching multiple collection roles can produce
            unexpected results for bag mappings, so user discretion is advised when formulating queries in this
            case. Finally, note that <literal>full join fetch</literal> and <literal>right join fetch</literal>
            are not meaningful.
        </para>

        <para>
            If you are using property-level lazy fetching (with bytecode instrumentation), it is
            possible to force Hibernate to fetch the lazy properties in the first query immediately
            using <literal>fetch all properties</literal>.
        </para>

        <programlisting><![CDATA[from Document fetch all properties order by name]]></programlisting>
        <programlisting><![CDATA[from Document doc fetch all properties where lower(doc.name) like '%cats%']]></programlisting>

    </section>

    <section xml:id="queryhql-joins-forms">
        <title>Forms of join syntax</title>

	    <para>
		    HQL supports two forms of association joining: <literal>implicit</literal> and <literal>explicit</literal>.
		</para>

	    <para>
		    The queries shown in the previous section all use the <literal>explicit</literal> form, that is, where
		    the join keyword is explicitly used in the from clause.  This is the recommended form.
	    </para>

	    <para>
		    The <literal>implicit</literal> form does not use the join keyword.  Instead, the
		    associations are "dereferenced" using dot-notation.  <literal>implicit</literal> joins
		    can appear in any of the HQL clauses.  <literal>implicit</literal> join result
		    in inner joins in the resulting SQL statement.
	    </para>

        <programlisting><![CDATA[from Cat as cat where cat.mate.name like '%s%']]></programlisting>
	</section>

    <section xml:id="queryhql-identifier-property">
        <title>Referring to identifier property</title>

        <para>
            There are 2 ways to refer to an entity's identifier property:
        </para>
        <itemizedlist spacing="compact">
            <listitem>
                <para>
                    The special property (lowercase) <literal>id</literal> may be used to reference the identifier
                    property of an entity <emphasis>provided that the entity does not define a non-identifier property
                    named id</emphasis>.
                </para>
            </listitem>
            <listitem>
                <para>
                    If the entity defines a named identifier property, you can use that property name.
                </para>
            </listitem>
        </itemizedlist>

        <para>
            References to composite identifier properties follow the same naming rules. If the
            entity has a non-identifier property named id, the composite identifier property can only
            be referenced by its defined named. Otherwise, the special <literal>id</literal> property
            can be used to reference the identifier property.
        </para>

	<important>
        <para>
            Please note that, starting in version 3.2.2, this has changed significantly. In previous versions,
            <literal>id</literal>  <emphasis>always</emphasis> referred to the identifier property
            regardless of its actual name.  A ramification of that decision was that non-identifier
            properties named <literal>id</literal> could never be referenced in Hibernate queries.
        </para>
	</important>  

  </section>

    <section xml:id="queryhql-select">
        <title>The select clause</title>

        <para>
            The <literal>select</literal> clause picks which objects and properties to return in
            the query result set. Consider the following:
        </para>

        <programlisting><![CDATA[select mate
from Cat as cat
    inner join cat.mate as mate]]></programlisting>

        <para>
            The query will select <literal>mate</literal>s of other <literal>Cat</literal>s.
            You can express this query more compactly as:
        </para>

        <programlisting><![CDATA[select cat.mate from Cat cat]]></programlisting>

        <para>
            Queries can return properties of any value type including properties of component type:
        </para>

        <programlisting><![CDATA[select cat.name from DomesticCat cat
where cat.name like 'fri%']]></programlisting>

        <programlisting><![CDATA[select cust.name.firstName from Customer as cust]]></programlisting>

        <para>
            Queries can return multiple objects and/or properties as an array of type
            <literal>Object[]</literal>:
        </para>

        <programlisting><![CDATA[select mother, offspr, mate.name
from DomesticCat as mother
    inner join mother.mate as mate
    left outer join mother.kittens as offspr]]></programlisting>

        <para>
            Or as a <literal>List</literal>:
        </para>

        <programlisting><![CDATA[select new list(mother, offspr, mate.name)
from DomesticCat as mother
    inner join mother.mate as mate
    left outer join mother.kittens as offspr]]></programlisting>

        <para>
            Or - assuming that the class <literal>Family</literal> has an appropriate constructor - as an actual typesafe Java object:
        </para>

        <programlisting><![CDATA[select new Family(mother, mate, offspr)
from DomesticCat as mother
    join mother.mate as mate
    left join mother.kittens as offspr]]></programlisting>

      
        <para>
            You can assign aliases to selected expressions using <literal>as</literal>:
        </para>

        <programlisting><![CDATA[select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n
from Cat cat]]></programlisting>

        <para>
            This is most useful when used together with <literal>select new map</literal>:
        </para>

        <programlisting><![CDATA[select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n )
from Cat cat]]></programlisting>

        <para>
            This query returns a <literal>Map</literal> from aliases to selected values.
        </para>

    </section>

    <section xml:id="queryhql-aggregation">
        <title>Aggregate functions</title>

        <para>
            HQL queries can even return the results of aggregate functions on properties:
        </para>

        <programlisting><![CDATA[select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat)
from Cat cat]]></programlisting>

<!-- NO LONGER SUPPORTED
        <para>
            Collections can also appear inside aggregate functions in the <literal>select</literal>
            clause.
        </para>

        <programlisting><![CDATA[select cat, count( elements(cat.kittens) )
from Cat cat group by cat]]></programlisting>
-->

        <para>
            The supported aggregate functions are:
        </para>

        <itemizedlist spacing="compact">
            <listitem>
                <para>
                    <literal>avg(...), sum(...), min(...), max(...)</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>count(*)</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>count(...), count(distinct ...), count(all...)</literal>
                </para>
            </listitem>
        </itemizedlist>

        <para>
            You can use arithmetic operators, concatenation, and recognized SQL functions
            in the select clause:
        </para>

        <programlisting><![CDATA[select cat.weight + sum(kitten.weight)
from Cat cat
    join cat.kittens kitten
group by cat.id, cat.weight]]></programlisting>

        <programlisting><![CDATA[select firstName||' '||initial||' '||upper(lastName) from Person]]></programlisting>

        <para>
            The <literal>distinct</literal> and <literal>all</literal> keywords can be used and
            have the same semantics as in SQL.
        </para>

        <programlisting><![CDATA[select distinct cat.name from Cat cat

select count(distinct cat.name), count(cat) from Cat cat]]></programlisting>

    </section>

    <section xml:id="queryhql-polymorphism">
        <title>Polymorphic queries</title>

        <para>
            A query like:
        </para>

        <programlisting><![CDATA[from Cat as cat]]></programlisting>

        <para>
            returns instances not only of <literal>Cat</literal>, but also of subclasses like
            <literal>DomesticCat</literal>. Hibernate queries can name <emphasis>any</emphasis> Java
            class or interface in the <literal>from</literal> clause. The query will return instances
            of all persistent classes that extend that class or implement the interface. The following
            query would return all persistent objects:
        </para>

        <programlisting><![CDATA[from java.lang.Object o]]></programlisting>

        <para>
            The interface <literal>Named</literal> might be implemented by various persistent
            classes:
        </para>

        <programlisting><![CDATA[from Named n, Named m where n.name = m.name]]></programlisting>

        <para>
            These last two queries will require more than one SQL <literal>SELECT</literal>. This
            means that the <literal>order by</literal> clause does not correctly order the whole result set.
            It also means you cannot call these queries using <literal>Query.scroll()</literal>.
        </para>

    </section>

    <section xml:id="queryhql-where" revision="1">
        <title>The where clause</title>

        <para>
            The <literal>where</literal> clause allows you to refine the list of instances returned.
            If no alias exists, you can refer to properties by name:
        </para>

        <programlisting><![CDATA[from Cat where name='Fritz']]></programlisting>

        <para>
        	If there is an alias, use a qualified property name:
        </para>

        <programlisting><![CDATA[from Cat as cat where cat.name='Fritz']]></programlisting>

        <para>
            This returns instances of <literal>Cat</literal> named 'Fritz'.
        </para>

		<para>
			The following query:
        </para>
        <programlisting><![CDATA[select foo
from Foo foo, Bar bar
where foo.startDate = bar.date]]></programlisting>

        <para>
            returns all instances of <literal>Foo</literal> with an
            instance of <literal>bar</literal> with a
            <literal>date</literal> property equal to the
            <literal>startDate</literal> property of the
            <literal>Foo</literal>. Compound path expressions make the
            <literal>where</literal> clause extremely powerful. Consider the following:
        </para>

        <programlisting><![CDATA[from Cat cat where cat.mate.name is not null]]></programlisting>

        <para>
            This query translates to an SQL query with a table (inner) join. For example:
            
        </para>

        <programlisting><![CDATA[from Foo foo
where foo.bar.baz.customer.address.city is not null]]></programlisting>

        <para>
            would result in a query that would require four table joins in SQL.
        </para>

        <para>
            The <literal>=</literal> operator can be used to compare not only properties, but also
            instances:
        </para>

        <programlisting><![CDATA[from Cat cat, Cat rival where cat.mate = rival.mate]]></programlisting>

        <programlisting><![CDATA[select cat, mate
from Cat cat, Cat mate
where cat.mate = mate]]></programlisting>

        <para>
            The special property (lowercase) <literal>id</literal> can be used to reference the
            unique identifier of an object. See <xref linkend="queryhql-identifier-property"/>
            for more information.
        </para>

        <programlisting><![CDATA[from Cat as cat where cat.id = 123

from Cat as cat where cat.mate.id = 69]]></programlisting>

        <para>
            The second query is efficient and does not require a table join.
        </para>

        <para>
            Properties of composite identifiers can also be used. Consider the following example where <literal>Person</literal>
            has composite identifiers consisting of <literal>country</literal> and
            <literal>medicareNumber</literal>:
        </para>

        <programlisting><![CDATA[from bank.Person person
where person.id.country = 'AU'
    and person.id.medicareNumber = 123456]]></programlisting>

        <programlisting><![CDATA[from bank.Account account
where account.owner.id.country = 'AU'
    and account.owner.id.medicareNumber = 123456]]></programlisting>

        <para>
            Once again, the second query does not require a table join.
        </para>
	
	<para>
	See <xref linkend="queryhql-identifier-property"/>
            for more information regarding referencing identifier properties)
	</para>
        
	<para>
            The special property <literal>class</literal> accesses the discriminator value
            of an instance in the case of polymorphic persistence. A Java class name embedded in the
            where clause will be translated to its discriminator value.
        </para>

        <programlisting><![CDATA[from Cat cat where cat.class = DomesticCat]]></programlisting>

        <para>
            You can also use components or composite user types, or properties of said
            component types. See <xref linkend="queryhql-components"/> for more information.
        </para>

        <para>
            An "any" type has the special properties <literal>id</literal> and <literal>class</literal> that allows you
            to express a join in the following way (where <literal>AuditLog.item</literal>
            is a property mapped with <literal>&lt;any&gt;</literal>):
        </para>

        <programlisting><![CDATA[from AuditLog log, Payment payment
where log.item.class = 'Payment' and log.item.id = payment.id]]></programlisting>

        <para>
            The <literal>log.item.class</literal> and <literal>payment.class</literal>
            would refer to the values of completely different database columns in the above query.
        </para>

    </section>

    <section xml:id="queryhql-expressions">
        <title>Expressions</title>

        <para>
            Expressions used in the <literal>where</literal> clause include the following:
            
        </para>

        <itemizedlist spacing="compact">
            <listitem>
                <para>
                    mathematical operators: <literal>+, -, *, /</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    binary comparison operators: <literal>=, &gt;=, &lt;=, &lt;&gt;, !=, like</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    logical operations <literal>and, or, not</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    Parentheses <literal>( )</literal> that indicates grouping
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>in</literal>,
                    <literal>not in</literal>,
                    <literal>between</literal>,
                    <literal>is null</literal>,
                    <literal>is not null</literal>,
                    <literal>is empty</literal>,
                    <literal>is not empty</literal>,
                    <literal>member of</literal> and
                    <literal>not member of</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                	"Simple" case, <literal>case ... when ... then ... else ... end</literal>, and
                    "searched" case, <literal>case when ... then ... else ... end</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    string concatenation <literal>...||...</literal> or <literal>concat(...,...)</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>current_date()</literal>, <literal>current_time()</literal>, and
                    <literal>current_timestamp()</literal>
                </para>
            </listitem>
            <listitem>
                <para>
					<literal>second(...)</literal>, <literal>minute(...)</literal>,
					<literal>hour(...)</literal>, <literal>day(...)</literal>,
					<literal>month(...)</literal>, and <literal>year(...)</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    Any function or operator defined by EJB-QL 3.0: <literal>substring(), trim(),
                    lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>coalesce()</literal> and <literal>nullif()</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>str()</literal> for converting numeric or temporal values to a
                    readable string
                </para>
            </listitem>
            <listitem>
                <para>
                    <literal>cast(... as ...)</literal>, where the second argument is the name of
                    a Hibernate type, and <literal>extract(... from ...)</literal> if ANSI
                    <literal>cast()</literal> and <literal>extract()</literal> is supported by
                    the underlying database
                </para>
            </listitem>
            <listitem>
                <para>
                    the HQL <literal>index()</literal> function, that applies to aliases of
                    a joined indexed collection
                </para>
            </listitem>
            <listitem>
                <para>
                    HQL functions that take collection-valued path expressions: <literal>size(),
                    minelement(), maxelement(), minindex(), maxindex()</literal>, along with the
                    special <literal>elements()</literal> and <literal>indices</literal> functions
                    that can be quantified using <literal>some, all, exists, any, in</literal>.
                </para>
            </listitem>
            <listitem>
                <para>
                    Any database-supported SQL scalar function like <literal>sign()</literal>,
                    <literal>trunc()</literal>, <literal>rtrim()</literal>, and <literal>sin()</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    JDBC-style positional parameters <literal>?</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    named parameters <literal>:name</literal>, <literal>:start_date</literal>, and <literal>:x1</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    SQL literals <literal>'foo'</literal>, <literal>69</literal>, <literal>6.66E+2</literal>,
                    <literal>'1970-01-01 10:00:01.0'</literal>
                </para>
            </listitem>
            <listitem>
                <para>
                    Java <literal>public static final</literal> constants <literal>eg.Color.TABBY</literal>
                </para>
            </listitem>
        </itemizedlist>

        <para>
            <literal>in</literal> and <literal>between</literal> can be used as follows:
        </para>

        <programlisting><![CDATA[from DomesticCat cat where cat.name between 'A' and 'B']]></programlisting>

        <programlisting><![CDATA[from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )]]></programlisting>

        <para>
            The negated forms can be written as follows:
        </para>

        <programlisting><![CDATA[from DomesticCat cat where cat.name not between 'A' and 'B']]></programlisting>

        <programlisting><![CDATA[from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )]]></programlisting>

        <para>
            Similarly, <literal>is null</literal> and <literal>is not null</literal> can be used to test
            for null values.
        </para>

        <para>
            Booleans can be easily used in expressions by declaring HQL query substitutions in Hibernate
            configuration:
        </para>

        <programlisting><![CDATA[<property name="hibernate.query.substitutions">true 1, false 0</property>]]></programlisting>

        <para>
            This will replace the keywords <literal>true</literal> and <literal>false</literal> with the
            literals <literal>1</literal> and <literal>0</literal> in the translated SQL from this HQL:
        </para>

        <programlisting><![CDATA[from Cat cat where cat.alive = true]]></programlisting>

        <para>
            You can test the size of a collection with the special property <literal>size</literal> or
            the special <literal>size()</literal> function.
        </para>

        <programlisting><![CDATA[from Cat cat where cat.kittens.size > 0]]></programlisting>

        <programlisting><![CDATA[from Cat cat where size(cat.kittens) > 0]]></programlisting>

        <para>
            For indexed collections, you can refer to the minimum and maximum indices using
            <literal>minindex</literal> and <literal>maxindex</literal> functions. Similarly,
            you can refer to the minimum and maximum elements of a collection of basic type
            using the <literal>minelement</literal> and <literal>maxelement</literal>
            functions. For example:
        </para>

        <programlisting><![CDATA[from Calendar cal where maxelement(cal.holidays) > current_date]]></programlisting>

        <programlisting><![CDATA[from Order order where maxindex(order.items) > 100]]></programlisting>

        <programlisting><![CDATA[from Order order where minelement(order.items) > 10000]]></programlisting>

        <para>
            The SQL functions <literal>any, some, all, exists, in</literal> are supported when passed the element
            or index set of a collection (<literal>elements</literal> and <literal>indices</literal> functions)
            or the result of a subquery (see below):
        </para>

        <programlisting><![CDATA[select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)]]></programlisting>

        <programlisting><![CDATA[select p from NameList list, Person p
where p.name = some elements(list.names)]]></programlisting>

        <programlisting><![CDATA[from Cat cat where exists elements(cat.kittens)]]></programlisting>

        <programlisting><![CDATA[from Player p where 3 > all elements(p.scores)]]></programlisting>

        <programlisting><![CDATA[from Show show where 'fizard' in indices(show.acts)]]></programlisting>

        <para>
            Note that these constructs - <literal>size</literal>, <literal>elements</literal>,
            <literal>indices</literal>, <literal>minindex</literal>, <literal>maxindex</literal>,
            <literal>minelement</literal>, <literal>maxelement</literal> - can only be used in
            the where clause in Hibernate.
        </para>

        <para>
            Elements of indexed collections (arrays, lists, and maps) can be referred to by
            index in a where clause only:
        </para>

        <programlisting><![CDATA[from Order order where order.items[0].id = 1234]]></programlisting>

        <programlisting><![CDATA[select person from Person person, Calendar calendar
where calendar.holidays['national day'] = person.birthDay
    and person.nationality.calendar = calendar]]></programlisting>

        <programlisting><![CDATA[select item from Item item, Order order
where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11]]></programlisting>

        <programlisting><![CDATA[select item from Item item, Order order
where order.items[ maxindex(order.items) ] = item and order.id = 11]]></programlisting>

        <para>
            The expression inside <literal>[]</literal> can even be an arithmetic expression:
        </para>

        <programlisting><![CDATA[select item from Item item, Order order
where order.items[ size(order.items) - 1 ] = item]]></programlisting>

        <para>
            HQL also provides the built-in <literal>index()</literal> function for elements
            of a one-to-many association or collection of values.
        </para>

        <programlisting><![CDATA[select item, index(item) from Order order
    join order.items item
where index(item) < 5]]></programlisting>

        <para>
            Scalar SQL functions supported by the underlying database can be used:
        </para>

        <programlisting><![CDATA[from DomesticCat cat where upper(cat.name) like 'FRI%']]></programlisting>

        <para>
            Consider how much longer and less readable the
            following query would be in SQL:
        </para>

        <programlisting><![CDATA[select cust
from Product prod,
    Store store
    inner join store.customers cust
where prod.name = 'widget'
    and store.location.name in ( 'Melbourne', 'Sydney' )
    and prod = all elements(cust.currentOrder.lineItems)]]></programlisting>

        <para>
            <emphasis>Hint:</emphasis> something like
        </para>

        <programlisting><![CDATA[SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
FROM customers cust,
    stores store,
    locations loc,
    store_customers sc,
    product prod
WHERE prod.name = 'widget'
    AND store.loc_id = loc.id
    AND loc.name IN ( 'Melbourne', 'Sydney' )
    AND sc.store_id = store.id
    AND sc.cust_id = cust.id
    AND prod.id = ALL(
        SELECT item.prod_id
        FROM line_items item, orders o
        WHERE item.order_id = o.id
            AND cust.current_order = o.id
    )]]></programlisting>

    </section>

    <section xml:id="queryhql-ordering">
        <title>The order by clause</title>

        <para>
            The list returned by a query can be ordered by any property of a returned class or components:
        </para>

        <programlisting><![CDATA[from DomesticCat cat
order by cat.name asc, cat.weight desc nulls first, cat.birthdate]]></programlisting>

        <para>
            The optional <literal>asc</literal> or <literal>desc</literal> indicate ascending or descending order
            respectively.
        </para>

        <para>
            The optional <literal>nulls first</literal> or <literal>nulls last</literal> indicate precedence of null
            values while sorting.
        </para>
    </section>

    <section xml:id="queryhql-grouping" revision="1">
        <title>The group by clause</title>

        <para>
            A query that returns aggregate values can be grouped by any property of a returned class or components:
        </para>

        <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color]]></programlisting>

        <programlisting><![CDATA[select foo.id, avg(name), max(name)
from Foo foo join foo.names name
group by foo.id]]></programlisting>

        <para>
            A <literal>having</literal> clause is also allowed.
        </para>

        <programlisting><![CDATA[select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)]]></programlisting>

        <para>
            SQL functions and aggregate functions are allowed in the <literal>having</literal>
            and <literal>order by</literal> clauses if they are supported by the underlying database
            (i.e., not in MySQL).
        </para>

        <programlisting><![CDATA[select cat
from Cat cat
    join cat.kittens kitten
group by cat.id, cat.name, cat.other, cat.properties
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc]]></programlisting>

        <para>
            Neither the <literal>group by</literal> clause nor the
            <literal>order by</literal> clause can contain arithmetic expressions.
            Hibernate also does not currently expand a grouped entity,
            so you cannot write <literal>group by cat</literal> if all properties
            of <literal>cat</literal> are non-aggregated. You have to list all
            non-aggregated properties explicitly.
        </para>

    </section>

    <section xml:id="queryhql-subqueries" revision="3">
        <title>Subqueries</title>

        <para>
            For databases that support subselects, Hibernate supports subqueries within queries. A subquery must
            be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries
            (subqueries that refer to an alias in the outer query) are allowed.
        </para>

        <programlisting><![CDATA[from Cat as fatcat
where fatcat.weight > (
    select avg(cat.weight) from DomesticCat cat
)]]></programlisting>

        <programlisting><![CDATA[from DomesticCat as cat
where cat.name = some (
    select name.nickName from Name as name
)]]></programlisting>

        <programlisting><![CDATA[from Cat as cat
where not exists (
    from Cat as mate where mate.mate = cat
)]]></programlisting>

        <programlisting><![CDATA[from DomesticCat as cat
where cat.name not in (
    select name.nickName from Name as name
)]]></programlisting>

        <programlisting><![CDATA[select cat.id, (select max(kit.weight) from cat.kitten kit)
from Cat as cat]]></programlisting>

        <para>
            Note that HQL subqueries can occur only in the select or where clauses.
        </para>

        <para>
            Note that subqueries can also utilize <literal>row value constructor</literal> syntax.  See
            <xref linkend="queryhql-tuple"/> for more information.
        </para>

    </section>

    <section xml:id="queryhql-examples">
        <title>HQL examples</title>

        <para>
            Hibernate queries can be quite powerful and complex. In fact, the power of the query language
            is one of Hibernate's main strengths. The following example queries are similar to queries
            that have been used on recent projects. Please note that most queries you will write will be much simpler than the following examples.
        </para>

        <para>
            The following query returns the order id, number of items, the given minimum total value and the total value of the order for all
            unpaid orders for a particular customer. The results are ordered by
            total value. In determining the prices, it uses the current catalog. The resulting SQL query,
            against the <literal>ORDER</literal>, <literal>ORDER_LINE</literal>, <literal>PRODUCT</literal>,
            <literal>CATALOG</literal> and <literal>PRICE</literal> tables has four inner joins and an
            (uncorrelated) subselect.
        </para>

        <programlisting><![CDATA[select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog.effectiveDate < sysdate
    and catalog.effectiveDate >= all (
        select cat.effectiveDate
        from Catalog as cat
        where cat.effectiveDate < sysdate
    )
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc]]></programlisting>

        <para>
            What a monster! Actually, in real life, I'm not very keen on subqueries, so my query was
            really more like this:
        </para>

        <programlisting><![CDATA[select order.id, sum(price.amount), count(item)
from Order as order
    join order.lineItems as item
    join item.product as product,
    Catalog as catalog
    join catalog.prices as price
where order.paid = false
    and order.customer = :customer
    and price.product = product
    and catalog = :currentCatalog
group by order
having sum(price.amount) > :minAmount
order by sum(price.amount) desc]]></programlisting>

        <para>
            The next query counts the number of payments in each status, excluding all payments in the
            <literal>AWAITING_APPROVAL</literal> status where the most recent status change was made by the
            current user. It translates to an SQL query with two inner joins and a correlated subselect
            against the <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal> and
            <literal>PAYMENT_STATUS_CHANGE</literal> tables.
        </para>

        <programlisting><![CDATA[select count(payment), status.name
from Payment as payment
    join payment.currentStatus as status
    join payment.statusChanges as statusChange
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or (
        statusChange.timeStamp = (
            select max(change.timeStamp)
            from PaymentStatusChange change
            where change.payment = payment
        )
        and statusChange.user <> :currentUser
    )
group by status.name, status.sortOrder
order by status.sortOrder]]></programlisting>

        <para>
            If the <literal>statusChanges</literal> collection was mapped as a list, instead of a set,
            the query would have been much simpler to write.
        </para>

        <programlisting><![CDATA[select count(payment), status.name
from Payment as payment
    join payment.currentStatus as status
where payment.status.name <> PaymentStatus.AWAITING_APPROVAL
    or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser
group by status.name, status.sortOrder
order by status.sortOrder]]></programlisting>

        <para>
            The next query uses the MS SQL Server <literal>isNull()</literal> function to return all
            the accounts and unpaid payments for the organization to which the current user belongs.
            It translates to an SQL query with three inner joins, an outer join and a subselect against
            the <literal>ACCOUNT</literal>, <literal>PAYMENT</literal>, <literal>PAYMENT_STATUS</literal>,
            <literal>ACCOUNT_TYPE</literal>, <literal>ORGANIZATION</literal> and
            <literal>ORG_USER</literal> tables.
        </para>

        <programlisting><![CDATA[select account, payment
from Account as account
    left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate]]></programlisting>

        <para>
            For some databases, we would need to do away with the (correlated) subselect.
        </para>

        <programlisting><![CDATA[select account, payment
from Account as account
    join account.holder.users as user
    left outer join account.payments as payment
where :currentUser = user
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate]]></programlisting>

   </section>

    <section xml:id="queryhql-bulk" revision="2">
        <title>Bulk update and delete</title>

        <para>
            HQL now supports <literal>update</literal>, <literal>delete</literal> and
            <literal>insert ... select ...</literal> statements.
            See <xref linkend="batch-direct"/> for more information.
        </para>
    </section>

    <section xml:id="queryhql-tipstricks">
        <title>Tips &amp; Tricks</title>

        <para>
            You can count the number of query results without returning them:
        </para>

        <programlisting role="JAVA"><![CDATA[( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()]]></programlisting>

        <para>
            To order a result by the size of a collection, use the following query:
        </para>

        <programlisting><![CDATA[select usr.id, usr.name
from User as usr
    left join usr.messages as msg
group by usr.id, usr.name
order by count(msg)]]></programlisting>

        <para>
            If your database supports subselects, you can place a condition upon selection
            size in the where clause of your query:
        </para>

        <programlisting><![CDATA[from User usr where size(usr.messages) >= 1]]></programlisting>

        <para>
            If your database does not support subselects, use the following query:
        </para>

        <programlisting><![CDATA[select usr.id, usr.name
from User usr
    join usr.messages msg
group by usr.id, usr.name
having count(msg) >= 1]]></programlisting>


        <para>
            As this solution cannot return a <literal>User</literal> with zero messages
            because of the inner join, the following form is also useful:
        </para>

        <programlisting><![CDATA[select usr.id, usr.name
from User as usr
    left join usr.messages as msg
group by usr.id, usr.name
having count(msg) = 0]]></programlisting>

        <para>
            Properties of a JavaBean can be bound to named query parameters:
        </para>

        <programlisting role="JAVA"><![CDATA[Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();]]></programlisting>

        <para>
            Collections are pageable by using the <literal>Query</literal> interface with a filter:
        </para>

        <programlisting role="JAVA"><![CDATA[Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();]]></programlisting>

        <para>
            Collection elements can be ordered or grouped using a query filter:
        </para>

        <programlisting role="JAVA"><![CDATA[Collection orderedCollection = s.filter( collection, "order by this.amount" );
Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );]]></programlisting>

        <para>
            You can find the size of a collection without initializing it:
        </para>

        <programlisting role="JAVA"><![CDATA[( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue();]]></programlisting>

    </section>

    <section xml:id="queryhql-components">
        <title>Components</title>

        <para>
            Components can be used similarly to the simple value types that are used in HQL
            queries.  They can appear in the <literal>select</literal> clause as follows:
        </para>

        <programlisting><![CDATA[select p.name from Person p]]></programlisting>
        <programlisting><![CDATA[select p.name.first from Person p]]></programlisting>

        <para>
            where the Person's name property is a component.  Components can also be used
            in the <literal>where</literal> clause:
        </para>

        <programlisting><![CDATA[from Person p where p.name = :name]]></programlisting>
        <programlisting><![CDATA[from Person p where p.name.first = :firstName]]></programlisting>

        <para>
            Components can also be used in the <literal>order by</literal> clause:
        </para>

        <programlisting><![CDATA[from Person p order by p.name]]></programlisting>
        <programlisting><![CDATA[from Person p order by p.name.first]]></programlisting>

        <para>
            Another common use of components is in <xref linkend="queryhql-tuple">row value constructors</xref>.
        </para>
    </section>

    <section xml:id="queryhql-tuple">
        <title>Row value constructor syntax</title>

        <para>
            HQL supports the use of ANSI SQL <literal>row value constructor</literal> syntax, sometimes
            referred to AS <literal>tuple</literal> syntax, even though the underlying database may not support
            that notion.  Here, we are generally referring to multi-valued comparisons, typically associated
            with components.  Consider an entity Person which defines a name component:
        </para>

        <programlisting><![CDATA[from Person p where p.name.first='John' and p.name.last='Jingleheimer-Schmidt']]></programlisting>

        <para>
            That is valid syntax although it is a little verbose.  You can make this more concise by using
            <literal>row value constructor</literal> syntax:
        </para>

        <programlisting><![CDATA[from Person p where p.name=('John', 'Jingleheimer-Schmidt')]]></programlisting>

        <para>
            It can also be useful to specify this in the <literal>select</literal> clause:
        </para>

        <programlisting><![CDATA[select p.name from Person p]]></programlisting>

        <para>
            Using <literal>row value constructor</literal> syntax can also be beneficial
            when using subqueries that need to compare against multiple values:
        </para>

        <programlisting><![CDATA[from Cat as cat
where not ( cat.name, cat.color ) in (
    select cat.name, cat.color from DomesticCat cat
)]]></programlisting>

        <para>
            One thing to consider when deciding if you want to use this syntax, is that the query will
            be dependent upon the ordering of the component sub-properties in the metadata.
        </para>

    </section>

</chapter>

