Full Query Language Syntax

BNF Symbols

BNF Grammar of the Java Persistence Query Language

FROM Clause

Identifiers

Identification Variables

Path Expressions

Examples of Path Expressions

Expression Types

Navigation

WHERE Clause

Literals

Input Parameters

Conditional Expressions

Operators and Their Precedence

BETWEEN Expressions

IN Expressions

LIKE Expressions

NULL Comparison Expressions

Empty Collection Comparison Expressions

Collection Member Expressions

Subqueries

Functional Expressions

NULL Values

Equality Semantics

SELECT Clause

Return Types

TheDISTINCT Keyword

Constructor Expressions

ORDER BY Clause

TheGROUP BY Clause

TheHAVING Clause

Full Query Language Syntax

This section discusses the query language syntax, as defined in the Java Persistencespecification. Much of the following material paraphrases or directly quotes the specification.

BNF Symbols

Table 27-1 describes the BNF symbols used in this chapter.

Table 27-1 BNF Symbol Summary

BNF Grammar of the Java Persistence Query Language

Here is the entire BNF diagram for the query language:

QL_statement ::= select_statement | update_statement | delete_statementselect_statement ::= select_clause from_clause [where_clause] [groupby_clause]     [having_clause] [orderby_clause]update_statement ::= update_clause [where_clause]delete_statement ::= delete_clause [where_clause]from_clause ::=    FROM identification_variable_declaration        {, {identification_variable_declaration |            collection_member_declaration}}*identification_variable_declaration ::=        range_variable_declaration { join | fetch_join }*range_variable_declaration ::= abstract_schema_name [AS]        identification_variablejoin ::= join_spec join_association_path_expression [AS]        identification_variablefetch_join ::= join_specFETCH join_association_path_expressionassociation_path_expression ::=        collection_valued_path_expression |        single_valued_association_path_expressionjoin_spec::= [LEFT [OUTER] |INNER] JOINjoin_association_path_expression ::=        join_collection_valued_path_expression |        join_single_valued_association_path_expressionjoin_collection_valued_path_expression::=    identification_variable.collection_valued_association_fieldjoin_single_valued_association_path_expression::=        identification_variable.single_valued_association_fieldcollection_member_declaration ::=        IN (collection_valued_path_expression) [AS]        identification_variablesingle_valued_path_expression ::=        state_field_path_expression |        single_valued_association_path_expressionstate_field_path_expression ::=    {identification_variable |    single_valued_association_path_expression}.state_fieldsingle_valued_association_path_expression ::=    identification_variable.{single_valued_association_field.}*    single_valued_association_fieldcollection_valued_path_expression ::=    identification_variable.{single_valued_association_field.}*    collection_valued_association_fieldstate_field ::=    {embedded_class_state_field.}*simple_state_fieldupdate_clause ::=UPDATE abstract_schema_name [[AS]    identification_variable] SET update_item {, update_item}*update_item ::= [identification_variable.]{state_field |    single_valued_association_field} = new_valuenew_value ::=     simple_arithmetic_expression |    string_primary |    datetime_primary |    boolean_primary |    enum_primary simple_entity_expression |    NULLdelete_clause ::= DELETE FROM abstract_schema_name [[AS]    identification_variable]select_clause ::= SELECT [DISTINCT] select_expression {,    select_expression}*select_expression ::=    single_valued_path_expression |    aggregate_expression |    identification_variable |    OBJECT(identification_variable) |    constructor_expressionconstructor_expression ::=    NEW constructor_name(constructor_item {,    constructor_item}*)constructor_item ::= single_valued_path_expression |    aggregate_expressionaggregate_expression ::=    {AVG |MAX |MIN |SUM} ([DISTINCT]        state_field_path_expression) |    COUNT ([DISTINCT] identification_variable |        state_field_path_expression |        single_valued_association_path_expression)where_clause ::= WHERE conditional_expressiongroupby_clause ::= GROUP BY groupby_item {, groupby_item}*groupby_item ::= single_valued_path_expressionhaving_clause ::= HAVING conditional_expressionorderby_clause ::= ORDER BY orderby_item {, orderby_item}*orderby_item ::= state_field_path_expression [ASC |DESC]subquery ::= simple_select_clause subquery_from_clause    [where_clause] [groupby_clause] [having_clause]subquery_from_clause ::=    FROM subselect_identification_variable_declaration        {, subselect_identification_variable_declaration}*subselect_identification_variable_declaration ::=    identification_variable_declaration |    association_path_expression [AS] identification_variable |    collection_member_declarationsimple_select_clause ::= SELECT [DISTINCT]    simple_select_expressionsimple_select_expression::=    single_valued_path_expression |    aggregate_expression |    identification_variableconditional_expression ::= conditional_term |    conditional_expression OR conditional_termconditional_term ::= conditional_factor | conditional_term AND    conditional_factorconditional_factor ::= [NOT] conditional_primaryconditional_primary ::= simple_cond_expression |(    conditional_expression)simple_cond_expression ::=    comparison_expression |    between_expression |    like_expression |    in_expression |    null_comparison_expression |    empty_collection_comparison_expression |    collection_member_expression |    exists_expressionbetween_expression ::=    arithmetic_expression [NOT] BETWEEN        arithmetic_expressionAND arithmetic_expression |    string_expression [NOT] BETWEEN string_expression AND        string_expression |    datetime_expression [NOT] BETWEEN        datetime_expression AND datetime_expressionin_expression ::=    state_field_path_expression [NOT] IN (in_item {, in_item}*    | subquery)in_item ::= literal | input_parameterlike_expression ::=    string_expression [NOT] LIKE pattern_value [ESCAPE        escape_character]null_comparison_expression ::=    {single_valued_path_expression | input_parameter} IS [NOT]        NULLempty_collection_comparison_expression ::=    collection_valued_path_expression IS [NOT] EMPTYcollection_member_expression ::= entity_expression    [NOT] MEMBER [OF] collection_valued_path_expressionexists_expression::= [NOT] EXISTS (subquery)all_or_any_expression ::= {ALL |ANY |SOME} (subquery)comparison_expression ::=    string_expression comparison_operator {string_expression |    all_or_any_expression} |    boolean_expression {= |<> } {boolean_expression |    all_or_any_expression} |    enum_expression {= |<> } {enum_expression |    all_or_any_expression} |    datetime_expression comparison_operator        {datetime_expression | all_or_any_expression} |    entity_expression {= |<> } {entity_expression |    all_or_any_expression} |    arithmetic_expression comparison_operator        {arithmetic_expression | all_or_any_expression}comparison_operator ::= = |> |>= |< |<= |<>arithmetic_expression ::= simple_arithmetic_expression |    (subquery)simple_arithmetic_expression ::=    arithmetic_term | simple_arithmetic_expression {+ |- }        arithmetic_termarithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ }    arithmetic_factorarithmetic_factor ::= [{+ |- }] arithmetic_primaryarithmetic_primary ::=    state_field_path_expression |    numeric_literal |    (simple_arithmetic_expression) |    input_parameter |    functions_returning_numerics |    aggregate_expressionstring_expression ::= string_primary | (subquery)string_primary ::=    state_field_path_expression |    string_literal |    input_parameter |    functions_returning_strings |    aggregate_expressiondatetime_expression ::= datetime_primary | (subquery)datetime_primary ::=    state_field_path_expression |    input_parameter |    functions_returning_datetime |    aggregate_expressionboolean_expression ::= boolean_primary | (subquery)boolean_primary ::=    state_field_path_expression |    boolean_literal |    input_parameter enum_expression ::= enum_primary | (subquery)enum_primary ::=    state_field_path_expression |    enum_literal |    input_parameterentity_expression ::=    single_valued_association_path_expression |        simple_entity_expressionsimple_entity_expression ::=    identification_variable |    input_parameterfunctions_returning_numerics::=    LENGTH(string_primary) |    LOCATE(string_primary, string_primary[,        simple_arithmetic_expression]) |    ABS(simple_arithmetic_expression) |    SQRT(simple_arithmetic_expression) |    MOD(simple_arithmetic_expression,        simple_arithmetic_expression) |    SIZE(collection_valued_path_expression)functions_returning_datetime ::=    CURRENT_DATE |    CURRENT_TIME |    CURRENT_TIMESTAMPfunctions_returning_strings ::=    CONCAT(string_primary, string_primary) |    SUBSTRING(string_primary,        simple_arithmetic_expression,        simple_arithmetic_expression)|    TRIM([[trim_specification] [trim_character] FROM]        string_primary) |    LOWER(string_primary) |    UPPER(string_primary)trim_specification ::= LEADING | TRAILING | BOTH

