Queries That Navigate to Related Entities

In the query language, an expression can traverse (or navigate) to related entities. These expressions are the primary difference between the Java Persistence query language and SQL. Queries navigates to related entities, whereas SQL joins tables.

A Simple Query with Relationships

SELECT DISTINCT p
FROM Player p, IN(p.teams) t

Data retrieved: All players who belong to a team.

Description: The FROM clause declares two identification variables: p and t. The p variable represents the Player entity, and the t variable represents the related Team entity. The declaration for t references the previously declared p variable. The IN keyword signifies that teams is a collection of related entities. The p.teams expression navigates from a Player to its related Team. The period in the p.teams expression is the navigation operator.

You may also use the JOIN statement to write the same query:

SELECT DISTINCT p
FROM Player p JOIN p.teams t

This query could also be rewritten as:

SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY

Navigating to Single-Valued Relationship Fields

Use the JOIN clause statement to navigate to a single-valued relationship field:

SELECT t
 FROM Team t JOIN t.league l
 WHERE l.sport = ’soccer’ OR l.sport =’football’

In this example, the query will return all teams that are in either soccer or football leagues.

Traversing Relationships with an Input Parameter

SELECT DISTINCT p
FROM Player p, IN (p.teams) AS t
WHERE t.city = :city

Data retrieved: The players whose teams belong to the specified city.

Description: This query is similar to the previous example, but it adds an input parameter. The AS keyword in the FROM clause is optional. In the WHERE clause, the period preceding the persistent variable city is a delimiter, not a navigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities), but not to persistent fields. To access a persistent field, an expression uses the period as a delimiter.

Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. In the syntax of an expression, a collection-valued field is a terminal symbol. Because the teams field is a collection, the WHERE clause cannot specify p.teams.city (an illegal expression).

See also: Path Expressions

Traversing Multiple Relationships

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league = :league

Data retrieved: The players that belong to the specified league.

Description: The expressions in this query navigate over two relationships. The p.teams expression navigates the Player-Team relationship, and the t.league expression navigates the Team-League relationship.

In the other examples, the input parameters are String objects, but in this example the parameter is an object whose type is a League. This type matches the league relationship field in the comparison expression of the WHERE clause.

Navigating According to Related Fields

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport

Data retrieved: The players who participate in the specified sport.

Description: The sport persistent field belongs to the League entity. To reach the sport field, the query must first navigate from the Player entity to Team (p.teams) and then from Team to the League entity (t.league). Because the league relationship field is not a collection, it can be followed by the sport persistent field.