Hql: Hibernate query language (3)

15.8. Expression
The expressions that can be used in the WHERE clause include the following types of expressions that you can use in SQL:
Mathematical operators + ,-,*,/
Binary comparison operator =, >=, <=, <> ,! =, Like
Logical operators And, or, not
In, not in, between, is null, is not null, is empty, is not empty, member of and not member
"Simple" Case, Case... When... then... else... end, And "Search" Case, Case when... then... else... end
String Connector ... |... Or Concat (...,...)
Current_date (), current_time (), current_timestamp ()
Second (...), minute (...), hour (...), day (...), month (...), year (...),
Any function or operation defined by EJB-QL 3.0: substring (), trim (), lower (), upper (), length (), locate (), ABS (), SQRT (), bit_length ()
Coalesce () and nullif ()
Cast (... as ...), the second parameter is the name of a hibernate type and extract (... from ...), as long as ANSI cast () and extract () are supported by the underlying database
SQL scalar functions supported by any database, such as sign (), trunc (), rtrim (), sin ()
JDBC parameter input?
Name parameter: name,: start_date,: x1
SQL direct constant 'foo', 69, '2017-01-01 10:00:01. 0'
Java public static final type constant eg. color. Tabby
You can use the keyword in and between as follows:

Code content From domesticcat cat where Cat. Name between 'a 'and' B' From domesticcat cat where Cat. Name in ('foo', 'bar', 'baz ')

The negative format can also be written as follows:

Code content From domesticcat cat where Cat. Name Not between 'a 'and' B' From domesticcat cat where Cat. Name not in ('foo', 'bar', 'baz ')

Similarly, clauses is null and is not null can be used to test null values ).
After the hql "query substitutions" is declared in the hibernate configuration file, the Boolean expression (booleans) can be easily used in other expressions:

Code content <Property name = "hibernate. query. substitutions"> true 1, false 0 </property>

When the system converts the hql statement to an SQL statement, this setting indicates that the keywords true and false will be replaced by characters 1 and 0:

Code content From Cat cat where Cat. Alive = true

You can use the special attribute size or the special function size () to test the size of a set.

Code content From Cat cat where Cat. Kittens. size> 0 From Cat cat where size (Cat. Kittens)> 0

For an indexed (ordered) set, you can use the minindex and maxindex functions to reference the minimum and maximum index ordinal numbers. Similarly, you can use the minelement and maxelement functions to reference the smallest and largest elements in a set of basic data types.

Code content 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

When passing the index set of a set or element set (elements and indices functions) or passing the results of a subquery, you can use the SQL function any, some, all, exists, in

Code content 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 where 'fizard 'in indices (show. Acts)

Note that in hibernate3, these Structure Variables- Size, elements, indices, minindex, maxindex, minelement, maxelement-It can only be used in the WHERE clause.
Elements (arrays, lists, maps) of an indexed (ordered) set can be referenced in other indexes (only in the WHERE clause ):

Code content From Order order where order. items [0]. ID = 1234 Select person from person, calendar Where calendar. Holidays ['National Day'] = person. Birthday And person. Nationality. Calendar = calendar Select item from item, order Where order. items [order. delivereditemindices [0] = item and order. ID = 11 Select item from item, order Where order. items [maxindex (order. Items)] = item and order. ID = 11 The expression in [] can even be an arithmetic expression. Select item from item, order Where order. items [size (order. Items)-1] = item

Hql also provides the built-in index () function for elements in a one-to-many association or a set of values,

Code content Select item, index (item) from order Join order. Items item Where index (item) <5

If the underlying database supports scalar SQL functions, they can also be used
From domesticcat cat where upper (Cat. Name) Like 'fri %'
If you cannot trust all of these questions, consider the following query. If SQL is used, the length of the statement increases and the readability decreases:

Code content Select cust From product prod, Store Inner join store. Customers cust Where prod. Name = 'widget' And store. Location. Name in ('melbourne', 'sydonnel ') And prod = all elements (Cust. currentorder. lineitems) Tip: the statement is as follows: Select Cust. Name, Cust. Address, Cust. Phone, Cust. ID, Cust. current_order From MERs 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', 'sydonnel ') 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 )