A select statement is a string which consists of the following clauses:

In BNF syntax, a select statement is defined as:

A select statement must always have a SELECT and a FROM clause. The square brackets [] indicate that the other clauses are optional.

Update and delete statements provide bulk operations over sets of entities. In BNF syntax, these operations are defined as:

The update and delete clauses determine the type of the entities to be updated or deleted. The WHERE clause may be used to restrict the scope of the update or delete operation. Update and delete statements are described further in Section 2.10, “ JPQL Bulk Update and Delete ”.

Entities are designated in query strings by their entity names. The entity name is defined by the name element of the Entity annotation (or the entity-name XML descriptor element), and defaults to the unqualified name of the entity class. Entity names are scoped within the persistence unit and must be unique within the persistence unit.

This example assumes that the application developer provides several entity classes, representing magazines, publishers, authors, and articles. The abstract schema types for these entities are Magazine, Publisher, Author, and Article.

Several Entities with Abstract Persistence Schemas Defined in the Same Persistence Unit. The entity Publisher has a one-to-many relationships with Magazine. There is also a one-to-many relationship between Magazine and Article . The entity Article is related to Author in a one-to-one relationship.

Queries to select magazines can be defined by navigating over the association-fields and state-fields defined by Magazine and Author. A query to find all magazines that have unpublished articles is as follows:

SELECT DISTINCT mag FROM Magazine AS mag JOIN mag.articles AS art WHERE art.published = FALSE

This query navigates over the association-field authors of the abstract schema type Magazine to find articles, and uses the state-field published of Article to select those magazines that have at least one article that is not published. Although predefined reserved identifiers, such as DISTINCT, FROM, AS, JOIN, WHERE, and FALSE appear in upper case in this example, predefined reserved identifiers are case insensitive.

The SELECT clause of this example designates the return type of this query to be of type Magazine.

Because the same persistence unit defines the abstract persistence schemas of the related entities, the developer can also specify a query over articles that utilizes the abstract schema type for products, and hence the state-fields and association-fields of both the abstract schema types Magazine and Author. For example, if the abstract schema type Author has a state-field named firstName, a query over articles can be specified using this state-field. Such a query might be to find all magazines that have articles authored by someone with the first name "John".

SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'

Because Magazine is related to Author by means of the relationships between Magazine and Article and between Article and Author, navigation using the association-fields authors and product is used to express the query. This query is specified by using the abstract schema name Magazine, which designates the abstract schema type over which the query ranges. The basis for the navigation is provided by the association-fields authors and product of the abstract schema types Magazine and Article respectively.

An identifier is a character sequence of unlimited length. The character sequence must begin with a Java identifier start character, and all other characters must be Java identifier part characters. An identifier start character is any character for which the method Character.isJavaIdentifierStart returns true. This includes the underscore (_) character and the dollar sign ($) character. An identifier part character is any character for which the method Character.isJavaIdentifierPart returns true. The question mark (?) character is reserved for use by the Java Persistence query language. The following are reserved identifiers:

Reserved identifiers are case insensitive. Reserved identifiers must not be used as identification variables or result variables.

An identification variable is a valid identifier declared in the FROM clause of a query.

All identification variables must be declared in the FROM clause. Identification variables cannot be declared in other clauses.

An identification variable must not be a reserved identifier or have the same name as any entity in the same persistence unit.

Identification variables are case insensitive.

An identification variable evaluates to a value of the type of the expression used in declaring the variable. For example, consider the previous query:

SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John'

In the FROM clause declaration mag.articles art, the identification variable art evaluates to any Article value directly reachable from Magazine. The association-field articles is a collection of instances of the abstract schema type Article and the identification variable art refers to an element of this collection. The type of auth is the abstract schema type of Author.

An identification variable can range over an entity, embeddable, or basic abstract schema type. An identification variable designates an instance of an entity abstract schema type or an element of a collection of entity abstract schema type instances.

Note that for identification variables referring to an instance of an association or collection represented as a java.util.Map, the identification variable is of the abstract schema type of the map value.

