Hibernate Framework : ORM Tool :: Hibernate Query Language – HQL

• Overview of HQL
• The From clause
• Associations and joins
• The Select Clause
• The where clause
• The order by clause
• The group by clause
• Subqueries
• Aggregate functions
• Expressions for Queries
• Polymorphic queries
• Criteria Query
• Native SQL

---

Overview of HQL

Hibernate Query Language – HQL

• 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.
• And it helps to construct queries, to access database in Hibernate Application.
• It is developed on the basis of SQL.
• However , due to its ability to manipulate persistent objects, HQL has become a prominent Query Language in hibernate based persistence solution.

Case Sensitivity

• With the exception of names of Java classes and properties, queries are case-insensitive.
• So SeLeCT is the same as sELEct is the same as SELECT, but org.javaskool.FOO is not org.javaskool.Foo, and foo.barSet is not foo.BARSET.
• 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.

The From clause

The From clause

• The simplest possible Hibernate query is of the form: from eg.Cat which simply returns all instances of the class eg.Cat.
• You do not usually need to qualify the class name, as auto-import is the default.
• So you almost always just write: from Cat
• Most of the time, you will need to assign an alias, as you will want to refer to the Cat in other parts of the query like from Cat as cat
• This query assigns the alias cat to Cat instances, so you could use that alias later in the query. The as keyword is optional, as you could also write as from Cat cat
• Multiple classes can appear, resulting in a cartesian product or “cross” join.
• 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. domesticCat).

Associations and joins

Associations and joins

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

from Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten
from Cat as cat left join cat.mate.kittens as kittens
from Formula form full join form.parameter param

The supported join types are borrowed from ANSI SQL:

• inner join
• left outer join
• right outer join
• full join (not usually useful)

The inner join, left outer join, and right outer join constructs may be abbreviated.

from Cat as cat
join cat.mate as mate
left join cat.kittens as kitten

You may supply extra join conditions using the HQL with keyword.

from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight < 10.0

The Select Clause

The Select Clause

The select clause picks which objects and properties to return in the query result set. Consider the following:

select mate
from Cat as cat
inner join cat.mate as mate

The query will select mates of other Cats.

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

select mother, offspr, mate.name
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr

Or as a List,

select new list(mother, offspr, mate.name)
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr

The where clause

The where clause

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

from Cat where name='Fritz'

If there is an alias, use a qualified property name:

from Cat as cat where cat.name='Fritz'

This returns instances of Cat named ‘Fritz’.

The following query:

select foo
from Foo foo, Bar bar
where foo.startDate = bar.date

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

from Cat cat where cat.mate.name is not null

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

from Foo foo
where foo.bar.baz.customer.address.city is not null

would result in a query that would require four table joins in SQL.

The = operator can be used to compare not only properties, but also instances:

from Cat cat, Cat rival where cat.mate = rival.mate
select cat, mate
from Cat cat, Cat mate
where cat.mate = mate

The special property (lowercase) id can be used to reference the unique identifier of an object.

from Cat as cat where cat.id = 1238
 
from Cat as cat where cat.mate.id = 690

The second query is efficient and does not require a table join.

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

from bank.Person person
where person.id.country = 'US'
    and person.id.medicareNumber = 123456
from bank.Account account
 
where account.owner.id.country = 'US'
    and account.owner.id.medicareNumber = 123456

Once again, the second query does not require a table join.

The order by clause

The order by clause

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

from DomesticCat cat
order by cat.name asc, cat.weight desc, cat.birthdate

The optional asc or desc indicate ascending or descending order respectively.

The group by clause

The group by clause

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

select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
select foo.id, avg(name), max(name)
from Foo foo join foo.names name
group by foo.id

A having clause is also allowed.

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)

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

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

Subqueries

Subqueries

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.

from Cat as fatcat
where fatcat.weight > (
    select avg(cat.weight) from DomesticCat cat
)
 
