Hibernate example source code file (query_ejbql.xml)

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

a, and, cat, cat, domesticcat, domesticcat, hibernate, hql, hql, java, jp-ql, order, sql, sql

The Hibernate query_ejbql.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="queryhql">
  <title>JP-QL: The Object Query Language

  <para>The Java Persistence Query Language (JP-QL) has been heavily inspired
  by HQL, the native Hibernate Query Language. Both are therefore very close
  to SQL, but portable and independent of the database schema. People familiar
  with HQL shouldn't have any problem using JP-QL. In fact HQL is a strict
  superset of JP-QL and you use the same query API for both types of queries.
  Portable JPA applications however should stick to JP-QL.</para>

  <note>
    <para>For a type-safe approach to query, we highly recommend you to use
    the Criteria query, see <xref linkend="querycriteria" />.
  </note>

  <sect1 id="queryhql-casesensitivity">
    <title>Case Sensitivity

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

    <para>This manual uses lowercase JP-QL keywords. Some users find queries
    with uppercase keywords more readable, but we find this convention ugly
    when embedded in Java code.</para>
  </sect1>

  <sect1 id="queryhql-from">
    <title>The from clause

    <para>The simplest possible JP-QL query is of the form:

    <programlisting>select c from eg.Cat c

    <para>which simply returns all instances of the class
    <literal>eg.Cat. Unlike HQL, the select clause is not optional
    in JP-QL. We don't usually need to qualify the class name, since the
    entity name defaults to the unqualified class name
    (<literal>@Entity). So we almost always just write:

    <programlisting>select c from Cat c

    <para>As you may have noticed you can assign aliases to classes, the
    <literal>as keywork is optional. An alias allows you to refer to
    <literal>Cat in other parts of the query.

    <programlisting>select cat from Cat as cat

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

    <programlisting>select formula, parameter from Formula as formula, Parameter as parameter

    <para>It is considered good practice to name query aliases using an
    initial lowercase, consistent with Java naming standards for local
    variables (eg. <literal>domesticCat).
  </sect1>

  <sect1 id="queryhql-joins" revision="1">
    <title>Associations and joins

    <para>You may also assign aliases to associated entities, or even to
    elements of a collection of values, using a
    <literal>join.

    <programlisting>select cat, mate, kitten from Cat as cat
    inner join cat.mate as mate
    left outer join cat.kittens as kitten</programlisting>

    <programlisting>select cat from Cat as cat left join cat.mate.kittens as kittens

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

    <itemizedlist spacing="compact">
      <listitem>
        <para>inner join
      </listitem>

      <listitem>
        <para>left outer join
      </listitem>
    </itemizedlist>

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

    <programlisting>select cat, mate, kitten from Cat as cat
    join cat.mate as mate
    left join cat.kittens as kitten</programlisting>

    <para>In addition, 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 fetching options in the associations and
    collection mapping metadata. See the Performance chapter of the Hibernate
    reference guide for more information.</para>

    <programlisting>select cat 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
    clause (or any other clause). Also, the associated objects are not
    returned directly in the query results. Instead, they may be accessed via
    the parent object. The only reason we might need an alias is if we are
    recursively join fetching a further collection:</para>

    <programlisting>select cat from Cat as cat 
    inner join fetch cat.mate
    left join fetch cat.kittens child
    left join fetch child.kittens</programlisting>

    <para>Note that the fetch construct may not be used in
    queries called using <literal>scroll() or
    <literal>iterate(). Nor should fetch be used
    together with <literal>setMaxResults() or
    <literal>setFirstResult(). It is possible to create a cartesian
    product by join fetching more than one collection in a query (as in the
    example above), be careful the result of this product isn't bigger than
    you expect. Join fetching multiple collection roles gives unexpected
    results for bag mappings as it is impossible for Hibernate to
    differentiate legit duplicates of a given bag from artificial duplicates
    created by the multi-table cartesian product.</para>

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

    <programlisting>select doc from Document doc fetch all properties order by doc.name

    <programlisting>select doc from Document doc fetch all properties where lower(doc.name) like '%cats%'
  </sect1>

  <sect1 id="queryhql-select">
    <title>The select clause

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

    <programlisting>select mate 