An identification variable always designates a reference to a single value. It is declared in one of three ways: in a range variable declaration, in a join clause, or in a collection member declaration. The identification variable declarations are evaluated from left to right in the FROM clause, and an identification variable declaration can use the result of a preceding identification variable declaration of the query string.

All identification variables used in the SELECT, WHERE, ORDER BY, GROUP BY, or HAVING clause of a SELECT or DELETE statement must be declared in the FROM clause. The identification variables used in the WHERE clause of an UPDATE statement must be declared in the UPDATE clause.

Identification variables are existentially quantified in these clauses. This means that an identification variable represents a member of a collection or an instance of an entity’s abstract schema type. An identification variable never designates a collection in its entirety.

An identification variable is scoped to the query (or subquery) in which it is defined and is also visible to any subqueries within that query scope that do not define an identification variable of the same name.

The syntax for declaring an identification variable as a range variable is similar to that of SQL; optionally, it uses the AS keyword. A range variable designates an entity abstract schema type.

Range variable declarations allow the developer to designate a "root" for objects which may not be reachable by navigation.

In order to select values by comparing more than one instance of an entity abstract schema type, more than one identification variable ranging over the abstract schema type is needed in the FROM clause.

The following query returns magazines whose price is greater than the price of magazines published by "Adventure" publishers. This example illustrates the use of two different identification variables in the FROM clause, both of the abstract schema type Magazine. The SELECT clause of this query determines that it is the magazines with prices greater than those of "Adventure" publisher's that are returned.

SELECT DISTINCT mag1 FROM Magazine mag1, Magazine mag2
WHERE mag1.price > mag2.price AND mag2.publisher.name = 'Adventure'

An identification variable followed by the navigation operator (.) and a state-field or association-field is a path expression. The type of the path expression is the type computed as the result of navigation; that is, the type of the state-field or association-field to which the expression navigates.

An identification variable qualified by the KEY, VALUE, or ENTRY operator is a path expression. The KEY, VALUE, and ENTRY operators may only be applied to identification variables that correspond to map-valued associations or map-valued element collections. The type of the path expression is the type computed as the result of the operation; that is, the abstract schema type of the field that is the value of the KEY, VALUE, or ENTRY operator (the map key, map value, or map entry respectively).

The syntax for qualified identification variables is as follows.

A path expression using the KEY or VALUE operator may be further composed. A path expression using the ENTRY operator is terminal. It cannot be further composed and can only appear in the SELECT list of a query.

In the following query, photos is a map from photo label to filename.

SELECT i.name, VALUE(p)
FROM Item i JOIN i.photos p
WHERE KEY(p) LIKE ‘egret’

In the above query the identification variable p designates an abstract schema type corresponding to the map value. The results of VALUE(p) and KEY(p) are the map value and the map key associated with p, respectively. The following query is equivalent:

SELECT i.name, p
FROM Item i JOIN i.photos p
WHERE KEY(p) LIKE ‘egret’

Depending on navigability, a path expression that leads to a association-field or to a field whose type is an embeddable class may be further composed. Path expressions can be composed from other path expressions if the original path expression evaluates to a single-valued type (not a collection) corresponding to a association-field.

In the following example, contactInfo denotes an embeddable class consisting of an address and set of phones. Phone is an entity.

SELECT p.vendor
FROM Employee e JOIN e.contactInfo.phones p
WHERE e.contactInfo.address.zipcode = '95054'

Path expression navigability is composed using "inner join" semantics. That is, if the value of a non-terminal association-field in the path expression is null, the path is considered to have no value, and does not participate in the determination of the result.

The following query is equivalent to the query above:

SELECT p.vendor
FROM Employee e JOIN e.contactInfo c JOIN c.phones p
WHERE e.contactInfo.address.zipcode = '95054'

The syntax for single-valued path expressions and collection valued path expressions is as follows:

A single_valued_object_field is designated by the name of an association-field in a one-to-one or many-to-one relationship or a field of embeddable class type. The type of a single_valued_object_field is the abstract schema type of the related entity or embeddable class.

A state_field is designated by the name of an entity or embeddable class state field that corresponds to a basic type.