FROM Clause

TheFROM clause defines the domain of the query by declaring identification variables.

Identifiers

An identifier is a sequence of one or more characters. The firstcharacter must be a valid first character (letter,$,_) in an identifier ofthe Java programming language (hereafter in this chapter called simply “Java”). Each subsequentcharacter in the sequence must be a valid non-first character (letter, digit,$,_) in a Java identifier. (For details, see the Java SE APIdocumentation of theisJavaIdentifierStart andisJavaIdentifierPart methods of theCharacter class.) The question mark(?) is a reserved character in the query language and cannot be usedin an identifier.

A query language identifier is case-sensitive with two exceptions:

• Keywords

• Identification variables

An identifier cannot be the same as a query language keyword. Hereis a list of query language keywords:

It is not recommended that you use a SQL keyword as an identifier,because the list of keywords may expand to include other reserved SQL wordsin the future.

Identification Variables

Anidentification variable is an identifier declared in theFROM clause. Although theSELECTandWHERE clauses can reference identification variables, they cannot declare them. All identificationvariables must be declared in theFROM clause.

Because an identification variable is an identifier, it has the same naming conventionsand restrictions as an identifier with the exception that an identification variables iscase-insensitive. For example, an identification variable cannot be the same as a querylanguage keyword. (See the preceding section for more naming rules.) Also, within agiven persistence unit, an identification variable name must not match the name ofany entity or abstract schema.

