Hibernate HQL syntax (Part 1)

Hibernate is equipped with a very powerful Query Language, which looks like SQL. But do not be confused by syntax-structure similarity. hql is consciously designed as a fully Object-Oriented Query That can understand concepts such as inheritance, polymorphism, and association.

1. Case sensitivity

Except for the names of Java classes and attributes, query statements are not case sensitive. SoSelectAndSelectAndSelectIs the same,Org. hibernate. eg. fooNot equivalentOrg. hibernate. eg. fooAndFoo. barsetIt is not equivalentFoo. barset.

The hql keywords in this manual use lower-case letters. many users find that the use of fully capitalized keywords makes the query statement more readable, but we find that it is difficult to use uppercase keywords when embedding the query statement into a Java statement.

2. From clause

The simplest query statement in Hibernate is as follows:

from eg.Cat

Simple return of this clauseEg. CatClass. Generally, we do not need to use a fully qualified class name, becauseAuto-Import(Automatically introduced) is the default situation. Therefore, we only use the following simple Syntax:

from Cat

In most cases, you need to specifyAliasThe reason is that you may need to referenceCat

from Cat as cat

This statement sets the aliasCatSpecify to classCatSo that we can use this alias in subsequent queries. KeywordsAsIt is optional. We can also write it like this:

from Cat cat

Multiple classes can appear in the clause at the same time. The query result is a Cartesian product or a cross-table join.

from Formula, Parameter

from Formula as form, Parameter as param

The lower-case characters at the beginning of an alias in a query statement are considered a good habit in practice. This is consistent with the Naming Standard of Java variables (for example,Domesticcat).

3. Join)

You can also specify an alias for an associated object or even for all the elements in a set.Join.

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 connection types are used for reference in ansi SQL.

• Inner join(Internal connection)

• Left Outer Join(Left Outer Join)

• Right Outer Join(Right Outer Join)

• Full join(Full connection, not commonly used)

StatementInner join,Left outer joinAndRight outer joinCan be abbreviated.

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

In addition, a "Fetch" connection allows only one selection statement to initialize the associated object or a set of values with the initialization of their parent object, this method is particularly useful when a set is used. For associations and sets, it effectively replaces the Outer Join and delay Declaration (lazy declarations) in the ing file ). to obtain more information.

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

A fetch connection usually does not need to be specified as an alias, because the associated object should not be usedWhereClause (or any other clause. At the same time, the associated objects are not directly returned in the query results, but they can be accessed through their parent objects.

Note:FetchThe constructor is usingScroll ()OrIterate ()Function query is not available. Note thatFull join fetchAndRight join fetchIs meaningless.

If you use the property-level latency acquisition (lazy fetching), you can useFetch all propertiesTo force hibernate to immediately obtain the attributes that originally require delayed loading (in the first query ).

from Document fetch all properties order by name

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

4. Select clause

SelectClause:

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

This statement will selectMateS of otherCatS. (Spouse of other cats) In fact, you can use the following query statement to express the same meaning:

select cat.mate from Cat cat

A query statement can return any type of attribute, including the attribute of a component whose return type is:

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

select cust.name.firstName from Customer as cust

The query statement can return multiple objects and/or attributes, which are stored inObject []Queue,

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

Or stored inListObject,

select new list(mother, offspr, mate.name) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr

You may also directly return an actual type-safe Java object,

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

Hypothesis classFamilyThere is a suitable constructor.

You can use keywordsAsAssign an alias to the selected expression:

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

In this waySelect new mapIt is most useful when used together:

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

This query returnsMapIs the name-value ing between the alias and the selected value.

5. Aggregate functions

Hql queries can even return the computing results of Aggregate functions acting on attributes:

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

The following aggregation functions are supported:

• AVG (...), sum (...), min (...), max (...)

• Count (*)

• Count (...), count (distinct...), count (all ...)

You can use mathematical operators, joins, and verified SQL functions in the selection 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

KeywordsDistinctAndAllIt can also be used. They have the same semantics as SQL.

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

6. multi-state Query

A query statement is as follows:

from Cat as cat

Not onlyCatClass instance, also return subclassDomesticCat. Hibernate can beFromSpecifyAnyFor a Java class or interface. query, instances of all persistence classes that inherit the class are returned, or instances of all persistence classes that declare the interface are returned. The following query statement returns all persistent objects:

from java.lang.Object o

InterfaceNamedMay be declared by various persistence classes:

from Named n, Named m where n.name = m.name

Note that the last two queries require more than one SQLSELECT. This indicatesOrderClause does not correctly sort the entire result set.Query. scroll ()Method .)

7. Where clause

WhereClause allows you to narrow the returned instance list. If no alias is specified, you can use the attribute name to directly reference the attribute:

