Hql query language basics!

1. From

1.1 Single Table query

From eg. CAT as Cat. In this example, cat is just an alias. It is easy to write with other substatements.

1.2 multi-table queries

From eg. Cat, eg. Dog
From eg. CAT as Cat, eg. Dog as dog
2 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 Marketing Department

In hibernate, we manipulate all objects, So we manipulate department and employee classes.
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 =
Department. ID
So what is the execution result?
Id1 name1 Id2 name2
++
001 jplateau 01 R & D department
002 Jony 01 R & D department
003 camel null
{That is to say, at this time, the number of records in the first table should prevail, and null should be filled when there is no corresponding record in the second table}
3). Right (outer) join
Select employee. ID as id1, employee. Name as name1, department. ID as Id2, department. Name
As name2 from employee as employee right join Department as department on employee. depno =
Department. ID
So what is the execution result?
Id1 name1 Id2 name2
++
001 jplateau 01 R & D department
002 Jony 01 R & D department
Null null 02 Marketing Department
{That is to say, the number of records in the second table should be taken as the standard, and null should be filled when no corresponding records exist in the first table}

3. Case Sensitive

4. Select statement
It is to determine which objects or attributes of which objects you want to return from the query. Let's write a few examples:
Select employee form employee as employee
Select employee form employee as employee where employee. name like 'J %'
Select employee. Name form employee as employee where employee. name like 'J %'
Select employee. ID as id1, employee. Name as name1, department. ID as Id2, department. Name
As name2 from employee as employee right join Department as department on employee. depno =
Department. ID

Select elements (employee. Name) from employee as employee
(Do not understand what elements is? Hope to explain)
And so on.
5. Mathematical functions
Currently, JDO does not seem to support such features.
AVG (...), sum (...), min (...), max (...)

Count (*)

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

The method and SQL are basically the same

Select distinct employee. name from employee as employee
Select count (distinct employee. Name), count (employee) from employee as employee

6. Polymorphism (do not know how to explain it for the moment ?)
From Com. Test. Animal as animal
Not only do we get all animal instances, but we can also get all animal subclasses (if we define a subclass cat)
An extreme example
From Java. Lang. object as O
You can obtain instances of all persistent classes.

7. Where statement
Define the query statement conditions. For example:
From employee as employee where employee. Name = 'jplateau'
From employee as employee where employee. name like 'J %'
From employee as employee where employee. name like '% U'
In the where statement, "=" can not only compare object attributes, but also compare objects, such:
Select animal from Com. Test. Animal as animal where animal. Name = dog

8. Expression

Most expressions in SQL statements can be used in hql:
Mathematical operators + ,-,*,/

Binary comparison operators =, >=, <=, <> ,! =, Like

Logical operations and, or, not

String concatenation |

SQL scalar functions like upper () and lower ()

Parentheses () indicate grouping

In, between, is null

JDBC in parameters?

Named parameters: name,: start_date,: x1 (this should be another "? "Work und)

SQL literals 'foo', 69, '2017-01-01 10:00:01. 0'

Java public static final constants eg. color. Tabby

I don't have to explain anything else. Here I just want to explain the parameters in the query:
We know that when passing parameters in SQL for query, we usually use preparedstatement to write a lot of "?" In the statement,
This method can also be used in hql, for example:
List mates = sess. Find (
"Select employee. name from employee as employee" +
"Where employee. Name =? ",
Name,
Hibernate. String
);
(Note: The above uses the find method in the session to reload many find methods in the hibernate API session, which can meet your needs for multiple forms of queries)
The preceding is a parameter. In this case, the type of the parameter and the defined parameter are introduced immediately. When multiple parameters are called and another find method is called, the last two
Parameters are in the form of arrays.

There is another way to solve the above problem. JDO also has this method, but it is different from hibernate in its form, but they are actually
Same, such:
Query q = sess. createquery ("select employee. name from employee as employee where employee. Name =: Name ");
Q. setstring ("name", "jplateau ");
// Define multiple parameters here
Iterator employees = Q. iterate ();

9. Order statement
There is no difference with SQL statements, such:
Select employee. name from employee as employee where employee. name like 'J % 'order by employee. id desc (or ASC)

10. Group by statement
Similar to SQL statements, for example:

Select employee. Name, employee. depno from employee as employee group by employee. depno

Select Foo. ID, AVG (elements (FOO. Names), max (indices (FOO. Names) from eg. Foo group by foo. ID
{Note: You may use the elements and indices constructs inside a select clause, even on databases with no subselects .}
Thank you!

11. Subquery
Hibernate also supports subqueries. Here are several examples:

From eg. CAT as fatcat where fatcat. weight> (select AVG (Cat. Weight) from eg. domesticcat)