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
- 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.
- 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
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.
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(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 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.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 ] = 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
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().
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();
- 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.