Hibernate.orgCommunity Documentation
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.
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.hibernate.eg.FOO
is not org.hibernate.eg.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.
가장 간단한 가능한 Hibernate 질의는 다음 형식이다:
from eg.Cat
This returns all instances of the class eg.Cat
. You do not usually need to qualify the class name, since auto-import
is the default. For example:
from Cat
In order to refer to the Cat
in other parts of the query, you will need to assign an alias. For example:
from Cat as cat
This query assigns the alias cat
to Cat
instances, so you can use that alias later in the query. The as
keyword is optional. You could also write:
from Cat cat
Multiple classes can appear, resulting in a cartesian product or "cross" join.
from Formula, Parameter
from Formula as form, Parameter as param
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
).
You can also assign aliases to associated entities or to elements of a collection of values using a join
. For example:
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
(대개 유용하지 않음)
inner join
, left outer join
, 그리고 right outer join
구조체들이 약칭될 수 있다.
from Cat as cat join cat.mate as mate left join cat.kittens as kitten
당신은 HQL with
키워드를 사용하여 특별한 조인 조건들을 제공할 수 있다.
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. See 19.1절. “페칭 방도들” for more information.
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 the where
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). Fetch
should 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%'
HQL은 두 가지 형식의 연관 조인을 지원한다: 암묵적
그리고 명시적
.
The queries shown in the previous section all use the explicit
form, that is, where the join keyword is explicitly used in the from clause. This is the recommended form.
함축적인
형식은 join 키워드를 사용하지 않는다. 대신에, 연관들은 dot(.) 표기를 사용하여 "dot-참조된다(dereferenced)". 함축적인
조인들은 임의의 HQL 절들내에 나타날 수 있다. 함축적인
join은 결과되는 SQL 문장에서 inner join으로 귀결된다.
from Cat as cat where cat.mate.name like '%s%'
There are 2 ways to refer to an entity's identifier property:
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.
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.
Please note that, starting in version 3.2.2, this has changed significantly. In previous versions, id
always referred to the identifier property regardless of its actual name. A ramification of that decision was that non-identifier properties named id
could never be referenced in Hibernate queries.
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 mate
s of other Cat
s. You can express this query more compactly as:
select cat.mate from Cat cat
Queries can return properties of any value type including properties of component type:
select cat.name from DomesticCat cat where cat.name like 'fri%'
select cust.name.firstName from Customer as cust
Queries can 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
Or - assuming that the class Family
has an appropriate constructor - as an actual typesafe Java object:
select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr
You can assign aliases to selected expressions using as
:
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
이 질의는 select된 값들에 대한 alias로부터 한 개의 Map
을 반환한다.
HQL queries can 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:
avg(...), sum(...), min(...), max(...)
count(*)
count(...), count(distinct ...), count(all...)
You can 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 can 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
다음과 같은 질의:
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
인터페이스 Named
는 여러 가지 영속 클래스들에 의해 구현될 수도 있다:
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 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'
만일 한 개의 alias가 존재할 경우, 하나의 수식어가 붙은 프로퍼티 이름을 사용하라:
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. See 14.5절. “Referring to identifier property” for more information.
from Cat as cat where cat.id = 123 from Cat as cat where cat.mate.id = 69
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 = 'AU' and person.id.medicareNumber = 123456
from bank.Account account where account.owner.id.country = 'AU' and account.owner.id.medicareNumber = 123456
Once again, the second query does not require a table join.
See 14.5절. “Referring to identifier property” for more information regarding referencing identifier properties)
The special property class
accesses the discriminator value of an instance in the case of polymorphic persistence. A Java class name embedded in the where clause will be translated to its discriminator value.
from Cat cat where cat.class = DomesticCat
You can also use components or composite user types, or properties of said component types. See 14.17절. “컴포넌트들” for more information.
An "any" type has the special properties id
and class
that allows you to express a join in the following way (where AuditLog.item
is a property mapped with <any>
):
from AuditLog log, Payment payment where log.item.class = 'Payment' and log.item.id = payment.id
The log.item.class
and payment.class
would refer to the values of completely different database columns in the above query.
Expressions used in the where
clause include the following:
mathematical operators: +, -, *, /
binary comparison operators: =, >=, <=, <>, !=, like
논리 연산들 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
"간단한" 경우, case ... when ... then ... else ... end
, 그리고 "검색인" 경우, case when ... then ... else ... end
문자열 연결 ...||...
or concat(...,...)
current_date()
, current_time()
, and current_timestamp()
second(...)
, minute(...)
, hour(...)
, day(...)
, month(...)
, and year(...)
EJB-QL 3.0에 의해 정의된 임의의 함수 또는 오퍼레이터: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()
coalesce()
그리고 nullif()
numeric 값들이나 temporal 값들을 가독성 있는 문자열로 변환시키는 str()
cast(... as ...)
, 여기서 두번 째 아규먼트는 Hibernate 타입의 이름이고, ANSI cast()
와 extract()
가 기반 데이터베이스에 의해 지원될 경우에는 extract(... from ...)
.
조인된 인덱싱된 콜렉션의 alias들에 적용되는, HQL index()
함수
HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex()
, along with the special elements()
and indices
functions that can be quantified using some, all, exists, any, in
.
Any database-supported SQL scalar function like sign()
, trunc()
, rtrim()
, and sin()
JDBC-스타일 위치 파라미터들 ?
named parameters :name
, :start_date
, and :x1
SQL 리터럴들 'foo'
, 69
, 6.66E+2
, '1970-01-01 10:00:01.0'
Java public static final
상수들. eg.Color.TABBY
in
and between
can be used as follows:
from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
The negated forms can be written as follows:
from DomesticCat cat where cat.name not between 'A' and 'B'
from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )
Similarly, is null
and is not null
can be used to test for null values.
Booleans can be easily used in expressions by declaring HQL query substitutions in Hibernate configuration:
<property name="hibernate.query.substitutions">true 1, false 0</property>
이것은 키워드 true
와 false
키워드들을 이 HQL로부터 번역된 SQL에서 리터럴 1
과 0
으로 대체될 것이다:
from Cat cat where cat.alive = true
You can test the size of a collection with the special property size
or the special size()
function.
from Cat cat where cat.kittens.size > 0
from Cat cat where size(cat.kittens) > 0
For indexed collections, you can refer to the minimum and maximum indices using minindex
and maxindex
functions. Similarly, you can refer to the minimum and maximum elements of a collection of basic type using the minelement
and maxelement
functions. For example:
from Calendar cal where maxelement(cal.holidays) > current_date
from Order order where maxindex(order.items) > 100
from Order order where minelement(order.items) > 10000
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 subquery (see below):
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
- can only be used in the where clause in Hibernate3.
Elements of indexed collections (arrays, lists, and maps) can 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 []
can 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
Scalar SQL functions supported by the underlying database can be used:
from DomesticCat cat where upper(cat.name) like 'FRI%'
Consider how much longer and less readable the following query would be in SQL:
select cust from Product prod, Store store inner join store.customers cust where prod.name = 'widget' and store.location.name in ( 'Melbourne', 'Sydney' ) and prod = all elements(cust.currentOrder.lineItems)
힌트 : 다음과 같은 어떤 것
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 )
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
asc
옵션 또는 desc
옵션은 각각 오름차순 또는 내림차순 정렬을 나타낸다.
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
또한 having
절이 허용된다.
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
Neither the group by
clause nor the order by
clause can contain arithmetic expressions. Hibernate also does not currently expand a grouped entity, so you cannot write group by cat
if all properties of cat
are non-aggregated. You have to list all non-aggregated properties explicitly.
subselect들을 지원하는 데이터베이스들의 경우, Hibernate는 질의들 내에 서브질의들을 지원한다. 서브질의는 괄호로 묶여져야 한다(자주 SQL 집계함수 호출에 의해). 심지어 서로 상관된 서브질의들(외부 질의 내에서 alias를 참조하는 서브질의들)이 허용된다.
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.
Note that subqueries can also utilize row value constructor
syntax. See 14.18절. “Row value constructor 구문” for more information.
Hibernate queries can be quite powerful and complex. In fact, the power of the query language is one of Hibernate's main strengths. The following example queries are similar to queries that have been used on recent projects. Please note that most queries you will write will be much simpler than the following examples.
The following query returns the order id, number of items, the given minimum total value and the total value of the order for all unpaid orders for a particular customer. The results are ordered by total value. In determining the prices, it uses the current catalog. The resulting SQL query, against the ORDER
, ORDER_LINE
, PRODUCT
, CATALOG
and PRICE
tables has four inner joins and an (uncorrelated) subselect.
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
다음 질의는 현재 사용자에 의해 가장 최근의 상태 변경이 행해졌던 AWAITING_APPROVAL
상태에 있는 모든 지불들을 제외한, 각각의 상태에 있는 지불들의 개수를 카운트 한다. 그것은 PAYMENT
, PAYMENT_STATUS
, PAYMENT_STATUS_CHANGE
테이블들에 대한 두 개의 inner 조인들과 하나의 상관관계 지워진 subselect를 가진 SQL 질의로 변환된다.
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
If the statusChanges
collection was mapped as a list, instead of a set, the query would have been much simpler to write.
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
다음 질의는 현재의 사용자가 속해 있는 조직의 모든 계정들과 지불되지 않은 지불들을 반환하는데 MS SQL Server isNull()
함수를 사용한다. 그것은 ACCOUNT
, PAYMENT
, PAYMENT_STATUS
, ACCOUNT_TYPE
, ORGANIZATION
, ORG_USER
테이블들에 대한 세 개의 inner 조인들, 하나의 outer 조인, 그리고 하나의 subselect를 가진 한 개의 SQL 질의로 번역된다.
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
몇몇 데이터베이스들의 경우, 우리는 (상관관계 지워진) subselect를 없앨 필요가 있을 것이다.
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
HQL now supports update
, delete
and insert ... select ...
statements. See 13.4절. “DML-ì¤íì¼ ì°ì°ë¤” for more information.
You can count the number of query results without returning them:
( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()
콜렉션의 크기에 따라 결과를 순서(ordering)지우려면, 다음 질의를 사용하라:
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)
만일 당신의 데이터베이스가 subselect들을 지원할 경우, 당신은 당신의 질의의 where 절 내에 selection 사이즈에 대한 조건을 위치지울 수 있다:
from User usr where size(usr.messages) >= 1
If your database does not support subselects, use the following query:
select usr.id, usr.name from User usr.name join usr.messages msg group by usr.id, usr.name having count(msg) >= 1
As this solution cannot return a User
with zero messages because of the inner join, the following form is also useful:
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name having count(msg) = 0
하나의 JavaBean의 프로퍼티들은 명명된 질의 파라미터들에 바인드될 수 있다:
Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size"); q.setProperties(fooBean); // fooBean has getName() and getSize() List foos = q.list();
콜렉션들은 필터를 가진 Query
인터페이스를 사용하여 쪼매김하는 것이 가능하다:
Query q = s.createFilter( collection, "" ); // the trivial filter q.setMaxResults(PAGE_SIZE); q.setFirstResult(PAGE_SIZE * pageNumber); List page = q.list();
Collection elements can be ordered or grouped using a query filter:
Collection orderedCollection = s.filter( collection, "order by this.amount" ); Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );
당신은 콜렉션을 초기화 하지 않고서 그것(콜렉션)의 크기를 찾을 수 있다:
( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue();
Components can be used similarly to the simple value types that are used in HQL queries. They can appear in the select
clause as follows:
select p.name from Person p
select p.name.first from Person p
여기서 Person의 name 속성은 컴포넌트이다. 컴포넌트들은 또한 where
절 내에 사용될 수 있다:
from Person p where p.name = :name
from Person p where p.name.first = :firstName
컴포넌트들은 또한 order by
절 내에 사용될 수 있다:
from Person p order by p.name
from Person p order by p.name.first
컴포넌트들에 대한 또 다른 공통적인 사용은 14.18절. “Row value constructor 구문”에 있다.
HQL supports the use of ANSI SQL row value constructor
syntax, sometimes referred to AS tuple
syntax, even though the underlying database may not support that notion. Here, we are generally referring to multi-valued comparisons, typically associated with components. Consider an entity Person which defines a name component:
from Person p where p.name.first='John' and p.name.last='Jingleheimer-Schmidt'
That is valid syntax although it is a little verbose. You can make this more concise by using row value constructor
syntax:
from Person p where p.name=('John', 'Jingleheimer-Schmidt')
select
절 내에 이것을 지정하는 것이 또한 유용할 수 있다:
select p.name from Person p
Using row value constructor
syntax can also be beneficial when using subqueries that need to compare against multiple values:
from Cat as cat where not ( cat.name, cat.color ) in ( select cat.name, cat.color from DomesticCat cat )
One thing to consider when deciding if you want to use this syntax, is that the query will be dependent upon the ordering of the component sub-properties in the metadata.
저작권 © 2004 Red Hat Middleware, LLC.