Rabbit Eight Brother Note 14 (2): Hibernate Query Language (HQL)
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, assuming that Store.WNer is an entity with a 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 Need to note: log.Item.class and payment.class in the query will refer to completely different database columns.
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)> 100From 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 built-in index () functions for one-to-portred association and value.
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 Storeinner 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 is supported, you can use the SQL function in the Having and Order By clause (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