TheFROM clause can contain multiple declarations, separated by commas. A declaration canreference another identification variable that has been previously declared (to the left). Inthe followingFROM clause, the variablet references the previously declared variablep:

FROM Player p, IN (p.teams) AS t

Even if an identification variable is not used in theWHERE clause, itsdeclaration can affect the results of the query. For an example, compare thenext two queries. The following query returns all players, whether or not theybelong to a team:

SELECT pFROM Player p

In contrast, because the next query declares thet identification variable, it fetchesall players that belong to a team:

SELECT pFROM Player p, IN (p.teams) AS t

The following query returns the same results as the preceding query, but theWHERE clause makes it easier to read:

SELECT pFROM Player pWHERE p.teams IS NOT EMPTY

An identification variable always designates a reference to a single value whose typeis that of the expression used in the declaration. There are two kindsof declarations: range variable and collection member.

Range Variable Declarations

To declare an identification variable as an abstract schema type, you specify arange variable declaration. In other words, an identification variable can range over theabstract schema type of an entity. In the following example, an identification variablenamedp represents the abstract schema namedPlayer:

FROM Player p

A range variable declaration can include the optionalAS operator:

FROM Player AS p

In most cases, to obtain objects a query uses path expressions to navigatethrough the relationships. But for those objects that cannot be obtained by navigation,you can use a range variable declaration to designate a starting point (or root).

If the query compares multiple values of the same abstract schema type, thentheFROM clause must declare multiple identification variables for the abstract schema:

FROM Player p1, Player p2

For a sample of such a query, seeComparison Operators.

Collection Member Declarations

In a one-to-many relationship, the multiple side consists of a collection of entities.An identification variable can represent a member of this collection. To access acollection member, the path expression in the variable’s declaration navigates through the relationships inthe abstract schema. (For more information on path expressions, see the following section.)Because a path expression can be based on another path expression, the navigationcan traverse several relationships. SeeTraversing Multiple Relationships.

A collection member declaration must include theIN operator, but it can omitthe optionalAS operator.

In the following example, the entity represented by the abstract schema namedPlayerhas a relationship field calledteams. The identification variable calledt represents asingle member of theteams collection.

FROM Player p, IN (p.teams) t

Joins

TheJOIN operator is used to traverse over relationships between entities, and isfunctionally similar to theIN operator.

In the following example, the query joins over the relationship between customers andorders:

SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1 AND o.totalPrice > 10000

TheINNER keyword is optional:

SELECT c FROM Customer c INNER JOIN c.orders o WHERE c.status = 1 AND o.totalPrice > 10000

