Example Queries

The following queries are from the PlayerBean entity bean of the RosterApp J2EE application, which is documented in Chapter 27. To see the relationships between the beans of the RosterApp, see Figure 27-3.

Simple Finder Queries

If you are unfamiliar with EJB QL, these simple queries are a good place to start.

Example 1   
SELECT OBJECT(p)
FROM Player p

Data retrieved: All players.

Finder method: findall()

Description: The FROM clause declares an identification variable named p, omitting the optional keyword AS. If the AS keyword were included, the clause would be written as follows: 

FROM Player AS p

The Player element is the abstract schema name of the PlayerBean entity bean. Because the bean defines the findall method in the LocalPlayerHome interface, the objects returned by the query have the LocalPlayer type.

See also: Identification Variables

Example 2   
SELECT DISTINCT OBJECT(p)
FROM Player p
WHERE p.position = ?1

Data retrieved: The players with the position specified by the finder method's parameter.

Finder method: findByPosition(String position)

Description: In a SELECT clause, the OBJECT keyword must precede a stand-alone identification variable such as p. (A stand-alone identification variable is not part of a path expression.) The DISTINCT keyword eliminates duplicate values.

The WHERE clause restricts the players retrieved by checking their position, a persistent field of the PlayerBean entity bean. The ?1 element denotes the input parameter of the findByPosition method.

See also: Input Parameters, DISTINCT and OBJECT Keywords

Example 3   
SELECT DISTINCT OBJECT(p)
FROM Player p
WHERE p.position = ?1 AND p.name = ?2

Data retrieved: The players having the specified positions and names.

Finder method: findByPositionAndName(String position, String name)

Description: The position and name elements are persistent fields of the PlayerBean entity bean. The WHERE clause compares the values of these fields with the parameters of the findByPositionAndName method. EJB QL denotes an input parameter using a question mark followed by an integer. The first input parameter is ?1, the second is ?2, and so forth.

Finder Queries That Navigate to Related Beans

In EJB QL, an expression can traverse (or navigate) to related beans. These expressions are the primary difference between EJB QL and SQL. EJB QL navigates to related beans, whereas SQL joins tables.

Example 4   
SELECT DISTINCT OBJECT(p)
FROM Player p, IN (p.teams) AS t
WHERE t.city = ?1

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

Finder method: findByCity(String city)

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

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 beans), 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

Example 5   
SELECT DISTINCT OBJECT(p)
FROM Player p, IN (p.teams) AS t
WHERE t.league = ?1

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

Finder method: findByLeague(LocalLeague league)

Description: The expressions in this query navigate over two relationships. The p.teams expression navigates the PlayerBean-TeamBean relationship, and the t.league expression navigates the TeamBean-LeagueBean relationship.

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

Example 6   
SELECT DISTINCT OBJECT(p)
FROM Player p, IN (p.teams) AS t
WHERE t.league.sport = ?1

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

Finder method: findBySport(String sport)

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

Finder Queries with Other Conditional Expressions

Every WHERE clause must specify a conditional expression, of which there are several kinds. In the previous examples, the conditional expressions are comparison expressions that test for equality. The following examples demonstrate some of the other kinds of conditional expressions. For descriptions of all conditional expressions, see the section WHERE Clause.

Example 7   
SELECT OBJECT(p)
FROM Player p
WHERE p.teams IS EMPTY

Data retrieved: All players who do not belong to a team.

Finder method: findNotOnTeam()

Description: The teams relationship field of the PlayerBean bean is a collection. If a player does not belong to a team, then the teams collection is empty and the conditional expression is TRUE.

See also: Empty Collection Comparison Expressions

Example 8   
SELECT DISTINCT OBJECT(p)
FROM Player p
WHERE p.salary BETWEEN ?1 AND ?2

Data retrieved: The players whose salaries fall within the range of the specified salaries.

Finder method: findBySalaryRange(double low, double high)

Description: This BETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and the two input parameters (?1 and ?2). The following expression is equivalent to the BETWEEN expression: 

p.salary >= ?1 AND p.salary <= ?2

See also: BETWEEN Expressions

Example 9   
SELECT DISTINCT OBJECT(p1)
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = ?1

Data retrieved: All players whose salaries are higher than the salary of the player with the specified name.

Finder method: findByHigherSalary(String name)

Description: The FROM clause declares two identification variables (p1 and p2) of the same type (Player). Two identification variables are needed because the WHERE clause compares the salary of one player (p2) with that of the other players (p1).

See also: Identification Variables

Select Queries

The queries in this section are for select methods. Unlike finder methods, a select method can return persistent fields or other entity beans.

Example 10   
SELECT DISTINCT t.league
FROM Player p, IN (p.teams) AS t
WHERE p = ?1

Data retrieved: The leagues to which the specified player belongs.

Select method: ejbSelectLeagues(LocalPlayer player)

Description: The return type of this query is the abstract schema type of the LeagueBean entity bean. This abstract schema type maps to the LocalLeagueHome interface. Because the expression t.league is not a stand-alone identification variable, the OBJECT keyword is omitted.

See also: SELECT Clause

Example 11   
SELECT DISTINCT t.league.sport
FROM Player p, IN (p.teams) AS t
WHERE p = ?1

Data retrieved: The sports that the specified player participates in.

Select method: ejbSelectSports(LocalPlayer player)

Description: This query returns a String named sport, which is a persistent field of the LeagueBean entity bean.