| <html><head> |
| <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> |
| <title>Chapter 10. JPA Query</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 User's Guide"><link rel="up" href="jpa_overview.html" title="Part 2. Java Persistence API"><link rel="prev" href="jpa_overview_trans_local.html" title="2. The EntityTransaction Interface"><link rel="next" href="jpa_langref.html" title="2. JPQL Language Reference"></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">Chapter 10. |
| JPA Query |
| </th></tr><tr><td width="20%" align="left"><a accesskey="p" href="jpa_overview_trans_local.html">Prev</a> </td><th width="60%" align="center">Part 2. Java Persistence API</th><td width="20%" align="right"> <a accesskey="n" href="jpa_langref.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en" id="jpa_overview_query"><div class="titlepage"><div><div><h2 class="title"><a name="jpa_overview_query"></a>Chapter 10. |
| JPA Query |
| </h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_query_api">1. |
| JPQL API |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_basic">1.1. |
| Query Basics |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_relations">1.2. |
| Relation Traversal |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_join_fetch">1.3. |
| Fetch Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_functions">1.4. |
| JPQL Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_inheritance">1.5. |
| Polymorphic Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_params">1.6. |
| Query Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_hints">1.7. |
| Query Hints |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.7.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.7.2. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.7.3. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.7.4. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d0e6512">1.7.5. |
| Oracle Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.7.6. |
| Named Query Hints |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_ordering">1.8. |
| Ordering |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_aggregates">1.9. |
| Aggregates |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_named">1.10. |
| Named Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_delete">1.11. |
| Delete By Query |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_update">1.12. |
| Update By Query |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html">2. |
| JPQL Language Reference |
| </a></span></dt><dd><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></dd></dl></div><a class="indexterm" name="d0e5728"></a><a class="indexterm" name="d0e5733"></a><div class="mediaobject"><table border="0" summary="manufactured viewport for HTML img" cellspacing="0" cellpadding="0" width="195"><tr><td><img src="img/jpa-query.png"></td></tr></table></div><p> |
| The <code class="classname">javax.persistence.Query</code> interface is the mechanism |
| for issuing queries in JPA. The primary query language used is the Java |
| Persistence Query Language, or <code class="literal">JPQL</code>. JPQL is syntactically |
| very similar to SQL, but is object-oriented rather than table-oriented. |
| </p><p> |
| The API for executing JPQL queries will be discussed in |
| <a href="jpa_overview_query.html#jpa_query_api" title="1. JPQL API">Section 1, “ |
| JPQL API |
| ”</a>, and a full language reference will be |
| covered in <a href="jpa_langref.html" title="2. JPQL Language Reference">Section 2, “ |
| JPQL Language Reference |
| ”</a>. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="jpa_query_api"></a>1. |
| JPQL API |
| </h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_basic">1.1. |
| Query Basics |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_relations">1.2. |
| Relation Traversal |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_join_fetch">1.3. |
| Fetch Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_functions">1.4. |
| JPQL Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_inheritance">1.5. |
| Polymorphic Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_params">1.6. |
| Query Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_hints">1.7. |
| Query Hints |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.7.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.7.2. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.7.3. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.7.4. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d0e6512">1.7.5. |
| Oracle Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.7.6. |
| Named Query Hints |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_ordering">1.8. |
| Ordering |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_aggregates">1.9. |
| Aggregates |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_named">1.10. |
| Named Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_delete">1.11. |
| Delete By Query |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_update">1.12. |
| Update By Query |
| </a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_basic"></a>1.1. |
| Query Basics |
| </h3></div></div></div><pre class="programlisting">SELECT x FROM Magazine x |
| </pre><p> |
| The preceding is a simple JPQL query for all <code class="classname">Magazine</code> |
| entities. |
| </p><pre class="programlisting"> |
| public Query createQuery(String jpql); |
| </pre><p> |
| The |
| <a xmlns:xlink="http://www.w3.org/1999/xlink" href="http://java.sun.com/javaee/5/docs/api/javax/persistence/EntityManager.html" target="_top"> |
| <code class="methodname">EntityManager.createQuery</code></a> method creates a |
| <code class="classname">Query</code> instance from a given JPQL string. |
| </p><pre class="programlisting"> |
| public List getResultList(); |
| </pre><p> |
| Invoking |
| <a xmlns:xlink="http://www.w3.org/1999/xlink" href="http://java.sun.com/javaee/5/docs/api/javax/persistence/Query.html#getResultList()" target="_top"> |
| <code class="methodname">Query.getResultList</code></a> executes the query and |
| returns a <code class="classname">List</code> containing the matching objects. The |
| following example executes our <code class="classname">Magazine</code> query above: |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createQuery("SELECT x FROM Magazine x"); |
| List<Magazine> results = (List<Magazine>) q.getResultList(); |
| </pre><p> |
| A JPQL query has an internal namespace declared in the <code class="literal">from</code> |
| clause of the query. Arbitrary identifiers are assigned to entities so that they |
| can be referenced elsewhere in the query. In the query example above, the |
| identifier <code class="literal">x</code> is assigned to the entity <code class="classname"> Magazine |
| </code>. |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> |
| The <code class="literal">as</code> keyword can optionally be used when declaring |
| identifiers in the <code class="literal">from</code> clause. <code class="literal">SELECT x FROM |
| Magazine x</code> and <code class="literal">SELECT x FROM Magazine AS x</code> are |
| synonymous. |
| </p></div><p> |
| Following the <code class="literal">select</code> clause of the query is the object or |
| objects that the query returns. In the case of the query above, the query's |
| result list will contain instances of the <code class="classname">Magazine</code> class. |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> |
| When selecting entities, you can optional use the keyword <code class="literal">object |
| </code>. The clauses <code class="literal">select x</code> and <code class="literal">SELECT |
| OBJECT(x)</code> are synonymous. |
| </p></div><p> |
| The optional <code class="literal">where</code> clause places criteria on matching |
| results. For example: |
| </p><pre class="programlisting">SELECT x FROM Magazine x WHERE x.title = 'JDJ'</pre><p> |
| Keywords in JPQL expressions are case-insensitive, but entity, identifier, and |
| member names are not. For example, the expression above could also be expressed |
| as: |
| </p><pre class="programlisting">select x from Magazine x where x.title = 'JDJ'</pre><p> |
| But it could not be expressed as: |
| </p><pre class="programlisting">SELECT x FROM Magazine x WHERE x.TITLE = 'JDJ'</pre><p> |
| As with the <code class="literal">select</code> clause, alias names in the <code class="literal">where |
| </code> clause are resolved to the entity declared in the <code class="literal">from |
| </code> clause. The query above could be described in English as "for all |
| <code class="classname">Magazine</code> instances <code class="literal">x</code>, return a list |
| of every <code class="literal">x</code> such that <code class="literal">x</code>'s <code class="literal">title |
| </code> field is equal to 'JDJ'". |
| </p><p> |
| JPQL uses SQL-like syntax for query criteria. The <code class="literal">and</code> and |
| <code class="literal">or</code> logical operators chain multiple criteria together: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' |
| </pre><p> |
| The <code class="literal">=</code> operator tests for equality. <code class="literal"><> |
| </code> tests for inequality. JPQL also supports the following arithmetic |
| operators for numeric comparisons: <code class="literal">>, >=, <, <=</code>. |
| For example: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price > 3.00 AND x.price <= 5.00 |
| </pre><p> |
| This query returns all magazines whose price is greater than 3.00 and less than |
| or equal to 5.00. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price <> 3.00 |
| </pre><p> |
| This query returns all Magazines whose price is not equal to 3.00. |
| </p><p> |
| You can group expressions together using parentheses in order to specify how |
| they are evaluated. This is similar to how parentheses are used in Java. For |
| example: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE (x.price > 3.00 AND x.price <= 5.00) OR x.price < 7.00 |
| </pre><p> |
| This expression would match magazines whose price is less than 7.00. |
| Alternately: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price > 3.00 AND (x.price <= 5.00 OR x.price < 7.00) |
| </pre><p> |
| This expression would match magazines whose price is 4.00, 5.00 or 6.00, but not |
| 1.00, 2.00 or 3.00. |
| </p><p> |
| JPQL also includes the following conditionals: |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| <a class="indexterm" name="d0e5930"></a> |
| <code class="literal">[NOT] BETWEEN</code>: Shorthand for expressing that a value falls |
| between two other values. The following two statements are synonymous: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price >= 3.00 AND x.price <= 5.00 |
| </pre><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e5944"></a> |
| <code class="literal">[NOT] LIKE</code>: Performs a string comparison with wildcard |
| support. The special character '_' in the parameter means to match any single |
| character, and the special character '%' means to match any sequence of |
| characters. The following statement matches title fields "JDJ" and "JavaPro", |
| but not "IT Insider": |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.title LIKE 'J%' |
| </pre><p> |
| The following statement matches the title field "JDJ" but not "JavaPro": |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.title LIKE 'J__' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e5960"></a> |
| <code class="literal">[NOT] IN</code>: Specifies that the member must be equal to one |
| element of the provided list. The following two statements are synonymous: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.title IN ('JDJ', 'JavaPro', 'IT Insider') |
| </pre><pre class="programlisting">SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' OR x.title = 'IT Insider' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e5974"></a> |
| <code class="literal">IS [NOT] EMPTY</code>: Specifies that the collection field holds no |
| elements. For example: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.articles is empty |
| </pre><p> |
| This statement will return all magazines whose <code class="literal"> articles</code> |
| member contains no elements. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e5991"></a> |
| <code class="literal">IS [NOT] NULL</code>: Specifies that the field is equal to null. |
| For example: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.publisher is null |
| </pre><p> |
| This statement will return all Magazine instances whose "publisher" field is set |
| to <code class="literal">null</code>. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e6008"></a> |
| <code class="literal">NOT</code>: Negates the contained expression. For example, the |
| following two statements are synonymous: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE NOT(x.price = 10.0) |
| </pre><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.price <> 10.0 |
| </pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_relations"></a>1.2. |
| Relation Traversal |
| </h3></div></div></div><p> |
| Relations between objects can be traversed using Java-like syntax. For example, |
| if the Magazine class has a field named "publisher" of type Company, that |
| relation can be queried as follows: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' |
| </pre><p> |
| This query returns all <code class="classname">Magazine</code> instances whose <code class="literal"> |
| publisher</code> field is set to a <code class="classname">Company</code> instance |
| whose name is "Random House". |
| </p><p> |
| Single-valued relation traversal implies that the relation is not null. In SQL |
| terms, this is known as an <span class="emphasis"><em>inner join</em></span>. If you want to also |
| include relations that are null, you can specify: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE x.publisher.name = 'Random House' or x.publisher is null |
| </pre><p> |
| You can also traverse collection fields in queries, but you must declare each |
| traversal in the <code class="literal">from</code> clause. Consider: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x, IN(x.articles) y WHERE y.authorName = 'John Doe' |
| </pre><p> |
| This query says that for each <code class="classname">Magazine</code><code class="literal"> x |
| </code>, traverse the <code class="literal">articles</code> relation and check each |
| <code class="classname">Article</code> <code class="literal">y</code>, and pass the filter if |
| <code class="literal">y</code>'s <code class="literal">authorName</code> field is equal to "John |
| Doe". In short, this query will return all magazines that have any articles |
| written by John Doe. |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> |
| The <code class="literal">IN()</code> syntax can also be expressed with the keywords |
| <code class="literal">inner join</code>. The statements <code class="literal">SELECT x FROM Magazine |
| x, IN(x.articles) y WHERE y.authorName = 'John Doe'</code> and <code class="literal"> |
| SELECT x FROM Magazine x inner join x.articles y WHERE y.authorName = 'John Doe' |
| </code> are synonymous. |
| </p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_join_fetch"></a>1.3. |
| Fetch Joins |
| </h3></div></div></div><p> |
| JPQL queries may specify one or more <code class="literal">join fetch</code> declarations, |
| which allow the query to specify which fields in the returned instances will be |
| pre-fetched. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x join fetch x.articles WHERE x.title = 'JDJ' |
| </pre><p> |
| The query above returns <code class="classname">Magazine</code> instances and guarantees |
| that the <code class="literal">articles</code> field will already be fetched in the |
| returned instances. |
| </p><p> |
| Multiple fields may be specified in separate <code class="literal">join fetch</code> |
| declarations: </p><pre class="programlisting"> |
| SELECT x FROM Magazine x join fetch x.articles join fetch x.authors WHERE x.title = 'JDJ' |
| </pre><p> |
| </p><p> |
| </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> Specifying the <code class="literal">join fetch</code> declaration is |
| functionally equivalent to adding the fields to the Query's <code class="classname"> |
| FetchConfiguration</code>. See <a href="ref_guide_fetch.html" title="7. Fetch Groups">Section 7, “ |
| Fetch Groups |
| ”</a>. |
| </p></div><p> |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_functions"></a>1.4. |
| JPQL Functions |
| </h3></div></div></div><p> |
| As well as supporting direct field and relation comparisons, JPQL supports a |
| pre-defined set of functions that you can apply. |
| </p><div class="itemizedlist"><ul type="disc"><li><p> |
| <a class="indexterm" name="d0e6137"></a> |
| <code class="literal">CONCAT(string1, string2)</code>: Concatenates two string fields or |
| literals. For example: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6149"></a> |
| <code class="literal">SUBSTRING(string, startIndex, length)</code>: Returns the part of |
| the <code class="literal">string</code> argument starting at <code class="literal">startIndex</code> |
| (1-based) and ending at <code class="literal">length</code> characters past <code class="literal"> |
| startIndex</code>. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6173"></a> |
| <code class="literal">TRIM([LEADING | TRAILING | BOTH] [character FROM] string</code>: |
| Trims the specified character from either the beginning ( <code class="literal">LEADING |
| </code>) end ( <code class="literal">TRAILING</code>) or both ( <code class="literal"> BOTH |
| </code>) of the string argument. If no trim character is specified, the |
| space character will be trimmed. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6194"></a> |
| <code class="literal">LOWER(string)</code>: Returns the lower-case of the specified |
| string argument. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6206"></a> |
| <code class="literal">UPPER(string)</code>: Returns the upper-case of the specified |
| string argument. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO' |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6218"></a> |
| <code class="literal">LENGTH(string)</code>: Returns the number of characters in the |
| specified string argument. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6230"></a> |
| <code class="literal">LOCATE(searchString, candidateString [, startIndex])</code>: |
| Returns the first index of <code class="literal">searchString</code> in <code class="literal"> |
| candidateString</code>. Positions are 1-based. If the string is not found, |
| returns 0. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6248"></a> |
| <code class="literal">ABS(number)</code>: Returns the absolute value of the argument. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6260"></a> |
| <code class="literal">SQRT(number)</code>: Returns the square root of the argument. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6272"></a> |
| <code class="literal">MOD(number, divisor)</code>: Returns the modulo of <code class="literal">number |
| </code> and <code class="literal">divisor</code>. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0 |
| </pre></li><li><p> |
| <a class="indexterm" name="d0e6290"></a> |
| <code class="literal">CURRENT_DATE</code>: Returns the current date. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e6300"></a> |
| <code class="literal">CURRENT_TIME</code>: Returns the current time. |
| </p></li><li><p> |
| <a class="indexterm" name="d0e6310"></a> |
| <code class="literal">CURRENT_TIMESTAMP</code>: Returns the current timestamp. |
| </p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_inheritance"></a>1.5. |
| Polymorphic Queries |
| </h3></div></div></div><p> |
| All JPQL queries are polymorphic, which means the <code class="literal">from</code> clause |
| of a query includes not only instances of the specific entity class to which it |
| refers, but all subclasses of that class as well. The instances returned by a |
| query include instances of the subclasses that satisfy the query conditions. For |
| example, the following query may return instances of <code class="classname"> Magazine |
| </code>, as well as <code class="classname">Tabloid</code> and <code class="classname">Digest |
| </code> instances, where <code class="classname">Tabloid</code> and <code class="classname"> |
| Digest</code> are <code class="classname">Magazine</code> subclasses. |
| </p><pre class="programlisting">SELECT x FROM Magazine x WHERE x.price < 5</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_params"></a>1.6. |
| Query Parameters |
| </h3></div></div></div><p> |
| JPQL provides support for parameterized queries. Either named parameters or |
| positional parameters may be specified in the query string. Parameters allow you |
| to re-use query templates where only the input parameters vary. A single query |
| can declare either named parameters or positional parameters, but is not allowed |
| to declare both named and positional parameters. |
| </p><pre class="programlisting"> |
| public Query setParameter (int pos, Object value); |
| </pre><p> |
| Specify positional parameters in your JPQL string using an integer prefixed by a |
| question mark. You can then populate the <code class="classname">Query</code> object |
| with positional parameter values via calls to the <code class="methodname">setParameter |
| </code> method above. The method returns the <code class="classname">Query</code> |
| instance for optional method chaining. |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = ?1 and x.price > ?2"); |
| q.setParameter(1, "JDJ").setParameter(2, 5.0); |
| List<Magazine> results = (List<Magazine>) q.getResultList(); |
| </pre><p> |
| This code will substitute <code class="literal">JDJ</code> for the <code class="literal">?1</code> |
| parameter and <code class="literal">5.0</code> for the <code class="literal">?2</code> parameter, |
| then execute the query with those values. |
| </p><pre class="programlisting"> |
| public Query setParameter(String name, Object value); |
| </pre><p> |
| Named parameter are denoted by prefixing an arbitrary name with a colon in your |
| JPQL string. You can then populate the <code class="classname"> Query</code> object with |
| parameter values using the method above. Like the positional parameter method, |
| this method returns the <code class="classname">Query</code> instance for optional |
| method chaining. |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createQuery("SELECT x FROM Magazine x WHERE x.title = :titleParam and x.price > :priceParam"); |
| q.setParameter("titleParam", "JDJ").setParameter("priceParam", 5.0); |
| List<Magazine> results = (List<Magazine>) q.getResultList(); |
| </pre><p> |
| This code substitutes <code class="literal">JDJ</code> for the <code class="literal"> :titleParam |
| </code> parameter and <code class="literal">5.0</code> for the <code class="literal">:priceParam |
| </code> parameter, then executes the query with those values. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_hints"></a>1.7. |
| Query Hints |
| </h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.7.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.7.2. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.7.3. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.7.4. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d0e6512">1.7.5. |
| Oracle Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.7.6. |
| Named Query Hints |
| </a></span></dt></dl></div><p> |
| JPQL provides support for hints which are name/value pairs used to control locking and optimization keywords in sql. |
| The following example shows how to use the JPA hint api to set the <code class="classname">ReadLockMode</code> and <code class="classname">ResultCount</code> in the OpenJPA fetch plan. This will result in the sql keywords OPTIMIZE FOR 2 ROWS and UPDATE to be emitted into the sql provided that a pessimistic LockManager is being used. |
| </p><div class="example"><a name="jpa_query_hint1"></a><p class="title"><b>Example 10.1. |
| Query Hints |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| ... |
| Query q = em.createQuery("select m from Magazine m where ... "); |
| q.setHint("openjpa.hint.OptimizeResultCount", new Integer(2)); |
| q.setHint("openjpa.FetchPlan.ReadLockMode","WRITE"); |
| List r = q.getResultList(); |
| ... |
| </pre></div></div><br class="example-break"><p> |
| Invalid hints or hints which can not be processed by a particular database are ignored. Otherwise, invalid hints will result in an ArgumentException being thrown. |
| </p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_hints_locking"></a>1.7.1. |
| Locking Hints |
| </h4></div></div></div><p> |
| To avoid deadlock and optimistic update exceptions among multiple updaters, use a pessimistic LockManager, specified in the persistence unit definition, and use a hint name of "openjpa.FetchPlan.ReadLockMode" on queries for entities that must be locked for serialization. The value of <code class="classname">ReadLockMode</code> can be either "READ" or "WRITE". This results in FOR UPDATE or USE AND KEEP UPDATE LOCKS in sql. |
| </p><p> |
| Using a <code class="classname">ReadLockMode</code> hint with JPA optimistic locking ( i.e. specifying LockManager = "version") will result in the entity version field either being reread at end of transaction in the case of a value of "READ" or the version field updated at end of transaction in the case of "WRITE". You must define a version field in the entity mapping when using a version LockManager and using ReadLockMode. |
| </p><div class="table"><a name="d0e6436"></a><p class="title"><b>Table 10.1. |
| Interaction of ReadLockMode hint and LockManager |
| </b></p><div class="table-contents"><table summary="
 Interaction of ReadLockMode hint and LockManager
 " border="1"><colgroup><col align="left"><col align="left"><col align="left"></colgroup><thead><tr><th align="left"> |
| ReadLockMode |
| </th><th align="left"> |
| LockManager=pessimistic |
| </th><th align="left"> |
| LockManager=version |
| </th></tr></thead><tbody><tr><td align="left"> |
| READ |
| </td><td align="left"> |
| sql with UPDATE |
| </td><td align="left">sql without update; |
| <p> |
| reread version field at the end of transaction and check for no change. |
| </p> |
| </td></tr><tr><td align="left"> |
| WRITE |
| </td><td align="left"> |
| sql with UPDATE |
| </td><td align="left"> |
| sql without update; |
| <p> |
| force update version field at the end of transaction |
| </p> |
| </td></tr><tr><td align="left"> |
| not specified |
| </td><td align="left"> |
| sql without update |
| </td><td align="left"> |
| sql without update |
| </td></tr></tbody></table></div></div><br class="table-break"></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_hints_resultset"></a>1.7.2. |
| Result Set Size Hint |
| </h4></div></div></div><p> |
| To specify a result set size hint to those databases that support it, specify a hint name of "openjpa.hint.OptimizeResultCount" with an integer value greater than zero. This causes the sql keyword OPTIMIZE FOR to be generated. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_hints_isolation"></a>1.7.3. |
| Isolation Level Hint |
| </h4></div></div></div><p> |
| To specify an isolation level, specify a hint name of "openjpa.FetchPlan.Isolation". The value will be used to specify isolation level using the sql WITH <isolation> clause for those databases that support it. This hint only works in conjunction with the ReadLockMode hint. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_hints_fetchplan"></a>1.7.4. |
| Other Fetchplan Hints |
| </h4></div></div></div><p> |
| Any property of an OpenJPA FetchPlan can be changed using a hint by using a name of the form "openjpa.FetchPlan."<property name>.Valid property names include : |
| <code class="classname">MaxFetchDepth</code>, <code class="classname">FetchBatchSize</code>, <code class="classname">LockTimeOut</code>, <code class="classname">EagerFetchMode</code>, <code class="classname">SubclassFetchMode</code> and <code class="classname">Isolation</code>. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="d0e6512"></a>1.7.5. |
| Oracle Query Hints |
| </h4></div></div></div><p> |
| The hint name "openjpa.hint.OracleSelectHint" can be used to specify a string value of an Oracle query hint that will inserted into sql for an Oracle database.See <a href="dbsupport_oracle.html#dbsupport_oracle_query_hints" title="15.1. Using Query Hints with Oracle">Section 15.1, “ |
| Using Query Hints with Oracle |
| ”</a> for an example. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="jpa_hints_named"></a>1.7.6. |
| Named Query Hints |
| </h4></div></div></div><p> |
| Hints can also be included as part of a NamedQuery definition. |
| </p><div class="example"><a name="jpa_query_hint2"></a><p class="title"><b>Example 10.2. |
| Named Query using Hints |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| ... |
| @NamedQuery(name=" magsOverPrice", |
| query="SELECT x FROM Magazine x WHERE x.price > ?1", |
| hints={ @QueryHint (name="openjpa.hint.OptimizeResultCount", value="2"), |
| @QueryHint (name="openjpa.FetchPlan.ReadLockMode",value="WRITE")} ) |
| ... |
| </pre></div></div><br class="example-break"></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_ordering"></a>1.8. |
| Ordering |
| </h3></div></div></div><p> |
| JPQL queries may optionally contain an <code class="literal">order by</code> clause which |
| specifies one or more fields to order by when returning query results. You may |
| follow the <code class="literal">order by field</code> clause with the <code class="literal">asc |
| </code> or <code class="literal">desc</code> keywords, which indicate that ordering |
| should be ascending or descending, respectively. If the direction is omitted, |
| ordering is ascending by default. |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x order by x.title asc, x.price desc |
| </pre><p> |
| The query above returns <code class="classname">Magazine</code> instances sorted by |
| their title in ascending order. In cases where the titles of two or more |
| magazines are the same, those instances will be sorted by price in descending |
| order. |
| </p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_aggregates"></a>1.9. |
| Aggregates |
| </h3></div></div></div><p> |
| JPQL queries can select aggregate data as well as objects. JPQL includes the |
| <code class="literal">min</code>, <code class="literal">max</code>, <code class="literal">avg</code>, and |
| <code class="literal">count</code> aggregates. These functions can be used for reporting |
| and summary queries. |
| </p><p> |
| The following query will return the average of all the prices of all the |
| magazines: |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createQuery("SELECT AVG(x.price) FROM Magazine x"); |
| Number result = (Number) q.getSingleResult(); |
| </pre><p> |
| The following query will return the highest price of all the magazines titled |
| "JDJ": |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createQuery("SELECT MAX(x.price) FROM Magazine x WHERE x.title = 'JDJ'"); |
| Number result = (Number) q.getSingleResult(); |
| </pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_named"></a>1.10. |
| Named Queries |
| </h3></div></div></div><p> |
| Query templates can be statically declared using the <code class="literal"> NamedQuery |
| </code> and <code class="literal">NamedQueries</code> annotations. For example: |
| </p><pre class="programlisting"> |
| @Entity |
| @NamedQueries({ |
| @NamedQuery(name="magsOverPrice", |
| query="SELECT x FROM Magazine x WHERE x.price > ?1"), |
| @NamedQuery(name="magsByTitle", |
| query="SELECT x FROM Magazine x WHERE x.title = :titleParam") |
| }) |
| public class Magazine { |
| ... |
| } |
| </pre><p> |
| These declarations will define two named queries called <code class="literal">magsOverPrice |
| </code> and <code class="literal">magsByTitle</code>. |
| </p><pre class="programlisting"> |
| public Query createNamedQuery(String name); |
| </pre><p> |
| You retrieve named queries with the above <code class="classname">EntityManager</code> |
| method. For example: |
| </p><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createNamedQuery("magsOverPrice"); |
| q.setParameter(1, 5.0f); |
| List<Magazine> results = (List<Magazine>) q.getResultList(); |
| </pre><pre class="programlisting"> |
| EntityManager em = ... |
| Query q = em.createNamedQuery("magsByTitle"); |
| q.setParameter("titleParam", "JDJ"); |
| List<Magazine> results = (List<Magazine>) q.getResultList(); |
| </pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_delete"></a>1.11. |
| Delete By Query |
| </h3></div></div></div><p> |
| Queries are useful not only for finding objects, but for efficiently deleting |
| them as well. For example, you might delete all records created before a certain |
| date. Rather than bring these objects into memory and delete them individually, |
| JPA allows you to perform a single bulk delete based on JPQL criteria. |
| </p><p> |
| Delete by query uses the same JPQL syntax as normal queries, with one exception: |
| begin your query string with the <code class="literal">delete</code> keyword instead of |
| the <code class="literal">select</code> keyword. To then execute the delete, you call the |
| following <code class="classname">Query</code> method: |
| </p><pre class="programlisting"> |
| public int executeUpdate(); |
| </pre><p> |
| This method returns the number of objects deleted. The following example deletes |
| all subscriptions whose expiration date has passed. |
| </p><div class="example"><a name="jpa_overview_query_deleteex"></a><p class="title"><b>Example 10.3. |
| Delete by Query |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| Query q = em.createQuery("DELETE FROM Subscription s WHERE s.subscriptionDate < :today"); |
| q.setParameter("today", new Date()); |
| int deleted = q.executeUpdate(); |
| </pre></div></div><br class="example-break"></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="jpa_overview_query_update"></a>1.12. |
| Update By Query |
| </h3></div></div></div><p> |
| Similar to bulk deletes, it is sometimes necessary to perform updates against a |
| large number of queries in a single operation, without having to bring all the |
| instances down to the client. Rather than bring these objects into memory and |
| modifying them individually, JPA allows you to perform a single bulk update |
| based on JPQL criteria. |
| </p><p> |
| Update by query uses the same JPQL syntax as normal queries, except that the |
| query string begins with the <code class="literal">update</code> keyword instead of |
| <code class="literal">select</code>. To execute the update, you call the following |
| <code class="classname">Query</code> method: |
| </p><pre class="programlisting"> |
| public int executeUpdate(); |
| </pre><p> |
| This method returns the number of objects updated. The following example updates |
| all subscriptions whose expiration date has passed to have the "paid" field set |
| to true.. |
| </p><div class="example"><a name="jpa_overview_query_updateex"></a><p class="title"><b>Example 10.4. |
| Update by Query |
| </b></p><div class="example-contents"><pre class="programlisting"> |
| Query q = em.createQuery("UPDATE Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today"); |
| q.setParameter("today", new Date()); |
| q.setParameter("paid", true); |
| int updated = q.executeUpdate(); |
| </pre></div></div><br class="example-break"></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_trans_local.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="jpa_overview.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="jpa_langref.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">2. |
| The EntityTransaction Interface |
| </td><td width="20%" align="center"><a accesskey="h" href="manual.html">Home</a></td><td width="40%" align="right" valign="top"> 2. |
| JPQL Language Reference |
| </td></tr></table></div></body></html> |