A collection_valued_field is designated by the name of an association-field in a one-to-many or a many-to-many relationship or by the name of an element collection field. The type of a collection_valued_field is a collection of values of the abstract schema type of the related entity or element type.

An identification variable used in a single_valued_object_path_expression or in a collection_valued_path_expression may be an unqualified identification variable or an identification variable to which the KEY or VALUE function has been applied.

It is syntactically illegal to compose a path expression from a path expression that evaluates to a collection. For example, if mag designates Magazine, the path expression mag.articles.author is illegal since navigation to authors results in a collection. This case should produce an error when the query string is verified. To handle such a navigation, an identification variable must be declared in the FROM clause to range over the elements of the articles collection. Another path expression must be used to navigate over each such element in the WHERE clause of the query, as in the following query which returns all authors that have any articles in any magazines:

SELECT DISTINCT art.author FROM Magazine AS mag, IN(mag.articles) art

It is illegal to use a collection_valued_path_expression other than in the FROM clause of a query except in an empty_collection_comparison_expression, in a collection_member_expression, or as an argument to the SIZE operator. See Section 2.5.12, “ JPQL Empty Collection Comparison Expressions ”, Section 2.5.13, “ JPQL Collection Member Expressions ”, and Section 2.6.2.2, “ JPQL Arithmetic Functions ”.

An inner join may be implicitly specified by the use of a cartesian product in the FROM clause and a join condition in the WHERE clause. In the absence of a join condition, this reduces to the cartesian product.

The main use case for this generalized style of join is when a join condition does not involve a foreign key relationship that is mapped to an entity relationship, for example:

SELECT c FROM Customer c, Employee e WHERE c.hatsize = e.shoesize

In general, use of this style of inner join (also referred to as theta-join) is less typical than explicitly defined joins over relationships.

The syntax for explicit join operations is as follows:

The inner and outer join operation types described in Section 2.3.5.1, “ JPQL Inner Joins (Relationship Joins) ” and Section 2.3.5.2, “ JPQL Outer Joins ” are supported.

SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue > 1000000

SELECT pub FROM Publisher pub INNER JOIN pub.magazines mag WHERE pub.revenue > 1000000

SELECT OBJECT(pub) FROM Publisher pub, IN(pub.magazines) mag WHERE pub.revenue > 1000000

SELECT p.vendor
FROM Employee e JOIN e.contactInfo c JOIN c.phones p
WHERE c.address.zipcode = '95054'

SELECT pub FROM Publisher pub LEFT JOIN pub.magazines mag WHERE pub.revenue > 1000000

SELECT pub FROM Publisher pub LEFT OUTER JOIN pub.magazines mags WHERE pub.revenue > 1000000

SELECT mag FROM Magazine mag LEFT JOIN FETCH mag.articles WHERE mag.id = 1

An identification variable declared by a collection_member_declaration ranges over values of a collection obtained by navigation using a path expression. Such a path expression represents a navigation involving the association-fields of an entity abstract schema type. Because a path expression can be based on another path expression, the navigation can use the association-fields of related entities.

An identification variable of a collection member declaration is declared using a special operator, the reserved identifier IN . The argument to the IN operator is a collection-valued path expression. The path expression evaluates to a collection type specified as a result of navigation to a collection-valued association-field of an entity or embeddable class abstract schema type.

The syntax for declaring a collection member identification variable is as follows:

For example, the query

SELECT DISTINCT mag FROM Magazine mag
    JOIN mag.articles art
    JOIN art.author auth
    WHERE auth.lastName = 'Grisham'

can equivalently be expressed as follows, using the IN operator:

SELECT DISTINCT mag FROM Magazine mag,
    IN(mag.articles) art
    WHERE art.author.lastName = 'Grisham'

In this example, articles is the name of an association-field whose value is a collection of instances of the abstract schema type Article. The identification variable art designates a member of this collection, a single Article abstract schema type instance. In this example, mag is an identification variable of the abstract schema type Magazine.