from DomesticCat as cat
where cat.name = some (select name.nickName from Name as name)
 
from Cat as cat
where not exists (from Cat as mate where mate.mate = cat)
 
from DomesticCat as cat
where cat.name not in (select name.nickName from Name as name)
 
select cat.id, (select max(kit.weight) from cat.kitten kit)
from Cat as cat

Note that HQL subqueries can occur only in the select or where clauses.

Aggregate functions

Aggregate functions

HQL queries may even return the results of aggregate functions on properties:

select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat)
from Cat cat

The supported aggregate functions are as follows:

• avg(…), sum(…), min(…), max(…)
• count(*)
• count(…), count(distinct …), count(all…)

You may use arithmetic operators, concatenation, and recognized SQL functions in the select clause:

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

select firstName||' '||initial||' '||upper(lastName) from Person

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

select distinct cat.name from Cat cat
select count(distinct cat.name), count(cat) from Cat cat

Expressions for Queries

Expressions

Expressions allowed in the where clause include most of the kind of things as you could write in SQL:

• Mathematical operators are +, -, *, /
• Binary comparison operators are =, >=, <=, <>, !=, like
• Logical operations are and, or, not
• Parentheses ( ), indicating grouping
• in, not in, between, is null, is not null, is empty, is not empty, member of and not member of,
• “Simple” case, case … when … then … else … end, and “searched” case, case when … then… else … end,string concatenation …||… or concat(…,…),
• current_date(), current_time(), current_timestamp(), second(…), minute(…), hour(…), day(…), month(…), year(…),
• Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod(), coalesce() and nullif(),
• str() for converting numeric or temporal values to a readable string
• cast(… as …), where the second argument is the name of a Hibernate type, and extract(… from…) if ANSI cast() and extract() is supported by the underlying database
• the HQL index() function, that applies to aliases of a joined indexed collection.
• HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions which may be quantified using some, all, exists, any, in.
• Any database-supported SQL scalar functions are like sign(), trunc(), rtrim(), sin()
• JDBC-style positional parameters are :name, :start_date, :x1
• SQL literals ‘foo’, 69, 6.66E+2, ‘1970-01-01 10:00:01.0’

Java public static final constants eg.Color.TABBY in and between may be used as follows:

from DomesticCat cat where cat.name between 'A' and 'B'

from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

And the negated forms may be written:

from DomesticCat cat where cat.name not between 'A' and 'B'

from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Likewise, is null and is not null may be used to test for null values. The SQL functions any some, all, exists, in are supported when passed the element or index set of a collection (elements and indices functions) or the result of a sub-query (Refer as follows).

select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
select p from NameList list, Person p
where p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)

Note that these constructs – size, elements, indices, minindex, maxindex, minelement, maxelement may only be used in the where clause in Hibernate3. Elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only):

from Order order where order.items[0].id = 1234
select person from Person person, Calendar calendar

where calendar.holidays['national day'] = person.birthDay
and person.nationality.calendar = calendar

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

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

The expression inside [] may even be an arithmetic expression.

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

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

select item, index(item) from Order order
join order.items item
where index(item) < 5

Polymorphic queries

Polymorphic queries

A query like:

from Cat as cat

returns instances not only of Cat, but also of subclasses like DomesticCat. Hibernate queries can name any Java class or interface in the from 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:

from java.lang.Object o
The interface Named might be implemented by various persistent classes:
from Named n, Named m where n.name = m.name

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

Criteria Query

Criteria Query

The interface org.hibernate.Criteria represents a query against a particular persistent class. The Session is a factory for Criteria instances.

Criteria crit = sess.createCriteria(Cat.class);
crit.setMaxResults(50);
List cats = crit.list();

Native SQL

Native SQL

• You can also express queries in the native SQL dialect of your database.
• This is useful if you want to utilize database-specific features such as query hints or the CONNECT keyword in Oracle.
• It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.
• Hibernate3 allows you to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.