from Cat as cat 
    inner join cat.mate as mate</programlisting>

    <para>The query will select mates of other
    <literal>Cats. Actually, you may express this query more
    compactly as:</para>

    <programlisting>select cat.mate from Cat cat

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

    <programlisting>select cat.name from DomesticCat cat
where cat.name like 'fri%'</programlisting>

    <programlisting>select cust.name.firstName from Customer as cust

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

    <programlisting>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 List (HQL specific feature)

    <programlisting>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 as an actual type-safe Java object (often called a view
    object),</para>

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

    <para>assuming that the class Family has an appropriate
    constructor.</para>

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

    <programlisting>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 select new
    map</literal> (HQL specific feature):

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

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

  <sect1 id="queryhql-aggregation">
    <title>Aggregate functions

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

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

    <para>The supported aggregate functions are

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

      <listitem>
        <para>count(*)
      </listitem>

      <listitem>
        <para>count(...), count(distinct ...),
        count(all...)</literal>
      </listitem>
    </itemizedlist>

    <para>You may use arithmetic operators, concatenation, and recognized SQL
    functions in the select clause (dpending on configured dialect, HQL
    specific feature):</para>

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

    <programlisting>select firstName||' '||initial||' '||upper(lastName) from Person

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

    <programlisting>select distinct cat.name from Cat cat

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

  <sect1 id="queryhql-polymorphism">
    <title>Polymorphic queries

    <para>A query like:

    <programlisting>select cat from Cat as cat

    <para>returns instances not only of Cat, but also of
    subclasses like <literal>DomesticCat. Hibernate queries may name
    <emphasis>any Java class or interface in the
    <literal>from clause (portable JP-QL queries should only name
    mapped entities). 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>from java.lang.Object o // HQL only

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

    <programlisting>from Named n, Named m where n.name = m.name // HQL only

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

  <sect1 id="queryhql-where">
    <title>The where clause

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

    <programlisting>select cat from Cat cat where cat.name='Fritz'

    <para>returns instances of Cat named 'Fritz'.

    <programlisting>select foo 
from Foo foo, Bar bar
where foo.startDate = bar.date</programlisting>

    <para>will return all instances of Foo for which there
    exists an instance of <literal>bar with a
    <literal>date property equal to the startDate
    property of the <literal>Foo. Compound path expressions make the
    <literal>where clause extremely powerful. Consider:

    <programlisting>select cat from Cat cat where cat.mate.name is not null

    <para>This query translates to an SQL query with a table (inner) join. If
    you were to write something like</para>

    <programlisting>select foo from Foo foo  
where foo.bar.baz.customer.address.city is not null</programlisting>

    <para>you would end up with a query that would require four table joins in
    SQL.</para>

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

    <programlisting>select cat, rival from Cat cat, Cat rival where cat.mate = rival.mate

    <programlisting>select cat, mate 
from Cat cat, Cat mate
where cat.mate = mate</programlisting>

    <para>The special property (lowercase) id may be used
    to reference the unique identifier of an object. (You may also use its
    mapped identifer property name.). Note that this keyword is specific to
    HQL.</para>

    <programlisting>select cat from Cat as cat where cat.id = 123

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

    <para>The second query is efficient. No table join is required!

    <para>Properties of composite identifiers may also be used. Suppose
    <literal>Person has a composite identifier consisting of
    <literal>country and medicareNumber.

    <programlisting>select person from bank.Person person
where person.id.country = 'AU' 
    and person.id.medicareNumber = 123456</programlisting>

    <programlisting>select account from bank.Account account
where account.owner.id.country = 'AU' 
    and account.owner.id.medicareNumber = 123456</programlisting>

    <para>Once again, the second query requires no table join.

    <para>Likewise, the special property class 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. Once again, this is specific to HQL.</para>

    <programlisting>select cat from Cat cat where cat.class = DomesticCat

    <para>You may also specify properties of components or composite user
    types (and of components of components, etc). Never try to use a
    path-expression that ends in a property of component type (as opposed to a
    property of a component). For example, if <literal>store.owner
    is an entity with a component <literal>address

    <programlisting>store.owner.address.city    // okay
