Download PDF Home History |  | API Search Feedback FAQ

> ## 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.

Download PDF Home History |  | API Search Feedback FAQ

All of the material in The J2EE(TM) 1.4 Tutorial is copyright-protected and may not be published in other works without express written permission from Sun Microsystems.