Hibernate-HQL

HQL是Hibernate Query Language的缩写，语法很像SQL，但是HQL是一种面向对象的查询语言。SQL的操作对象是数据列、表等数据库对象，而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.

Hibernate使用了功能强大的查询语言（HQL），在外观上类似SQL是。与SQL，但是，HQL是完全面向对象的概念和理解如继承、多态性和关联。

1. Case Sensitivity  (大小写敏感)

除了java的类和属性的名称外，查询是不区分大小写。所以Select和select是相同的，但org.hibernate.eg.FOO不等于org.hibernate.eg.foo，foo.barset不等于foo.BARSET。

2. The from clause  (From从句)

最简单的Hibernate查询的形式:

from eg.Cat

返回类的所有实例eg.cat。您通常不需要限定名称，因为自动导入是默认的。例如：

from Cat

为了查询该结果的其他部分，您可能需要指定一个别名。例如：

from Cat as cat

这个别名代替了Cat实例，在之后的查询中你可以使用别名了。关键词as是可选的。例如：

from Cat cat

可以同时查询多个类，结果是它们的笛卡尔积(cross)。例如：

from Formula, Parameter
from Formula as form, Parameter as param

使用别名是一个好的习惯。

3. Associations and joins  (关联和连接)

ANSI SQL标准支持的连接：

inner join

left outer join

right outer join

full join (不是经常使用)

inner join，left outer join，right outer join可以简写。例如：

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

你可以加入额外的查询条件。例如：

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

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 outer join and lazy declarations of the mapping file for associations and collections.

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens

A fetch join does not usually need to assign an alias, because the associated objects should not be used in thewhere clause (or any other clause). The associated objects are also not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason you might need an alias is if you are recursively join fetching a further collection:

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens child
    left join fetch child.kittens

The fetch construct cannot be used in queries called using iterate() (though scroll() can be used). Fetchshould not be used together with setMaxResults() or setFirstResult(), as these operations are based on the result rows which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you would expect. Fetch should also not be used together with impromptu with condition. It is possible to create a cartesian product by join fetching more than one collection in a query, so take care in this case. Join fetching multiple collection roles can produce unexpected results for bag mappings, so user discretion is advised when formulating queries in this case. Finally, note that full join fetch and right join fetch are not meaningful.

If you are using property-level lazy fetching (with bytecode instrumentation), it is possible to force Hibernate to fetch the lazy properties in the first query immediately using fetch all properties.

from Document fetch all properties order by name
from Document doc fetch all properties where lower(doc.name) like '%cats%'

4. Forms of join syntax  (连接的语法形式)

HQL支持两种形式的协会加入:隐式和显式。

之前的介绍都是显示的语法形式，比较推荐。HQL也可以用隐士的，不显式写出join关键字，而用"点表示法"来间接关联。例如：

from Cat as cat where cat.mate.name like '%s%'

5. Referring to identifier property  (引用标识符属性)

2种方法引用实体的标识符属性：

1.The special property (lowercase) id may be used to reference the identifier property of an entity provided that the entity does not define a non-identifier property named id.

2.If the entity defines a named identifier property, you can use that property name.

注意：References to composite identifier properties follow the same naming rules. If the entity has a non-identifier property named id, the composite identifier property can only be referenced by its defined named. Otherwise, the special id property can be used to reference the identifier property.

6. The select clause  (Select从句)

select子句查询对象和属性，返回结果集。例如：

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

如果你只想查询Cat的mate属性，你可以写的简洁一些：

select cat.mate from Cat cat

查询可以返回任何类型的属性，包括组件类型的属性：

select cat.name from DomesticCat cat
where cat.name like 'fri%'

select cust.name.firstName from Customer as cust

查询可以将多个对象或者属性的结果集作为数组对象返回：

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

或者返回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

或者返回一个封装对象：

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

你可以为函数选择的的结果取别名：

select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n
from Cat cat

别名与select new map一起用很好：

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

这个查询返回一个别名映射到选定值的Map。

7. Aggregate functions  (聚合函数)

 HQL甚至可以返回聚合函数的值：

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

支持的聚合函数：

avg(...), sum(...), min(...), max(...)

count(*)

count(...), count(distinct ...), count(all...)

你也可以使用数学运算，连接，通用sql在select从句中：

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

distinct 和其他关键字可以被用到，和它们在通用sql里面的语义是一样的：

select distinct cat.name from Cat cat

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

8. 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().

9. The where clause  (Where从句)

where从句用来筛选结果集，如果没有别名你可以用属性名代替：

from Cat where name='Fritz'

有别名就应用具体属性：

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

如下查询：

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

将返回所有满足条件foo.startDate = bar.date的foo对象。

where从句的条件可以串联：

from bank.Account account
where account.owner.id.country = 'AU'
    and account.owner.id.medicareNumber = 123456

10. Expressions  (表达式)

Expressions used in the where clause include the following:

• mathematical operators: +, -, *, /

• binary comparison operators: =, >=, <=, <>, !=, like

• logical operations and, or, not

• Parentheses ( ) that indicates 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(), and current_timestamp()

• second(...), minute(...), hour(...), day(...), month(...), and 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 indicesfunctions that can be quantified using some, all, exists, any, in.

• Any database-supported SQL scalar function like sign(), trunc(), rtrim(), and sin()

• JDBC-style positional parameters ?

• named parameters :name, :start_date, and :x1

• SQL literals 'foo', 69, 6.66E+2, '1970-01-01 10:00:01.0'

• Java public static final constants eg.Color.TABBY

例子：

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

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

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

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

from Cat cat where cat.alive = true

from Cat cat where cat.kittens.size > 0

from Cat cat where size(cat.kittens) > 0

from Calendar cal where maxelement(cal.holidays) > current_date

from Order order where maxindex(order.items) > 100

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)

from Order order where order.items[0].id = 1234

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
    )

11. The order by clause  (order by从句)

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

12. The group by clause  (group by从句)

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

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)

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

13. Subqueries  (子查询)

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

14. HQL examples  (HQL例子)

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

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

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

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

select account, payment
from Account as account
    left outer join account.payments as payment
where :currentUser in elements(account.holder.users)
    and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID)
order by account.type.sortOrder, account.accountNumber, payment.dueDate

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

15. Bulk update and delete  (批量更新和删除)

略

16. Tips & Tricks  (技巧)

You can count the number of query results without returning them:

( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()

To order a result by the size of a collection, use the following query:

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)

17. Components  (组件)

18. Row value constructor syntax  (行值构造语法)