store.owner.address         // error!</programlisting>

    <para>An "any" type has the special properties id and
    <literal>class, allowing us to express a join in the following
    way (where <literal>AuditLog.item is a property mapped with
    <literal><any>). Any is specific to
    Hibernate</para>

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

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

  <sect1 id="queryhql-expressions">
    <title>Expressions

    <para>Expressions allowed in the where clause include
    most of the kind of things you could write in SQL:</para>

    <itemizedlist spacing="compact">
      <listitem>
        <para>mathematical operators +, -, *, /
      </listitem>

      <listitem>
        <para>binary comparison operators =, >=, <=, <>,
        !=, like</literal>
      </listitem>

      <listitem>
        <para>logical operations and, or, not
      </listitem>

      <listitem>
        <para>Parentheses ( ), indicating grouping
      </listitem>

      <listitem>
        <para>in, not in,
        <literal>between, is null, is
        not null</literal>, is empty, is not
        empty</literal>, member of and not member
        of</literal>
      </listitem>

      <listitem>
        <para>exists, all,
        <literal>any, some (taking
        subqueries)</para>
      </listitem>

      <listitem>
        <para>"Simple" case, case ... when ... then ... else ...
        end</literal>, and "searched" case, case when ... then ...
        else ... end</literal>
      </listitem>

      <listitem>
        <para>string concatenation ...||... or
        <literal>concat(...,...) (use concat() for portable JP-QL
        queries)</literal>
      </listitem>

      <listitem>
        <para>current_date(),
        <literal>current_time(),
        <literal>current_timestamp()
      </listitem>

      <listitem>
        <para>second(...), minute(...),
        <literal>hour(...), day(...),
        <literal>month(...), year(...), (specific
        to HQL)</para>
      </listitem>

      <listitem>
        <para>Any function or operator: substring(), trim(), lower(),
        upper(), length(), locate(), abs(), sqrt(),
        bit_length()</literal>
      </listitem>

      <listitem>
        <para>coalesce() and
        <literal>nullif()
      </listitem>

      <listitem>
        <para>TYPE ... in ..., where the first argument is
        an identifier variable and the second argument is the subclass to
        restrict polymorphism to (or a list of subclasses surrounded by
        parenthesis)</para>
      </listitem>

      <listitem>
        <para>cast(... as ...), where the second argument
        is the name of a Hibernate type, and <literal>extract(... from
        ...)</literal> if ANSI cast() and
        <literal>extract() is supported by the underlying
        database</para>
      </listitem>

      <listitem>
        <para>Any database-supported SQL scalar function like
        <literal>sign(), trunc(),
        <literal>rtrim(), sin()
      </listitem>

      <listitem>
        <para>JDBC IN parameters ?
      </listitem>

      <listitem>
        <para>named parameters :name,
        <literal>:start_date, :x1
      </listitem>

      <listitem>
        <para>SQL literals 'foo', 69,
        <literal>'1970-01-01 10:00:01.0'
      </listitem>

      <listitem>
        <para>JDBC escape syntax for dates (dependent on your JDBC driver
        support) (eg. <code>where date = {d '2008-12-31'})
      </listitem>

      <listitem>
        <para>Java public static final constants
        <literal>eg.Color.TABBY
      </listitem>
    </itemizedlist>

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

    <programlisting>select cat from DomesticCat cat where cat.name between 'A' and 'B'

    <programlisting>select cat from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

    <para>and the negated forms may be written

    <programlisting>select cat from DomesticCat cat where cat.name not between 'A' and 'B'

    <programlisting>select cat from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

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

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

    <programlisting>hibernate.query.substitutions true 1, false 0

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

    <programlisting>select cat from Cat cat where cat.alive = true

    <para>You may test the size of a collection with the special property
    <literal>size, or the special size() function
    (HQL specific feature).</para>

    <programlisting>select cat from Cat cat where cat.kittens.size > 0

    <programlisting>select cat from Cat cat where size(cat.kittens) > 0

    <para>For indexed collections, you may refer to the minimum and maximum
    indices using <literal>minindex and maxindex
    functions. Similarly, you may refer to the minimum and maximum elements of
    a collection of basic type using the <literal>minelement and
    <literal>maxelement functions. These are HQL specific
    features.</para>

    <programlisting>select cal from Calendar cal where maxelement(cal.holidays) > current date

    <programlisting>select order from Order order where maxindex(order.items) > 100

    <programlisting>select order from Order order where minelement(order.items) > 10000

    <para>The SQL functions any, some, all, exists, in are
    supported when passed the element or index set of a collection
    (<literal>elements and indices functions) or
    the result of a subquery (see below). While subqueries are supported by
    JP-QL, <literal>elements and indices are
    specific HQL features.</para>

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

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

    <programlisting>select cat from Cat cat where exists elements(cat.kittens)

    <programlisting>select cat from Player p where 3 > all elements(p.scores)

    <programlisting>select cat from Show show where 'fizard' in indices(show.acts)

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

    <para>JP-QL lets you access the key or the value of a map by using the
    <literal>KEY() and VALUE() operations (even
    access the Entry object using <literal>ENTRY())

    <programlisting>SELECT i.name, VALUE(p) FROM Item i JOIN i.photos p WHERE KEY(p) LIKE ‘%egret’

    <para>In HQL, elements of indexed collections (arrays, lists, maps) may be
    referred to by index (in a where clause only):</para>

    <programlisting>select order from Order order where order.items[0].id = 1234

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

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

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

    <para>The expression inside [] may even be an
    arithmetic expression.</para>

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

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

    <programlisting>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 may be
    used</para>

    <programlisting>select cat from DomesticCat cat where upper(cat.name) like 'FRI%'

    <para>If you are not yet convinced by all this, think how much longer and
    less readable the following query would be in SQL:</para>

    <programlisting>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>Hint: something like

    <programlisting>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>
  </sect1>

  <sect1 id="queryhql-ordering">
    <title>The order by clause

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

    <programlisting>select cat from DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate</programlisting>

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

  <sect1 id="queryhql-grouping">
    <title>The group by clause

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

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

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

    <para>A having clause is also allowed.

    <programlisting>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 and order by clauses, if
    supported by the underlying database (eg. not in MySQL).</para>

    <programlisting>select cat
from Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc</programlisting>

    <para>Note that neither the group by clause nor the
    <literal>order by clause may contain arithmetic
    expressions.</para>
  </sect1>

  <sect1 id="queryhql-subqueries">
    <title>Subqueries

    <para>For databases that support subselects, JP-QL 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>select fatcat from Cat as fatcat 
where fatcat.weight > ( 
    select avg(cat.weight) from DomesticCat cat 
)</programlisting>

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

    <programlisting>select cat from Cat as cat 
where not exists ( 
    from Cat as mate where mate.mate = cat 
)</programlisting>

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

    <para>For subqueries with more than one expression in the select list, you
    can use a tuple constructor:</para>

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

    <para>Note that on some databases (but not Oracle or HSQLDB), you can use
    tuple constructors in other contexts, for example when querying components
    or composite user types:</para>

    <programlisting>select cat from Person where name = ('Gavin', 'A', 'King')

    <para>Which is equivalent to the more verbose:

    <programlisting>select cat from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')

    <para>There are two good reasons you might not want to do this kind of
    thing: first, it is not completely portable between database platforms;
    second, the query is now dependent upon the ordering of properties in the
    mapping document.</para>
  </sect1>

  <sect1 id="queryhql-examples">
    <title>JP-QL examples

    <para>Hibernate queries can be quite powerful and complex. In fact, the
    power of the query language is one of Hibernate's main selling points (and
    now JP-QL). Here are some example queries very similar to queries that I
    used on a recent project. Note that most queries you will write are much
    simpler than these!</para>

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

    <programlisting>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>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 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, PAYMENT_STATUS
    and <literal>PAYMENT_STATUS_CHANGE tables.

    <programlisting>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 I would have mapped the statusChanges
    collection as a list, instead of a set, the query would have been much
    simpler to write.</para>

    <programlisting>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>However the query would have been HQL specific.

    <para>The next query uses the MS SQL Server isNull()
    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, PAYMENT,
    <literal>PAYMENT_STATUS, ACCOUNT_TYPE,
    <literal>ORGANIZATION and ORG_USER
    tables.</para>

    <programlisting>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>
  </sect1>

  <sect1 id="queryhql-bulk">
    <title>Bulk UPDATE & DELETE Statements

    <para>Hibernate now supports UPDATE and DELETE statements in HQL/JP-QL.
    See <xref linkend="batch-direct" /> for details.
  </sect1>

  <sect1 id="queryhql-tipstricks">
    <title>Tips & Tricks

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

    <programlisting>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>from User usr where size(usr.messages) >= 1

    <para>If your database doesn't support subselects, use the following
    query:</para>

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

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

    <programlisting>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>
  </sect1>
</chapter>