These examples are equivalent to the following query, which uses theIN operator:

SELECT c FROM Customer c, IN(c.orders) o WHERE c.status = 1 AND o.totalPrice > 10000

You can also join a single-valued relationship.

SELECT t FROM Team t JOIN t.league l WHERE l.sport = :sport

ALEFT JOIN orLEFT OUTER JOIN retrieves a set of entities where matching values inthe join condition may be absent. TheOUTER keyword is optional.

SELECT c.name, o.totalPriceFROM Order o LEFT JOIN o.customer c

AFETCH JOIN is a join operation that returns associated entities as a side-effectof running the query. In the following example, the query returns a setof departments, and as a side-effect, the associated employees of the departments, eventhough the employees were not explicitly retrieved by theSELECT clause.

SELECT dFROM Department d LEFT JOIN FETCH d.employeesWHERE d.deptno = 1

Path Expressions

Path expressions are important constructs in the syntax of the query language, forseveral reasons. First, they define navigation paths through the relationships in the abstract schema.These path definitions affect both the scope and the results of a query.Second, they can appear in any of the main clauses of a query(SELECT,DELETE,HAVING,UPDATE,WHERE,FROM,GROUP BY,ORDER BY). Finally, although much ofthe query language is a subset of SQL, path expressions are extensions notfound in SQL.

Examples of Path Expressions

Here, theWHERE clause contains asingle_valued_path_expression. Thep is an identificationvariable, andsalary is a persistent field ofPlayer.

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

Here, theWHERE clause also contains asingle_valued_path_expression. Thet is anidentification variable,league is a single-valued relationship field, andsport is a persistentfield ofleague.

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

Here, theWHERE clause contains acollection_valued_path_expression. Thep is an identificationvariable, andteams designates a collection-valued relationship field.

SELECT DISTINCT pFROM Player p WHERE p.teams IS EMPTY

Expression Types

The type of a path expression is the type of the objectrepresented by the ending element, which can be one of the following:

• Persistent field

• Single-valued relationship field

• Collection-valued relationship field

For example, the type of the expressionp.salary isdouble because theterminating persistent field (salary) is adouble.

In the expressionp.teams, the terminating element is a collection-valued relationship field (teams).This expression’s type is a collection of the abstract schema type namedTeam.BecauseTeam is the abstract schema name for theTeam entity, this typemaps to the entity. For more information on the type mapping of abstractschemas, see the sectionReturn Types.

Navigation

A path expression enables the query to navigate to related entities. The terminatingelements of an expression determine whether navigation is allowed. If an expression containsa single-valued relationship field, the navigation can continue to an object that isrelated to the field. However, an expression cannot navigate beyond a persistent fieldor a collection-valued relationship field. For example, the expressionp.teams.league.sport is illegal, becauseteamsis a collection-valued relationship field. To reach thesport field, theFROM clause coulddefine an identification variable namedt for theteams field:

FROM Player AS p, IN (p.teams) t WHERE t.league.sport = ’soccer’

WHERE Clause

TheWHERE clause specifies a conditional expression that limits the values returned bythe query. The query returns all corresponding values in the data store forwhich the conditional expression isTRUE. Although usually specified, theWHERE clause is optional.If theWHERE clause is omitted, then the query returns all values. Thehigh-level syntax for theWHERE clause follows:

where_clause ::= WHERE conditional_expression

Literals

There are four kinds of literals: string, numeric, Boolean, and enum.

String Literals

A string literal is enclosed in single quotes:

’Duke’

If a string literal contains a single quote, you indicate the quote byusing two single quotes:

’Duke’’s’

Like a JavaString, a string literal in the query language uses theUnicode character encoding.

Numeric Literals

There are two types of numeric literals: exact and approximate.

An exact numeric literal is a numeric value without a decimal point, suchas 65,– 233, and +12. Using the Java integer syntax, exact numeric literalssupport numbers in the range of a Javalong.

An approximate numeric literal is a numeric value in scientific notation, such as57.,– 85.7, and +2.1. Using the syntax of the Java floating-point literal, approximatenumeric literals support numbers in the range of a Javadouble.

Boolean Literals

A Boolean literal is eitherTRUE orFALSE. These keywords are not case-sensitive.

Enum Literals

