Hibernate HQL and hibernatehql

Hibernate HQL and hibernatehql
1. Hibernate HQL explanation 1.1. hql Introduction

Hibernate is equipped with a very powerful Query Language, which looks like SQL. But no

To be confused by syntax-structure similarity, HQL is designed to be fully Object-oriented for queries.

It can understand concepts such as inheritance, polymorphism, and association.

Let's look at an example to see If SQL and HQL are the same and different:

SQL: select * fromtbl_user where uuid = '000000'

HQL: select Object (o) from UserModel o where o. uuid = '000000'

1.2. hql features

1) HQL is case sensitive to Java classes and attributes and is not case sensitive to others.

2) Basically, SQL and HQL can be converted, because according to the implementation principle of Hibernate, the final run is SQL, but it is automatically generated.

3) HQL supports internal and external connections

4) HQL supports clustering functions, such as count, avg, sum, min, and max.

5) HQL supports order by and group

6) HQL supports conditional expressions, such as in, like, and.

1.3. select clause

1) directly return an object set, such as: select o from UserModel o

2) returns a set of specific types, such as select o. name from UserModel o.

3) The returned Object [], in the form of select o. uuid, o. name from UserModel o

4) return List, in the form of select new List (o. uuid, o. name) from UserModel o

5) returns any object, for example, select new cn. javass. h3.hello. A (o. uuid, o. name) from UserModel o, which requires A constructor for object A to input these two parameters.

6) returns the Map type, for example, select new Map (o. uuid as Id, o. name as N) from UserModel o, the returned result is the map key using the alias after as, and the corresponding data is used as the value.

Session session = HibernateSessionFactory.GetSession(); // Query query = session. createQuery ("select d from Department d "); // List <Department> list = query. list (); // For (Department department: list ){ // System. out. println (department. getDeptname ()); //} // Query query = session. createQuery ("select d. deptname from Department d "); // List <String> list = query. list (); // For (String string: list ){ // System. out. println (string ); //} // Query query = session. createQuery ("select d. deptid, d. deptname from Department d "); // List <Object []> list = query. list (); // For (Object []Strs: List ){ // System. out. println (Arrays. toString (Strs)); //} // Query query = session. createQuery ("select new List (d. deptid, d. deptname) from Department d "); // List <String> list = query. list (); // For (List <String> list2: list ){ // For (String string: list2 ){ // System. out. println (string ); //} //} Query query = session. createQuery ("select new Map (d. deptid as id, d. deptname as name) from Department d "); List <Map <String, String> list =Query. list (); For(Map <String, String> map: list ){ System.Out. Println (map. toString ()); }

1.4. from clause

1) Direct from object, such as: from UserModel

2) aliases can be allocated, for example, from UserModel as um. The as keyword can be omitted.

3) If there are multiple objects following from, for example, from UserModel and DepModel, it is equivalent to multi-table joint query and Their Cartesian product is returned.

Query query = session. createQuery ("from Department, Employee "); List <Object []> list = query. list (); For (Object [] objects: list ){ Department department = (Department) objects [0]; Employee employee = (Employee) objects [1]; System. out. println (department ); System. out. println ("------------"); System. out. println (employee ); }

1.5. Aggregate functions

1) supported avg, sum, min, max, count

2) the distinct and all keywords can also be used. They have the same semantics as SQL, for example:

Selectcount (distinct o. name) from UserModel o

1.6. where clause

1) if no alias is specified, use the attribute name directly.

2) if an alias is assigned, you must use the alias. Attribute method.

3) The expressions allowed in the where clause include most expressions used in SQL, including:

A) mathematical operators + ,-,*,/

B) binary comparison operator =, >=, <=, <> ,! =, Like

C) logical operators and, or, not

D) parentheses (), indicating groups

E) in, not in, between, is null, is not null, is empty, is not empty, member of and not member

F) string connector... |... or concat (...,...)

G) current_date (), current_time (), and current_timestamp ()

H) second (...), minute (...), hour (...), day (...), month (...) and year (...)

I) any function or operator defined by EJB-QL 3.0: substring (), trim (), lower (), upper (), length (), locate (), abs (), sqrt (), bit_length (), mod ()

J) coalesce () and nullif ()

K) str () converts a number or time value to a readable string

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

M) HQL index () function, acting on the alias of the sorted set of join.

