JPQL and jpql statements

JPQL and jpql statements
5. JPQL

JPQL (JavaPersistence Query Language) is an object-based Query. The queried content is an object or an object attribute.

5.1. query objects

SELECT c FROM Customer c

JPQL statements are similar to SQL statements, but they are entity-based queries. Pay attention to the following issues during initial use.

1) Entity Names and attributes are case sensitive;

2) reserved keywords in JPQL are case insensitive;

3) the alias used to identify an object is case-insensitive;

4) the alias of an object can be identified by the AS keyword, or the AS keyword can be omitted;

5.2. Object Attributes

If the alias of an object is used during the query, You can reference the attribute of the object (rather than the field name of the table) in the SELECT query, as follows:

SELECT c. name FROM Customer c

If the queried object property is another object (one-to-one relationship), you can also obtain the object property by "." after the property, as shown below:

SELECT c. address FROM Customer c

SELECT c. address. street FROM Customer c (Multi-Level navigation)

 

5.3 join query

JPA provides two ways to display correlated entities: INNERJOIN and leftouter join ).

First, review the internal connection, external connection, left connection, and right connection methods in traditional SQL statements. Assume that the table tb_customer and table tb_order correspond to the entity Customer and Order respectively. The data in the table is as follows:

Tb_customer data

Id	Name	Email	Address_id
1	Zhangsan	Zhangsan@163.com	6
2	Lisi	Lisi@163.com	7
3	Wangwu	Wangwu@163.com	8

Tb_order data

Id	Num	Customer_id	Create_date
5	10002	1
6	10003	1
7	10004	5
8	10005	1
9	10006	3

1) Internal Connection

An inner join query is also called a natural join query. It is the smallest set associated with two tables. That is to say, the data in the left table and the data in the right table are associated only when both exist, and no null value will appear during the execution of the inner join query. For example, execute the following SQL statement

SELECT * from tb_customer c inner join tb_order o ONc. id = o. customer_id

The query result is as follows:

C. id	C. name	O. id	O. num	O. customer_id
1	Zhangsan	5	10002	1
1	Zhangsan	6	10003	1
1	Zhangsan	8	10005	1
3	Wangwu	9	10006	3

The left table is tb_customer, And the right table is tb_oreder. The id = 2 record in the left table, which does not exist in the right table. The records with id = 7 in the right table do not exist in the left table, so there are no two data items in the use of inner join query.

2) left join

The left join is based on the left table. If the data in the right table is associated, it is displayed. If there is no data in the right table, it is displayed as null. That is to say, all the data queried by the left join is the coordinate data, but not all the data in the right table, as shown below:

SELECT * from tb_customer c left join tb_order o ONc. id = o. customer_id

C. id	C. name	O. id	O. num	O. customer_id
1	Zhangsan	5	10002	1
1	Zhangsan	6	10003	1
1	Zhangsan	8	10005	1
2	Lisi	Null	Null	Null
3	Wangwu	9	10006	3

 

3) Right join query

The right join query is opposite to the left join query, which is based on the right table. If the data in the left table is associated, it is displayed. If no, It is null. As follows:

SELECT * from tb_customer c right join tb_order o ONc. id = o. customer_id

C. id	C. name	O. id	O. num	O. customer_id
1	Zhangsan	5	10002	1
1	Zhangsan	6	10003	1
Null	Null	7	10004	5
1	Zhangsan	8	10005	1
3	Wangwu	9	10006	3

4) query external connections

External Connection query is relative to internal connection query. In general, both left connection query and right connection query belong to external connection query, but the direction is different.

Shows the table connection query relationship:

Compared with traditional SQL statements, JPA only provides two table connections: inner join and left join.

L inner connection

The internal connection is the most common connection method. After the connection, the associated data does not have a null value. The syntax is as follows:

[INNER] JOIN

SELECT c, o FROM Customer c JOIN c. orders o

L left join

The left join can also be called the left outer join. It is based on the left table and is associated with the right table. The data in the right table after the connection may be null, as shown below:

LEFT [OUTER] JOIN

SELECT c, o FROMCustomer c left join c. orders o

In this case, the query results are all customers and can be queried even if the customer does not have an order.

L capture connections

There are two ways to load object attributes: instant loading and lazy loading. Similarly, for Object-related queries, you can also set the loading method during the query, which is to capture connections, as shown below:

[LEFT | INNER] JOIN FETCH

Simply put, adding the FETCH keyword after the JOIN keyword indicates that the query is a crawling query. In fact, the crawling connection mainly targets object attributes in the lazy loading mode. Using the inner connection does not load the associated entities, as shown below:

SELECT c FROM Customer c JOIN c. orders o

In this case, an exception is thrown when the client calls the getOrders method. However, if the associated query is set to capture

SELECT c FROM Customer c JOIN FETCH

Then, the customer pair of the query result is loaded with the associated orders attribute.

L uniqueness

When performing a connection query, duplicate data is usually generated. To remove duplicate data, you can use the DISTINCT keyword when associating the query, as shown below:

Select distinct c FROM Customer c JOIN c. orders o

5.4 Operator

The WHERE condition expression can contain comparison operators and logical operators, as shown below:

L comparison operator

=,>,> =, <, <=, <>, [NOT] BETWEEN, [NOT] LIKE,

[NOT] BETWEEN, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER

L logical operators

NOT, AND, OR

5.5.

The BETWEEN operator is the abbreviation of a query value within a specified range. The syntax is as follows:

