HQL Reference

From Javapedia

[edit] The from clause

The simplest possible Hibernate query is of the form:

From com.test.User

which returns all instances of the class com.test.User.

Most of the time you will need to assign an alias, since you will want to refer to the User in other parts of the query.

from User as user123

or

from User user123

Both of the above queries assign the alias user123 to User instances, so we could use user123 later in the query.

You may join multiple classes together:

from User, Group
from User as user, Group as group

[edit] The select clause

The select clause picks which objects and properties to return in the query result set. Queries may return properties of any value type including properties of component type:

select user.name from User user
where user.name like 'mary%'

select customer.contact.firstName from Customer as cust

[edit] The where clause

The where clause allows you to narrow the list of instances returned.

from User as user where user.name='mary'

returns instances of User named 'mary'.

Compound path expressions make the where clause extremely powerful. Consider:

from org.applabs.base.Customer cust where cust.contact.name is not null

This query translates to an SQL query with a table (inner) join. If you were to write something like The = operator may be used to compare not only properties, but also instances:

from Document doc, User user where doc.user.name = user.name

The special property (lowercase) id may be used to reference the unique identifier of an object. (You may also use its property name.)

from Document as doc where doc.id = 131512

from Document as doc where doc.author.id = 69

[edit] The order by clause

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

from User user order by user.name asc, user.creationDate desc, user.email

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

[edit] The group by clause

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

select sum(document) from Document document group by document.category

A having clause is also allowed.

select sum(document) from Document document group by document.category
having document.category in (Category.HIBERNATE, Category.STRUTS)

[edit] Associations and joins

We may also assign aliases to associated entities, or even to elements of a collection of values, using a join. 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.

[edit] Aggregate functions

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

• avg(...)
• sum(...)
• min(...)
• max(...)
• count(*)
• count(...)
• count(distinct ...)
• count(all...)

[edit] Expressions

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

• mathematical operators +, -, *, /
• binary comparison operators =, >=, <=, <>, !=, like
• logical operations and, or, not
• string concatenation ||
• SQL scalar functions like upper() and lower()
• Parentheses ( ) indicate grouping
• in, between, is null
• JDBC IN parameters ?
• named parameters :name, :start_date, :x1
• SQL literals 'foo', 69, '1970-01-01 10:00:01.0'
• Java public static final constants eg.Color.TABBY

[edit] Sub queries

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.