N) The HQL function uses the set as the parameter: size (), minelement (), maxelement (), minindex (), maxindex (), and special elements () and indices functions, it can be limited with quantifiers: some, all, exists, any, in.

O) SQL scalar functions supported by any database, such as sign (), trunc (), rtrim (), sin ()

P) is the JDBC-style parameter passed in?

Q) name parameter: name,: start_date,: x1

R) SQL directly constants 'foo', 69, 6.66E + 2, '2017-01-01 10:00:01. 0'

S) Java public static final type constant eg. Color. TABBY

1.7. group by clause

1) query of returned aggregate values can be grouped by any attribute.

2) You can use the having clause.

3) Aggregate functions in SQL can appear in the having clause.

4) The group by clause and order by clause cannot contain arithmetic expressions.

5) You cannot group by an object. You must explicitly list all clustering attributes.

1.8. order by clause

The list returned by a query can be sorted by any attribute in a returned class or component. The optional asc or desc keyword indicates that the list is sorted in ascending or descending order.

1.9. subquery

For databases that support subqueries, Hibernate supports using subqueries in queries. A subquery must be surrounded by parentheses.

1.10. join)

1) Hibernate can use join between related entities. Similar to SQL, it supports inner join, left outerjoin, right outer join, and full join (full join, not commonly used ).

2) inner join can be abbreviated as join, while left outerjoin and right outer join can remove outer when abbreviated.

1.11.

With the with keyword of HQL, You can provide additional join conditions.

Example: from Cat as cat left join cat. kittens as kitten

Kitten. bodyWeight & gt; 10.0

1.12. fetch

You can request to immediately return the associated set object, such:

From Cat as cat

Innerjoin fetch cat. mate

Left join fetch cat. kittens

 

Hql statement in Hibernate

The hql of earlier versions does not support subqueries. The new version does.

However, Hibernate HQL subqueries do not support from subqueries. solution:

1. Original SQL

Select * from xxx a, (select distinct (id), max (date) time from xxx group by id) B where. id = B. id and. date = B. time

2. hql after modification

Select * from xxx a where a. date = (select max (B. date) from xxx B where a. id = B. id group by B. id)

What is the difference between HQL of Hibernate and SQL?

HQL: Hibernate Qusery Language. If you are familiar with it, you will find that it is very similar to SQL. However, you should not be confused by the illusion that HQL is object-oriented (OO, looking at every object with a life perspective, they are so vivid ). If you have some knowledge about JAVA and SQL statements, HQL is easy for you. You can take full advantage of it on the bus. The following is a step-by-step process from several aspects: 1. Sensitive
As you know, Query is case-insensitive, but in HQL (previously mentioned as OO), the names and attributes of object classes are indeed case-sensitive (in line with java programming syntax ).
For example, sElect cat. name from Cat as cat is the same as select cat. name from Cat as cat.
However:
SElect cat. name from CAT as cat and select cat. name from Cat as cat are indeed different. 2. From Statement
Simplest:
From eg. Cat
It simply returns all eg. Cat instances.
Usually we will use the alias for eg. Cat at this time, because it may be used in the rest of the query (see the preceding section about Case sensitivity.
Sensitive examples), such:
From eg. Cat as cat can be omitted here.
The preceding statement is only for single-table queries. Multiple tables are written as follows:
From eg. Cat, eg. Dog
From eg. Cat as cat, eg. Dog as dog 3. Join Problems
(Inner) join
Left (outer) join
Right (outer) join
Full join
HQL also supports these features in SQL
The following is a small topic. I have never used any of the above features. Now, I want
Let's take a look at the usage of the above features to supplement our own:
Assume there are two tables: department and employee. The following table lists some data:
Employee (Employee ):
ID Name DepNo
001 Jplateau 01
002 Jony 01
003 Camel 02
Department ):
ID Name
01 R & D department
02 in Hibernate, the marketing department is operating on objects, so we are operating on departments and employees.
1). (inner) join
Select employee. ID as id1, employee. Name as name1, department. ID as id2, department. Name
As name2 from Employee as employee join Department as department on employee. DepNo =
Department. ID (note that the condition statement I use on does not use where)
So what is the execution result?
Id1 name1 id2 name2
++
001 Jplateau 01 R & D department
002 Jony 01 R & D department 2). left (outer) join
Select employee. ID as id1, employee. Name as name1, department. ID as id2, department. Name
As name2 from Employee as employee left join Department as department on employee. DepNo =
Dep ...... remaining full text>