Home TOC Index

Search Feedback

Example Queries

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

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 PlayerEJB 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. The DISTINCT keyword eliminates duplicate values.

The WHERE clause restricts the players retrieved by checking their position, a persistent field of the PlayerEJB 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 with the specified position and name.

Finder method: findByPositionAndName(String position, String name)

Description: The position and name elements are persistent fields of the PlayerEJB entity bean. The WHERE clause compares the values of these fields with the parameters of the findByPositionAndName method. EJB QL denotes an input parameter with 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 PlayerEJB entity bean, and the t variable represents the related TeamEJB 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 PlayerEJB bean to its related TeamEJB 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 may not 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 PlayerEJB-TeamEJB relationship, and the t.league expression navigates the TeamEJB-LeagueEJB 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 LeagueEJB bean. To reach the sport field, the query must first navigate from the PlayerEJB bean to the TeamEJB bean (p.teams) and then from the TeamEJB bean to the LeagueEJB bean (t.league). Because the league relationship field is not a collection, it may 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 PlayerEJB 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 may 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 LeagueEJB 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 LeagueEJB entity bean.

Home TOC Index

Search Feedback