The Java Persistence Query Language supports the use of enum literals using theJava enum literal syntax. The enum class name must be specified as fullyqualified class name.

SELECT e FROM Employee e WHERE e.status = com.xyz.EmployeeStatus.FULL_TIME

Input Parameters

An input parameter can be either a named parameter or a positional parameter.

A named input parameter is designated by a colon (:) followed by astring. For example,:name.

A positional input parameter is designated by a question mark (?) followed byan integer. For example, the first input parameter is?1, the second is?2, and so forth.

The following rules apply to input parameters:

• They can be used only in aWHERE orHAVING clause.

• Positional parameters must be numbered, starting with the integer 1.

• Named parameters and positional parameters may not be mixed in a single query.

• Named parameters are case-sensitive.

Conditional Expressions

AWHERE clause consists of a conditional expression, which is evaluated from leftto right within a precedence level. You can change the order of evaluationby using parentheses.

Operators and Their Precedence

Table 27-2 lists the query language operators in order of decreasing precedence.

Table 27-2 Query Language Order Precedence

BETWEEN Expressions

ABETWEEN expression determines whether an arithmetic expression falls within a range ofvalues.

These two expressions are equivalent:

p.age BETWEEN 15 AND 19 p.age >= 15 AND p.age <= 19

The following two expressions are also equivalent:

p.age NOT BETWEEN 15 AND 19 p.age < 15 OR p.age > 19

If an arithmetic expression has aNULL value, then the value of theBETWEEN expression is unknown.

IN Expressions

AnIN expression determines whether or not a string belongs to a setof string literals, or whether a number belongs to a set of numbervalues.

The path expression must have a string or numeric value. If thepath expression has aNULL value, then the value of theIN expressionis unknown.

In the following example, if the country isUK the expression isTRUE.If the country isPeru it isFALSE.

o.country IN (’UK’, ’US’, ’France’)

You may also use input parameters:

o.country IN (’UK’, ’US’, ’France’, :country)

LIKE Expressions

ALIKE expression determines whether a wildcard pattern matches a string.

The path expression must have a string or numeric value. If this valueisNULL, then the value of theLIKE expression is unknown. Thepattern value is a string literal that can contain wildcard characters. The underscore(_) wildcard character represents any single character. The percent (%) wildcard character represents zeroor more characters. TheESCAPE clause specifies an escape character for the wildcardcharacters in the pattern value.Table 27-3 shows some sampleLIKE expressions.

Table 27-3LIKE Expression Examples

NULL Comparison Expressions

ANULL comparison expression tests whether a single-valued path expression or an inputparameter has aNULL value. Usually, theNULL comparison expression is used totest whether or not a single-valued relationship has been set.

SELECT t FROM Team t WHERE t.league IS NULL

This query selects all teams where the league relationship is not set. Pleasenote, the following query isnot equivalent:

SELECT t FROM Team t WHERE t.league = NULL

The comparison withNULL using the equals operator (=) always returns an unknownvalue, even if the relationship is not set. The second query will alwaysreturn an empty result.

Empty Collection Comparison Expressions

TheIS [NOT] EMPTY comparison expression tests whether a collection-valued path expression has no elements.In other words, it tests whether or not a collection-valued relationship has beenset.

If the collection-valued path expression isNULL, then the empty collection comparison expressionhas aNULL value.

Here is an example that finds all orders that do not haveany line items:

SELECT oFROM Order oWHERE o.lineItems IS EMPTY

Collection Member Expressions

The[NOT]MEMBER [OF] collection member expression determines whether a value is a memberof a collection. The value and the collection members must have the sametype.

If either the collection-valued or single-valued path expression is unknown, then the collectionmember expression is unknown. If the collection-valued path expression designates an empty collection,then the collection member expression isFALSE.

TheOF keyword is optional.

The following example tests whether a line item is part of anorder:

SELECT o FROM Order o WHERE :lineItem MEMBER OF o.lineItems

Subqueries

Subqueries may be used in theWHERE orHAVING clause of a query.Subqueries must be surrounded by parentheses.

The following example find all customers who have placed more than 10 orders:

SELECT cFROM Customer cWHERE (SELECT COUNT(o) FROM c.orders o) > 10

EXISTS Expressions