The Java Persistence query language treats the FROM clause similarly to SQL in that the declared identification variables affect the results of the query even if they are not used in the WHERE clause. Application developers should use caution in defining identification variables because the domain of the query can depend on whether there are any values of the declared type.

For example, the FROM clause below defines a query over all orders that have line items and existing products. If there are no Product instances in the database, the domain of the query is empty and no order is selected.

SELECT o
FROM Order AS o JOIN o.lineItems l JOIN l.product p

Java Persistence queries are automatically polymorphic. The FROM clause of a query designates not only instances of the specific entity classes to which explicitly refers but of subclasses as well. The instances returned by a query include instances of the subclasses that satisfy the query criteria.

Non-polymorphic queries or queries whose polymorphism is restricted can be specified using entity type expressions in the WHERE clause to restrict the domain of the query. See Section 2.6.4, “ Entity Type Expressions ”.

A string literal is enclosed in single quotes--for example: 'literal'. A string literal that includes a single quote is represented by two single quotes--for example: 'literal''s'. String literals in queries, like Java String literals, use unicode character encoding. The use of Java escape notation is not supported in query string literals.

Exact numeric literals support the use of Java integer literal syntax as well as SQL exact numeric literal syntax.

Approximate literals support the use of Java floating point literal syntax as well as SQL approximate numeric literal syntax.

Enum literals support the use of Java enum literal syntax. The enum class name must be specified.

Appropriate suffixes can be used to indicate the specific type of a numeric literal in accordance with the Java Language Specification. The boolean literals are TRUE and FALSE. Although predefined reserved literals appear in upper case, they are case insensitive.

The JDBC escape syntax may be used for the specification of date, time, and timestamp literals. For example:

SELECT o
FROM Customer c JOIN c.orders o
WHERE c.name = 'Smith'
AND o.submissionDate < {d '2008-12-31'}
            

Date, time, and timestamp literals are passed as is to the JDBC driver in use.

Entity type literals are specified by entity names—for example: Customer.

Although reserved literals appear in upper case, they are case insensitive.

All identification variables used in the WHERE or HAVING clause of a SELECT or DELETE statement must be declared in the FROM clause, as described in Section 2.3.2, “ JPQL Identification Variables ”. The identification variables used in the WHERE clause of an UPDATE statement must be declared in the UPDATE clause.

Identification variables are existentially quantified in the WHERE and HAVING clause. This means that an identification variable represents a member of a collection or an instance of an entity's abstract schema type. An identification variable never designates a collection in its entirety.

It is illegal to use a collection_valued_path_expression within a WHERE or HAVING clause as part of a conditional expression except in an empty_collection_comparison_expression, in a collection_member_expression, or as an argument to the SIZE operator.

Either positional or named parameters may be used. Positional and named parameters may not be mixed in a single query.

Input parameters can only be used in the WHERE clause or HAVING clause of a query.

Note that if an input parameter value is null, comparison operations or arithmetic operations involving the input parameter will return an unknown value. See Section 2.11, “ JPQL Null Values ”.

All input parameters must be single-valued, except in IN expressions (see Section 2.5.9, “ JPQL In Expressions ” ), which support the use of collection-valued input parameters.

SELECT pub FROM Publisher pub WHERE pub.revenue > :rev

Conditional expressions are composed of other conditional expressions, comparison operations, logical operations, path expressions that evaluate to boolean values, boolean literals, and boolean input parameters.

The scalar expressions described in Section 2.6, “ JPQL Scalar Expressions ” can be used in conditional expressions.

Standard bracketing () for ordering expression evaluation is supported.

Aggregate functions can only be used in conditional expressions in a HAVING clause. See Section 2.7, “ JPQL GROUP BY, HAVING ”.

Conditional expressions are defined as follows:

The operators are listed below in order of decreasing precedence.

The following sections describe other operators used in specific expressions.

The syntax for the use of comparison expressions in a conditional expression is as follows:

Examples:

item.cost * 1.08 <= 100.00

CONCAT(person.lastName, ‘, ’, person.firstName)) = ‘Jones, Sam’

TYPE(e) = ExemptEmployee

