This article translated from Hibernate documents Chapter 10 "Hibernate Query Language".
Hibernate has a very powerful query language, which is intentionally very similar to SQL, which is convenient for developers. But don't be confused by HQL's syntax surface, HQL is completely object-oriented, can be used to process polymorphism, inheritance, association and other relationships.
10.1 Sensitive Sensitive (Case Sensitivity)
The Java's class name and attribute name in HQL are sensitive, and other keywords are not sensitive. So "select" equivalent to "SELECT", is also equivalent to "SELECT" because it is not a Java class name, nor is the property name of the Java class. However, Java class net.sf.hibernate.eg.foo is not equivalent to net.sf.hibernate.eg.foo, and the same foo.barset is not equivalent to foo.barset.
In this manual, keywords in HQL use lowercase, some users may find that HQL's keywords are more readily read, but we also found that when these HQL embedded into the Java code, it appeared ugly.
10.2FROM clause (the from clause)
The easiest from Hibernate may be:
From eg.cat
Just simply returns all instances of the Eg.cat class.
Many times you might need to set alias (alias) because you may need to reference Cat in other parts of the query.
From eg.cat as cat
Keyword AS is optional, we can also write:
From eg.cat cat
Multiple classes can appear, then return a "Cartesi" or cross-connection:
From Formula As Form, Parameter As PARAM
Alias in HQL uses lowercase letters to be a good habit, complying with naming specifications for Java local variables.
10.3 Association and connection (Associations and Joint)
We use an alias associated entity, even with a collection of a collection of values with Join.
From eg.cat as cat
Inner Join Cat.mate As Mate
LEFT OUTER JOIN Cat.kittens As Kitten
From eg.cat as cat left join cat.mate.kittens As kittens
From Formula Form Full Join Form.Parameter Param
Supported connection types learn from ANSI SQL:
· Inner Join
· Left outer Join
Right Outer Join
· Full Join (Not common)
Inner Join, Left Outer Join and Right Outer Join can be skilled.
From eg.cat as cat
Join Cat.mate As Mate
Left Join Cat.kittens As Kitten
Additionally, a "fetch" connection allows you to use a single connection to associate a set of values, allowing them to initialize with the parent object. This is especially useful in the case of using Collection.
From eg.cat as cat
Inner Join Fetch Cat.mate
Left join fetch cat.kittens
Fetch Join usually does not need to set an alias because the object being associated should not be used in the WHERE clause, and cannot be used in any other clause. The associated object cannot be returned directly in the query result, they can access through the parent object.
Please note: In the current implementation, only one collection can be returned in the query. Also note that Fetch may not be in a query called by scroll () and iTerator (). Finally, it is important to note that Full Join Fetch and Right Join Fetch are meaningless.
10.4 SELECT clause (The Select Clause)
The SELECT slave is used to select the objects and properties returned in the result set:
Select cat.mate from eg.cat cat
The above query returns all cats of the spouse.
You can also use the Elements function to return a collection of elements. The following query will return all kittens for any cat (Kitten).
Select Elements (cat.kittens) from eg.cat cat
The inquiry can also return the properties of any value type (including properties of the Component type):
Select cat.name from eg.domesticcat cat
Where cat.name like 'fri%'
Select custom.name.firstname from customer as cup
The query can return multiple objects or return an attribute of an array as an Object [] type.
SELECT Mother, Offspr, Mate.Name
From eg.domesticcat as mother
Inner Join Mother.mate As Mate
Left outer Join Mother.kittens As Offspr
Or as an actual Java object:
SELECT New Family (Mother, Mate, Offspr)
From eg.domesticcat as mother
Join Mother.mate As Mate
Left join mother.kittens As offspr
The above query statement assumes that the Family class has an appropriate constructor.
10.5 Gathering Function (AGGREGATE function)
The query can use the aggregation function of the attribute:
SELECT AVG (Cat.Weight), Sum (Cat.Weight), Max (Cat.Weight), Count (cat) from eg.cat cat
A collection of Select clauses can occur in the aggregation function of the Sentence:
Select Cat, Count (Elements (Cat.kittens)) from eg.cat cat group by cat
Supported aggregates are:
· AVG (...), SUM (...), min (...), max (...)
· COUNT (*)
COUNT (...), Count ...), Count (all ...)
The meaning of the Distinct and all keywords is the same as the usage and SQL:
Select distinct cat.name from eg.cat cat
Select Count (Distinct Cat.Name), Count (cat) from eg.cat cat
10.6 polymorphism (Polymorphism)
A query: from eg.cat as cat, it is not just CAT, and there is a subclass such as Domsticcat. Hibernate can specify any Java classes and interfaces in the FROM clause, and the query returns an instance of all persistent classes inherited from the class and implements the interface. The following query will return all persistent objects: from java.lang.object O
The specified interface can be implemented by multiple different persistent classes:
From eg.named n, eg.named m where n.name = m.name
Note that the last 2 queries will take more than 1 SQL SELECT, which means that the entire result set cannot be arranged in the arrangement of the clause. This also means that you can't use query.scroll () to call these queries.
10.7 WHERE clause (the where clause)
WHERE clause is that you can return an instance more accurately according to your own conditions:
From eg.cat as cat where cat.name = 'fritz'
Compound expressions make WHERE slave features very powerful:
From eg.cat as cat where cat.name = 'fritz'
This query will be translated into a connected SQL query statement.
If you write this query:
From eg.foo foo where foo.bar.baz.customer.address.city is not null
This query is translated into a SQL statement that requires 4 tables.
"=" Operator can not only compare attributes, but also a comparative example:
From eg.cat cat, eg.cat rival where cat.mate = rival.mate
SELECT CAT, MATE FROM Eg.cat Cat, Eg.cat Mate Where Cat.mate = MATE
A special attribute of a ID is used to reference an object's unique identifier, you can also use the object's property name.
From eg.cat as cat where cat.id = 123
From eg.cat as cat where cat.mate.id = 69 This query is to be more efficient than the previous.
You can use the properties of the composite primary key. Suppose Person has a compliant primary key composed of Medicarenumber and Country:
From bank.person person
Where persot.id.country = 'au'
And person.id.medicarenumber = 123456
From bank.account account
Where account.owner.id.country = 'au
And Account.Owner.id.medicarenumber = 123456
Repeat once, the second query efficiency is higher.
Similarly, a class specified by the attribute in the case of polymorphic persistence, accessing the entity discriminator value.
A Java class name embedded in the WHERE clause will be translated into its discriminator value.
From eg.cat cat where cat.class = eg.domsticcat
You can also specify the properties of the component and the user's own defined synthetic type (and components of components, etc.).
Never use a path expression ending with the component's attribute. For example, it is assumed that the Store.WNER is an entity called Address component: Store.Owner.address.city // correct
Store.owner.address // Error!
One type named "Any" has 2 special properties, one is ID, the other is Class, which allows us to connect (Join) with the following method. Auditlog.Item is an attribute with
From eg.auditlog log, eg.payment payment
WHERE log.Item.class = 'eg.payment' and log.Item.id = payment.id
It should be noted that log.item.class and payment.class in the query will refer to a completely different database column.
10.8 Expression (Expressions)
The expression in the WHERE clause allows you to use a lot of things in SQL:
· Mathematical operators: , -, *, /
· Binary comparison operator: =,> =, <=, <>,! =, LIKE
· Logic operator: and, or, not
· String Connector: ||
· SQL functions, such as Upper () and lower ()
· Parentheses: ()
In, Between, IS NULL
· JDBC input parameters:?
· Specified parameters :: name,: start_date,: x1
· IN and Between:
From eg.domesticcat cat where cat.name Between 'A' and 'b'
From eg.domesticcat cat where cat.name in ('foo', 'bar', 'baz')
And negative forms: NEGATED FORMS:
From eg.domesticcat cat where cat.name not between 'A' and 'b'
From eg.domesticcat cat where cat.name not in ('foo', 'bar', 'baz')
· Is Null and IS Not NULL
· You can also use special attributes or size () functions to test the size of the collection:
From eg.cat cat where cat.kittens.size> 0
From eg.cat cat where size (cat.kittens)> 0
· For collections with indexes, you can use special properties MinIndex and maxindex to reference minimum indexes and maximum indexes. Similarly, you can also use MINElement and MaxElement to reference the basic type of collection of minimum and maximum elements.
From Calendar Cal Where Cal.holidays.MaxElement> Current Date
It can also be the form of a function:
From Order Order Where MaxIndex (Order.Items)> 100
From Order Order Where MineElement (Order.Items)> 10000
SQL functions Any, Some, All, Exists, and IN are supported when transmitting indexes and elements to the collection (Elements and Indices function) and the result of the subqueries, SQL functions.
Select Mother from Eg.cat As Mother, Eg.cat As Kit
WHERE KIT IN Elements (Foo.Kittens)
Select P from Eg.Namelist List, Eg.Person P
Where p.name = Some Elements (list.names)
From eg.cat cat where exissrs elements (cat.kittens)
From Eg.Player P Where 3> All Elements (P.Scores)
From eg.show show where 'fizard' in indeices (show.acts)
Note: size, elements, indices, minindex, maxindex, minelement, MaxElement have some restrictions:
The in the V where clause is only used for subqueries of the database.
The in the V SELECT clause is only used for Elements and INDICES functions.
v Collection (arrays, lists, maps) with an indexed element can only be referenced by an index in the WHERE clause:
From Order Order Where Order.Items [0] .id = 1234
SELECT PERSON from Person Person, Calendar Calendar
Where calendar.holidays ['national day'] = person.birdhday
And person.national.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 interior of [] in the expression can be an arithmetic expression:
Select Item from Item Item, Order ORDER
WHERE Order.Items [Size (Order.Items) - 1] = ITEM
HQL provides a built-in index () function: select item, index (item) from Order ORDER
Join ORDER.Items Item
WHERE INDEX (Item) <5
SQL functions supported by some particular databases can be used:
From eg.domesticcat cat where upper (cat.name) LIKE 'FRI%'
If you still don't believe in everything above, think about longer and shorter readable queries:
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)
Tip: Something Like
Select Cust.Name, Cust.address, Cust.Phone, Cust.Id, Cust.current_order
From customers Cust,
Storest,
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 product.id = all
Select item.prod_id
From line_items item, ORDERS O
Where item.order_id = o.ID
And custom.current_order = o.id)
10.9 Order By clause (the order by clause)
The list returned by the query can be sorted using any attribute of the component or class:
From eg.domesticcat cat
ORDER by Cat.Name ASC, Cat.Weight Desc, Cat.Birthdate
Keywords ASCs and DESC are optional, and the decision is determined in ascending order or descending results.
10.10 Group BY clause (the group by clause)
The aggregation returned by the query can group according to any attribute of the specified class or component:
Select Cat.color, Sum (Cat.Weight), Count (cat)
From eg.cat cat
GROUP BY CAT.COLOR
Select Foo.ID, AVG (Elements (Foo.Names)), MAX (INDICES (FOO.NAMES))
From eg.foo foo
GROUP BY FOO.ID
Note: You can use Elements and Indices structures in the Select clause, even if there is no subquery in the database.
Having clauses are also allowed:
Select Cat.color, Sum (Cat.Weight), Count (cat)
From eg.cat cat
GROUP BY CAT.COLOR
Having cat.color in (eg.color.tabby, eg.color.black) If your database support, you can use the SQL function in the Having and Order By classes (IE: MySQL):
Select Cat from Eg.cat Cat Join Cat.kittens Kitten
Group by Cat Having Avg (Kitten.Weight)> 100
ORDER BY Count (Kitten) ASC, SUM (Kitten.Weight) DESC
Note: The arithmetic expression is not supported by Group by Clause Nor the Order By. 10.11 subqueries (SUBQUERIES)
Hibernate supports subquery, and subquery must be surrounded by parentheses. Subsequet usually called by SQL aggregation function. The alias that references an external query in the subquery is also allowed.
From eg.cat as fatcat
WHERE fatcat.weight>
SELECT AVG (Cat.Weight) from eg.domsticcat cat
)
From eg.domesticcat as cat
Where cat.name = Some
Select name.nickname from eg.name as name
)
From eg.cat as cat
WHERE NOT EXISTS
From eg.cat as worth where code.mate = cat
)
From eg.domesticcat as cat
Where cat.name not in (
Select name.nickname from eg.name as name
)
10.12 examples (Examples)
Hibernate's query features are quite powerful and complicated. In fact, the powerful query language is the main selling point of Hibernate (SELLING POINTS. It turns out that this is a foreign word, today know. ^ _ ^).
Here are some simple query examples, which is picked out from my nearest project. In most cases, you should write more simpler than these.
The table below is used below: Order, Order_Line, Product, Catalog and Price. There are 4 internal connections and a subquery that does not have a uncorrelated.
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
And catalog.effectiveDate And catalog.effectiveDate> = all Select Cat.effectiveDate From catalog as cat Where cat.effectiveDate ) GROUP BY Order Having Sum (Price.Amount)>: Minamount Order by Sum (Price.Amount) DESC It's a monster! (What a monster!). In Real Life, we are not very concerned about the child, and most of our queries are more like this: 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 And catalog =: CurrentCatalog GROUP BY Order Having Sum (Price.Amount)>: Minamount Order by Sum (Price.Amount) DESC The following query is translated into SQL to need 2 internal connections and a associated subquery. It uses the table: payment, payment_status and payment_status_change. Select count (payment), status.name From payment as payment Join payment.currentstatus as status Join payment.statuschange asse 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 I mapping TatusChanges for a list, then our query will be simpler: 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.statuschange)] .user <>: currentuser Group by status.name, Status.Sortorder Order by status.sortorder The following query uses the MS SQL's ISNULL () function to use 3 internal connections and 1 outer join, with large tables: account, payment, payment_status, account_type, organization, and org_user. 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 For other databases, we need to use the associated subquery: 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 10.13 Tips and Tips You can don't have to return to the query results, and you know the quantity of the query results: (Integer) session.Ike ("Select count (*) from ...."). Next ()) .intValue () Sort by the size of the collections returned, you can use the following statement: 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) If your database supports subqueries, then you can specify a query to return a result size in the WHERE clause: From user usr where size (usr.Messages)> = 1 If your database does not support sub-inquiry, then you can use the front query: SELECT usr.id, usr.name From user usr.name Join usr.Messages MSG Group by usr.id, usr.name haVing count (msg)> = 1 The above approach is useful because of the information that has a user who has 0 messages cannot be returned. The following query is 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's properties can be bound as the specified parameters of the query: Query Q = S.CREATEQUERY ("from foo in class foo where foo.name =: name and foo.size =: size"); Q.SetProperties (FOOBEAN); // foobean Has getName () and getsize () List foos = q.list (); The collection can be implemented by using the Query interface with a filter: Query Q = S.CreateFilter (Collection, "); // The Trivial Filter Q.SetMaxResults (Page_Size); Q.SetFirstResult (Page_SIZE * PAGENUMBER); List page = q.list (); Using the query filter, you can achieve the ordering and packet of the collection element: Collection OrderedCollection = S.Filter (Collection, "Order by this.amount"); Collection Counts = S.Filter (Collection, "Select this.type, count (this) group by this.type"); You can get its size without initializing the collection, you can get its size: (Integer) session.Ist ("SELECT COUNT (*) from ...."). Next ()) .intValue ();