The[NOT] EXISTS expression is used with a subquery, and is true only ifthe result of the subquery consists of one or more values and isfalse otherwise.

The following example finds all employees whose spouse is also an employee:

SELECT DISTINCT empFROM Employee empWHERE EXISTS (    SELECT spouseEmp    FROM Employee spouseEmp    WHERE spouseEmp = emp.spouse)

ALL andANY Expressions

TheALL expression is used with a subquery, and is true if allthe values returned by the subquery are true, or if the subquery isempty.

TheANY expression is used with a subquery, and is true if someof the values returned by the subquery are true. AnANY expression isfalse if the subquery result is empty, or if all the valuesreturned are false. TheSOME keyword is synonymous withANY.

TheALL andANY expressions are used with the=,<,<=,>,>=,<> comparison operators.

The following example finds all employees whose salary is higher than the salaryof the managers in the employee’s department:

SELECT empFROM Employee empWHERE emp.salary > ALL (    SELECT m.salary    FROM Manager m    WHERE m.department = emp.department)

Functional Expressions

The query language includes several string and arithmetic functions which may be usedin theWHERE orHAVING clause of a query. The functions are listedin the following tables. InTable 27-4, thestart andlength arguments areof typeint. They designate positions in theString argument. The first positionin a string is designated by 1. InTable 27-5, thenumber argument can beeither anint, afloat, or adouble.

Table 27-4 String Expressions

TheCONCAT function concatenates two strings into one string.

TheLENGTH function returns the length of a string in characters as aninteger.

TheLOCATE function returns the position of a given string within a string.It returns the first position at which the string was found as aninteger. The first argument is the string to be located. The second argumentis the string to be searched. The optional third argument is an integerthat represents the starting string position. By default,LOCATE starts at thebeginning of the string. The starting position of a string is1. Ifthe string cannot be located,LOCATE returns0.

TheSUBSTRING function returns a string that is a substring of the firstargument based on the starting position and length.

TheTRIM function trims the specified character from the beginning and/or end ofa string. If no character is specified,TRIM removes spaces or blanksfrom the string. If the optionalLEADING specification is used,TRIM removesonly the leading characters from the string. If the optionalTRAILING specification is used,TRIM removes only the trailing characters from the string. The default isBOTH,which removes the leading and trailing characters from the string.

TheLOWER andUPPER functions convert a string to lower or upper case,respectively.

Table 27-5 Arithmetic Expressions

TheABS function takes a numeric expression and returns a number of thesame type as the argument.

TheMOD function returns the remainder of the first argument divided by thesecond.

TheSQRT function returns the square root of a number.

TheSIZE function returns an integer of the number of elements in thegiven collection.

NULL Values

If the target of a reference is not in the persistent store, thenthe target isNULL. For conditional expressions containingNULL, the query language uses thesemantics defined by SQL92. Briefly, these semantics are as follows:

• If a comparison or arithmetic operation has an unknown value, it yields aNULL value.

• TwoNULL values are not equal. Comparing twoNULL values yields an unknown value.

• TheIS NULL test converts aNULL persistent field or a single-valued relationship field toTRUE. TheIS NOT NULL test converts them toFALSE.

• Boolean operators and conditional tests use the three-valued logic defined byTable 27-6 andTable 27-7. (In these tables, T stands forTRUE, F forFALSE, and U for unknown.)

Table 27-6AND Operator Logic

Table 27-7OR Operator Logic

Equality Semantics

In the query language, only values of the same type can be compared.However, this rule has one exception: Exact and approximate numeric values can becompared. In such a comparison, the required type conversion adheres to the rulesof Java numeric promotion.

The query language treats compared values as if they were Java types andnot as if they represented types in the underlying data store. Forexample, if a persistent field could be either an integer or aNULL, thenit must be designated as anInteger object and not as anintprimitive. This designation is required because a Java object can beNULL buta primitive cannot.

Two strings are equal only if they contain the same sequence of characters.Trailing blanks are significant; for example, the strings’abc’ and’abc ’ arenot equal.

Two entities of the same abstract schema type are equal only if theirprimary keys have the same value.Table 27-8 shows the operator logic ofa negation, andTable 27-9 shows the truth values of conditional tests.

Table 27-8NOT Operator Logic

