The following queries are from the
PlayerEJBentity bean of the
RosterAppJ2EE 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.
FROMclause declares an identification variable named
p, omitting the optional keyword
AS. If the
ASkeyword were included, the clause would be written as follows:FROM Player AS p
Playerelement is the abstract schema name of the
PlayerEJBentity bean. Because the bean defines the
findallmethod in the
LocalPlayerHomeinterface, the objects returned by the query have the
See also: Identification VariablesExample 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.
Description: In a
OBJECTkeyword must precede a stand-alone identification variable such as
DISTINCTkeyword eliminates duplicate values.
WHEREclause restricts the players retrieved by checking their
position, a persistent field of the
PlayerEJBentity bean. The
?1element denotes the input parameter of the
See also: Input Parameters, DISTINCT and OBJECT KeywordsExample 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.
findByPositionAndName(String position, String name)
nameelements are persistent fields of the
PlayerEJBentity bean. The
WHEREclause compares the values of these fields with the parameters of the
findByPositionAndNamemethod. 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.
FROMclause declares two identification variables:
pvariable represents the
PlayerEJBentity bean, and the
tvariable represents the related
TeamEJBbeans. The declaration for
treferences the previously declared
INkeyword signifies that
teamsis a collection of related beans. The
p.teamsexpression navigates from a
PlayerEJBbean to its related
TeamEJBbeans. The period in the
p.teamsexpression is the navigation operator.
WHEREclause, the period preceding the persistent variable
cityis 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
teamsfield is a collection, the
WHEREclause cannot specify
p.teams.city--an illegal expression.
See also: Path ExpressionsExample 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.
Description: The expressions in this query navigate over two relationships. The
p.teamsexpression navigates the
TeamEJBrelationship, and the
t.leagueexpression navigates the
In the other examples, the input parameters areExample 6
Stringobjects, but in this example the parameter is an object whose type is a
LocalLeagueinterface. This type matches the
leaguerelationship field in the comparison expression of the
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.
sportpersistent field belongs to the
LeagueEJBbean. To reach the
sportfield, the query must first navigate from the
PlayerEJBbean to the
p.teams) and then from the
TeamEJBbean to the
t.league). Because the
leaguerelationship field is not a collection, it may be followed by the
Finder Queries with Other Conditional Expressions
WHEREclause 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.
SELECT OBJECT(p) FROM Player p WHERE p.teams IS EMPTY
Data retrieved: All players who do not belong to a team.
teamsrelationship field of the
PlayerEJBbean is a collection. If a player does not belong to a team, then the
teamscollection is empty and the conditional expression is
See also: Empty Collection Comparison ExpressionsExample 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.
findBySalaryRange(double low, double high)
BETWEENexpression has three arithmetic expressions: a persistent field (
p.salary) and the two input parameters (
?2). The following expression is equivalent to the
BETWEENexpression:p.salary >= ?1 AND p.salary <= ?2
See also: BETWEEN ExpressionsExample 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.
FROMclause declares two identification variables (
p2) of the same type (
Player). Two identification variables are needed because the
WHEREclause compares the salary of one player (
p2) with that of the other players (
See also: Identification Variables
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.
Description: The return type of this query is the abstract schema type of the
LeagueEJBentity bean. This abstract schema type maps to the
LocalLeagueHomeinterface. Because the expression
t.leagueis not a stand-alone identification variable, the
OBJECTkeyword is omitted.
See also: SELECT ClauseExample 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.
Description: This query returns a
sport, which is a persistent field of the