The syntax for the use of the comparison operator [ NOT ] BETWEEN in a conditional expression is as follows:

arithmetic_expression [NOT] BETWEEN arithmetic_expression AND arithmetic_expression | string_expression [NOT] BETWEEN string_expression AND string_expression | datetime_expression [NOT] BETWEEN datetime_expression AND datetime_expression

The BETWEEN expression

x BETWEEN y AND z

is semantically equivalent to:

y <= x AND x <= z

The rules for unknown and NULL values in comparison operations apply. See Section 2.11, “ JPQL Null Values ” .

Examples are:

p.age BETWEEN 15 and 19

is equivalent to:

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

The following expression:

p.age NOT BETWEEN 15 and 19

excludes the range, and is equivalent to:

p.age < 15 OR p.age > 19

In the following example, transactionHistory is a list of credit card transactions defined using an order column.

SELECT t
FROM CreditCard c JOIN c.transactionHistory t
WHERE c.holder.name = ‘John Doe’ AND INDEX(t) BETWEEN 0 AND 9

The syntax for the use of the comparison operator [ NOT ] IN in a conditional expression is as follows:

The state_field_path_expression must have a string, numeric, date, time, timestamp, or enum value.

The literal and/or input_parameter values must be like the same abstract schema type of the state_field_path_expression in type. (See Section 2.12, “ JPQL Equality and Comparison Semantics ” ).

The results of the subquery must be like the same abstract schema type of the state_field_path_expression in type. Subqueries are discussed in Section 2.5.16, “ JPQL Subqueries ”.

Examples:

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

(o.country = 'UK') OR (o.country = 'US') OR (o.country = ' France')

o.country NOT IN ('UK', 'US', 'France')

NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'France'))

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If the value of a state_field_path_expression or in_item in an IN or NOT IN expression is NULL or unknown, the value of the expression is unknown.

Note that use of a collection-valued input parameter will mean that a static query cannot be precompiled.

The syntax for the use of the comparison operator [ NOT ] LIKE in a conditional expression is as follows:

like_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters (including the empty sequence), and all other characters stand for themselves. The optional escape_character is a single-character string literal or a character-valued input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore and percent characters in pattern_value.

Examples:

If the value of the string_expression or pattern_value is NULL or unknown, the value of the LIKE expression is unknown. If the escape_character is specified and is NULL, the value of the LIKE expression is unknown.

The syntax for the use of the comparison operator IS NULL in a conditional expression is as follows:

null_comparison_expression ::= {single_valued_path_expression | input_parameter } IS [NOT] NULL

A null comparison expression tests whether or not the single-valued path expression or input parameter is a NULL value.

Null comparisons over instances of embeddable class types are not supported.

The syntax for the use of the comparison operator IS EMPTY in an empty_collection_comparison_expression is as follows:

empty_collection_comparison_expression ::= collection_valued_path_expression IS [NOT] EMPTY

This expression tests whether or not the collection designated by the collection-valued path expression is empty (i.e. has no elements).

For example, the following query will return all magazines that don't have any articles at all:

SELECT mag FROM Magazine mag WHERE mag.articles IS EMPTY

If the value of the collection-valued path expression in an empty collection comparison expression is unknown, the value of the empty comparison expression is unknown.

The syntax for the use of the comparison operator MEMBER OF in an collection_member_expression is as follows:

This expression tests whether the designated value is a member of the collection specified by the collection-valued path expression.

If the collection valued path expression designates an empty collection, the value of the MEMBER OF expression is FALSE and the value of the NOT MEMBER OF expression is TRUE. Otherwise, if the value of the collection_valued_path_expression or entity_or_value_expression in the collection member expression is NULL or unknown, the value of the collection member expression is unknown.

The use of the reserved word OF is optional in this expression.

Example:

SELECT p
FROM Person p
WHERE 'Joe' MEMBER OF p.nicknames
                

An EXISTS expression is a predicate that is true only if the result of the subquery consists of one or more values and that is false otherwise. The syntax of an exists expression is

Example:

SELECT DISTINCT auth FROM Author auth
    WHERE EXISTS
        (SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)

