Movatterモバイル変換

Example Queries

The following queries are from thePlayer entity of theroster application, whichis documented inChapter 26, Persistence in the EJB Tier.

Simple Queries

If you are unfamiliar with the query language, these simple queries are agood place to start.

A Basic Select Query

SELECT pFROM Player p

Data retrieved: All players.

Description: TheFROM clause declares an identification variable namedp, omitting the optional keywordAS. If theAS keyword were included, the clause would be written asfollows:

FROM Player AS p

ThePlayer element is the abstract schema name of thePlayer entity.

See also:Identification Variables

Eliminating Duplicate Values

SELECT DISTINCT pFROM Player pWHERE p.position = ?1

Data retrieved: The players with the position specified by the query’s parameter.

Description: TheDISTINCT keyword eliminates duplicate values.

TheWHERE clause restricts the players retrieved by checking theirposition, a persistentfield of thePlayer entity. The?1 element denotes the input parameter ofthe query.

Using Named Parameters

SELECT DISTINCT pFROM Player pWHERE p.position = :position AND p.name = :name

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

Description: Theposition andname elements are persistent fields of thePlayer entity. TheWHERE clause compares the values of these fields with the named parameters ofthe query, set using theQuery.setNamedParameter method. The query language denotes a named inputparameter using colon (:) followed by an identifier. The first input parameter is:position, the second is:name.

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 pFROM Player p, IN(p.teams) t

Data retrieved: All players who belong to a team.

Description: TheFROM clause declares two identification variables:p andt. Thep variablerepresents thePlayer entity, and thet variable represents the relatedTeam entity. Thedeclaration fort references the previously declaredp variable. TheIN keywordsignifies thatteams is a collection of related entities. Thep.teams expression navigatesfrom aPlayer to its relatedTeam. The period in thep.teams expressionis the navigation operator.

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

SELECT DISTINCT pFROM Player p JOIN p.teams t

This query could also be rewritten as:

SELECT DISTINCT pFROM Player pWHERE p.team IS NOT EMPTY

Navigating to Single-Valued Relationship Fields

Use theJOIN 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 ineither soccer or football leagues.

Traversing Relationships with an Input Parameter

SELECT DISTINCT pFROM Player p, IN (p.teams) AS tWHERE 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 aninput parameter. TheAS keyword in theFROM clause is optional. In theWHERE clause, the period preceding the persistent variablecity is a delimiter, not anavigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities), butnot to persistent fields. To access a persistent field, an expression uses theperiod as a delimiter.

Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. Inthe syntax of an expression, a collection-valued field is a terminal symbol. Becausetheteams field is a collection, theWHERE clause cannot specifyp.teams.city (anillegal expression).

See also:Path Expressions

Traversing Multiple Relationships

SELECT DISTINCT pFROM Player p, IN (p.teams) tWHERE t.league = :league

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

Description: The expressions in this query navigate over two relationships. Thep.teams expression navigatesthePlayer-Team relationship, and thet.league expression navigates theTeam-League relationship.

In the other examples, the input parameters areString objects, but in thisexample the parameter is an object whose type is aLeague. This typematches theleague relationship field in the comparison expression of theWHERE clause.

Navigating According to Related Fields

SELECT DISTINCT pFROM Player p, IN (p.teams) tWHERE t.league.sport = :sport

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

Description: Thesport persistent field belongs to theLeague entity. To reach thesport field, the query must first navigate from thePlayer entity toTeam(p.teams) and then fromTeam to theLeague entity (t.league). Because theleaguerelationship field is not a collection, it can be followed by thesportpersistent field.

Queries with Other Conditional Expressions

EveryWHERE clause must specify a conditional expression, of which there are severalkinds. In the previous examples, the conditional expressions are comparison expressions that test forequality. The following examples demonstrate some of the other kinds of conditional expressions.For descriptions of all conditional expressions, see the sectionWHERE Clause.

The`LIKE` Expression

SELECT p FROM Player p WHERE p.name LIKE ’Mich%’

Data retrieved: All players whose names begin with “Mich.”

Description: TheLIKE expression uses wildcard characters to search for strings that matchthe wildcard pattern. In this case, the query uses theLIKE expression andthe% wildcard to find all players whose names begin with thestring “Mich.” For example, “Michael” and “Michelle” both match the wildcard pattern.

See also:LIKE Expressions

The`IS NULL` Expression

SELECT t FROM Team t WHERE t.league IS NULL

Data retrieved: All teams not associated with a league.

Description: TheIS NULL expression can be used to check if a relationship hasbeen set between two entities. In this case, the query checks to seeif the teams are associated with any leagues, and returns the teams thatdo not have a league.

The`IS EMPTY` Expression

SELECT pFROM Player pWHERE p.teams IS EMPTY

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

Description: Theteams relationship field of thePlayer entity is a collection. Ifa player does not belong to a team, then theteams collection is emptyand the conditional expression isTRUE.

The`BETWEEN` Expression

SELECT DISTINCT pFROM Player pWHERE p.salary BETWEEN :lowerSalary AND :higherSalary

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

Description: ThisBETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and thetwo input parameters (:lowerSalary and:higherSalary). The following expression is equivalent totheBETWEEN expression:

p.salary >= :lowerSalary AND p.salary <= :higherSalary

See also:BETWEEN Expressions

Comparison Operators

SELECT DISTINCT p1FROM Player p1, Player p2WHERE p1.salary > p2.salary AND p2.name = :name

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

Description: TheFROM clause declares two identification variables (p1 andp2) of the sametype (Player). Two identification variables are needed because theWHERE clause compares thesalary of one player (p2) with that of the other players (p1).

See also:Identification Variables

Bulk Updates and Deletes

The following examples show how to use theUPDATE andDELETE expressions in queries.UPDATE andDELETE operate on multiple entities according to the condition or conditionsset in theWHERE clause. TheWHERE clause inUPDATE andDELETEqueries follows the same rules asSELECT queries.

Update Queries

UPDATE Player pSET p.status = ’inactive’WHERE p.lastPlayed < :inactiveThresholdDate

Description: This query sets the status of a set of players toinactiveif the player’s last game was longer than the date specified ininactiveThresholdDate.

Delete Queries

DELETEFROM Player pWHERE p.status = ’inactive’AND p.teams IS EMPTY

Description: This query deletes all inactive players who are not on a team.