Table 27-9 Conditional Test

SELECT Clause

TheSELECT clause defines the types of the objects or values returned bythe query.

Return Types

The return type of theSELECT clause is defined by the result typesof the select expressions contained within it. If multiple expressions are used, theresult of the query is anObject[], and the elements in the arraycorrespond to the order of the expressions in theSELECT clause, and intype to the result types of each expression.

ASELECT clause cannot specify a collection-valued expression. For example, theSELECT clausep.teams is invalid becauseteams is a collection. However, the clause in the followingquery is valid because thet is a single element of theteamscollection:

SELECT tFROM Player p, IN (p.teams) t

The following query is an example of a query with multiple expressions inthe select clause:

SELECT c.name, c.country.name FROM customer c WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

It returns a list ofObject[] elements where the first array element isa string denoting the customer name and the second array element is astring denoting the name of the customer’s country.

Aggregate Functions in theSELECT Clause

The result of a query may be the result of an aggregatefunction, listed inTable 27-10.

Table 27-10 Aggregate Functions in Select Statements

For select method queries with an aggregate function (AVG,COUNT,MAX,MIN,orSUM) in theSELECT clause, the following rules apply:

• For theAVG,MAX,MIN, andSUM functions, the functions returnnull if there are no values to which the function can be applied.

• For theCOUNT function, if there are no values to which the function can be applied,COUNT returns 0.

The following example returns the average order quantity:

SELECT AVG(o.quantity) FROM Order o

The following example returns the total cost of the items ordered by RoxaneCoss:

SELECT SUM(l.price)FROM Order o JOIN o.lineItems l JOIN o.customer cWHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

The following example returns the total number of orders:

SELECT COUNT(o)FROM Order o

The following example returns the total number of items in Hal Incandenza’s orderthat have prices:

SELECT COUNT(l.price)FROM Order o JOIN o.lineItems l JOIN o.customer cWHERE c.lastname = ’Incandenza’ AND c.firstname = ’Hal’

TheDISTINCT Keyword

TheDISTINCT keyword eliminates duplicate return values. If a query returns ajava.util.Collection, whichallows duplicates, then you must specify theDISTINCT keyword to eliminate duplicates.

Constructor Expressions

Constructor expressions allow you to return Java instances that store a query resultelement instead of anObject[].

The following query creates aCustomerDetail instance perCustomer matching theWHERE clause. ACustomerDetail stores the customer name and customer’s country name. So the query returnsaList ofCustomerDetail instances:

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name) FROM customer cWHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

ORDER BY Clause

As its name suggests, theORDER BY clause orders the values or objects returnedby the query.

If theORDER BY clause contains multiple elements, the left-to-right sequence of the elementsdetermines the high-to-low precedence.

TheASC keyword specifies ascending order (the default), and theDESC keyword indicates descendingorder.

When using theORDER BY clause, theSELECT clause must return an orderable setof objects or values. You cannot order the values or objects for valuesor objects not returned by theSELECT clause. For example, the following queryis valid because theORDER BY clause uses the objects returned by theSELECTclause:

SELECT oFROM Customer c JOIN c.orders o JOIN c.address aWHERE a.state = ’CA’ORDER BY o.quantity, o.totalcost

The following example isnot valid because theORDER BY clause uses avalue not returned by theSELECT clause:

SELECT p.product_nameFROM Order o, IN(o.lineItems) l JOIN o.customer cWHERE c.lastname = ’Faehmel’ AND c.firstname = ’Robert’ORDER BY o.quantity

TheGROUP BY Clause

TheGROUP BY clause allows you to group values according to a set ofproperties.

The following query groups the customers by their country and returns the numberof customers per country:

SELECT c.country, COUNT(c) FROM Customer c GROUP BY c.country

TheHAVING Clause

TheHAVING clause is used with theGROUP BY clause to further restrictthe returned result of a query.

The following query groups orders by the status of their customer and returnsthe customer status plus the averagetotalPrice for all orders where the correspondingcustomers has the same status. In addition, it considers only customers with status1,2, or3, so orders of other customers are not taken intoaccount:

SELECT c.status, AVG(o.totalPrice) FROM Order o JOIN o.customer cGROUP BY c.status HAVING c.status IN (1, 2, 3)

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.Legal Notices