The result of this query consists of all authors whose spouse is also an author.

An ALL conditional expression is a predicate over a subquery that is true if the comparison operation is true for all values in the result of the subquery or the result of the subquery is empty. An ALL conditional expression is false if the result of the comparison is false for at least one value of the result of the subquery, and is unknown if neither true nor false.

An ANY conditional expression is a predicate over a subquery that is true if the comparison operation is true for some value in the result of the subquery. An ANY conditional expression is false if the result of the subquery is empty or if the comparison operation is false for every value in the result of the subquery, and is unknown if neither true nor false. The keyword SOME is synonymous with ANY.

The comparison operators used with ALL or ANY conditional expressions are =, <, <=, >, >=, <>. The result of the subquery must be like that of the other argument to the comparison operator in type. See Section 2.12, “ JPQL Equality and Comparison Semantics ”. The syntax of an ALL or ANY expression is specified as follows:

The following example select the authors who make the highest salary for their magazine:

SELECT auth FROM Author auth
    WHERE auth.salary >= ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine)

Subqueries may be used in the WHERE or HAVING clause. The syntax for subqueries is as follows:

Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of the specification.

Examples:

SELECT DISTINCT auth FROM Author auth
    WHERE EXISTS (SELECT spouseAuth FROM Author spouseAuth WHERE spouseAuth = auth.spouse)

SELECT mag FROM Magazine mag
    WHERE (SELECT COUNT(art) FROM mag.articles art) > 10

Note that some contexts in which a subquery can be used require that the subquery be a scalar subquery (i.e., produce a single result). This is illustrated in the following example involving a numeric comparison operation.

SELECT goodPublisher FROM Publisher goodPublisher
    WHERE goodPublisher.revenue < (SELECT AVG(p.revenue) FROM Publisher p)

SELECT goodCustomer
FROM Customer goodCustomer
WHERE goodCustomer.balanceOwed < (
SELECT AVG(c.balanceOwed)/2.0 FROM Customer c)

The arithmetic operators are:

Arithmetic operations use numeric promotion.

Arithmetic functions are described in Section 2.6.2.2, “ JPQL Arithmetic Functions ”.

JPQL includes the built-in functions described in subsections Section 2.6.2.1, “ JPQL String Functions ”, Section 2.6.2.2, “ JPQL Arithmetic Functions ”, Section 2.6.2.3, “ JPQL Datetime Functions ”, which may be used in the SELECT, WHERE or HAVING clause of a query.

If the value of any argument to a functional expression is null or unknown, the value of the functional expression is unknown.

The following forms of case expressions are supported: general case expressions, simple case expressions, coalesce expressions, and nullif expressions.

Examples:

UPDATE Employee e
SET e.salary =
    CASE WHEN e.rating = 1 THEN e.salary * 1.1
         WHEN e.rating = 2 THEN e.salary * 1.05
         ELSE e.salary * 1.01
    END

UPDATE Employee e
SET e.salary =
    CASE e.rating WHEN 1 THEN e.salary * 1.1
                  WHEN 2 THEN e.salary * 1.05
                  ELSE e.salary * 1.01
    END

SELECT e.name,
    CASE TYPE(e) WHEN Exempt THEN 'Exempt'
                 WHEN Contractor THEN 'Contractor'
                 WHEN Intern THEN 'Intern'
                 ELSE 'NonExempt'
    END
FROM Employee e
WHERE e.dept.name = 'Engineering'

SELECT e.name,
       f.name,
       CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum '
                   WHEN f.annualMiles > 25000 THEN 'Gold '
                   ELSE ''
                   END,
              'Frequent Flyer')
FROM Employee e JOIN e.frequentFlierPlan f  

An entity type expression can be used to restrict query polymorphism. The TYPE operator returns the exact type of the argument.

The syntax of an entity type expression is as follows:

An entity_type_literal is designated by the entity name.

The Java class of the entity is used as an input parameter to specify the entity type.

Examples:

SELECT e
FROM Employee e
WHERE TYPE(e) IN (Exempt, Contractor)

