| <html><head> |
| <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> |
| <title>2. JPQL Language Reference</title><link rel="stylesheet" href="css/docbook.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.72.0"><link rel="start" href="manual.html" title="Apache OpenJPA 1.2 User's Guide"><link rel="up" href="jpa_overview_query.html" title="Chapter 10. JPA Query"><link rel="prev" href="jpa_overview_query.html" title="Chapter 10. JPA Query"><link rel="next" href="jpa_overview_sqlquery.html" title="Chapter 11. SQL Queries"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">2. |
| JPQL Language Reference |
| </th></tr><tr><td width="20%" align="left"><a accesskey="p" href="jpa_overview_query.html">Prev</a> </td><th width="60%" align="center">Chapter 10. |
| JPA Query |
| </th><td width="20%" align="right"> <a accesskey="n" href="jpa_overview_sqlquery.html">Next</a></td></tr></table><hr></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="jpa_langref"></a>2. |
| JPQL Language Reference |
| </h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_stmnttypes">2.1. |
| JPQL Statement Types |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select">2.1.1. |
| JPQL Select Statement |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk">2.1.2. |
| JPQL Update and Delete Statements |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schematypes">2.2. |
| JPQL Abstract Schema Types and Query Domains |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemanaming">2.2.1. |
| JPQL Entity Naming |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemaexample">2.2.2. |
| JPQL Schema Example |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fromclause">2.3. |
| JPQL FROM Clause and Navigational Declarations |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_identifiers">2.3.1. |
| JPQL FROM Identifiers |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_vars">2.3.2. |
| JPQL Identification Variables |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_range">2.3.3. |
| JPQL Range Declarations |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path">2.3.4. |
| JPQL Path Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_Joins">2.3.5. |
| JPQL Joins |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1. |
| JPQL Inner Joins (Relationship Joins) |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2. |
| JPQL Outer Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3. |
| JPQL Fetch Joins |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_dec">2.3.6. |
| JPQL Collection Member Declarations |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_polymorph">2.3.7. |
| JPQL Polymorphism |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_where">2.4. |
| JPQL WHERE Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond">2.5. |
| JPQL Conditional Expressions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_lit">2.5.1. |
| JPQL Literals |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_idvar">2.5.2. |
| JPQL Identification Variables |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path_exp">2.5.3. |
| JPQL Path Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_input_params">2.5.4. |
| JPQL Input Parameters |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1. |
| JPQL Positional Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2. |
| JPQL Named Parameters |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond_comp">2.5.5. |
| JPQL Conditional Expression Composition |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_operators">2.5.6. |
| JPQL Operators and Operator Precedence |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_between">2.5.7. |
| JPQL Between Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_in">2.5.8. |
| JPQL In Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_like">2.5.9. |
| JPQL Like Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null">2.5.10. |
| JPQL Null Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_empty_comp">2.5.11. |
| JPQL Empty Collection Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_member">2.5.12. |
| JPQL Collection Member Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_exists">2.5.13. |
| JPQL Exists Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_all_any">2.5.14. |
| JPQL All or Any Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_subqueries">2.5.15. |
| JPQL Subqueries |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_functional">2.5.16. |
| JPQL Functional Expressions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1. |
| JPQL String Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2. |
| JPQL Arithmetic Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3. |
| JPQL Datetime Functions |
| </a></span></dt></dl></dd></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_group">2.6. |
| JPQL GROUP BY, HAVING |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select_clause">2.7. |
| JPQL SELECT Clause |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_resulttype">2.7.1. |
| JPQL Result Type of the SELECT Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_constructor">2.7.2. |
| JPQL Constructor Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_select">2.7.3. |
| JPQL Null Values in the Query Result |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_aggregates">2.7.4. |
| JPQL Aggregate Functions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1. |
| JPQL Aggregate Examples |
| </a></span></dt></dl></dd></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_orderby">2.8. |
| JPQL ORDER BY Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk_ops">2.9. |
| JPQL Bulk Update and Delete |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_values">2.10. |
| JPQL Null Values |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_equality">2.11. |
| JPQL Equality and Comparison Semantics |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bnf">2.12. |
| JPQL BNF |
| </a></span></dt></dl></div><p> |
| The Java Persistence Query Language (JPQL) is used to define searches against |
| persistent entities independent of the mechanism used to store those entities. |
| As such, JPQL is "portable", and not constrained to any particular data store. |
| The Java Persistence query language is an extension of the Enterprise JavaBeans |
| query language, <code class="literal">EJB QL</code>, adding operations such as bulk |
| deletes and updates, join operations, aggregates, projections, and subqueries. |
| Furthermore, JPQL queries can be declared statically in metadata, or can be |
| dynamically built in code. This chapter provides the full definition of the |
| language. |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> |
| Much of this section is paraphrased or taken directly from Chapter 4 of the |
| JSR 220 specification. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_stmnttypes"></a>2.1. |
| JPQL Statement Types |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select">2.1.1. |
| JPQL Select Statement |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk">2.1.2. |
| JPQL Update and Delete Statements |
| </a></span></dt></dl></div><p> |
| A JPQL statement may be either a <code class="literal">SELECT</code> statement, an |
| <code class="literal">UPDATE</code> statement, or a <code class="literal">DELETE</code> statement. |
| This chapter refers to all such statements as "queries". Where it is important |
| to distinguish among statement types, the specific statement type is referenced. |
| In BNF syntax, a query language statement is defined as: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| QL_statement ::= select_statement | update_statement | delete_statement |
| </p></li></ul></div><p> |
| The complete BNF for JPQL is defined in <a href="jpa_langref.html#jpa_langref_bnf" title="2.12. JPQL BNF">Section 2.12, “ |
| JPQL BNF |
| ”</a>. |
| Any JPQL statement may be constructed dynamically or may be statically defined |
| in a metadata annotation or XML descriptor element. All statement types may |
| have parameters, as discussed in <a href="jpa_langref.html#jpa_langref_input_params" title="2.5.4. JPQL Input Parameters">Section 2.5.4, “ |
| JPQL Input Parameters |
| ”</a>. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_select"></a>2.1.1. |
| JPQL Select Statement |
| </h4></div></div></div><p> |
| A select statement is a string which consists of the following clauses: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| a <code class="literal">SELECT</code> clause, which determines the type of the objects |
| or values to be selected. |
| </p></li><li><p> |
| a <code class="literal">FROM</code> clause, which provides declarations that designate the |
| domain to which the expressions specified in the other clauses of the query |
| apply. |
| </p></li><li><p> |
| an optional <code class="literal">WHERE</code> clause, which may be used to restrict the |
| results that are returned by the query. |
| </p></li><li><p> |
| an optional <code class="literal">GROUP BY</code> clause, which allows query results to be |
| aggregated in terms of groups. |
| </p></li><li><p> |
| an optional <code class="literal">HAVING</code> clause, which allows filtering over |
| aggregated groups. |
| </p></li><li><p> |
| an optional <code class="literal">ORDER BY</code> clause, which may be used to order the |
| results that are returned by the query. |
| </p></li></ul></div><p> |
| In BNF syntax, a select statement is defined as: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| select_statement ::= select_clause from_clause [where_clause] [groupby_clause] |
| [having_clause] [orderby_clause] |
| </p></li></ul></div><p> |
| A select statement must always have a <code class="literal">SELECT</code> and a |
| <code class="literal">FROM</code> clause. The square brackets [] indicate that the other |
| clauses are optional. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_bulk"></a>2.1.2. |
| JPQL Update and Delete Statements |
| </h4></div></div></div><p> |
| Update and delete statements provide bulk operations over sets of entities. In |
| BNF syntax, these operations are defined as: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| update_statement ::= update_clause [where_clause] |
| </p></li><li><p> |
| delete_statement ::= delete_clause [where_clause] |
| </p></li></ul></div><p> |
| The update and delete clauses determine the type of the entities to be updated |
| or deleted. The <code class="literal">WHERE</code> clause may be used to restrict the |
| scope of the update or delete operation. Update and delete statements are |
| described further in <a href="jpa_langref.html#jpa_langref_bulk_ops" title="2.9. JPQL Bulk Update and Delete">Section 2.9, “ |
| JPQL Bulk Update and Delete |
| ”</a>. |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_schematypes"></a>2.2. |
| JPQL Abstract Schema Types and Query Domains |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemanaming">2.2.1. |
| JPQL Entity Naming |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_schemaexample">2.2.2. |
| JPQL Schema Example |
| </a></span></dt></dl></div><p> |
| The Java Persistence query language is a typed language, and every expression |
| has a type. The type of an expression is derived from the structure of the |
| expression, the abstract schema types of the identification variable |
| declarations, the types to which the persistent fields and relationships |
| evaluate, and the types of literals. The abstract schema type of an entity is |
| derived from the entity class and the metadata information provided by Java |
| language annotations or in the XML descriptor. |
| </p><p> |
| Informally, the abstract schema type of an entity can be characterized as |
| follows: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| For every persistent field or get |
| accessor method (for a persistent property) of the entity class, there is a |
| field ("state-field") whose abstract schema type corresponds to that of the |
| field or the result type of the accessor method. |
| </p></li><li><p> |
| For every persistent relationship field or get accessor method (for a persistent |
| relationship property) of the entity class, there is a field |
| ("association-field") whose type is the abstract schema type of the related |
| entity (or, if the relationship is a one-to-many or many-to-many, a collection |
| of such). Abstract schema types are specific to the query language data model. |
| The persistence provider is not required to implement or otherwise materialize |
| an abstract schema type. The domain of a query consists of the abstract schema |
| types of all entities that are defined in the same persistence unit. The domain |
| of a query may be restricted by the navigability of the relationships of the |
| entity on which it is based. The association-fields of an entity's abstract |
| schema type determine navigability. Using the association-fields and their |
| values, a query can select related entities and use their abstract schema types |
| in the query. |
| </p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_schemanaming"></a>2.2.1. |
| JPQL Entity Naming |
| </h4></div></div></div><p> |
| 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. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_schemaexample"></a>2.2.2. |
| JPQL Schema Example |
| </h4></div></div></div><p> |
| 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 <code class="literal">Magazine</code>, <code class="literal"> |
| Publisher</code>, <code class="literal">Author</code>, and <code class="literal">Article</code>. |
| </p><p> |
| Several Entities with Abstract Persistence Schemas Defined in the Same |
| Persistence Unit. The entity <code class="literal">Publisher</code> has a one-to-many |
| relationships with <code class="literal">Magazine</code>. There is also a one-to-many |
| relationship between <code class="literal">Magazine</code> and <code class="literal">Article</code> |
| . The entity <code class="literal">Article</code> is related to <code class="literal">Author</code> |
| in a one-to-one relationship. |
| </p><p> |
| 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: |
| </p><pre class="programlisting"> |
| SELECT DISTINCT mag FROM Magazine AS mag JOIN mag.articles AS art WHERE art.published = FALSE |
| </pre><p> |
| This query navigates over the association-field authors of the |
| abstract schema type <code class="literal">Magazine</code> to find articles, and uses the |
| state-field <code class="literal">published</code> of <code class="literal">Article</code> to select |
| those magazines that have at least one article that is published. Although |
| predefined reserved identifiers, such as <code class="literal">DISTINCT</code>, <code class="literal"> |
| FROM</code>, <code class="literal">AS</code>, <code class="literal">JOIN</code>, <code class="literal"> |
| WHERE</code>, and <code class="literal">FALSE</code> appear in upper case in this |
| example, predefined reserved identifiers are case insensitive. The <code class="literal"> |
| SELECT</code> 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 <code class="literal">articles</code> 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". |
| </p><pre class="programlisting"> |
| SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John' |
| </pre><p> |
| 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. |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_fromclause"></a>2.3. |
| JPQL FROM Clause and Navigational Declarations |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_identifiers">2.3.1. |
| JPQL FROM Identifiers |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_from_vars">2.3.2. |
| JPQL Identification Variables |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_range">2.3.3. |
| JPQL Range Declarations |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path">2.3.4. |
| JPQL Path Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_Joins">2.3.5. |
| JPQL Joins |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1. |
| JPQL Inner Joins (Relationship Joins) |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2. |
| JPQL Outer Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3. |
| JPQL Fetch Joins |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_dec">2.3.6. |
| JPQL Collection Member Declarations |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_polymorph">2.3.7. |
| JPQL Polymorphism |
| </a></span></dt></dl></div><p> |
| The <code class="literal">FROM</code> clause of a query defines the domain of the query by |
| declaring identification variables. An identification variable is an identifier |
| declared in the <code class="literal">FROM</code> clause of a query. The domain of the |
| query may be constrained by path expressions. Identification variables designate |
| instances of a particular entity abstract schema type. The <code class="literal">FROM |
| </code> clause can contain multiple identification variable declarations |
| separated by a comma (,). |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| from_clause ::= FROM identification_variable_declaration {, |
| {identification_variable_declaration | collection_member_declaration}}* |
| </p></li><li><p> |
| identification_variable_declaration ::= range_variable_declaration { join | |
| fetch_join }* |
| </p></li><li><p> |
| range_variable_declaration ::= abstract_schema_name [AS] identification_variable |
| </p></li><li><p> |
| join ::= join_spec join_association_path_expression [AS] identification_variable |
| </p></li><li><p> |
| fetch_join ::= join_spec FETCH join_association_path_expression |
| </p></li><li><p> |
| join_association_path_expression ::= join_collection_valued_path_expression | |
| join_single_valued_association_path_expression |
| </p></li><li><p> |
| join_spec ::= [ LEFT [OUTER] | INNER ] JOIN |
| </p></li><li><p> |
| collection_member_declaration ::= IN (collection_valued_path_expression) [AS] |
| identification_variable |
| </p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_from_identifiers"></a>2.3.1. |
| JPQL FROM Identifiers |
| </h4></div></div></div><p> |
| 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 <code class="methodname"> |
| Character.isJavaIdentifierStart</code> returns <code class="literal">true</code>. |
| This includes the underscore (_) character and the dollar sign ($) character. An |
| identifier part character is any character for which the method <code class="methodname"> |
| Character.isJavaIdentifierPart</code> returns <code class="literal">true</code>. |
| The question mark (?) character is reserved for use by the Java Persistence |
| query language. The following are reserved identifiers: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| <code class="literal">SELECT</code> |
| </p></li><li><p> |
| <code class="literal">FROM</code> |
| </p></li><li><p> |
| <code class="literal">WHERE</code> |
| </p></li><li><p> |
| <code class="literal">UPDATE</code> |
| </p></li><li><p> |
| <code class="literal">DELETE</code> |
| </p></li><li><p> |
| <code class="literal">JOIN</code> |
| </p></li><li><p> |
| <code class="literal">OUTER</code> |
| </p></li><li><p> |
| <code class="literal">INNER</code> |
| </p></li><li><p> |
| <code class="literal">LEFT</code> |
| </p></li><li><p> |
| <code class="literal">GROUP</code> |
| </p></li><li><p> |
| <code class="literal">BY</code> |
| </p></li><li><p> |
| <code class="literal">HAVING</code> |
| </p></li><li><p> |
| <code class="literal">FETCH</code> |
| </p></li><li><p> |
| <code class="literal">DISTINCT</code> |
| </p></li><li><p> |
| <code class="literal">OBJECT</code> |
| </p></li><li><p> |
| <code class="literal">NULL</code> |
| </p></li><li><p> |
| <code class="literal">TRUE</code> |
| </p></li><li><p> |
| <code class="literal">FALSE</code> |
| </p></li><li><p> |
| <code class="literal">NOT</code> |
| </p></li><li><p> |
| <code class="literal">AND</code> |
| </p></li><li><p> |
| <code class="literal">OR</code> |
| </p></li><li><p> |
| <code class="literal">BETWEEN</code> |
| </p></li><li><p> |
| <code class="literal">LIKE</code> |
| </p></li><li><p> |
| <code class="literal">IN</code> |
| </p></li><li><p> |
| <code class="literal">AS</code> |
| </p></li><li><p> |
| <code class="literal">UNKNOWN</code> |
| </p></li><li><p> |
| <code class="literal">EMPTY</code> |
| </p></li><li><p> |
| <code class="literal">MEMBER</code> |
| </p></li><li><p> |
| <code class="literal">OF</code> |
| </p></li><li><p> |
| <code class="literal">IS</code> |
| </p></li><li><p> |
| <code class="literal">AVG</code> |
| </p></li><li><p> |
| <code class="literal">MAX</code> |
| </p></li><li><p> |
| <code class="literal">MIN</code> |
| </p></li><li><p> |
| <code class="literal">SUM</code> |
| </p></li><li><p> |
| <code class="literal">COUNT</code> |
| </p></li><li><p> |
| <code class="literal">ORDER</code> |
| </p></li><li><p> |
| <code class="literal">BY</code> |
| </p></li><li><p> |
| <code class="literal">ASC</code> |
| </p></li><li><p> |
| <code class="literal">DESC</code> |
| </p></li><li><p> |
| <code class="literal">MOD</code> |
| </p></li><li><p> |
| <code class="literal">UPPER</code> |
| </p></li><li><p> |
| <code class="literal">LOWER</code> |
| </p></li><li><p> |
| <code class="literal">TRIM</code> |
| </p></li><li><p> |
| <code class="literal">POSITION</code> |
| </p></li><li><p> |
| <code class="literal">CHARACTER_LENGTH</code> |
| </p></li><li><p> |
| <code class="literal">CHAR_LENGTH</code> |
| </p></li><li><p> |
| <code class="literal">BIT_LENGTH</code> |
| </p></li><li><p> |
| <code class="literal">CURRENT_TIME</code> |
| </p></li><li><p> |
| <code class="literal">CURRENT_DATE</code> |
| </p></li><li><p> |
| <code class="literal">CURRENT_TIMESTAMP</code> |
| </p></li><li><p> |
| <code class="literal">NEW</code> |
| </p></li><li><p> |
| <code class="literal">EXISTS</code> |
| </p></li><li><p> |
| <code class="literal">ALL</code> |
| </p></li><li><p> |
| <code class="literal">ANY</code> |
| </p></li><li><p> |
| <code class="literal">SOME</code> |
| </p></li></ul></div><p> |
| Reserved identifiers are case insensitive. Reserved identifiers must not be |
| used as identification variables. It is recommended that other SQL reserved |
| words also not be as identification variables in queries because they may be |
| used as reserved identifiers in future releases of the specification. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_from_vars"></a>2.3.2. |
| JPQL Identification Variables |
| </h4></div></div></div><p> |
| An identification variable is a valid identifier declared in the <code class="literal">FROM |
| </code> clause of a query. All identification variables must be declared in |
| the <code class="literal">FROM</code> 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: </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag JOIN mag.articles art JOIN art.author auth WHERE auth.firstName = 'John' |
| </pre><p> In the <code class="literal">FROM</code> clause declaration <code class="literal"> |
| mag.articles</code><code class="literal">art</code>, the identification variable |
| <code class="literal">art</code> evaluates to any <code class="literal">Article</code> value |
| directly reachable from <code class="literal">Magazine</code>. The association-field |
| <code class="literal">articles</code> is a collection of instances of the abstract schema |
| type <code class="literal">Article</code> and the identification variable <code class="literal">art |
| </code> refers to an element of this collection. The type of <code class="literal">auth |
| </code> is the abstract schema type of <code class="literal">Author</code>. An |
| identification variable ranges over the abstract schema type of an entity. An |
| identification variable designates an instance of an entity abstract schema type |
| or an element of a collection of entity abstract schema type instances. |
| Identification variables are existentially quantified in a query. 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 <code class="literal">FROM</code> |
| clause, and an identification variable declaration can use the result of a |
| preceding identification variable declaration of the query string. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_range"></a>2.3.3. |
| JPQL Range Declarations |
| </h4></div></div></div><p> |
| The syntax for declaring an identification variable as a range variable is |
| similar to that of SQL; optionally, it uses the AS keyword. |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| range_variable_declaration ::= abstract_schema_name [AS] |
| identification_variable |
| </p></li></ul></div><p> |
| 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 <code class="literal">FROM</code> clause. |
| </p><p> |
| 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 <code class="literal">FROM</code> clause, |
| both of the abstract schema type Magazine. The <code class="literal">SELECT</code> clause |
| of this query determines that it is the magazines with prices greater than those |
| of "Adventure" publisher's that are returned. |
| </p><pre class="programlisting"> |
| SELECT DISTINCT mag1 FROM Magazine mag1, Magazine mag2 |
| WHERE mag1.price > mag2.price AND mag2.publisher.name = 'Adventure' |
| </pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_path"></a>2.3.4. |
| JPQL Path Expressions |
| </h4></div></div></div><p> |
| 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. |
| Depending on navigability, a path expression that leads to a association-field |
| 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. 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 syntax for single-valued path expressions and collection valued |
| path expressions is as follows: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| single_valued_path_expression ::= state_field_path_expression | |
| single_valued_association_path_expression |
| </p></li><li><p> |
| state_field_path_expression ::= {identification_variable | |
| single_valued_association_path_expression}.state_field |
| </p></li><li><p> |
| single_valued_association_path_expression ::= |
| identification_variable.{single_valued_association_field.}*single_valued_association_field |
| </p></li><li><p> |
| collection_valued_path_expression ::= |
| identification_variable.{single_valued_association_field.}*collection_valued_association_field |
| </p></li><li><p> |
| state_field ::= {embedded_class_state_field.}*simple_state_field |
| </p></li></ul></div><p> |
| A single_valued_association_field is designated by the name of an |
| association-field in a one-to-one or many-to-one relationship. The type of a |
| single_valued_association_field and thus a |
| single_valued_association_path_expression is the abstract schema type of the |
| related entity. A collection_valued_association_field is designated by the name |
| of an association-field in a one-to-many or a many-to-many relationship. The |
| type of a collection_valued_association_field is a collection of values of the |
| abstract schema type of the related entity. An embedded_class_state _field is |
| designated by the name of an entity state field that corresponds to an embedded |
| class. Navigation to a related entity results in a value of the related entity's |
| abstract schema type. |
| </p><p> |
| The evaluation of a path expression terminating in a state-field results in the |
| abstract schema type corresponding to the Java type designated by the |
| state-field. It is syntactically illegal to compose a path expression from a |
| path expression that evaluates to a collection. For example, if <code class="literal">mag |
| </code> designates <code class="literal">Magazine</code>, the path expression <code class="literal"> |
| mag.articles.author</code> 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 <code class="literal">FROM</code> clause to range over the elements of the |
| <code class="literal">articles</code> collection. Another path expression must be used to |
| navigate over each such element in the <code class="literal">WHERE</code> clause of the |
| query, as in the following query which returns all authors that have any |
| articles in any magazines: </p><pre class="programlisting">SELECT DISTINCT art.author FROM Magazine AS mag, IN(mag.articles) art |
| </pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_Joins"></a>2.3.5. |
| JPQL Joins |
| </h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_inner_joins">2.3.5.1. |
| JPQL Inner Joins (Relationship Joins) |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_outer_joins">2.3.5.2. |
| JPQL Outer Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_fetch_joins">2.3.5.3. |
| JPQL Fetch Joins |
| </a></span></dt></dl></div><p> |
| An inner join may be implicitly specified by the use of a cartesian product in |
| the <code class="literal">FROM</code> clause and a join condition in the <code class="literal">WHERE |
| </code> clause. |
| </p><p> |
| The syntax for explicit join operations is as follows: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| join ::= join_spec join_association_path_expression [AS] identification_variable |
| </p></li><li><p> |
| fetch_join ::= join_spec FETCH join_association_path_expression |
| </p></li><li><p> |
| join_association_path_expression ::= join_collection_valued_path_expression | |
| join_single_valued_association_path_expression |
| </p></li><li><p> |
| join_spec ::= [ LEFT [OUTER] | INNER ] JOIN |
| </p></li></ul></div><p> |
| The following inner and outer join operation types are supported. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_inner_joins"></a>2.3.5.1. |
| JPQL Inner Joins (Relationship Joins) |
| </h5></div></div></div><p> |
| The syntax for the inner join operation is </p><pre class="programlisting"> |
| [ INNER ] JOIN join_association_path_expression [AS] identification_variable |
| </pre><p> For example, the query below joins over the relationship |
| between publishers and magazines. This type of join typically equates to a join |
| over a foreign key relationship in the database. |
| </p><pre class="programlisting"> |
| SELECT pub FROM Publisher pub JOIN pub.magazines mag WHERE pub.revenue > 1000000 |
| </pre><p> |
| The keyword <code class="literal">INNER</code> may optionally be used: |
| </p><pre class="programlisting"> |
| SELECT pub FROM Publisher pub INNER JOIN pub.magazines mag WHERE pub.revenue > 1000000 |
| </pre><p> |
| This is equivalent to the following query using the earlier |
| <code class="literal">IN</code> construct. It selects those publishers with revenue of |
| over 1 million for which at least one magazine exists: |
| </p><pre class="programlisting"> |
| SELECT OBJECT(pub) FROM Publisher pub, IN(pub.magazines) mag WHERE pub.revenue > 1000000 |
| </pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_outer_joins"></a>2.3.5.2. |
| JPQL Outer Joins |
| </h5></div></div></div><p> |
| <code class="literal">LEFT JOIN</code> and <code class="literal">LEFT OUTER JOIN</code> are |
| synonymous. They enable the retrieval of a set of entities where matching values |
| in the join condition may be absent. The syntax for a left outer join is: |
| </p><pre class="programlisting">LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable |
| </pre><p> |
| </p><p> |
| For example: </p><pre class="programlisting">SELECT pub FROM Publisher pub LEFT JOIN pub.magazines mag WHERE pub.revenue > 1000000 |
| </pre><p> The keyword <code class="literal">OUTER</code> may optionally be used: |
| </p><pre class="programlisting">SELECT pub FROM Publisher pub LEFT OUTER JOIN pub.magazines mags WHERE pub.revenue > 1000000 |
| </pre><p> An important use case for <code class="literal">LEFT JOIN</code> is in |
| enabling the prefetching of related data items as a side effect of a query. This |
| is accomplished by specifying the <code class="literal">LEFT JOIN</code> as a <code class="literal"> |
| FETCH JOIN</code>. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_fetch_joins"></a>2.3.5.3. |
| JPQL Fetch Joins |
| </h5></div></div></div><p> |
| A <code class="literal">FETCH JOIN</code> enables the fetching of an association as a side |
| effect of the execution of a query. A <code class="literal">FETCH JOIN</code> is specified |
| over an entity and its related entities. The syntax for a fetch join is |
| </p><div class="itemizedlist"><ul type="disc"><li><p>fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN |
| FETCH join_association_path_expression |
| </p></li></ul></div><p> |
| </p><p> |
| The association referenced by the right side of the <code class="literal">FETCH JOIN |
| </code> clause must be an association that belongs to an entity that is |
| returned as a result of the query. It is not permitted to specify an |
| identification variable for the entities referenced by the right side of the |
| <code class="literal">FETCH JOIN</code> clause, and hence references to the implicitly |
| fetched entities cannot appear elsewhere in the query. The following query |
| returns a set of magazines. As a side effect, the associated articles for those |
| magazines are also retrieved, even though they are not part of the explicit |
| query result. The persistent fields or properties of the articles that are |
| eagerly fetched are fully initialized. The initialization of the relationship |
| properties of the <code class="literal">articles</code> that are retrieved is determined |
| by the metadata for the <code class="literal">Article</code> entity class. |
| </p><pre class="programlisting">SELECT mag FROM Magazine mag LEFT JOIN FETCH mag.articles WHERE mag.id = 1 |
| </pre><p> |
| </p><p> |
| A fetch join has the same join semantics as the corresponding inner or outer |
| join, except that the related objects specified on the right-hand side of the |
| join operation are not returned in the query result or otherwise referenced in |
| the query. Hence, for example, if magazine id 1 has five articles, the above |
| query returns five references to the magazine 1 entity. |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_collection_dec"></a>2.3.6. |
| JPQL Collection Member Declarations |
| </h4></div></div></div><p> |
| 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 <code class="literal">IN</code> |
| . The argument to the <code class="literal">IN</code> 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 |
| abstract schema type. The syntax for declaring a collection member |
| identification variable is as follows: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>collection_member_declaration ::= IN |
| (collection_valued_path_expression) [AS] identification_variable |
| </p></li></ul></div><p> |
| </p><p> |
| For example, the query </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag |
| JOIN mag.articles art |
| JOIN art.author auth |
| WHERE auth.lastName = 'Grisham'</pre><p> may equivalently be |
| expressed as follows, using the <code class="literal">IN</code> operator: </p><pre class="programlisting">SELECT DISTINCT mag FROM Magazine mag, |
| IN(mag.articles) art |
| WHERE art.author.lastName = 'Grisham'</pre><p> In this example, |
| <code class="literal">articles</code> is the name of an association-field whose value is a |
| collection of instances of the abstract schema type <code class="literal">Article</code>. |
| The identification variable <code class="literal">art</code> designates a member of this |
| collection, a single <code class="literal">Article</code> abstract schema type instance. |
| In this example, <code class="literal">mag</code> is an identification variable of the |
| abstract schema type <code class="literal">Magazine</code>. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_polymorph"></a>2.3.7. |
| JPQL Polymorphism |
| </h4></div></div></div><p> |
| Java Persistence queries are automatically polymorphic. The <code class="literal">FROM |
| </code> 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. |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_where"></a>2.4. |
| JPQL WHERE Clause |
| </h3></div></div></div><p> |
| The <code class="literal">WHERE</code> clause of a query consists of a conditional |
| expression used to select objects or values that satisfy the expression. The |
| <code class="literal">WHERE</code> clause restricts the result of a select statement or |
| the scope of an update or delete operation. A <code class="literal">WHERE</code> clause is |
| defined as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>where_clause ::= WHERE |
| conditional_expression |
| </p></li></ul></div><p> |
| </p><p> |
| The <code class="literal">GROUP BY</code> construct enables the aggregation of values |
| according to the properties of an entity class. The <code class="literal">HAVING</code> |
| construct enables conditions to be specified that further restrict the query |
| result as restrictions upon the groups. The syntax of the <code class="literal">HAVING |
| </code> clause is as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>having_clause |
| ::= HAVING conditional_expression |
| </p></li></ul></div><p> |
| </p><p> |
| The <code class="literal">GROUP BY</code> and <code class="literal">HAVING</code> constructs are |
| further discussed in <a href="jpa_langref.html#jpa_langref_group" title="2.6. JPQL GROUP BY, HAVING">Section 2.6, “ |
| JPQL GROUP BY, HAVING |
| ”</a>. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_cond"></a>2.5. |
| JPQL Conditional Expressions |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_lit">2.5.1. |
| JPQL Literals |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_idvar">2.5.2. |
| JPQL Identification Variables |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_path_exp">2.5.3. |
| JPQL Path Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_input_params">2.5.4. |
| JPQL Input Parameters |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1. |
| JPQL Positional Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2. |
| JPQL Named Parameters |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_cond_comp">2.5.5. |
| JPQL Conditional Expression Composition |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_operators">2.5.6. |
| JPQL Operators and Operator Precedence |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_between">2.5.7. |
| JPQL Between Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_in">2.5.8. |
| JPQL In Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_like">2.5.9. |
| JPQL Like Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null">2.5.10. |
| JPQL Null Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_empty_comp">2.5.11. |
| JPQL Empty Collection Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_member">2.5.12. |
| JPQL Collection Member Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_exists">2.5.13. |
| JPQL Exists Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_all_any">2.5.14. |
| JPQL All or Any Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_subqueries">2.5.15. |
| JPQL Subqueries |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_functional">2.5.16. |
| JPQL Functional Expressions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1. |
| JPQL String Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2. |
| JPQL Arithmetic Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3. |
| JPQL Datetime Functions |
| </a></span></dt></dl></dd></dl></div><p> |
| The following sections describe the language constructs that can be used in a |
| conditional expression of the <code class="literal">WHERE</code> clause or <code class="literal"> |
| HAVING</code> clause. State-fields that are mapped in serialized form or as |
| lobs may not be portably used in conditional expressions. </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> The |
| implementation is not expected to perform such query operations involving such |
| fields in memory rather than in the database. |
| </p></div><p> |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_lit"></a>2.5.1. |
| JPQL Literals |
| </h4></div></div></div><p> |
| 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 may be used |
| to indicate the specific type of a numeric literal in accordance with the Java |
| Language Specification. The boolean literals are <code class="literal">TRUE</code> and |
| <code class="literal">FALSE</code>. Although predefined reserved literals appear in upper |
| case, they are case insensitive. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_idvar"></a>2.5.2. |
| JPQL Identification Variables |
| </h4></div></div></div><p> |
| All identification variables used in the <code class="literal">WHERE</code> or <code class="literal"> |
| HAVING</code> clause of a <code class="literal">SELECT</code> or <code class="literal">DELETE |
| </code> statement must be declared in the <code class="literal">FROM</code> clause, as |
| described in <a href="jpa_langref.html#jpa_langref_from_vars" title="2.3.2. JPQL Identification Variables">Section 2.3.2, “ |
| JPQL Identification Variables |
| ”</a>. The identification |
| variables used in the <code class="literal">WHERE</code> clause of an <code class="literal">UPDATE |
| </code> statement must be declared in the <code class="literal">UPDATE</code> clause. |
| Identification variables are existentially quantified in the <code class="literal">WHERE |
| </code> and <code class="literal">HAVING</code> 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. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_path_exp"></a>2.5.3. |
| JPQL Path Expressions |
| </h4></div></div></div><p> |
| It is illegal to use a collection_valued_path_expression within a <code class="literal"> |
| WHERE</code> or <code class="literal">HAVING</code> 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 <code class="literal">SIZE</code> |
| operator. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_input_params"></a>2.5.4. |
| JPQL Input Parameters |
| </h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_pos_params">2.5.4.1. |
| JPQL Positional Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_named_params">2.5.4.2. |
| JPQL Named Parameters |
| </a></span></dt></dl></div><p> |
| 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 <code class="literal">WHERE</code> clause or <code class="literal">HAVING</code> clause of a |
| query. |
| </p><p> |
| Note that if an input parameter value is null, comparison operations or |
| arithmetic operations involving the input parameter will return an unknown |
| value. See <a href="jpa_langref.html#jpa_langref_null_values" title="2.10. JPQL Null Values">Section 2.10, “ |
| JPQL Null Values |
| ”</a>. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_pos_params"></a>2.5.4.1. |
| JPQL Positional Parameters |
| </h5></div></div></div><p> |
| The following rules apply to positional parameters. </p><div class="itemizedlist"><ul type="disc"><li><p> Input parameters are designated by the question mark (?) prefix followed |
| by an integer. For example: ?1. |
| </p></li><li><p> |
| Input parameters are numbered starting from 1. Note that the same parameter can |
| be used more than once in the query string and that the ordering of the use of |
| parameters within the query string need not conform to the order of the |
| positional parameters. |
| </p></li></ul></div><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_named_params"></a>2.5.4.2. |
| JPQL Named Parameters |
| </h5></div></div></div><p> |
| A named parameter is an identifier that is prefixed by the ":" symbol. It |
| follows the rules for identifiers defined in |
| <a href="jpa_langref.html#jpa_langref_from_identifiers" title="2.3.1. JPQL FROM Identifiers">Section 2.3.1, “ |
| JPQL FROM Identifiers |
| ”</a>. Named parameters are case |
| sensitive. |
| </p><p> |
| Example: </p><pre class="programlisting">SELECT pub FROM Publisher pub WHERE pub.revenue > :rev |
| </pre><p> |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_cond_comp"></a>2.5.5. |
| JPQL Conditional Expression Composition |
| </h4></div></div></div><p> |
| 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. Arithmetic |
| expressions can be used in comparison expressions. Arithmetic expressions are |
| composed of other arithmetic expressions, arithmetic operations, path |
| expressions that evaluate to numeric values, numeric literals, and numeric input |
| parameters. Arithmetic operations use numeric promotion. Standard bracketing () |
| for ordering expression evaluation is supported. Conditional expressions are |
| defined as follows: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>conditional_expression ::= conditional_term | |
| conditional_expression OR conditional_term |
| </p></li><li><p> |
| conditional_term ::= conditional_factor | conditional_term AND |
| conditional_factor |
| </p></li><li><p> |
| conditional_factor ::= [ NOT ] conditional_primary |
| </p></li><li><p> |
| conditional_primary ::= simple_cond_expression | (conditional_expression) |
| </p></li><li><p> |
| simple_cond_expression ::= comparison_expression | between_expression | |
| like_expression | in_expression | null_comparison_expression | |
| empty_collection_comparison_expression | collection_member_expression | |
| exists_expression |
| </p></li></ul></div><p> |
| </p><p> |
| Aggregate functions can only be used in conditional expressions in a <code class="literal"> |
| HAVING</code> clause. See <a href="jpa_langref.html#jpa_langref_group" title="2.6. JPQL GROUP BY, HAVING">Section 2.6, “ |
| JPQL GROUP BY, HAVING |
| ”</a>. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_operators"></a>2.5.6. |
| JPQL Operators and Operator Precedence |
| </h4></div></div></div><p> |
| The operators are listed below in order of decreasing precedence. </p><div class="itemizedlist"><ul type="disc"><li><p> Navigation operator (.) |
| </p></li><li><p> |
| Arithmetic operators: +, - unary *, / multiplication and division +, - addition |
| and subtraction |
| </p></li><li><p> |
| Comparison operators: =, >, >=, <, <=, <> (not equal), [ |
| <code class="literal">NOT</code> ] <code class="literal">BETWEEN</code>, [ <code class="literal">NOT</code> ] |
| <code class="literal">LIKE</code>, [ <code class="literal">NOT</code> ] <code class="literal">IN</code>, |
| <code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">NULL</code>, |
| <code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">EMPTY</code>, [ |
| <code class="literal">NOT</code> ] <code class="literal">MEMBER</code> [ <code class="literal">OF</code> ] |
| </p></li><li><p> |
| Logical operators: <code class="literal">NOT</code>, <code class="literal">AND</code>, |
| <code class="literal">OR</code> |
| </p></li></ul></div><p> |
| |
| |
| The following sections describe other operators used in specific expressions. |
| |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_between"></a>2.5.7. |
| JPQL Between Expressions |
| </h4></div></div></div><p> |
| The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ] |
| <code class="literal">BETWEEN</code> in a conditional expression is as follows: |
| </p><p> |
| 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 |
| </p><p> |
| The BETWEEN expression </p><pre class="programlisting">x BETWEEN y AND z</pre><p> is |
| semantically equivalent to: </p><pre class="programlisting">y <= x AND x <= z |
| </pre><p> The rules for unknown and <code class="literal">NULL</code> values in |
| comparison operations apply. See <a href="jpa_langref.html#jpa_langref_null_values" title="2.10. JPQL Null Values">Section 2.10, “ |
| JPQL Null Values |
| ”</a> |
| . Examples are: </p><pre class="programlisting">p.age BETWEEN 15 and 19</pre><p> is |
| equivalent to </p><pre class="programlisting">p.age >= 15 AND p.age <= 19</pre><p> |
| </p><p> |
| </p><pre class="programlisting">p.age NOT BETWEEN 15 and 19</pre><p> is equivalent to |
| </p><pre class="programlisting">p.age < 15 OR p.age > 19</pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_in"></a>2.5.8. |
| JPQL In Expressions |
| </h4></div></div></div><p> |
| The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ] |
| <code class="literal">IN</code> in a conditional expression is as follows: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>in_expression ::= state_field_path_expression |
| [NOT] IN ( in_item {, in_item}* | subquery) |
| </p></li><li><p> |
| in_item ::= literal | input_parameter |
| </p></li></ul></div><p> |
| </p><p> |
| The state_field_path_expression must have a string, numeric, 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 |
| <a href="jpa_langref.html#jpa_langref_equality" title="2.11. JPQL Equality and Comparison Semantics">Section 2.11, “ |
| JPQL Equality and Comparison Semantics |
| ”</a> ). |
| </p><p> |
| 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 |
| <a href="jpa_langref.html#jpa_langref_subqueries" title="2.5.15. JPQL Subqueries">Section 2.5.15, “ |
| JPQL Subqueries |
| ”</a>. Examples are: </p><pre class="programlisting">o.country IN ('UK', 'US', 'France') |
| </pre><p> is true for UK and false for Peru, and is equivalent to the |
| expression: </p><pre class="programlisting">(o.country = 'UK') OR (o.country = 'US') OR (o.country = ' France') |
| </pre><p> In the following expression: </p><pre class="programlisting">o.country NOT IN ('UK', 'US', 'France') |
| </pre><p> is false for UK and true for Peru, and is equivalent to the |
| expression: </p><pre class="programlisting">NOT ((o.country = 'UK') OR (o.country = 'US') OR (o.country = 'France')) |
| </pre><p> There must be at least one element in the comma separated list |
| that defines the set of values for the <code class="literal">IN</code> expression. If the |
| value of a state_field_path_expression in an <code class="literal">IN</code> or <code class="literal"> |
| NOT IN</code> expression is <code class="literal">NULL</code> or unknown, the value of |
| the expression is unknown. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_like"></a>2.5.9. |
| JPQL Like Expressions |
| </h4></div></div></div><p> |
| The syntax for the use of the comparison operator [ <code class="literal">NOT</code> ] |
| <code class="literal">LIKE</code> in a conditional expression is as follows: |
| </p><p> |
| string_expression [NOT] LIKE pattern_value [ESCAPE escape_character] |
| </p><p> |
| 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 are: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><pre class="programlisting">address.phone LIKE '12%3' |
| </pre><p> is true for '123' '12993' and false for '1234' |
| </p></li><li><p> |
| </p><pre class="programlisting">asentence.word LIKE 'l_se'</pre><p> is true for 'lose' |
| and false for 'loose' |
| </p></li><li><p> |
| </p><pre class="programlisting">aword.underscored LIKE '\_%' ESCAPE '\'</pre><p> is true |
| for '_foo' and false for 'bar' |
| </p></li><li><p> |
| </p><pre class="programlisting">address.phone NOT LIKE '12%3'</pre><p> is false for |
| '123' and '12993' and true for '1234'. If the value of the string_expression or |
| pattern_value is <code class="literal">NULL</code> or unknown, the value of the <code class="literal"> |
| LIKE</code> expression is unknown. If the escape_character is specified and |
| is <code class="literal">NULL</code>, the value of the <code class="literal">LIKE</code> expression |
| is unknown. |
| </p></li></ul></div><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_null"></a>2.5.10. |
| JPQL Null Comparison Expressions |
| </h4></div></div></div><p> |
| The syntax for the use of the comparison operator <code class="literal">IS NULL</code> in |
| a conditional expression is as follows: |
| </p><p> |
| {single_valued_path_expression | input_parameter } IS [NOT] NULL |
| </p><p> |
| A null comparison expression tests whether or not the single-valued path |
| expression or input parameter is a <code class="literal">NULL</code> value. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_empty_comp"></a>2.5.11. |
| JPQL Empty Collection Comparison Expressions |
| </h4></div></div></div><p> |
| The syntax for the use of the comparison operator <code class="literal">IS EMPTY</code> in |
| an empty_collection_comparison_expression is as follows: |
| </p><p> |
| collection_valued_path_expression IS [NOT] EMPTY |
| </p><p> |
| This expression tests whether or not the collection designated by the |
| collection-valued path expression is empty (i.e, has no elements). |
| </p><p> |
| For example, the following query will return all magazines that don't have any |
| articles at all: </p><pre class="programlisting">SELECT mag FROM Magazine mag WHERE mag.articles IS EMPTY |
| </pre><p> 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. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_collection_member"></a>2.5.12. |
| JPQL Collection Member Expressions |
| </h4></div></div></div><p> |
| The use of the comparison collection_member_expression is as follows: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| collection_member_expression ::= entity_expression [NOT] MEMBER [OF] |
| collection_valued_path_expression |
| </p></li><li><p> |
| entity_expression ::= single_valued_association_path_expression | |
| simple_entity_expression |
| </p></li><li><p> |
| simple_entity_expression ::= identification_variable | input_parameter |
| </p></li></ul></div><p> |
| </p><p> |
| 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 <code class="literal"> |
| MEMBER OF</code> expression is <code class="literal">FALSE</code> and the value of the |
| <code class="literal">NOT MEMBER OF</code> expression is <code class="literal">TRUE</code>. |
| Otherwise, if the value of the collection-valued path expression or |
| single-valued association-field path expression in the collection member |
| expression is <code class="literal">NULL</code> or unknown, the value of the collection |
| member expression is unknown. |
| </p><p> |
| The use of the reserved word OF is optional in this expression. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_exists"></a>2.5.13. |
| JPQL Exists Expressions |
| </h4></div></div></div><p> |
| An <code class="literal">EXISTS</code> 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 </p><div class="itemizedlist"><ul type="disc"><li><p> |
| exists_expression ::= [NOT] EXISTS (subquery) |
| </p></li></ul></div><p> |
| </p><p> |
| Example: </p><pre class="programlisting">SELECT DISTINCT auth FROM Author auth |
| WHERE EXISTS |
| (SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse) |
| </pre><p> The result of this query consists of all authors whose spouse |
| is also an author. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_all_any"></a>2.5.14. |
| JPQL All or Any Expressions |
| </h4></div></div></div><p> |
| An <code class="literal">ALL</code> conditional expression is a predicate 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 <code class="literal">ALL</code> conditional |
| expression is false if the result of the comparison is false for at least one |
| row, and is unknown if neither true nor false. An <code class="literal">ANY</code> |
| conditional expression is a predicate that is true if the comparison operation |
| is true for some value in the result of the subquery. An <code class="literal">ANY</code> |
| 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 <code class="literal">SOME</code> is |
| synonymous with <code class="literal">ANY</code>. The comparison operators used with |
| <code class="literal">ALL</code> or <code class="literal">ANY</code> conditional expressions are =, |
| <, <=, >, >=, <>. The result of the subquery must be like that |
| of the other argument to the comparison operator in type. See |
| <a href="jpa_langref.html#jpa_langref_equality" title="2.11. JPQL Equality and Comparison Semantics">Section 2.11, “ |
| JPQL Equality and Comparison Semantics |
| ”</a>. The syntax of an <code class="literal">ALL |
| </code> or <code class="literal">ANY</code> expression is specified as follows: |
| </p><div class="itemizedlist"><ul type="disc"><li><p>all_or_any_expression ::= { ALL | ANY | SOME} |
| (subquery) |
| </p></li></ul></div><p> |
| </p><p> |
| The following example select the authors who make the highest salary for their |
| magazine: </p><pre class="programlisting">SELECT auth FROM Author auth |
| WHERE auth.salary >= ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine) |
| </pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_subqueries"></a>2.5.15. |
| JPQL Subqueries |
| </h4></div></div></div><p> |
| Subqueries may be used in the <code class="literal">WHERE</code> or <code class="literal">HAVING |
| </code> clause. The syntax for subqueries is as follows: </p><div class="itemizedlist"><ul type="disc"><li><p>subquery ::= simple_select_clause subquery_from_clause |
| [where_clause] [groupby_clause] [having_clause] |
| </p></li></ul></div><p> |
| </p><p> |
| Subqueries are restricted to the <code class="literal">WHERE</code> and <code class="literal">HAVING |
| </code> clauses in this release. Support for subqueries in the <code class="literal">FROM |
| </code> clause will be considered in a later release of the specification. |
| </p><div class="itemizedlist"><ul type="disc"><li><p>simple_select_clause ::= SELECT [DISTINCT] |
| simple_select_expression |
| </p></li><li><p> |
| subquery_from_clause ::= FROM subselect_identification_variable_declaration {, |
| subselect_identification_variable_declaration}* |
| </p></li><li><p> |
| subselect_identification_variable_declaration ::= |
| identification_variable_declaration | association_path_expression [AS] |
| identification_variable | collection_member_declaration |
| </p></li><li><p> |
| simple_select_expression ::= single_valued_path_expression | |
| aggregate_expression | identification_variable |
| </p></li></ul></div><p> |
| </p><p> |
| Examples: </p><pre class="programlisting">SELECT DISTINCT auth FROM Author auth |
| WHERE EXISTS (SELECT spouseAuth FROM Author spouseAuth WHERE spouseAuth = auth.spouse) |
| </pre><pre class="programlisting">SELECT mag FROM Magazine mag |
| WHERE (SELECT COUNT(art) FROM mag.articles art) > 10</pre><p> |
| 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. |
| </p><pre class="programlisting">SELECT goodPublisher FROM Publisher goodPublisher |
| WHERE goodPublisher.revenue < (SELECT AVG(p.revenue) FROM Publisher p) |
| </pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_functional"></a>2.5.16. |
| JPQL Functional Expressions |
| </h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.5.16.1. |
| JPQL String Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.5.16.2. |
| JPQL Arithmetic Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.5.16.3. |
| JPQL Datetime Functions |
| </a></span></dt></dl></div><p> |
| The JPQL includes the following built-in functions, which may be used in the |
| <code class="literal">WHERE</code> or <code class="literal">HAVING</code> 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. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_string_fun"></a>2.5.16.1. |
| JPQL String Functions |
| </h5></div></div></div><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>functions_returning_strings ::= |
| CONCAT(string_primar y, string_primary) | SUBSTRING(string_primar y, |
| simple_arithmetic_expression, simple_arithmetic_expression) | |
| TRIM([[trim_specification] [trim_character] FROM] string_primary) | |
| LOWER(string_primar y) | UPPER(string_primar y) |
| </p></li><li><p> |
| trim_specification ::= LEADING | TRAILING | BOTH |
| </p></li><li><p> |
| functions_returning_numerics ::= LENGTH(string_primar y) | LOCATE(string_primar |
| y, string_primar y[, simple_arithmetic_expression]) |
| </p></li></ul></div><p> |
| </p><p> |
| The <code class="literal">CONCAT</code> function returns a string that is a concatenation |
| of its arguments. The second and third arguments of the <code class="literal">SUBSTRING |
| </code> function denote the starting position and length of the substring to |
| be returned. These arguments are integers. The first position of a string is |
| denoted by 1. The <code class="literal">SUBSTRING</code> function returns a string. The |
| <code class="literal">TRIM</code> function trims the specified character from a string. If |
| the character to be trimmed is not specified, it is assumed to be space (or |
| blank). The optional trim_character is a single-character string literal or a |
| character-valued input parameter (i.e., char or Character). If a trim |
| specification is not provided, <code class="literal">BOTH</code> is assumed. The <code class="literal"> |
| TRIM</code> function returns the trimmed string. The <code class="literal">LOWER</code> |
| and <code class="literal">UPPER</code> functions convert a string to lower and upper case, |
| respectively. They return a string. The <code class="literal">LOCATE</code> function |
| returns the position of a given string within a string, starting the search at a |
| specified position. It returns the first position at which the string was found |
| as an integer. The first argument is the string to be located; the second |
| argument is the string to be searched; the optional third argument is an integer |
| that represents the string position at which the search is started (by default, |
| the beginning of the string to be searched). The first position in a string is |
| denoted by 1. If the string is not found, 0 is returned. The <code class="literal">LENGTH |
| </code> function returns the length of the string in characters as an |
| integer. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_arithmetic"></a>2.5.16.2. |
| JPQL Arithmetic Functions |
| </h5></div></div></div><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>functions_returning_numerics ::= |
| ABS(simple_arithmetic_expression) | SQRT(simple_arithmetic_expression) | |
| MOD(simple_arithmetic_expression, simple_arithmetic_expression) | |
| SIZE(collection_valued_path_expression) |
| </p></li></ul></div><p> |
| </p><p> |
| The <code class="literal">ABS</code> function takes a numeric argument and returns a |
| number (integer, float, or double) of the same type as the argument to the |
| function. The <code class="literal">SQRT</code> function takes a numeric argument and |
| returns a double. |
| </p><p> |
| Note that not all databases support the use of a trim character other than the |
| space character; use of this argument may result in queries that are not |
| portable. Note that not all databases support the use of the third argument to |
| <code class="literal">LOCATE</code>; use of this argument may result in queries that are |
| not portable. |
| </p><p> |
| The <code class="literal">MOD</code> function takes two integer arguments and returns an |
| integer. The <code class="literal">SIZE</code> function returns an integer value, the |
| number of elements of the collection. If the collection is empty, the <code class="literal"> |
| SIZE</code> function evaluates to zero. Numeric arguments to these functions |
| may correspond to the numeric Java object types as well as the primitive numeric |
| types. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_datetime"></a>2.5.16.3. |
| JPQL Datetime Functions |
| </h5></div></div></div><p> |
| functions_returning_datetime:= CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP |
| </p><p> |
| The datetime functions return the value of current date, time, and timestamp on |
| the database server. |
| </p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_group"></a>2.6. |
| JPQL GROUP BY, HAVING |
| </h3></div></div></div><p> |
| The <code class="literal">GROUP BY</code> construct enables the aggregation of values |
| according to a set of properties. The <code class="literal">HAVING</code> construct |
| enables conditions to be specified that further restrict the query result. Such |
| conditions are restrictions upon the groups. The syntax of the <code class="literal">GROUP |
| BY</code> and <code class="literal">HAVING</code> clauses is as follows: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>groupby_clause ::= GROUP BY groupby_item {, |
| groupby_item}* |
| </p></li><li><p> |
| groupby_item ::= single_valued_path_expression | identification_variable |
| </p></li><li><p> |
| having_clause ::= HAVING conditional_expression |
| </p></li></ul></div><p> |
| </p><p> |
| If a query contains both a <code class="literal">WHERE</code> clause and a <code class="literal">GROUP |
| BY</code> clause, the effect is that of first applying the where clause, and |
| then forming the groups and filtering them according to the <code class="literal">HAVING |
| </code> clause. The <code class="literal">HAVING</code> clause causes those groups to |
| be retained that satisfy the condition of the <code class="literal">HAVING</code> clause. |
| The requirements for the <code class="literal">SELECT</code> clause when <code class="literal">GROUP |
| BY</code> is used follow those of SQL: namely, any item that appears in the |
| <code class="literal">SELECT</code> clause (other than as an argument to an aggregate |
| function) must also appear in the <code class="literal">GROUP BY</code> clause. In forming |
| the groups, null values are treated as the same for grouping purposes. Grouping |
| by an entity is permitted. In this case, the entity must contain no serialized |
| state fields or lob-valued state fields. The <code class="literal">HAVING</code> clause |
| must specify search conditions over the grouping items or aggregate functions |
| that apply to grouping items. |
| </p><p> |
| If there is no <code class="literal">GROUP BY</code> clause and the <code class="literal">HAVING |
| </code> clause is used, the result is treated as a single group, and the |
| select list can only consist of aggregate functions. When a query declares a |
| <code class="literal">HAVING</code> clause, it must always also declare a <code class="literal">GROUP |
| BY</code> clause. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_select_clause"></a>2.7. |
| JPQL SELECT Clause |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_resulttype">2.7.1. |
| JPQL Result Type of the SELECT Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_constructor">2.7.2. |
| JPQL Constructor Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_select">2.7.3. |
| JPQL Null Values in the Query Result |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_aggregates">2.7.4. |
| JPQL Aggregate Functions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1. |
| JPQL Aggregate Examples |
| </a></span></dt></dl></dd></dl></div><p> |
| The <code class="literal">SELECT</code> clause denotes the query result. More than one |
| value may be returned from the <code class="literal">SELECT</code> clause of a query. The |
| <code class="literal">SELECT</code> clause may contain one or more of the following |
| elements: a single range variable or identification variable that ranges over an |
| entity abstract schema type, a single-valued path expression, an aggregate |
| select expression, a constructor expression. The <code class="literal">SELECT</code> |
| clause has the following syntax: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>select_clause ::= SELECT [DISTINCT] |
| select_expression {, select_expression}* |
| </p></li><li><p> |
| select_expression ::= single_valued_path_expression | aggregate_expression | |
| identification_variable | OBJECT(identification_variable) | |
| constructor_expression |
| </p></li><li><p> |
| constructor_expression ::= NEW constructor_name ( constructor_item {, |
| constructor_item}*) |
| </p></li><li><p> |
| constructor_item ::= single_valued_path_expression | aggregate_expression |
| </p></li><li><p> |
| aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] |
| state_field_path_expression) | COUNT ([DISTINCT] identification_variable | |
| state_field_path_expression | single_valued_association_path_expression) |
| </p></li></ul></div><p> |
| </p><p> |
| For example: </p><pre class="programlisting">SELECT pub.id, pub.revenue |
| FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00 |
| </pre><p> |
| </p><p> |
| Note that the <code class="literal">SELECT</code> clause must be specified to return only |
| single-valued expressions. The query below is therefore not valid: |
| </p><pre class="programlisting">SELECT mag.authors FROM Magazine AS mag</pre><p> The |
| <code class="literal">DISTINCT</code> keyword is used to specify that duplicate values |
| must be eliminated from the query result. If <code class="literal">DISTINCT</code> is not |
| specified, duplicate values are not eliminated. Standalone identification |
| variables in the <code class="literal">SELECT</code> clause may optionally be qualified by |
| the <code class="literal">OBJECT</code> operator. The <code class="literal">SELECT</code> clause |
| must not use the OBJECT operator to qualify path expressions. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_resulttype"></a>2.7.1. |
| JPQL Result Type of the SELECT Clause |
| </h4></div></div></div><p> |
| The type of the query result specified by the <code class="literal">SELECT</code> clause |
| of a query is an entity abstract schema type, a state-field type, the result of |
| an aggregate function, the result of a construction operation, or some sequence |
| of these. The result type of the <code class="literal">SELECT</code> clause is defined by |
| the result types of the select_expressions contained in it. When multiple |
| select_expressions are used in the <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code> |
| 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: </p><div class="itemizedlist"><ul type="disc"><li><p> A single_valued_path_expression that is a |
| state_field_path_expression results in an object of the same type as the |
| corresponding state field of the entity. If the state field of the entity is a |
| primitive type, the corresponding object type is returned. |
| </p></li><li><p> |
| single_valued_path_expression that is a |
| single_valued_association_path_expression results in an entity object of the |
| type of the relationship field or the subtype of the relationship field of the |
| entity object as determined by the object/relational mapping. |
| </p></li><li><p> |
| The result type of an identification_variable is the type of the entity to which |
| that identification variable corresponds or a subtype as determined by the |
| object/relational mapping. |
| </p></li><li><p> |
| The result type of aggregate_expression is defined in section |
| <a href="jpa_langref.html#jpa_langref_aggregates" title="2.7.4. JPQL Aggregate Functions">Section 2.7.4, “ |
| JPQL Aggregate Functions |
| ”</a>. |
| </p></li><li><p> |
| The result type of a constructor_expression is the type of the class for which |
| the constructor is defined. The types of the arguments to the constructor are |
| defined by the above rules. |
| </p></li></ul></div><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_constructor"></a>2.7.2. |
| JPQL Constructor Expressions |
| </h4></div></div></div><p> |
| A constructor may be used in the |
| <code class="literal">SELECT</code> 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. |
| </p><p> |
| If an entity class name is specified in the <code class="literal">SELECT NEW</code> |
| clause, the resulting entity instances are in the new state. </p><pre class="programlisting">SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price) |
| FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00 |
| </pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_null_select"></a>2.7.3. |
| JPQL Null Values in the Query Result |
| </h4></div></div></div><p> |
| 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 <code class="literal">IS NOT NULL</code> 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 <code class="literal">NULL |
| </code> values in the query result. A query that returns such a state-field |
| type as a result type must not return a null value. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_langref_aggregates"></a>2.7.4. |
| JPQL Aggregate Functions |
| </h4></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.7.4.1. |
| JPQL Aggregate Examples |
| </a></span></dt></dl></div><p> |
| 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 <code class="literal">SELECT</code> clause of a query: |
| <code class="literal">AVG</code>, <code class="literal">COUNT</code>, <code class="literal">MAX</code>, |
| <code class="literal">MIN</code>, <code class="literal">SUM</code>. For all aggregate functions |
| except <code class="literal">COUNT</code>, the path expression that is the argument to |
| the aggregate function must terminate in a state-field. The path expression |
| argument to <code class="literal">COUNT</code> may terminate in either a state-field or a |
| association-field, or the argument to <code class="literal">COUNT</code> may be an |
| identification variable. Arguments to the functions <code class="literal">SUM</code> and |
| <code class="literal">AVG</code> must be numeric. Arguments to the functions <code class="literal">MAX |
| </code> and <code class="literal">MIN</code> 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: </p><div class="itemizedlist"><ul type="disc"><li><p><code class="literal">COUNT</code> returns |
| Long. |
| </p></li><li><p> |
| <code class="literal">MAX</code>, <code class="literal">MIN</code> return the type of the |
| state-field to which they are applied. |
| </p></li><li><p> |
| <code class="literal">AVG</code> returns Double. |
| </p></li><li><p> |
| <code class="literal">SUM</code> returns Long when applied to state-fields of integral |
| types (other than BigInteger); Double when applied to state-fields of floating |
| point types; BigInteger when applied to state-fields of type BigInteger; and |
| BigDecimal when applied to state-fields of type BigDecimal. If <code class="literal">SUM |
| </code>, <code class="literal">AVG</code>, <code class="literal">MAX</code>, or <code class="literal">MIN |
| </code> is used, and there are no values to which the aggregate function can |
| be applied, the result of the aggregate function is <code class="literal">NULL</code>. If |
| <code class="literal">COUNT</code> is used, and there are no values to which <code class="literal"> |
| COUNT</code> can be applied, the result of the aggregate function is 0. |
| </p></li></ul></div><p> |
| </p><p> |
| The argument to an aggregate function may be preceded by the keyword <code class="literal"> |
| DISTINCT</code> to specify that duplicate values are to be eliminated before |
| the aggregate function is applied. |
| It is legal to specify <code class="literal">DISTINCT</code> with <code class="literal">MAX</code> |
| or <code class="literal">MIN</code>, but it does not affect the result. |
| Null values are eliminated before the |
| aggregate function is applied, regardless of whether the keyword <code class="literal"> |
| DISTINCT</code> is specified. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h5 class="title"><a name="jpa_langref_agg_examples"></a>2.7.4.1. |
| JPQL Aggregate Examples |
| </h5></div></div></div><p> |
| The following query returns the average price of all magazines: |
| </p><pre class="programlisting">SELECT AVG(mag.price) FROM Magazine mag</pre><p> The |
| following query returns the sum of all the prices from all the |
| magazines published by 'Larry': </p><pre class="programlisting">SELECT SUM(mag.price) FROM Publisher pub JOIN pub.magazines mag pub.firstName = 'Larry' |
| </pre><p> The following query returns the total number of magazines: |
| </p><pre class="programlisting">SELECT COUNT(mag) FROM Magazine mag</pre><p> |
| </p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_orderby"></a>2.8. |
| JPQL ORDER BY Clause |
| </h3></div></div></div><p> |
| The <code class="literal">ORDER BY</code> clause allows the objects or values that are |
| returned by the query to be ordered. The syntax of the <code class="literal">ORDER BY |
| </code> clause is |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>orderby_clause ::= ORDER BY orderby_item {, |
| orderby_item}* |
| </p></li><li><p> |
| orderby_item ::= state_field_path_expression [ASC | DESC] |
| </p></li></ul></div><p> |
| </p><p> |
| When the <code class="literal">ORDER BY</code> clause is used in a query, each element of |
| the <code class="literal">SELECT</code> clause of the query must be one of the following: |
| an identification variable x, optionally denoted as <code class="literal">OBJECT(x)</code> |
| , a single_valued_association_path_expression, or a state_field_path_expression. |
| For example: </p><pre class="programlisting"> |
| SELECT pub FROM Publisher pub ORDER BY pub.revenue, pub.name |
| </pre><p> If more than one orderby_item is specified, the left-to-right |
| sequence of the orderby_item elements determines the precedence, whereby the |
| leftmost orderby_item has highest precedence. The keyword <code class="literal">ASC</code> |
| specifies that ascending ordering be used; the keyword <code class="literal">DESC</code> |
| specifies that descending ordering be used. Ascending ordering is the default. |
| SQL rules for the ordering of null values apply: that is, all null values must |
| appear before all non-null values in the ordering or all null values must appear |
| after all non-null values in the ordering, but it is not specified which. The |
| ordering of the query result is preserved in the result of the query method if |
| the <code class="literal">ORDER BY</code> clause is used. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_bulk_ops"></a>2.9. |
| JPQL Bulk Update and Delete |
| </h3></div></div></div><p> |
| Bulk update and delete operations apply to entities of a single |
| entity class (together with its subclasses, if any). Only one entity abstract |
| schema type may be specified in the <code class="literal">FROM</code> or <code class="literal">UPDATE |
| </code> clause. The syntax of these operations is as follows: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p>update_statement ::= update_clause [where_clause] |
| </p></li><li><p> |
| update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET |
| update_item {, update_item}* |
| </p></li><li><p> |
| update_item ::= [identification_variable.]{state_field | |
| single_valued_association_field} = new_value |
| </p></li><li><p> |
| new_value ::= simple_arithmetic_expression | string_primary | datetime_primary | |
| boolean_primary | enum_primary simple_entity_expression | NULL |
| </p></li><li><p> |
| delete_statement ::= delete_clause [where_clause] |
| </p></li><li><p> |
| delete_clause ::= DELETE FROM abstract_schema_name [[AS] |
| identification_variable] |
| </p></li></ul></div><p> |
| </p><p> |
| The syntax of the <code class="literal">WHERE</code> clause is described in |
| <a href="jpa_langref.html#jpa_langref_where" title="2.4. JPQL WHERE Clause">Section 2.4, “ |
| JPQL WHERE Clause |
| ”</a>. A delete operation only applies to |
| entities of the specified class and its subclasses. It does not cascade to |
| related entities. The new_value specified for an update operation must be |
| compatible in type with the state-field to which it is assigned. Bulk update |
| maps directly to a database update operation, bypassing optimistic locking |
| checks. Portable applications must manually update the value of the version |
| column, if desired, and/or manually validate the value of the version column. |
| The persistence context is not synchronized with the result of the bulk update |
| or delete. Caution should be used when executing bulk update or delete |
| operations because they may result in inconsistencies between the database and |
| the entities in the active persistence context. In general, bulk update and |
| delete operations should only be performed within a separate transaction or at |
| the beginning of a transaction (before entities have been accessed whose state |
| might be affected by such operations). |
| </p><p> |
| Examples: </p><pre class="programlisting">DELETE FROM Publisher pub WHERE pub.revenue > 1000000.0 |
| </pre><pre class="programlisting">DELETE FROM Publisher pub WHERE pub.revenue = 0 AND pub.magazines IS EMPTY |
| </pre><pre class="programlisting">UPDATE Publisher pub SET pub.status = 'outstanding' |
| WHERE pub.revenue < 1000000 AND 20 > (SELECT COUNT(mag) FROM pub.magazines mag) |
| </pre><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_null_values"></a>2.10. |
| JPQL Null Values |
| </h3></div></div></div><p> |
| When the target of a reference does not exist in the database, its value is |
| regarded as <code class="literal">NULL</code>. SQL 92 <code class="literal">NULL</code> semantics |
| defines the evaluation of conditional expressions containing <code class="literal">NULL |
| </code> values. The following is a brief description of these semantics: |
| </p><p> |
| </p><div class="itemizedlist"><ul type="disc"><li><p> Comparison or arithmetic operations with a |
| <code class="literal">NULL</code> value always yield an unknown value. |
| </p></li><li><p> |
| Two <code class="literal">NULL</code> values are not considered to be equal, the |
| comparison yields an unknown value. |
| </p></li><li><p> |
| Comparison or arithmetic operations with an unknown value always yield an |
| unknown value. |
| </p></li><li><p> |
| The <code class="literal">IS NULL</code> and <code class="literal">IS NOT NULL</code> operators |
| convert a <code class="literal">NULL</code> state-field or single-valued association-field |
| value into the respective <code class="literal">TRUE</code> or <code class="literal">FALSE</code> |
| value. |
| </p></li></ul></div><p> |
| </p><p> |
| Note: The JPQL defines the empty string, "", as a string with 0 length, which is |
| not equal to a <code class="literal">NULL</code> value. However, <code class="literal">NULL</code> |
| values and empty strings may not always be distinguished when queries are mapped |
| to some databases. Application developers should therefore not rely on the |
| semantics of query comparisons involving the empty string and <code class="literal">NULL |
| </code> value. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_equality"></a>2.11. |
| JPQL Equality and Comparison Semantics |
| </h3></div></div></div><p> |
| Only the values of like types are permitted to be compared. A type is like |
| another type if they correspond to the same Java language type, or if one is a |
| primitive Java language type and the other is the wrappered Java class type |
| equivalent (e.g., int and Integer are like types in this sense). There is one |
| exception to this rule: it is valid to compare numeric values for which the |
| rules of numeric promotion apply. Conditional expressions attempting to compare |
| non-like type values are disallowed except for this numeric case. Note that the |
| arithmetic operators and comparison operators are permitted to be applied to |
| state-fields and input parameters of the wrappered Java class equivalents to the |
| primitive numeric Java types. Two entities of the same abstract schema type are |
| equal if and only if they have the same primary key value. Only |
| equality/inequality comparisons over enums are required to be supported. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_langref_bnf"></a>2.12. |
| JPQL BNF |
| </h3></div></div></div><p> |
| The following is the BNF for the Java Persistence query language, from section |
| 4.14 of the JSR 220 specification. |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| QL_statement ::= select_statement | update_statement | delete_statement |
| </p></li><li><p> |
| select_statement ::= select_clause from_clause [where_clause] [groupby_clause] |
| [having_clause] [orderby_clause] |
| </p></li><li><p> |
| update_statement ::= update_clause [where_clause] |
| </p></li><li><p> |
| delete_statement ::= delete_clause [where_clause] |
| </p></li><li><p> |
| from_clause ::= <code class="literal">FROM</code> identification_variable_declaration {, |
| {identification_variable_declaration | collection_member_declaration}}* |
| </p></li><li><p> |
| identification_variable_declaration ::= range_variable_declaration { join | |
| fetch_join }* |
| </p></li><li><p> |
| range_variable_declaration ::= abstract_schema_name [ <code class="literal">AS</code> ] |
| identification_variable |
| </p></li><li><p> |
| join ::= join_spec join_association_path_expression [ <code class="literal">AS</code> ] |
| identification_variable |
| </p></li><li><p> |
| fetch_join ::= join_spec <code class="literal">FETCH</code> |
| join_association_path_expression |
| </p></li><li><p> |
| association_path_expression ::= collection_valued_path_expression | |
| single_valued_association_path_expression |
| </p></li><li><p> |
| join_spec ::= [ <code class="literal">LEFT</code> [ <code class="literal">OUTER</code> ]| <code class="literal"> |
| INNER</code> ] <code class="literal">JOIN</code> |
| </p></li><li><p> |
| join_association_path_expression ::= join_collection_valued_path_expression | |
| join_single_valued_association_path_expression |
| </p></li><li><p> |
| join_collection_valued_path_expression ::= |
| identification_variable.collection_valued_association_field |
| </p></li><li><p> |
| join_single_valued_association_path_expression ::= |
| identification_variable.single_valued_association_field |
| </p></li><li><p> |
| collection_member_declaration ::= <code class="literal">IN</code> |
| (collection_valued_path_expression) [ <code class="literal">AS</code> ] |
| identification_variable |
| </p></li><li><p> |
| single_valued_path_expression ::= state_field_path_expression | |
| single_valued_association_path_expression |
| </p></li><li><p> |
| state_field_path_expression ::= {identification_variable | |
| single_valued_association_path_expression}.state_field |
| </p></li><li><p> |
| single_valued_association_path_expression ::= |
| identification_variable.{single_valued_association_field.}* |
| single_valued_association_field |
| </p></li><li><p> |
| collection_valued_path_expression ::= |
| identification_variable.{single_valued_association_field.}*collection_valued_association_field |
| </p></li><li><p> |
| state_field ::= {embedded_class_state_field.}*simple_state_field |
| </p></li><li><p> |
| update_clause ::= <code class="literal">UPDATE</code> abstract_schema_name [[ <code class="literal">AS |
| </code> ] identification_variable] <code class="literal">SET</code> update_item {, |
| update_item}* |
| </p></li><li><p> |
| update_item ::= [identification_variable.]{state_field | |
| single_valued_association_field}= new_value |
| </p></li><li><p> |
| new_value ::= simple_arithmetic_expression | string_primary | datetime_primary | |
| boolean_primary | enum_primary simple_entity_expression | <code class="literal">NULL |
| </code> |
| </p></li><li><p> |
| delete_clause ::= <code class="literal">DELETE</code><code class="literal">FROM</code> |
| abstract_schema_name [[ <code class="literal">AS</code> ] identification_variable] |
| </p></li><li><p> |
| select_clause ::= <code class="literal">SELECT</code> [ <code class="literal">DISTINCT</code> ] |
| select_expression {, select_expression}* |
| </p></li><li><p> |
| select_expression ::= single_valued_path_expression | aggregate_expression | |
| identification_variable | <code class="literal">OBJECT</code> (identification_variable)| |
| constructor_expression |
| </p></li><li><p> |
| constructor_expression ::= <code class="literal">NEW</code> constructor_name( |
| constructor_item {, constructor_item}*) |
| </p></li><li><p> |
| constructor_item ::= single_valued_path_expression | aggregate_expression |
| </p></li><li><p> |
| aggregate_expression ::= { <code class="literal">AVG</code> | <code class="literal">MAX</code> | |
| <code class="literal">MIN</code> | <code class="literal">SUM</code> }([ <code class="literal">DISTINCT</code> |
| ] state_field_path_expression) | <code class="literal">COUNT</code> ([ <code class="literal">DISTINCT |
| </code> ] identification_variable | state_field_path_expression | |
| single_valued_association_path_expression) |
| </p></li><li><p> |
| where_clause ::= <code class="literal">WHERE</code> conditional_expression |
| </p></li><li><p> |
| groupby_clause ::= <code class="literal">GROUP</code><code class="literal">BY</code> groupby_item {, |
| groupby_item}* |
| </p></li><li><p> |
| groupby_item ::= single_valued_path_expression | identification_variable |
| </p></li><li><p> |
| having_clause ::= <code class="literal">HAVING</code> conditional_expression |
| </p></li><li><p> |
| orderby_clause ::= <code class="literal">ORDER</code><code class="literal">BY</code> orderby_item {, |
| orderby_item}* |
| </p></li><li><p> |
| orderby_item ::= state_field_path_expression [ <code class="literal">ASC</code> | |
| <code class="literal">DESC</code> ] |
| </p></li><li><p> |
| subquery ::= simple_select_clause subquery_from_clause [where_clause] |
| [groupby_clause] [having_clause] |
| </p></li><li><p> |
| subquery_from_clause ::= <code class="literal">FROM</code> |
| subselect_identification_variable_declaration {, |
| subselect_identification_variable_declaration}* |
| </p></li><li><p> |
| subselect_identification_variable_declaration ::= |
| identification_variable_declaration | association_path_expression [ <code class="literal">AS |
| </code> ] identification_variable | collection_member_declaration |
| </p></li><li><p> |
| simple_select_clause ::= <code class="literal">SELECT</code> [ <code class="literal">DISTINCT</code> |
| ] simple_select_expression |
| </p></li><li><p> |
| simple_select_expression ::= single_valued_path_expression | |
| aggregate_expression | identification_variable |
| </p></li><li><p> |
| conditional_expression ::= conditional_term | conditional_expression <code class="literal"> |
| OR</code> conditional_term |
| </p></li><li><p> |
| conditional_term ::= conditional_factor | conditional_term <code class="literal">AND |
| </code> conditional_factor |
| </p></li><li><p> |
| conditional_factor ::= [ <code class="literal">NOT</code> ] conditional_primary |
| </p></li><li><p> |
| conditional_primary ::= simple_cond_expression |(conditional_expression) |
| </p></li><li><p> |
| simple_cond_expression ::= comparison_expression | between_expression | |
| like_expression | in_expression | null_comparison_expression | |
| empty_collection_comparison_expression | collection_member_expression | |
| exists_expression |
| </p></li><li><p> |
| between_expression ::= arithmetic_expression [ <code class="literal">NOT</code> ] |
| <code class="literal">BETWEEN</code> arithmetic_expression <code class="literal">AND</code> |
| arithmetic_expression | string_expression [ <code class="literal">NOT</code> ] <code class="literal"> |
| BETWEEN</code> string_expression <code class="literal">AND</code> string_expression | |
| datetime_expression [ <code class="literal">NOT</code> ] <code class="literal">BETWEEN</code> |
| datetime_expression <code class="literal">AND</code> datetime_expression |
| </p></li><li><p> |
| in_expression ::= state_field_path_expression [ <code class="literal">NOT</code> ] |
| <code class="literal">IN</code> ( in_item {, in_item}* | subquery) |
| </p></li><li><p> |
| in_item ::= literal | input_parameter |
| </p></li><li><p> |
| like_expression ::= string_expression [ <code class="literal">NOT</code> ] <code class="literal">LIKE |
| </code> pattern_value [ <code class="literal">ESCAPE</code> escape_character] |
| </p></li><li><p> |
| null_comparison_expression ::= {single_valued_path_expression | input_parameter} |
| <code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">NULL</code> |
| </p></li><li><p> |
| empty_collection_comparison_expression ::= collection_valued_path_expression |
| <code class="literal">IS</code> [ <code class="literal">NOT</code> ] <code class="literal">EMPTY</code> |
| </p></li><li><p> |
| collection_member_expression ::= entity_expression [ <code class="literal">NOT</code> ] |
| <code class="literal">MEMBER</code> [ <code class="literal">OF</code> ] |
| collection_valued_path_expression |
| </p></li><li><p> |
| exists_expression ::= [ <code class="literal">NOT</code> ] <code class="literal">EXISTS</code> |
| (subquery) |
| </p></li><li><p> |
| all_or_any_expression ::= { <code class="literal">ALL</code> | <code class="literal">ANY</code> | |
| <code class="literal">SOME</code> }(subquery) |
| </p></li><li><p> |
| comparison_expression ::= |
| string_expressioncomparison_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} |
| </p></li><li><p> |
| comparison_operator ::== |> |>= |< |<= |<> |
| </p></li><li><p> |
| arithmetic_expression ::= simple_arithmetic_expression |(subquery) |
| </p></li><li><p> |
| simple_arithmetic_expression ::= arithmetic_term | simple_arithmetic_expression |
| {+ |- } arithmetic_term |
| </p></li><li><p> |
| arithmetic_term ::= arithmetic_factor | arithmetic_term {* |/ } |
| arithmetic_factor |
| </p></li><li><p> |
| arithmetic_factor ::= [{+ |-}] arithmetic_primary |
| </p></li><li><p> |
| arithmetic_primary ::= state_field_path_expression | numeric_literal | |
| (simple_arithmetic_expression) | input_parameter | functions_returning_numerics |
| | aggregate_expression |
| </p></li><li><p> |
| string_expression ::= string_primary |(subquery) |
| </p></li><li><p> |
| string_primary ::= state_field_path_expression | string_literal | |
| input_parameter | functions_returning_strings | aggregate_expression |
| </p></li><li><p> |
| datetime_expression ::= datetime_primary |(subquery) |
| </p></li><li><p> |
| datetime_primary ::= state_field_path_expression | input_parameter | |
| functions_returning_datetime | aggregate_expression |
| </p></li><li><p> |
| boolean_expression ::= boolean_primary |(subquery) |
| </p></li><li><p> |
| boolean_primary ::= state_field_path_expression | boolean_literal | |
| input_parameter | |
| </p></li><li><p> |
| enum_expression ::= enum_primary |(subquery) |
| </p></li><li><p> |
| enum_primary ::= state_field_path_expression | enum_literal | input_parameter | |
| </p></li><li><p> |
| entity_expression ::= single_valued_association_path_expression | |
| simple_entity_expression |
| </p></li><li><p> |
| simple_entity_expression ::= identification_variable | input_parameter |
| </p></li><li><p> |
| functions_returning_numerics ::= <code class="literal">LENGTH</code> (string_primary)| |
| <code class="literal">LOCATE</code> (string_primary,string_primary [, |
| simple_arithmetic_expression]) | <code class="literal">ABS</code> |
| (simple_arithmetic_expression) | <code class="literal">SQRT</code> |
| (simple_arithmetic_expression) | <code class="literal">MOD</code> |
| (simple_arithmetic_expression, simple_arithmetic_expression) | <code class="literal">SIZE |
| </code> (collection_valued_path_expression) |
| </p></li><li><p> |
| functions_returning_datetime ::= <code class="literal">CURRENT_DATE</code> | <code class="literal"> |
| CURRENT_TIME</code> | <code class="literal">CURRENT_TIMESTAMP</code> |
| </p></li><li><p> |
| functions_returning_strings ::= <code class="literal">CONCAT</code> (string_primary, |
| string_primary) | <code class="literal">SUBSTRING</code> (string_primary, |
| simple_arithmetic_expression,simple_arithmetic_expression)| <code class="literal">TRIM |
| </code> ([[trim_specification] [trim_character] <code class="literal">FROM</code> ] |
| string_primary) | <code class="literal">LOWER</code> (string_primary) | <code class="literal">UPPER |
| </code> (string_primary) |
| </p></li><li><p> |
| trim_specification ::= <code class="literal">LEADING</code> | <code class="literal">TRAILING</code> |
| | <code class="literal">BOTH</code> |
| </p></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="jpa_overview_query.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="jpa_overview_query.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="jpa_overview_sqlquery.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 10. |
| JPA Query |
| </td><td width="20%" align="center"><a accesskey="h" href="manual.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 11. |
| SQL Queries |
| </td></tr></table></div></body></html> |