Hibernate
is equipped with an extremely powerful
query language that looks very much like
SQL. Queries are case-insensitive, except
for names of Java classes and properties.
Clause
The from clause
The simplest possible Hibernate query
is of the form:
From org.applabs.base.User
From User
which simply returns all instances of
the class org.applabs.base.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 user
This query assigns the alias user to User
instances, so we could use that alias
later in the query. The as keyword is
optional; we could also write:
from User user
Multiple classes may appear, resulting
in a cartesian product or "cross"
join.
from User, Group
from User as user, Group as group
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
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
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.
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)
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.
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...)
The distinct and all keywords may be used
and have the same semantics as in SQL.
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
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.
|