SELECT e
FROM Employee e
WHERE TYPE(e) IN (:empType1, :empType2)

SELECT e
FROM Employee e
WHERE TYPE(e) IN :empTypes

SELECT TYPE(e)
FROM Employee e
WHERE TYPE(e) <> Exempt

The type of the query result specified by the SELECT clause of a query is an entity abstract schema type, a state-field type, the result of of a scalar expression, the result of an aggregate function, the result of a construction operation, or some sequence of these.

The result type of the SELECT clause is defined by the result types of the select_expressions contained in it. When multiple select expressions are used in the SELECT clause, the result of the query is of type Object[], and the elements in this result correspond in order to the order of their specification in the SELECT clause and in type to the result types of each of the select expressions.

The type of the result of a select_expression is as follows:

A constructor may be used in the SELECT list to return one or more Java instances. The specified class is not required to be an entity or to be mapped to the database. The constructor name must be fully qualified.

If an entity class name is specified in the SELECT NEW clause, the resulting entity instances are in the new state.

If a single_valued_path_expression or identification_variable that is an argument to the constructor references an entity, the resulting entity instance referenced by that single_valued_path_expression or identification_variable will be in the managed state.

If PublisherInfo is an entity class, the following 2 queries return instances of PublisherInfo that will be in the new state. In the second example, mag is an identification_variable passed as an argument to the constructor PublisherInfo(Magazine mag); the entity instances of Magazine created during query evaluation will be in the managed state. Example:

SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
    FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00

SELECT NEW com.company.PublisherInfo(mag)
    FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00

If the result of a query corresponds to a association-field or state-field whose value is null, that null value is returned in the result of the query method. The IS NOT NULL construct can be used to eliminate such null values from the result set of the query.

Note, however, that state-field types defined in terms of Java numeric primitive types cannot produce NULL values in the query result. A query that returns such a state-field type as a result type must not return a null value.

If the result of a query corresponds to an identification variable or state field whose value is an embeddable, the embeddable instance returned by the query will not be in the managed state (i.e., it will not be part of the state of any managed entity).

In the following example, the Address instances returned by the query will reference Phone instances. While the Phone instances will be managed, the Address> instances referenced by the addr result variable will not be. Modifications to these embeddable instances are not allowed.

@Entity
public class Employee {
  @Id int id;
  Address address;
  ...
}

@Embeddable
public class Address { 
  String street;
  ...
  @OneToOne Phone phone; // fetch=EAGER
}

@Entity
public class Phone {
  @Id int id;
  ...
  @OneToOne(mappedBy="address.phone") Employee emp; // fetch=EAGER
}

SELECT e.address AS addr
FROM Employee e

The result of a query may be the result of an aggregate function applied to a path expression.

The following aggregate functions can be used in the SELECT clause of a query: AVG, COUNT, MAX, MIN, SUM.

For all aggregate functions except COUNT, the path expression that is the argument to the aggregate function must terminate in a state-field. The path expression argument to COUNT may terminate in either a state-field or a association-field, or the argument to COUNT may be an identification variable.

Arguments to the functions SUM and AVG must be numeric. Arguments to the functions MAX and MIN must correspond to orderable state-field types (i.e., numeric types, string types, character types, or date types).

The Java type that is contained in the result of a query using an aggregate function is as follows:

If SUM , AVG, MAX, or MIN is used, and there are no values to which the aggregate function can be applied, the result of the aggregate function is NULL.

If COUNT is used, and there are no values to which COUNT can be applied, the result of the aggregate function is 0.

The argument to an aggregate function may be preceded by the keyword DISTINCT to specify that duplicate values are to be eliminated before the aggregate function is applied. It is legal to specify DISTINCT with MAX or MIN, but it does not affect the result.

Null values are eliminated before the aggregate function is applied, regardless of whether the keyword DISTINCT is specified.

The use of DISTINCT with COUNT is not supported for arguments of embeddable types or map entry types.

SELECT AVG(mag.price) FROM Magazine mag

SELECT SUM(mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE pub.firstName = 'Larry'

SELECT COUNT(mag) FROM Magazine mag