Google Git
Sign in
eclipse / www.eclipse.org / eclipselink / 60356dbcf0388ffefc51c777ff1abfe335174a3a / . / documentation / 2.7 / jpa / extensions / jpql.htm
blob: 85daec9db29c9529fbfa4ead22bf0d06149b0306 [file] [log] [blame]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<title>Java Persistence Query&nbsp;Language&nbsp;Extensions | EclipseLink 2.7.x Java Persistence API (JPA) Extensions Reference</title>
<meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 1.0.22 Build 1" />
<meta name="date" content="2014-06-10T10:29:49Z" />
<meta name="description" content="This chapter describes the extensions EclipseLink providesto the standard JPA Java Persistence Query Language (JPQL). These extensions, referred to as the EclipseLink Query Language (EQL), provide access to additional database features many of which are part of standard SQL, provide access to native database features and functions, and provide access to EclipseLink specific features." />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Java Persistence Query&nbsp;Language&nbsp;Extensions" />
<meta name="relnum" content="Release 2.7" />
<link rel="stylesheet" type="text/css" href="../../../dcommon/style.css" media="screen" />
<link rel="copyright" href="http://www.eclipse.org/org/documents/epl-v10.php" title="Copyright" type="text/html" />
<link rel="start" href="../../index.htm" title="Home" type="text/html" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="annotations_ref.htm" title="Previous" type="text/html" />
<link rel="next" href="queryhints.htm" title="Next" type="text/html" />
<!-- START: Disqus --><script type="text/javascript"> var disqus_developer = 0; </script><!-- END: Disqus --><!-- START: Sharethis --><script type="text/javascript">var switchTo5x=true;</script><script type="text/javascript" src="http://w.sharethis.com/button/buttons.js"></script><script type="text/javascript" src="http://s.sharethis.com/loader.js"></script> <!-- END: Sharethis --></head>
<body bgcolor="#FFFFFF"><iframe id="docheader" frameborder="0" framemargin="0" scrolling="no" src="../../../dcommon/header.html"></iframe><script src="http://www.google.com/jsapi" type="text/javascript"></script><script type="text/javascript"> google.load('search', '1', {language : 'en'}); google.setOnLoadCallback(function() { var customSearchOptions = {}; var googleAnalyticsOptions = {}; googleAnalyticsOptions['queryParameter'] = 'q'; googleAnalyticsOptions['categoryParameter'] = ''; customSearchOptions['googleAnalyticsOptions'] = googleAnalyticsOptions; var customSearchControl = new google.search.CustomSearchControl( '016171230611334810008:4sqnhke2any', customSearchOptions); customSearchControl.setResultSetSize(google.search.Search.FILTERED_CSE_RESULTSET); var options = new google.search.DrawOptions(); options.setSearchFormRoot('cse-search-form'); customSearchControl.draw('cse', options); }, true);</script><link rel="stylesheet" href="http://www.google.com/cse/style/look/default.css" type="text/css" /><div id="cse" style="width:100%;"></div>
<div class="header"><a id="top" name="top"></a>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td align="left" valign="top"><div class="booktitle">Java Persistence API (JPA) Extensions Reference for EclipseLink,
Release 2.7</div></td>
<td valign="bottom" align="right" width="144">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td>&nbsp;</td>
<td align="center" valign="top"><a href="toc.htm"><img src="../../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br />
</td><td>&nbsp;</td><td align="center"><a href="../../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td>&nbsp;</td><td align="center"><a href="../../eclipselink_jpa_extensions.pdf" title="PDF" target="_blank"><img src="../../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td><td align="center"><a href="#disqus_thread" title="Comments""><img src="../../../dcommon/images/comments.png" style="padding-right:5px;border:0" alt="Comments"><br /><span class="mini">Comments</span></a>
</tr>
</table>
</td>
</tr>
</table>
<hr />
<table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center">
<tr>
<td align="center"><a href="annotations_ref.htm"><img src="../../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="queryhints.htm"><img src="../../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td>
<td>&nbsp;</td>
</tr>
</table>
</div>
<!-- class="header" -->
<div class="ind"><!-- End Header --><a id="BABCIECD" name="BABCIECD"></a><a id="TLJPA603" name="TLJPA603"></a>
<h1 class="chapter"><font face="arial, helvetica, sans-serif" color="#330099"><span class="secnum">3</span> Java Persistence Query&nbsp;Language&nbsp;Extensions</font></h1>
<a id="TLJPA54065" name="TLJPA54065"></a>
<p>This chapter describes the extensions EclipseLink providesto the standard JPA Java Persistence Query Language (JPQL). These extensions, referred to as the EclipseLink Query Language (EQL), provide access to additional database features many of which are part of standard SQL, provide access to native database features and functions, and provide access to EclipseLink specific features.</p>
<p>This chapter includes the following sections:</p>
<ul>
<li>
<p><a href="#BABEJJHG">Special Operators</a></p>
</li>
<li>
<p><a href="#BABEAIIA">EclipseLink Query Language</a></p>
</li>
</ul>
<p>For more information on JQPL, see:</p>
<ul>
<li>
<p>"Query Language" in the JPA Specification (<code><a href="http://jcp.org/en/jsr/detail?id=317">http://jcp.org/en/jsr/detail?id=317</a></code>)</p>
</li>
<li>
<p>"The Java Persistence Query Language" in <em>The Java&nbsp;EE 6 Tutorial</em> (<code><a href="http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html">http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html</a></code>)</p>
</li>
<li>
<p>"EclipseLink User Guide"</p>
<p>(<code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code>)</p>
</li>
</ul>
<a id="BABEJJHG" name="BABEJJHG"></a><a id="TLJPA604" name="TLJPA604"></a>
<div class="sect1">
<h2 class="sect1"><font face="arial, helvetica, sans-serif" color="#330099">Special Operators</font></h2>
<p>EclipseLink defines the following operators to perform database operations that would not be possible in standard JPQL:</p>
<ul>
<li>
<p><a href="#column">COLUMN</a></p>
</li>
<li>
<p><a href="#func">FUNCTION</a></p>
</li>
<li>
<p><a href="#operator">OPERATOR</a></p>
</li>
<li>
<p><a href="#sql">SQL</a></p>
</li>
</ul>
</div>
<!-- class="sect1" -->
<a id="BABEAIIA" name="BABEAIIA"></a><a id="TLJPA605" name="TLJPA605"></a>
<div class="sect1">
<h2 class="sect1"><font face="arial, helvetica, sans-serif" color="#330099">EclipseLink Query Language</font></h2>
<ul>
<li>
<p><a href="#cast">CAST</a></p>
</li>
<li>
<p><a href="#except">EXCEPT</a></p>
</li>
<li>
<p><a href="#extract">EXTRACT</a></p>
</li>
<li>
<p><a href="#intersect">INTERSECT</a></p>
</li>
<li>
<p><a href="#on">ON</a></p>
</li>
<li>
<p><a href="#CIHGBAEC">REGEXP</a></p>
</li>
<li>
<p><a href="#table">TABLE</a></p>
</li>
<li>
<p><a href="#treat">TREAT</a></p>
</li>
<li>
<p><a href="#union">UNION</a></p>
</li>
</ul>
</div>
<!-- class="sect1" -->
<a id="cast" name="cast"></a><a id="TLJPA606" name="TLJPA606"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">CAST</font></h2>
<p>Use <code>CAST</code> to convert a value to a specific database type.</p>
<a id="sthref499" name="sthref499"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>The <code>CAST</code> function is database independent, but requires database support.</p>
<a id="sthref500" name="sthref500"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CIHGHBIC">Example 3-1</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="CIHGHBIC" name="CIHGHBIC"></a><a id="TLJPA607" name="TLJPA607"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-1 Using CAST EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
CAST(e.salary NUMERIC(10,2))
</pre></div>
<!-- class="example" --></div>
<!-- class="refsect1" -->
<a id="column" name="column"></a><a id="TLJPA608" name="TLJPA608"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">COLUMN</font></h2>
<p>Use <code>COLUMN</code> to access to unmapped columns in an object's table.</p>
<a id="sthref501" name="sthref501"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>You can use <code>COLUMN</code> to access foreign key columns, inheritance discriminators, or primitive columns (such as <code>ROWID</code>). You can also use <code>COLUMN</code> in JPQL fragments inside the <code><a href="annotations_ref.htm#additionalcriteria">@AdditionalCriteria</a></code> annotation.</p>
<a id="sthref502" name="sthref502"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CDCCFCBE">Example 3-2</a> shows how to use the <code>COLUMN</code> EQL.</p>
<div class="example"><a id="CDCCFCBE" name="CDCCFCBE"></a><a id="TLJPA609" name="TLJPA609"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-2 Using COLUMN EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e WHERE COLUMN('MANAGER_ID', e) = :id
</pre></div>
<!-- class="example" -->
<p>In <a href="#CDCFDDGF">Example 3-3</a>, uses <code>COLUMN</code> EQL access a primitive column (<code>ROWID</code>).</p>
<div class="example"><a id="CDCFDDGF" name="CDCFDDGF"></a><a id="TLJPA610" name="TLJPA610"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-3 Using COLUMN with a Primitive Column</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e WHERE COLUMN('ROWID', e) = :id
</pre></div>
<!-- class="example" -->
<a id="sthref503" name="sthref503"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="annotations_ref.htm#additionalcriteria">"@AdditionalCriteria"</a></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="except" name="except"></a><a id="TLJPA611" name="TLJPA611"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">EXCEPT</font></h2>
<p>When performing multiple queries, use <code>EXCEPT</code> to remove the results of a second query from the results of a first query.</p>
<a id="sthref504" name="sthref504"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>The <code>EXCEPT</code> function is database independent, but requires database support.</p>
<a id="sthref505" name="sthref505"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BABJIEDC">Example 3-4</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BABJIEDC" name="BABJIEDC"></a><a id="TLJPA612" name="TLJPA612"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-4 Using EXCEPT EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e
EXCEPT SELECT e FROM Employee e WHERE e.salary &gt; e.manager.salary
</pre></div>
<!-- class="example" -->
<a id="sthref506" name="sthref506"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="#union">"UNION"</a></p>
</li>
<li>
<p><a href="#intersect">"INTERSECT"</a></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="extract" name="extract"></a><a id="TLJPA613" name="TLJPA613"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">EXTRACT</font></h2>
<p>Use <code>EXTRACT</code> to retrieve the date portion of a date/time value.</p>
<a id="sthref507" name="sthref507"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>The <code>EXTRACT</code> function is database independent, but requires database support</p>
<a id="sthref508" name="sthref508"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CHDJGBFJ">Example 3-5</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="CHDJGBFJ" name="CHDJGBFJ"></a><a id="TLJPA614" name="TLJPA614"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-5 Using EXTRACT EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
EXTRACT(YEAR, e.startDate)
</pre></div>
<!-- class="example" --></div>
<!-- class="refsect1" -->
<a id="func" name="func"></a><a id="TLJPA615" name="TLJPA615"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">FUNCTION</font></h2>
<p>Use <code>FUNCTION</code> (formerly <code>FUNC</code>) to call database specific functions from JPQL</p>
<a id="sthref509" name="sthref509"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>You can use <code>FUNCTION</code> to call database functions that are not supported directly in JPQL and to call user or library specific functions.</p>
<div align="center">
<div class="inftblnote"><br />
<table class="Note oac_no_warn" summary="" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">
<tbody>
<tr>
<td align="left">
<p class="note"><img src="../../../dcommon/images/note_icon.png" width="16" height="16" alt="Note" style="vertical-align:middle;padding-right:5px;" />Note:</p>
<p><code>FUNCTION</code> is database specific &ndash; it does not translate the function call in any way to support different databases as other JPQL functions do.</p>
</td>
</tr>
</tbody>
</table>
<br /></div>
<!-- class="inftblnote" --></div>
<p>Use <code>FUNCTION</code> to call functions with normal syntax. Functions that require special syntax cannot be called with <code>FUNCTION</code>. Instead, use <code><a href="#operator">OPERATOR</a></code></p>
<a id="sthref510" name="sthref510"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CIHCCHIC">Example 3-6</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="CIHCCHIC" name="CIHCCHIC"></a><a id="TLJPA616" name="TLJPA616"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-6 Using FUNCTION EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) &gt; 613
SELECT FUNCTION('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year
</pre></div>
<!-- class="example" -->
<p><a href="#CIHFDEIJ">Example 3-7</a> shows how to use <code>FUNCTION</code> with Oracle Spatial queries</p>
<div class="example"><a id="CIHFDEIJ" name="CIHFDEIJ"></a><a id="TLJPA617" name="TLJPA617"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-7 Using FUNCTION EQL Oracle Spatial examples</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'
</pre>
<pre xml:space="preserve" class="oac_no_warn">
SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC
</pre></div>
<!-- class="example" -->
<a id="sthref511" name="sthref511"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="#operator">"OPERATOR"</a></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="intersect" name="intersect"></a><a id="TLJPA618" name="TLJPA618"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">INTERSECT</font></h2>
<p>When performing multiple queries, use <code>INTERSECT</code> to return only results that are found in both queries.</p>
<a id="sthref512" name="sthref512"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BABGGIFA">Example 3-8</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BABGGIFA" name="BABGGIFA"></a><a id="TLJPA54133" name="TLJPA54133"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-8 Using INTERSECT EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2
SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode1
INTERSECT SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode2
SELECT e FROM Employee e
EXCEPT SELECT e FROM Employee e WHERE e.salary &gt; e.manager.salary
</pre></div>
<!-- class="example" -->
<a id="sthref513" name="sthref513"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="#union">"UNION"</a></p>
</li>
<li>
<p><a href="#except">"EXCEPT"</a></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="on" name="on"></a><a id="TLJPA620" name="TLJPA620"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">ON</font></h2>
<p>Use the <code>ON</code> clause to append additional conditions to a <code>JOIN</code> condition, such as for outer joins.</p>
<a id="sthref514" name="sthref514"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>EclipseLink supports using the <code>ON</code> clause between two root level objects.</p>
<a id="sthref515" name="sthref515"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BABFGBAD">Example 3-9</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BABFGBAD" name="BABFGBAD"></a><a id="TLJPA621" name="TLJPA621"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-9 Using ON Clause EQ</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e LEFT JOIN e.address ON a.city = :city
</pre>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address
</pre></div>
<!-- class="example" -->
<a id="sthref516" name="sthref516"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="operator" name="operator"></a><a id="TLJPA622" name="TLJPA622"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">OPERATOR</font></h2>
<p>Use <code>OPERATION</code> to call any EclipseLink operator.</p>
<a id="sthref517" name="sthref517"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>EclipseLink supports many database functions using standard operator names that are translated to different databases. EclipseLink operators are supported on any database that has an equivalent function (or set of functions). Use the EclipseLink <code>ExpressionOperator</code> class to define a custom operator or allow <code>DatabasePlatform</code> to override an operator..</p>
<p><code>OPERATOR</code> is similar to <code><a href="#func">FUNCTION</a></code>, but allows the function to be database independent, and you can call functions that require special syntax.</p>
<p>The supported EclipseLink operators include:</p>
<ul>
<li>
<p>Abs</p>
</li>
<li>
<p>ToUpperCase</p>
</li>
<li>
<p>ToLowerCase</p>
</li>
<li>
<p>Chr</p>
</li>
<li>
<p>Concat</p>
</li>
<li>
<p>Coalesce</p>
</li>
<li>
<p>Case</p>
</li>
<li>
<p>HexToRaw</p>
</li>
<li>
<p>Initcap</p>
</li>
<li>
<p>Instring</p>
</li>
<li>
<p>Soundex</p>
</li>
<li>
<p>LeftPad</p>
</li>
<li>
<p>LeftTrim</p>
</li>
<li>
<p>RightPad</p>
</li>
<li>
<p>RightTrim</p>
</li>
<li>
<p>Substring</p>
</li>
<li>
<p>Translate</p>
</li>
<li>
<p>Ascii</p>
</li>
<li>
<p>Length</p>
</li>
<li>
<p>CharIndex</p>
</li>
<li>
<p>Cast</p>
</li>
<li>
<p>Extract</p>
</li>
<li>
<p>CharLength</p>
</li>
<li>
<p>Difference</p>
</li>
<li>
<p>Reverse</p>
</li>
<li>
<p>Replicate</p>
</li>
<li>
<p>Right</p>
</li>
<li>
<p>Locate</p>
</li>
<li>
<p>ToNumber</p>
</li>
<li>
<p>ToChar</p>
</li>
<li>
<p>AddMonths</p>
</li>
<li>
<p>DateToString</p>
</li>
<li>
<p>MonthsBetween</p>
</li>
<li>
<p>NextDay</p>
</li>
<li>
<p>RoundDate</p>
</li>
<li>
<p>AddDate</p>
</li>
<li>
<p>DateName</p>
</li>
<li>
<p>DatePart</p>
</li>
<li>
<p>DateDifference</p>
</li>
<li>
<p>TruncateDate</p>
</li>
<li>
<p>NewTime</p>
</li>
<li>
<p>Nvl</p>
</li>
<li>
<p>NewTime</p>
</li>
<li>
<p>Ceil</p>
</li>
<li>
<p>Cos</p>
</li>
<li>
<p>Cosh</p>
</li>
<li>
<p>Acos</p>
</li>
<li>
<p>Asin</p>
</li>
<li>
<p>Atan</p>
</li>
<li>
<p>Exp</p>
</li>
<li>
<p>Sqrt</p>
</li>
<li>
<p>Floor</p>
</li>
<li>
<p>Ln</p>
</li>
<li>
<p>Log</p>
</li>
<li>
<p>Mod</p>
</li>
<li>
<p>Power</p>
</li>
<li>
<p>Round</p>
</li>
<li>
<p>Sign</p>
</li>
<li>
<p>Sin</p>
</li>
<li>
<p>Sinh</p>
</li>
<li>
<p>Tan</p>
</li>
<li>
<p>Tanh</p>
</li>
<li>
<p>Trunc</p>
</li>
<li>
<p>Greatest</p>
</li>
<li>
<p>Least</p>
</li>
<li>
<p>Add</p>
</li>
<li>
<p>Subtract</p>
</li>
<li>
<p>Divide</p>
</li>
<li>
<p>Multiply</p>
</li>
<li>
<p>Atan2</p>
</li>
<li>
<p>Cot</p>
</li>
<li>
<p>Deref</p>
</li>
<li>
<p>Ref</p>
</li>
<li>
<p>RefToHex</p>
</li>
<li>
<p>Value</p>
</li>
<li>
<p>ExtractXml</p>
</li>
<li>
<p>ExtractValue</p>
</li>
<li>
<p>ExistsNode</p>
</li>
<li>
<p>GetStringVal</p>
</li>
<li>
<p>GetNumberVal</p>
</li>
<li>
<p>IsFragment</p>
</li>
<li>
<p>SDO_WITHIN_DISTANCE</p>
</li>
<li>
<p>SDO_RELATE</p>
</li>
<li>
<p>SDO_FILTER</p>
</li>
<li>
<p>SDO_NN</p>
</li>
<li>
<p>NullIf</p>
</li>
</ul>
<a id="sthref518" name="sthref518"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BGBGIAEE">Example 3-10</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BGBGIAEE" name="BGBGIAEE"></a><a id="TLJPA623" name="TLJPA623"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-10 Using OPERATOR EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') &gt; 10
</pre></div>
<!-- class="example" -->
<a id="sthref519" name="sthref519"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="#func">"FUNCTION"</a></p>
</li>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="CIHGBAEC" name="CIHGBAEC"></a><a id="TLJPA54066" name="TLJPA54066"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">REGEXP</font></h2>
<p>Use <code>REGEXP</code> to determine if a string matches a regular expression.</p>
<a id="sthref520" name="sthref520"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>To use the <code>REGEXP</code> function, your database must support regular expressions.</p>
<a id="sthref521" name="sthref521"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BABHHDCH">Example 3-11</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BABHHDCH" name="BABHHDCH"></a><a id="TLJPA54067" name="TLJPA54067"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-11 Using REGEXP EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
e.lastName REGEXP '&icirc;Dr\.*'
</pre></div>
<!-- class="example" -->
<a id="sthref522" name="sthref522"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="sql" name="sql"></a><a id="TLJPA626" name="TLJPA626"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">SQL</font></h2>
<p>Use <code>SQL</code> to integrate SQL within a JPQL statement. This provides an alternative to using native SQL queries simply because the query may require a function not supported in JPQL.</p>
<a id="sthref523" name="sthref523"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>The <code>SQL</code> function includes both the SQL string (to inline into the JPQL statement) and the arguments to translate into the SQL string. Use a question mark character (&nbsp;<strong>?</strong>&nbsp;) to define parameters within the SQL that are translated from the SQL function arguments.</p>
<p>You can use <code>SQL</code> to call database functions with non standard syntax, embed SQL literals, and perform any other SQL operations within JPQL. With SQL, you can still use JPQL for the query.</p>
<a id="sthref524" name="sthref524"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CHDCEHAI">Example 3-12</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="CHDCEHAI" name="CHDCEHAI"></a><a id="TLJPA627" name="TLJPA627"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-12 Using SQL EQ</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT p FROM Phone p WHERE SQL('CAST(? AS CHAR(3))', e.areaCode) = '613'
</pre>
<pre xml:space="preserve" class="oac_no_warn">
SELECT SQL('EXTRACT(YEAR FROM ?)', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year
</pre>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e ORDER BY SQL('? NULLS FIRST', e.startDate)
</pre>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee e WHERE e.startDate = SQL('(SELECT SYSDATE FROM DUAL)')
</pre></div>
<!-- class="example" -->
<a id="sthref525" name="sthref525"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="table" name="table"></a><a id="TLJPA628" name="TLJPA628"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">TABLE</font></h2>
<p>Use <code>TABLE</code> to access unmapped tables.</p>
<a id="sthref526" name="sthref526"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>With the <code>TABLE</code> function, you use join, collection, history, auditing, or system tables in a JPQL query.</p>
<a id="sthref527" name="sthref527"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CIAFABDE">Example 3-13</a> shows how to use an <strong>audit</strong> table (unmapped) within a <code>SELECT</code> statement.</p>
<div class="example"><a id="CIAFABDE" name="CIAFABDE"></a><a id="TLJPA629" name="TLJPA629"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-13 Using TABLE EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)
</pre></div>
<!-- class="example" -->
<a id="sthref528" name="sthref528"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" -->
<a id="treat" name="treat"></a><a id="TLJPA630" name="TLJPA630"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">TREAT</font></h2>
<p>Use <code>TREAT</code> to cast an object as its subclass value (that is, downcast related entities with inheritance).</p>
<a id="sthref529" name="sthref529"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#BEHECHCD">Example 3-14</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="BEHECHCD" name="BEHECHCD"></a><a id="TLJPA631" name="TLJPA631"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-14 Using TREAT EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT e FROM Employee JOIN TREAT(e.projects AS LargeProject)
p WHERE p.budget &gt; 1000000
</pre></div>
<!-- class="example" --></div>
<!-- class="refsect1" -->
<a id="union" name="union"></a><a id="TLJPA632" name="TLJPA632"></a>
<hr />
<div class="refsect1">
<h2 class="refsect1"><font face="arial, helvetica, sans-serif" color="#330099">UNION</font></h2>
<p>Use <code>UNION</code> to combine the results of two queries into a single query.</p>
<a id="sthref530" name="sthref530"></a>
<p><strong><br /><span class="refsubsect">Usage</span></strong></p>
<p>With <code>UNION</code>, the unique results from both queries will be returned. If you include the <code>ALL</code> option, the results found in both queries will be duplicated.</p>
<a id="sthref531" name="sthref531"></a>
<p><strong><br /><span class="refsubsect">Examples</span></strong></p>
<p><a href="#CJHHJIDB">Example 3-15</a> shows how to use this JPQL extension.</p>
<div class="example"><a id="CJHHJIDB" name="CJHHJIDB"></a><a id="TLJPA633" name="TLJPA633"></a>
<p><strong><em><font face="arial, helvetica, sans-serif">Example 3-15 Using UNION EQL</font></em></strong></p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2
</pre></div>
<!-- class="example" -->
<a id="sthref532" name="sthref532"></a>
<p><strong><br /><span class="refsubsect">See Also</span></strong></p>
<p>For more information, see:</p>
<ul>
<li>
<p><a href="#except">"EXCEPT"</a></p>
</li>
<li>
<p><a href="#intersect">"INTERSECT"</a></p>
</li>
<li>
<p>"JPQL" <code><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a></code></p>
</li>
</ul>
</div>
<!-- class="refsect1" --></div>
<!-- class="ind" -->
<!-- Start Footer -->
<div class="footer">
<hr />
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="33%" />
<col width="*" />
<col width="33%" />
<tr>
<td valign="bottom">
<table class="navigation simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100" align="center">
<col width="*" />
<col width="48%" />
<col width="48%" />
<tr>
<td>&nbsp;</td>
<td align="center"><a href="annotations_ref.htm"><img src="../../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="queryhints.htm"><img src="../../../dcommon/images/rarrow.png" alt="Next" border="0" height="16" width="16" /></a></td>
</tr>
</table>
</td>
<td align="center" width="34%"><a href="http://www.eclipse.org/eclipselink/" title="EclipseLink home"><img src="../../../dcommon/images/ellogo.png" alt="EclipseLink" width="150" border="0" /></a><br />
</td>
<td valign="bottom" align="right">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="225">
<tr>
<td>&nbsp;</td>
<td align="center" valign="top"><a href="toc.htm"><img src="../../../dcommon/images/contents.png" alt="Go To Table Of Contents" border="0" height="16" width="16" /><br />
</td><td>&nbsp;</td><td align="center"><a href="../../../" target="_top" class="external text" title="Search" rel="nofollow"><img src="../../../dcommon/images/search.png" alt="Search" style="border:0;" /><br /><span class="mini"></span></a></td><td>&nbsp;</td><td align="center"><a href="../../eclipselink_jpa_extensions.pdf" title="PDF" target="_blank"><img src="../../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td><td align="center"><a href="#disqus_thread" title="Comments""><img src="../../../dcommon/images/comments.png" style="padding-right:5px;border:0" alt="Comments"><br /><span class="mini">Comments</span></a>
</tr>
</table>
</td>
</tr>
</table>
</div>
<!-- class="footer" -->
<div id="copyright">Copyright &copy; 2014 by The Eclipse Foundation under the <a href="http://www.eclipse.org/org/documents/epl-v10.php">Eclipse Public License (EPL)</a><br /> <script type="text/javascript">var LastUpdated = document.lastModified;document.writeln ("Updated: " + LastUpdated);</script> </div><!-- START: Analytics --><script type="text/javascript"> var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-1608008-2']); _gaq.push(['_trackPageview']); (function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })(); </script><!-- END: Analytics --><!-- START: Sharethis --><script>var options={ "publisher": "e2fe9e07-fab6-4f84-83ea-0991b429842c", "position": "right", "ad": { "visible": false, "openDelay": 5, "closeDelay": 0}};var st_hover_widget = new sharethis.widgets.hoverbuttons(options);</script><!-- END: Sharethis --></body>
</html>