| <html><head> |
| <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> |
| <title>Chapter 10. JPA Query</title><base href="display"><link rel="stylesheet" type="text/css" href="css/docbook.css"><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"><link rel="home" href="manual.html" title="Apache OpenJPA 3.0 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" id="jpa_overview_query"><div class="titlepage"><div><div><h2 class="title">Chapter 10. |
| JPA Query |
| </h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl class="toc"><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_query_embeddables">1.3. |
| Embeddable Traversal |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_join_fetch">1.4. |
| Fetch Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_functions">1.5. |
| JPQL Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_inheritance">1.6. |
| Polymorphic Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_params">1.7. |
| Query Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_hints">1.8. |
| Query Hints |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.8.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locktimeout">1.8.2. |
| Lock Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_querytimeout">1.8.3. |
| Query Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.8.4. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.8.5. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.8.6. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d5e3356">1.8.7. |
| Database-Specific Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.8.8. |
| Named Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#multi-hints-handling">1.8.9. |
| Handling of Multiple Similar Query Hints |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_ordering">1.9. |
| Ordering |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_aggregates">1.10. |
| Aggregates |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_named">1.11. |
| Named Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_delete">1.12. |
| Delete By Query |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_update">1.13. |
| 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_from_clause_and_sql">2.3.7. |
| JPQL FROM Clause and SQL |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_polymorph">2.3.8. |
| 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_comparison_expressions">2.5.7. |
| JPQL Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_between">2.5.8. |
| JPQL Between Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_in_expressions">2.5.9. |
| JPQL In Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_like">2.5.10. |
| JPQL Like Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null">2.5.11. |
| JPQL Null Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_empty_comp">2.5.12. |
| JPQL Empty Collection Comparison Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_collection_member">2.5.13. |
| JPQL Collection Member Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_exists">2.5.14. |
| JPQL Exists Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_all_any">2.5.15. |
| JPQL All or Any Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_subqueries">2.5.16. |
| JPQL Subqueries |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_scalar_expressions">2.6. |
| JPQL Scalar Expressions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_math_expressions">2.6.1. |
| Arithmetic Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_functional_expressions">2.6.2. |
| String, Arithmetic, and Datetime Functional Expressions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_string_fun">2.6.2.1. |
| JPQL String Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_arithmetic">2.6.2.2. |
| JPQL Arithmetic Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_datetime">2.6.2.3. |
| JPQL Datetime Functions |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_case_expressions">2.6.3. |
| Case Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_entity_type_expressions">2.6.4. |
| Entity Type Expressions |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_group">2.7. |
| JPQL GROUP BY, HAVING |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_select_clause">2.8. |
| JPQL SELECT Clause |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_resulttype">2.8.1. |
| JPQL Result Type of the SELECT Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_constructor">2.8.2. |
| JPQL Constructor Expressions |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_select">2.8.3. |
| JPQL Null Values in the Query Result |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_embeddables">2.8.4. |
| JPQL Embeddables in the Query Result |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_aggregates">2.8.5. |
| JPQL Aggregate Functions |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_langref.html#jpa_langref_agg_examples">2.8.5.1. |
| JPQL Aggregate Examples |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_numeric_expressions_in_select">2.8.5.2. |
| JPQL Numeric Expressions in the SELECT Clause |
| </a></span></dt></dl></dd></dl></dd><dt><span class="section"><a href="jpa_langref.html#jpa_langref_orderby">2.9. |
| JPQL ORDER BY Clause |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bulk_ops">2.10. |
| JPQL Bulk Update and Delete |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_null_values">2.11. |
| JPQL Null Values |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_equality">2.12. |
| JPQL Equality and Comparison Semantics |
| </a></span></dt><dt><span class="section"><a href="jpa_langref.html#jpa_langref_bnf">2.13. |
| JPQL BNF |
| </a></span></dt></dl></dd></dl></div> |
| |
| <a class="indexterm" name="d5e2943"></a> |
| <a class="indexterm" name="d5e2946"></a> |
| <div class="mediaobject"><table border="0" summary="manufactured viewport for HTML img" style="cellpadding: 0; cellspacing: 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 class="xref" 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 class="xref" href="jpa_langref.html" title="2. JPQL Language Reference">Section 2, “ |
| JPQL Language Reference |
| ”</a>. |
| </p> |
| <div class="section" id="jpa_query_api"><div class="titlepage"><div><div><h2 class="title" style="clear: both">1. |
| JPQL API |
| </h2></div></div></div><div class="toc"><dl class="toc"><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_query_embeddables">1.3. |
| Embeddable Traversal |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_join_fetch">1.4. |
| Fetch Joins |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_functions">1.5. |
| JPQL Functions |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_inheritance">1.6. |
| Polymorphic Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_params">1.7. |
| Query Parameters |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_hints">1.8. |
| Query Hints |
| </a></span></dt><dd><dl><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.8.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locktimeout">1.8.2. |
| Lock Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_querytimeout">1.8.3. |
| Query Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.8.4. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.8.5. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.8.6. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d5e3356">1.8.7. |
| Database-Specific Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.8.8. |
| Named Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#multi-hints-handling">1.8.9. |
| Handling of Multiple Similar Query Hints |
| </a></span></dt></dl></dd><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_ordering">1.9. |
| Ordering |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_aggregates">1.10. |
| Aggregates |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_named">1.11. |
| Named Queries |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_delete">1.12. |
| Delete By Query |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_overview_query_update">1.13. |
| Update By Query |
| </a></span></dt></dl></div> |
| |
| <div class="section" id="jpa_overview_query_basic"><div class="titlepage"><div><div><h3 class="title">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 class="ulink" href="http://download.oracle.com/javaee/6/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 class="ulink" href="http://download.oracle.com/javaee/6/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 optionally 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 class="itemizedlist" style="list-style-type: disc; "><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3032"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3039"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3047"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3054"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3062"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3070"></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" id="jpa_overview_query_relations"><div class="titlepage"><div><div><h3 class="title">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" id="jpa_overview_query_embeddables"><div class="titlepage"><div><div><h3 class="title">1.3. |
| Embeddable Traversal |
| </h3></div></div></div> |
| |
| <p> |
| Similar to relation traversal, nested embeddable objects can be traversed using Java-like syntax. |
| For example, if the <code class="classname">Company</code> class has a field named "address" of |
| an embeddable type <code class="classname">Address</code>, |
| and the <code class="classname">Address</code> has a field named "geocode" of |
| an embeddable type <code class="classname">Geocode</code>, |
| the <code class="literal">geocode</code> of a company's address can be queried as follows: |
| </p> |
| <pre class="programlisting"> |
| SELECT c.address.geocode FROM Company c WHERE c.name = 'Random House' |
| </pre> |
| <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3> |
| <p> |
| The <code class="literal">geocode</code> returned by the above query will not be part of the state of any managed |
| entity. Modifications to these embeddable instances are not allowed. |
| </p> |
| </div> |
| <p> |
| Traversal into embeddable's state field is also allowed as shown in the following query: |
| </p> |
| <pre class="programlisting"> |
| SELECT c.address.geocode.latitude FROM Company c WHERE c.name = 'Random House' |
| </pre> |
| <p> |
| Embeddable objects may contain single-valued or collection-valued relations. |
| These relations can also be traversed using Java-like syntax. |
| For example, if the Address has a relation field named "phoneLists" of |
| an entity type <code class="classname">PhoneNumber</code>, |
| the following query returns the <code class="classname">PhoneNumber</code> entities of the <code class="classname">Company</code> |
| named 'Random House': |
| </p> |
| <pre class="programlisting"> |
| SELECT p FROM Company c, IN(c.address.phoneLists) p WHERE c.name = 'Random House' |
| </pre> |
| </div> |
| <div class="section" id="jpa_overview_join_fetch"><div class="titlepage"><div><div><h3 class="title">1.4. |
| 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> |
| Notice that in the above query, both <code class="literal">articles</code> and <code class="literal">authors</code> |
| are relation property in <code class="classname">Magazine</code>. |
| JPQL syntax does not allow range variable declared for paths on the right-hand side of |
| <code class="literal">join fetch</code>. |
| Therefore, if <code class="classname">Article</code> entity has a relation property of |
| <code class="literal">publishers</code>, |
| it is not possible to specify a query |
| that returns <code class="classname">Magazine</code> instances and pre-fetch |
| the <code class="literal">articles</code> and the <code class="literal">publishers</code>. |
| The following query will result in syntax error: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine x join fetch x.articles a join fetch a.publishers p 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 class="xref" href="ref_guide_fetch.html" title="7. Fetch Groups">Section 7, “ |
| Fetch Groups |
| ”</a>. |
| </p> |
| </div><p> |
| </p> |
| </div> |
| <div class="section" id="jpa_overview_query_functions"><div class="titlepage"><div><div><h3 class="title">1.5. |
| 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 class="itemizedlist" style="list-style-type: disc; "><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3156"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3162"></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 optionally ending at <code class="literal">length</code> characters past <code class="literal"> |
| startIndex</code>. If the <code class="literal">length</code> argument is not specified, |
| the substring from the <code class="literal">startIndex</code> to the end of the <code class="literal">string</code> |
| is returned. |
| </p> |
| <pre class="programlisting"> |
| SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J' |
| </pre> |
| </li><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3175"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3184"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3190"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3196"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3202"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3210"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3216"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3222"></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 class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3230"></a> |
| <code class="literal">INDEX(identification_variable)</code>: Returns an integer value corresponding |
| to the position of its argument in an ordered list. |
| The INDEX function can only be applied to identification variables denoting types for |
| which an order column has been specified. |
| </p> |
| <p> |
| In the following example, <code class="literal">studentWaitlist</code> is a list of |
| students for which an order column has |
| been specified, the query returns the name of the first student on the waiting list of |
| the course named 'Calculus': |
| </p> |
| <pre class="programlisting"> |
| SELECT w.name FROM Course c JOIN c.studentWaitlist w WHERE c.name = ‘Calculus’ AND INDEX(w) = 0 |
| </pre> |
| </li><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3238"></a> |
| <code class="literal">CURRENT_DATE</code>: Returns the current date. |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3243"></a> |
| <code class="literal">CURRENT_TIME</code>: Returns the current time. |
| </p> |
| </li><li class="listitem"> |
| <p> |
| <a class="indexterm" name="d5e3248"></a> |
| <code class="literal">CURRENT_TIMESTAMP</code>: Returns the current timestamp. |
| </p> |
| </li></ul></div> |
| </div> |
| <div class="section" id="jpa_overview_query_inheritance"><div class="titlepage"><div><div><h3 class="title">1.6. |
| 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> |
| <p> |
| Non-polymorphic queries or queries whose polymorphism is restricted can be specified using entity |
| type expressions (see <a class="xref" href="jpa_langref.html#jpa_langref_entity_type_expressions" title="2.6.4. Entity Type Expressions">Section 2.6.4, “ |
| Entity Type Expressions |
| ”</a> ) |
| in the <code class="literal">WHERE</code> clause to restrict the domain of the query. |
| For example, the following query returns instances of <code class="classname">Digest</code>: |
| </p><pre class="programlisting"> |
| SELECT x FROM Magazine WHERE TYPE(x) = Digest |
| </pre><p> |
| </p> |
| </div> |
| <div class="section" id="jpa_overview_query_params"><div class="titlepage"><div><div><h3 class="title">1.7. |
| 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 parameters 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> |
| <p> |
| All input parameters must be single-valued, except in IN expressions |
| (see <a class="xref" href="jpa_langref.html#jpa_langref_in_expressions" title="2.5.9. JPQL In Expressions">Section 2.5.9, “ |
| JPQL In Expressions |
| ”</a>), which support the use of collection-valued |
| input parameters. |
| </p> |
| </div> |
| <div class="section" id="jpa_overview_query_hints"><div class="titlepage"><div><div><h3 class="title">1.8. |
| Query Hints |
| </h3></div></div></div><div class="toc"><dl class="toc"><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locking">1.8.1. |
| Locking Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_locktimeout">1.8.2. |
| Lock Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_querytimeout">1.8.3. |
| Query Timeout Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_resultset">1.8.4. |
| Result Set Size Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_isolation">1.8.5. |
| Isolation Level Hint |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_fetchplan">1.8.6. |
| Other Fetchplan Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#d5e3356">1.8.7. |
| Database-Specific Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#jpa_hints_named">1.8.8. |
| Named Query Hints |
| </a></span></dt><dt><span class="section"><a href="jpa_overview_query.html#multi-hints-handling">1.8.9. |
| Handling of Multiple Similar 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 |
| a database-specific SQL keyword (usually FOR UPDATE) to be emitted into the SQL provided that a |
| pessimistic LockManager is being used. Additionally, if a DB2 database is being used, |
| the OPTIMIZE FOR 2 ROWS clause will also be emitted. |
| </p> |
| <div class="example" id="jpa_query_hint1"><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> |
| Hints which can not be processed by a particular database or are unknown to OpenJPA are ignored. |
| Hints known to OpenJPA but supplied with an incompatible value will result in an |
| <code class="classname">IllegalArgumentException</code> being thrown. |
| </p> |
| <div class="section" id="jpa_hints_locking"><div class="titlepage"><div><div><h4 class="title">1.8.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 a database-specific locking keyword (usually FOR UPDATE) to be emitted into the 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" id="d5e3309"><p class="title"><b>Table 10.1. |
| Interaction of ReadLockMode hint and LockManager |
| </b></p><div class="table-contents"> |
| |
| <table class="table" summary="
 Interaction of ReadLockMode hint and LockManager
 " border="1"><colgroup><col align="left" class="interaction"><col align="left" class="pessimistic"><col align="left" class="version"></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 FOR UPDATE |