[NOT] BETWEEN range value 1 AND range value 2

SELECT c FROM Customer WHRER c. asset BETWEEN 1000.0 and2000.0

5.6. IN

The IN operator can query multiple specified values. The syntax is as follows:

[NOT] IN (value {, value} * | subquery)

5.7. LIKE

The LIKE operator is used to query matched strings. The key characters that match strings are as follows:

1) Draw line "_": Indicates matching a certain character;

2) percent sign: matches zero or multiple characters;

If the string to be queried has "_" or "%", you need to transfer the character "\" before matching the string.

5.8, NULL

The NULL operator is used to determine whether the attribute is null.

If the property is null, use the keyword "ISNULL", as shown below:

SELECT c FROM Customer c WHERE c. address IS NULL

If the property IS not null, use the keyword "is notnull", as shown below:

SELECT c FROM Customer c WHERE c. address IS NULL

The NULL operator can also be used in parameter queries to determine whether the input parameter is null, as shown below:

SELECT c FROM Customer c WHERE: zip is not nulll ANDc.address.zip =: zip

5.9. EMPTY

The EMPTY operator is used to determine whether the collection class attribute of an object is null. It is not the same as the NULL operator, mainly for the judgment of the Set class as the attribute.

Determine whether the set class attribute IS null and use is empty, as shown below:

SELECT c FROM Customer c WHERE c. orders IS EMPTY

Note: When you use an inner join query, because the joined table does not have a null value, the query results will not appear even if a null value is found, therefore, do not use is mpty to determine the null value when using the internal connection query, as shown below:

SELECT c FROM Customer c JOIN c. orders o WHERE c. orders ISEMPTY

Use is not empty to determine whether the set class attribute is not a null value, as shown below:

SELECT c FROM Customer c WHERE c. orders IS NOT EMPTY

 

5.10. MEMBER

The member of operator is used to determine whether an object is included in a collection Class Object (NOT can be added before MEMBEROF ).

// Query the customer entity whose order number is 1

SELECT c FROM Customer c WHERE: order member of c. orders

Query query = entityManager. createQuery (jpql );

Order o = entityManager. find (Order. class, 1 );

Query. setParameter ("order", o );

5.11 function expressions

JPQL also defines some common functions that can be used for numeric, numeric, and datetime values.

String Functions

String functions can be used for query. For example, the JPQL statement for querying a customer whose name is longer than 10 is as follows:

SELECT c FROM Customer c where length (c. name)> 10

The following string functions are provided in JPQL:

3) CONCAT (str1, str2): returns the value connecting two strings.

4) SUBSTRING (str, start, len): return a string segment. start indicates the start index position of the string (the first character is 1), and len indicates the length of the string;

5) TRIM (str): removes spaces at the beginning and end of the string;

6) LENGTH (str): returns the LENGTH of the string;

Numeric Functions

Numeric functions can also be used in queries as follows:

SELECT c FROM Customer c where abs (c. asset)> 20

The main numeric functions provided in JPQL are:

L ABS (num): returns the absolute value of the number;

L SQRT (num): returns the square of the number;

L MOD (int, int): modulo;

L SIZE: returns the total number of collection classes;

 

Date Functions

JPA provides three methods to get the current time format of the system:

L CURRENT_DATE

L CURRENT_TIME

L CURRENT_TIMESTAMP

5.12 subquery

When one query condition depends on another query result, subquery (nested query) is required, as shown below:

SELECT c FROM Customer c WHERE c. age> (select avg (c. age) FROM Customer c)

EXISTS expression

The EXISTS expression is used to determine the results of a subquery. If one or more subquery results exist, true is returned. If the subquery does not return any results, false is returned. Syntax:

[NOT] EXISTS (subquery expression)

If you want to query the customers of orders before the current date

SELECT c FROM Customer c WHERE EXISTS

(SELECT o FROM c. orders o WHERE o. createTime <CURRENT_DATE)

You can also add "NOT" before the EXISTS keyword to indicate that the query result does NOT exist.

ALL and ANY expressions

When the subquery returns multiple results, the top-level query conditions use =, <, <=, >,>=, <> to meet the conditions, you need to use the ALL, ANY, and SOME (ANY, SOME and not all are equivalent, as long as there is one) expression. The basic statement is as follows:

{ALL | ANY | SOME} (subquery)

SELECT o FROM Order o WHERE 50 <ANY (SELECT l. quantityFROM o. lineItems l) // query the Order details for more than 50 orders

5.13 Group

Grouping query is a very important query in JPQL. It can group data according to specified attributes, which is usually used in statistics.

Group query usually uses the GROUPBY and HAVING expressions. The basic syntax of group query is as follows:

Group by <GROUP clause> (HAVING

The "GROUPBY" keyword specifies the attributes of the group. "HAVING" can filter the grouped data. It serves as the WHERE clause and can only be used in group queries.

SELECT c FROM Customer c group by c. asset having avg (c. asset)> 1000

 

5.14 sorting

The order by clause can sort the query results. The syntax is as follows:

Order by sorting attribute [ASC | DESC] {, sorting attribute [ASC | DESC]} *

The "ORDERBY" keyword specifies the sorting attribute. Multiple Attributes are separated by semicolons, as shown below:

SELECT c FROM Customer c order by c. id ASC, c. name DESC

Note: When JPQL is dynamically assembled to specify multiple sorting fields, HashMap cannot be used instead of javashashmap.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.