blob: 4eb7b705fa3928df8ccf5e5e9534216819018c14 [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>About Native SQL Queries | EclipseLink 2.5.x Understanding EclipseLink</title>
<meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 1.0.17" />
<meta name="date" content="2013-10-03T10:37:16Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="About Native SQL Queries" />
<meta name="relnum" content="2.5" />
<link rel="stylesheet" type="text/css" href="../../dcommon/style.css" media="screen" />
<link rel="copyright" href="../../dcommon/html/cpyr.htm" 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="queries004.htm" title="Previous" type="text/html" />
<link rel="next" href="queries006.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:mdbgdwjv8zu', 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">Understanding EclipseLink,
<b>2.5</b><br /></font></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_otlcg.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td>
</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="queries004.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="queries006.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="A7724128" name="A7724128"></a><a id="OTLCG94397" name="OTLCG94397"></a>
<div class="sect1"><!-- infolevel="all" infotype="General" -->
<h1 class="sect1"><font face="arial, helvetica, sans-serif" color="#330099">About Native SQL Queries</font></h1>
<p>JPA allows SQL to be used for querying entity objects, or data. SQL queries are not translated, and passed directly to the database. SQL queries can be used for advanced queries that require database specific syntax, or by users who are more comfortable in the SQL language than JPQL or Java.</p>
<p>SQL queries are created from the <code>EntityManager</code> using the <code>createNativeQuery</code> API or via named queries. A Query object is returned and executed the same as any other JPA query. An SQL query can be created for an entity class, or return an object array of data. If returning entities, the SQL query must return the column names that the entity's mappings expect, or an <code>SqlResultSetMapping</code> can be used. An <code>SqlResultSetMapping</code> allows the SQL result set to be mapped to an entity, or set of entities and data.</p>
<p>SQL queries can be used to execute SQL or DML (Data Manipulation Language) statements. For SQL queries that return results, <code>getSingleResult</code> or <code>getResultList</code> can be used. For SQL queries that do not return results, <code>executeUpdate</code> must be used. <code>executeUpdate</code> can only be used within a transaction. SQL queries can be used to execute database operations and some stored procedures and functions. Stored procedures that return output parameters, or certain complex stored procedures, cannot be executed with SQL queries. EclipseLink supports stored procedures through stored procedure queries.</p>
<p>Query settings and query hints that affect the generated SQL are not supported with SQL queries. Unsupported query hints include:</p>
<ul>
<li>
<p><code>batch</code></p>
</li>
<li>
<p><code>history.as-of</code></p>
</li>
<li>
<p><code>inheritance.outer-join</code></p>
</li>
<li>
<p><code>sql.hint</code></p>
</li>
<li>
<p><code>join-fetch</code>&mdash;<code>join-fetch</code> is supported, but requires that the SQL selects all of the joined columns.</p>
</li>
<li>
<p><code>fetch-group</code>&mdash;<code>fetch-group</code> is supported, but requires that the SQL selects all of the fetched columns.</p>
</li>
<li>
<p><code>pessimistic-lock</code>&mdash;<code>pessimistic-lock</code> is supported, but requires that the SQL locks the result rows.</p>
</li>
</ul>
<p>For descriptions of these extensions, see "EclipseLink Query Language" in <em>Java Persistence API (JPA) Extensions Reference for EclipseLink</em>.</p>
<a id="OTLCG94398" name="OTLCG94398"></a>
<div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref102" name="sthref102"></a>
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Parameters</font></h2>
<p>Parameters to SQL queries are delimited using the question mark (<code>?</code>) character. Only indexed parameters are supported, named parameters are not supported. The index can be used in the delimiter, such as <code>?1</code>. Parameter values are set on the Query using the <code>setParameter</code> API. Indexed parameters start at the index 1 not 0.</p>
</div>
<!-- class="sect2" -->
<a id="OTLCG94399" name="OTLCG94399"></a>
<div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref103" name="sthref103"></a>
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">Named Native SQL Queries</font></h2>
<p>Native SQL queries can be defined as named queries in annotations or XML using the <code>NamedNativeQuery</code> annotation or <code>&lt;named-native-query&gt;</code> XML element. Named native SQL queries are executed the same as any named query.</p>
</div>
<!-- class="sect2" -->
<a id="OTLCG94400" name="OTLCG94400"></a>
<div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref104" name="sthref104"></a>
<h2 class="sect2"><font face="arial, helvetica, sans-serif" color="#330099">SQL Result Set Mapping</font></h2>
<p>An <code>SqlResultSetMapping</code> can be used to map the results of an SQL query to an entity if the result column names do not match what the entity mappings expect. It can also be used to return multiple entities, or entities and data from a single SQL query. <code>EntityResult</code> and <code>FieldResult</code> are used to map the SQL query result column to the entity attribute. <code>ColumnResult</code> can be used to add a data element to the result.</p>
<p><code>SqlResultSetMappings</code> are defined through annotations or XML using the <code>@SqlResultSetMapping</code> annotation or <code>&lt;sql-result-set-mapping&gt;</code> XML element. They are referenced from native SQL queries by name.</p>
</div>
<!-- class="sect2" --></div>
<!-- class="sect1" --></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="queries004.htm"><img src="../../dcommon/images/larrow.png" alt="Previous" border="0" height="16" width="16" /></a></td>
<td align="center"><a href="queries006.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 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_otlcg.pdf" title="PDF" target="_blank"><img src="../../dcommon/images/pdf_icon.png" style="padding-right:5px;border:0" alt="PDF"></a></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<!-- class="footer" -->
<div id="copyright">Copyright &copy; 2012 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>