| </td><td align="left">SQL without FOR 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 FOR UPDATE |
| </td><td align="left"> |
| SQL without FOR 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 FOR UPDATE |
| </td><td align="left"> |
| SQL without FOR UPDATE |
| </td></tr></tbody></table> |
| </div></div><br class="table-break"> |
| </div> |
| <div class="section" id="jpa_hints_locktimeout"><div class="titlepage"><div><div><h4 class="title">1.8.2. |
| Lock Timeout Hint |
| </h4></div></div></div> |
| |
| <p> |
| To specify a lock timeout hint in milliseconds to those databases that support |
| it, specify a hint name of "openjpa.LockTimeout" or |
| "javax.persistence.lock.timeout" with an integer value greater than |
| zero, or zero for no timeout which is the default behavior. |
| </p> |
| </div> |
| <div class="section" id="jpa_hints_querytimeout"><div class="titlepage"><div><div><h4 class="title">1.8.3. |
| Query Timeout Hint |
| </h4></div></div></div> |
| |
| <p> |
| To specify a query timeout hint in milliseconds to those database drivers that |
| support it, specify a hint name of "javax.persistence.query.timeout" |
| with an integer value greater than zero, or zero for no timeout which is the |
| default behavior. |
| </p> |
| </div> |
| <div class="section" id="jpa_hints_resultset"><div class="titlepage"><div><div><h4 class="title">1.8.4. |
| 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" id="jpa_hints_isolation"><div class="titlepage"><div><div><h4 class="title">1.8.5. |
| 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" id="jpa_hints_fetchplan"><div class="titlepage"><div><div><h4 class="title">1.8.6. |
| 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" id="d5e3356"><div class="titlepage"><div><div><h4 class="title">1.8.7. |
| Database-Specific Hints |
| </h4></div></div></div> |
| |
| <p> |
| The hint names "openjpa.hint.MySQLSelectHint" and |
| "openjpa.hint.OracleSelectHint" can be used to specify a string value |
| of a query hint that will be inserted into SQL for MySQL and Oracle databases. |
| See <a class="xref" href="dbsupport_mysql.html#dbsupport_mysql_query_hints" title="19.1. Using Query Hints with MySQL">Section 19.1, “ |
| Using Query Hints with MySQL |
| ”</a> and |
| <a class="xref" href="dbsupport_oracle.html#dbsupport_oracle_query_hints" title="21.1. Using Query Hints with Oracle">Section 21.1, “ |
| Using Query Hints with Oracle |
| ”</a> for examples. |
| </p> |
| </div> |
| <div class="section" id="jpa_hints_named"><div class="titlepage"><div><div><h4 class="title">1.8.8. |
| Named Query Hints |
| </h4></div></div></div> |
| |
| <p> |
| Hints can also be included as part of a NamedQuery definition. |
| </p> |
| <div class="example" id="jpa_query_hint2"><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 class="section" id="multi-hints-handling"><div class="titlepage"><div><div><h4 class="title">1.8.9. |
| Handling of Multiple Similar Query Hints |
| </h4></div></div></div> |
| |
| <p> |
| When similar hints in different prefix scopes are specified in a query, |
| the following prefix precedence order is used to select the effective hint: |
| </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"> |
| javax.persistence.* |
| </li><li class="listitem"> |
| openjpa.FetchPlan.* |
| </li><li class="listitem"> |
| openjpa.jdbc.* |
| </li><li class="listitem"> |
| openjpa.* |
| </li></ul></div><p> |
| </p><div class="example" id="multi-hints-example"><p class="title"><b>Example 10.3. |
| Setting Multiple Similar Query Hints |
| </b></p><div class="example-contents"> |
| |
| <pre class="programlisting"> |
| ... |
| Query q = em.createQuery(.....); |
| q.setHint("openjpa.FetchPlan.LockTimeout", 1000); |
| q.setHint("javax.persistence.lock.timeout", 2000); |
| q.setHint("openjpa.LockTimeout", 3000); |
| // Lock time out of 2000 ms is in effect for query q |
| ... |
| </pre> |
| </div></div><p><br class="example-break"> |
| </p> |
| </div> |
| </div> |
| <div class="section" id="jpa_overview_query_ordering"><div class="titlepage"><div><div><h3 class="title">1.9. |
| 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" id="jpa_overview_query_aggregates"><div class="titlepage"><div><div><h3 class="title">1.10. |
| 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" id="jpa_overview_query_named"><div class="titlepage"><div><div><h3 class="title">1.11. |
| 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" id="jpa_overview_query_delete"><div class="titlepage"><div><div><h3 class="title">1.12. |
| 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" id="jpa_overview_query_deleteex"><p class="title"><b>Example 10.4. |
| 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" id="jpa_overview_query_update"><div class="titlepage"><div><div><h3 class="title">1.13. |
| 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" id="jpa_overview_query_updateex"><p class="title"><b>Example 10.5. |
| 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> |