from Cat where name='Fritz'

If an alias is assigned, use the complete attribute name:

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

Returns the object named (attribute name equals) 'fritz '.CatClass.

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

AllFooClass instance: The followingBarAn instance, itsDateAttribute equalsFooOfStartDateAttribute. The compound path expression causesWhereClause is very powerful. Consider the following:

from Cat cat where cat.mate.name is not null

The query is translated into an SQL query that contains table connections (internal connections. If you want to write a query statement like this

from Foo foo where foo.bar.baz.customer.address.city is not null

In SQL, You need to query a four-table join for this purpose.

=Operators can be used not only to compare attribute values, but also to compare instances:

from Cat cat, Cat rival where cat.mate = rival.mate

select cat, mate from Cat cat, Cat mate where cat.mate = mate

Special attribute (lower case)IdIt can be used to represent a unique identifier of an object. (You can also use the property name of this object .)

from Cat as cat where cat.id = 123 from Cat as cat where cat.mate.id = 69

The second query is valid. No table connection is required!

You can also use compound identifiers. For examplePersonClass has a composite identifier, which consistsCountryAttribute andMedicareNumberAttribute composition.

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

No table connection is required for the second query.

Similarly, special attributesClassIt is used to access the discriminator value of an instance for multi-state persistence ). The name of a Java class embedded in the where clause is converted to the value of this class.

from Cat cat where cat.class = DomesticCat

You can also declare that the type of an attribute is a component or a composite user type (and components composed of components, etc ). Never try to use a path expression ending with a component type (path-expression_r) (Instead, you should end with an attribute of the component ). For example, ifStore. ownerContains an entity that contains components.Address

Store. owner. address. city // The correct store. owner. address // error!

An "arbitrary" type has two special attributes.IdAndClassTo allow us to express a connection (AuditLog. itemIs an attribute mapped<Any>).

from AuditLog log, Payment payment where log.item.class = 'Payment' and log.item.id = payment.id

Note: In the preceding query and sentence,Log. item. classAndPayment. classColumns in different databases will be involved.

8. Expression

InWhereThe expressions that can be used in a clause include the types of expressions that you can use in SQL:

• Mathematical Operators+ ,-,*,/

• Binary comparison operator=, >=, <=, <> ,! =, Like

• Logical operatorsAnd, or, not

• In,Not in,Between,Is null,Is not null,Is empty,Is not empty,MemberAndNot member

• "Simple" case,Case... when... then... else... end, And "Search" case,Case when... then... else... end

• String Connector... |...OrConcat (...,...)

• 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 ()AndNullif ()

• Cast (... ...)The second parameter is the name of a Hibernate type, andExtract (... from ...), As long as ANSICast ()AndExtract ()Supported by underlying databases

• SQL scalar functions supported by any database, suchSign (),Trunc (),Rtrim (),Sin ()

• JDBC parameter input?

• Naming Parameters: Name,: Start_date,: X1

• SQL direct constant'Foo',69,'2017-01-01 10:00:01. 0'

• JavaPublic static finalType constantEg. Color. TABBY

KeywordsInAndBetweenYou can use the following method:

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:

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

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

Similarly, the clauseIs nullAndIs not nullIt 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:

<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 it will use characters1And0To replace keywordsTrueAndFalse:

from Cat cat where cat.alive = true

You can use special attributesSizeOr special functions.Size ()Test the size of a set.

from Cat cat where cat.kittens.size > 0

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

You can useMinindexAndMaxindexFunction to reference the smallest and largest index ordinal numbers. Similarly, you can useMinelementAndMaxelementFunction to reference the smallest and largest elements in a set of basic data types.

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 the index set or element set of a set is passed (ElementsAndIndicesFunction) or when passing the results of a subquery, you can use the SQL function.Any, some, all, exists, in

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 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 ):

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

In[]Or even an arithmetic expression.

select item from Item item, Order order where order.items[ size(order.items) - 1 ] = item

HQL also provides built-inIndex ()Function,

select item, index(item) from Order 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:

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:The statement is as follows:

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 )

9. Order by clause

The list returned by a query can be sorted by any property in a returned class or component:

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

OptionalAscOrDescThe keyword indicates sorting in ascending or descending order.

10. Group by clause

A query that returns aggregate values can be grouped by any property in a returned class or component:

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

HavingClause is also allowed here.

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)

If the underlying database supports this function (for example, it cannot be used in MySQL), General SQL functions and Aggregate functions can also appear inHavingAndOrderClause.

select cat from Cat cat join cat.kittens kitten group by cat having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) desc

Note:GroupClause andOrderClause cannot contain arithmetic expressions (arithmetic expression_rs ).