Introduction to Java Persistence Query Language (JPQL)

The Java Persistence Query Language(JPQL) is one of the basic area of Java Persistence which allows you to write portable queries that work regardless of the underlying data store. It uses a SQL-like syntax to select objects or values based on entity abstract schema types and relationships among them. An entity is a lightweight persistence domain object. Typically an entity represents a table in a relational database, and each entity instance corresponds to a row in that table.

Here i describes some syntax of the language so that you can build
your own query in JPQL easily.

Lets start from a basic select query
SELECT p FROM Player p
This statement retrieves all players from Player entity.

Now write a query with WHERE clause and input parameter
SELECT DISTINCT p FROM Player p WHERE p.position = ?1
Retrieves players with the position specified. The DISTINCT keyword eliminates duplicate values. ?1 element denotes the input parameter of the query. Here position a persistent field of the Player Entity.

Select statement with named parameters
SELECT DISTINCT p FROM Player p WHERE p.position = :position AND p.name = :name
The WHERE clause compares the values of these fields with the
named parameters of the query, set using the Query.setNamedParameter
method. The query language denotes a named input parameter using
colon (:) followed by an identifier. The first input parameter is
:position, the second is :name.

At the time of writing Entity we relates one entity with another. Lets
talk about queries that navigate to related entities. One thing to
remember"Expressions can navigate to relationship fields (related
entities), but not to persistent fields"
SELECT DISTINCT p FROM Player p, IN(p.teams) t
Retrieves All players who belong to a team. The IN keyword signifies
that teams is a collection of the Player entity. The p.teams
expression navigates from a Player to its related Team. The period
in the p.teams expression is the navigation operator.

JOIN statement for the same query
SELECT DISTINCT p FROM Player p JOIN p.teams t

Another statement that serves the same purpose
SELECT DISTINCT p FROM Player p WHERE p.team IS NOT EMPTY

Navigating to single valued relationship field
SELECT t FROM Team t JOIN t.league l WHERE l.sport = 'soccer' OR l.sport ='football'

Navigating relationship field with input parameter
SELECT DISTINCT p FROM Player p, IN (p.teams) t WHERE t.city = :city

Some times you need to traverse multiple relationships. Following query navigate over two relationships.
SELECT DISTINCT p FROM Player p, IN (p.teams) t WHERE t.league = :league

To reach to a persistent field of league you can navigate by following way
SELECT DISTINCT p FROM Player p, IN (p.teams) t WHERE t.league.sport = :sport
Here sport is a persistent field of League. And you have to navigate from Player to League via Team.

After WHERE clause you have to use some conditional expression.
Lets talk about conditional expressions of JPQL
LIKE:
SELECT p FROM Player p WHERE p.name LIKE 'Mich%'

IS NULL:
SELECT t FROM Team t WHERE t.league IS NULL
Retrieves All teams not associated with a league.

IS EMPTY:
SELECT p FROM Player p WHERE p.teams IS EMPTY
Retrieves all players not belong to a team

BETWEEN:
SELECT DISTINCT p FROM Player p WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

[NOT] MEMBER [OF]:(Determines whether a value is a member of a collection. The value and the collection members must have the same type):
SELECT o FROM Order o WHERE :lineItem MEMBER OF o.lineItems


[NOT] EXISTS:(Return true only if the result of the subquery consists of one or more values and is false otherwise):
SELECT DISTINCT emp FROM Employee emp WHERE EXISTS (SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = emp.spouse)

ALL and ANY:
The ALL expression is used with a sub-query, and is true if all the
values returned by the sub-query are true, or if the sub-query is empty.

The ANY expression is used with a sub-query, and is true if some of
the values returned by the sub-query are true. An ANY expression
is false if the sub-query result is empty, or if all the values returned
are false. The SOME keyword is synonymous with ANY.
The ALL and ANY expressions are used with the =, <, <=, >, >=, <>
comparison operators.
SELECT emp FROM Employee emp WHERE emp.salary > ALL (
SELECT m.salary FROM Manager m WHERE m.department = emp.department)

The following two queries tell you how to write UPDATE and DELETE
statement in JPQL
UPDATE Player p SET p.status = 'inactive' WHERE p.lastPlayed < :inactiveThr
DELETE FROM Player p WHERE p.status = 'inactive' AND p.teams IS EMPTY

Lets talk about aggregate functions of JPQL, Some frequently used aggregate functions are AVG, COUNT, MAX, MIN, SUM.
SELECT AVG(o.quantity) FROM Order o

SELECT SUM(l.price) FROM Order o JOIN o.lineItems l JOIN o.customer c WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

In most cases after aggregate functions you have to use GROUP_BY or HAVING clause. but how to use them in JPQL? Here is the answer
SELECT c.country, COUNT(c) FROM Customer c GROUP BY c.country

SELECT c.status, AVG(o.totalPrice) FROM Order o JOIN o.customer c GROUP BY c.status HAVING c.status IN (1, 2, 3)

If you want to fetch data by your defined order then use ORDER_BY in the query
SELECT o FROM Customer c JOIN c.orders o JOIN c.address a WHERE a.state = 'CA' ORDER BY o.quantity, o.totalcost


Constructor expressions: It is an advance feature of JPQL. It allows you to return Java instances that store a query result element instead of an Object[]. The following query creates a CustomerDetail instance per Customer matching the WHERE clause. A CustomerDetail stores the customer name and customer’s country name. So the query returns a List of CustomerDetail instances.
SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name) FROM customer c WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

For more information about JPQL click here