Google Git
Sign in
apache / db-jdo-site / refs/heads/publish / . / content / query_sql.html
blob: 8656d54f35bd37d2a2352f6ca60eefcd38486fad [file] [log] [blame]
<!DOCTYPE html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- No caching headers -->
<meta http-equiv="cache-control" content="no-cache"/>
<meta http-equiv="pragma" content="no-cache"/>
<meta http-equiv="expires" content="-1"/>
<meta name="keywords" content="JDO, Apache, Java, Data, Objects"/>
<meta name="description" content="Apache JDO - Java Data Objects"/>
<title>SQL Queries</title>
<link rel="icon" type="image/png" href="images/JDOx150.png"/>
<!--
Apache JDO Documentation Template
==================================
This template derived various aspects from Apache Deltaspike template, the Apache ISIS template and the Datanucleus template.
This template uses
* Bootstrap v3.3.7 (https://getbootstrap.com/) for navbar.
* Asciidoctor "foundation" CSS
* Bootswatch "cosmo" theme for Bootstrap (https://bootswatch.com/cosmo).
* Bootstrap TOC plugin v0.4.1 (https://afeld.github.io/bootstrap-toc/) for the table of contents.
* jQuery (necessary for Bootstrap's JavaScript plugins)
* Font-Awesome for some icons used by Asciidoctor
NOTE: tried using Font-Awesome CSS hosted locally but then fails to work!
-->
<link href="css/bootswatch/3.3.7/bootstrap-cosmo.css" rel="stylesheet"/>
<link href="css/bootstrap-toc/0.4.1/bootstrap-toc.min.css" rel="stylesheet"/>
<link href="css/asciidoctor/foundation.css" rel="stylesheet"/>
<link href="css/datanucleus_theme.css" rel="stylesheet"/>
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.3.0/css/font-awesome.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="js/bootstrap/3.3.7/bootstrap.min.js"></script>
<script src="js/bootstrap-toc/0.4.1/bootstrap-toc.min.js"></script>
<!-- Coderay syntax formatter -->
<style type="text/css">
/* Stylesheet for CodeRay to match GitHub theme | MIT License | http://foundation.zurb.com */
pre.CodeRay{background:#f7f7f8}
.CodeRay .line-numbers{border-right:1px solid currentColor;opacity:.35;padding:0 .5em 0 0}
.CodeRay span.line-numbers{display:inline-block;margin-right:.75em}
.CodeRay .line-numbers strong{color:#000}
table.CodeRay{border-collapse:separate;border:0;margin-bottom:0;background:none}
table.CodeRay td{vertical-align:top;line-height:inherit}
table.CodeRay td.line-numbers{text-align:right}
table.CodeRay td.code{padding:0 0 0 .75em}
.CodeRay .debug{color:#fff !important;background:#000080 !important}
.CodeRay .annotation{color:#007}
.CodeRay .attribute-name{color:#000080}
.CodeRay .attribute-value{color:#700}
.CodeRay .binary{color:#509}
.CodeRay .comment{color:#998;font-style:italic}
.CodeRay .char{color:#04d}
.CodeRay .char .content{color:#04d}
.CodeRay .char .delimiter{color:#039}
.CodeRay .class{color:#458;font-weight:bold}
.CodeRay .complex{color:#a08}
.CodeRay .constant,.CodeRay .predefined-constant{color:#008080}
.CodeRay .color{color:#099}
.CodeRay .class-variable{color:#369}
.CodeRay .decorator{color:#b0b}
.CodeRay .definition{color:#099}
.CodeRay .delimiter{color:#000}
.CodeRay .doc{color:#970}
.CodeRay .doctype{color:#34b}
.CodeRay .doc-string{color:#d42}
.CodeRay .escape{color:#666}
.CodeRay .entity{color:#800}
.CodeRay .error{color:#808}
.CodeRay .exception{color:inherit}
.CodeRay .filename{color:#099}
.CodeRay .function{color:#900;font-weight:bold}
.CodeRay .global-variable{color:#008080}
.CodeRay .hex{color:#058}
.CodeRay .integer,.CodeRay .float{color:#099}
.CodeRay .include{color:#555}
.CodeRay .inline{color:#000}
.CodeRay .inline .inline{background:#ccc}
.CodeRay .inline .inline .inline{background:#bbb}
.CodeRay .inline .inline-delimiter{color:#d14}
.CodeRay .inline-delimiter{color:#d14}
.CodeRay .important{color:#555;font-weight:bold}
.CodeRay .interpreted{color:#b2b}
.CodeRay .instance-variable{color:#008080}
.CodeRay .label{color:#970}
.CodeRay .local-variable{color:#963}
.CodeRay .octal{color:#40e}
.CodeRay .predefined{color:#369}
.CodeRay .preprocessor{color:#579}
.CodeRay .pseudo-class{color:#555}
.CodeRay .directive{font-weight:bold}
.CodeRay .type{font-weight:bold}
.CodeRay .predefined-type{color:inherit}
.CodeRay .reserved,.CodeRay .keyword {color:#000;font-weight:bold}
.CodeRay .key{color:#808}
.CodeRay .key .delimiter{color:#606}
.CodeRay .key .char{color:#80f}
.CodeRay .value{color:#088}
.CodeRay .regexp .delimiter{color:#808}
.CodeRay .regexp .content{color:#808}
.CodeRay .regexp .modifier{color:#808}
.CodeRay .regexp .char{color:#d14}
.CodeRay .regexp .function{color:#404;font-weight:bold}
.CodeRay .string{color:#d20}
.CodeRay .string .string .string{background:#ffd0d0}
.CodeRay .string .content{color:#d14}
.CodeRay .string .char{color:#d14}
.CodeRay .string .delimiter{color:#d14}
.CodeRay .shell{color:#d14}
.CodeRay .shell .delimiter{color:#d14}
.CodeRay .symbol{color:#990073}
.CodeRay .symbol .content{color:#a60}
.CodeRay .symbol .delimiter{color:#630}
.CodeRay .tag{color:#008080}
.CodeRay .tag-special{color:#d70}
.CodeRay .variable{color:#036}
.CodeRay .insert{background:#afa}
.CodeRay .delete{background:#faa}
.CodeRay .change{color:#aaf;background:#007}
.CodeRay .head{color:#f8f;background:#505}
.CodeRay .insert .insert{color:#080}
.CodeRay .delete .delete{color:#800}
.CodeRay .change .change{color:#66f}
.CodeRay .head .head{color:#f4f}
</style>
</head>
<body data-spy="scroll" data-target="#toc">
<!-- Navbar -->
<nav class="navbar navbar-default navbar-static-top">
<div class="container">
<div class="navbar-header">
<!-- Three line menu button for use on mobile screens -->
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<!-- 'style' added to align image with navbar. FIX THIS -->
<a class="navbar-brand" href="https://db.apache.org/jdo/">
<img style="margin-top: -12px;" alt="Brand" width="45.5" height="45.5" src="images/JDOx150.png"/>
</a>
<!-- TODO Any way to abstract the version from Maven? -->
<a class="navbar-brand" href="index.html">Apache JDO</a>
</div>
<!-- Navbar that will collapse on mobile screens -->
<div id="navbar" class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li class="dropdown">
<!-- menu item General -->
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">General<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="why_jdo.html">Why JDO?</a></li>
<li><a href="getting-started.html">Getting Started</a></li>
<li><a href="specifications.html">Specifications</a></li>
<li><a href="tck.html">TCK</a></li>
<li><a href="javadoc.html">API Javadoc</a></li>
<li role="separator" class="divider"></li>
<li><a href="jdo_v_jpa.html">JDO v JPA</a></li>
<li><a href="jdo_v_jpa_api.html">JDO v JPA : API</a></li>
<li><a href="jdo_v_jpa_orm.html">JDO v JPA : ORM</a></li>
<li role="separator" class="divider"></li>
<li><a href="license.html">License</a></li>
<li><a href="impls.html">Implementations</a></li>
<li><a href="jdo_3_0_overview.html">JDO 3.0 Overview</a></li>
<li><a href="references.html">References</a></li>
<li><a href="glossary.html">Glossary</a></li>
</ul>
</li>
<!-- menu item API Usage -->
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">API Usage<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="pmf.html">PersistenceManagerFactory</a></li>
<li><a href="pm.html">PersistenceManager</a></li>
<li><a href="transactions.html">Transactions</a></li>
<li><a href="attach_detach.html">Attach-Detach</a></li>
<li><a href="fetchgroups.html">Fetch Plan/Groups</a></li>
<li><a href="state_transition.html">Object States</a></li>
<li><a href="object_retrieval.html">Object Retrieval</a></li>
<li><a href="exceptions.html">Exceptions</a></li>
<li><a href="jdohelper.html">JDO Helper</a></li>
<li role="separator" class="divider"></li>
<li><a href="guides-replication.html">Data Replication</a></li>
</ul>
</li>
<!-- menu item Types & Metadata -->
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Types &amp; Metadata<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="class_types.html">Types of Classes</a></li>
<li><a href="field_types.html">Types of Fields</a></li>
<li role="separator" class="divider"></li>
<!-- <li class="dropdown-header">Metadata</li> -->
<li><a href="metadata.html">MetaData</a></li>
<li><a href="annotations.html">Annotations</a></li>
<li><a href="jdo_dtd.html">jdo DTD/XSD</a></li>
<li><a href="orm_dtd.html">orm DTD/XSD</a></li>
<li><a href="jdoquery_dtd.html">jdoquery DTD/XSD</a></li>
<li><a href="jdoconfig_dtd.html">jdoconfig DTD/XSD</a></li>
<li role="separator" class="divider"></li>
<li><a href="enhancement.html">Bytecode Enhancement</a></li>
</ul>
</li>
<!-- menu item Query -->
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false" style="background-color:#2385c6">Query<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="query_api.html">Query API</a></li>
<li role="separator" class="divider"></li>
<li><a href="jdoql.html">JDOQL</a></li>
<li><a href="jdoql_methods.html">Methods</a></li>
<li><a href="jdoql_result.html">Result</a></li>
<li><a href="jdoql_quickref.pdf">Quick Ref PDF</a></li>
<li><a href="jdoql_typed.html">JDOQL Typed API</a></li>
<li role="separator" class="divider"></li>
<li><a href="query_sql.html">SQL</a></li>
</ul>
</li>
<!-- menu item Community -->
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Community<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="get-involved.html">Get Involved</a></li>
<li><a href="team-list.html">Project Team</a></li>
<li><a href="mail-lists.html">Mailing Lists</a></li>
<li><a href="faq.html">FAQ</a></li>
</ul>
</li>
<!-- menu item Development -->
<li class="dropdown">
<a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">Development<span class="caret"></span></a>
<ul class="dropdown-menu">
<li><a href="roadmap.html">RoadMap / TODO</a></li>
<li><a href="downloads.html">Downloads</a></li>
<li><a href="source-code.html">Source Code</a></li>
<li><a href="coding-standards.html">Coding Standards</a></li>
<li><a href="issuetracking.html">Issue Tracking</a></li>
</ul>
</li>
</ul>
<!-- 'style' added to fix height of input box. FIX THIS -->
<form class="navbar-form navbar-left" role="search" id="search-form" action="https://www.google.com/search" method="get" style="padding: 1px 15px;">
<div class="form-group">
<input name="sitesearch" value="db.apache.org/jdo" type="hidden">
<input name="q" type="text" class="form-control" placeholder="Search" style="margin-top: 7px; padding: 1px; border-radius: 5px; height: 30px; vertical-align: middle;">
</div>
</form>
</div>
</div>
</nav>
<div class="container">
<div class="row-fluid">
<div class="col-sm-9">
<!-- <div class="page-title">
<h3>SQL Queries</h3>
</div>
-->
<div id="doc-content">
<div class="sect1">
<h2 id="SQL">SQL</h2>
<div class="sectionbody">
<div class="paragraph">
<p>As we have described earlier, JDO allows access to many query languages to give the user full flexibility over what they utilise.
To demonstrate how you would utilise a different query language with JDO, take the example that you are using an RDBMS (i.e your JDO provider supports RDBMS)
it is sometimes desirable to query using <strong>SQL</strong>.</p>
</div>
<div class="admonitionblock note">
<table>
<tr>
<td class="icon">
<i class="fa icon-note" title="Note"></i>
</td>
<td class="content">
Please be aware that the SQL query that you invoke has to be valid for your RDBMS, and that the SQL syntax differs across almost all RDBMS.
</td>
</tr>
</table>
</div>
<div class="paragraph">
<p>To utilise <strong>SQL</strong> syntax in queries, you create a Query as follows</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="predefined-type">Query</span> q = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, the_sql_query);</code></pre>
</div>
</div>
<div class="paragraph">
<p>You have several forms of SQL queries, depending on what form of output you require.</p>
</div>
<div class="ulist">
<ul>
<li>
<p><strong>No candidate class and no result class</strong> - the result will be a List of Objects (when there is a single column in the query),
or a List of Object[]s (when there are multiple columns in the query)</p>
</li>
<li>
<p><strong>Candidate class specified, no result class</strong> - the result will be a List of candidate class objects, or will be a single candidate class object (when you have specified "unique").
The columns of the querys result set are matched up to the fields of the candidate class by name. You need to select a minimum of the PK columns in the SQL statement.</p>
</li>
<li>
<p><strong>No candidate class, result class specified</strong> - the result will be a List of result class objects, or will be a single result class object (when you have specified "unique").
Your result class has to abide by the rules of JDO result classes (see <a href="query_api.html#resultclass">Result Class specification</a>) - this
typically means either providing public fields matching the columns of the result, or providing setters/getters for the columns of the result.</p>
</li>
<li>
<p><strong>Candidate class and result class specified</strong> - the result will be a List of result class objects, or will be a single result class object (when you have specified "unique").
The result class has to abide by the rules of JDO result classes (see <a href="query_api.html#resultclass">Result Class specification</a>).</p>
</li>
</ul>
</div>
<div class="sect2">
<h3 id="candidate">Setting candidate class</h3>
<div class="paragraph">
<p>If you want to return instances of persistable types, then you can set the candidate class.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="predefined-type">Query</span> query = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT MY_ID, MY_NAME FROM MYTABLE</span><span class="delimiter">&quot;</span></span>);
query.setClass(MyClass.class);
<span class="predefined-type">List</span>&lt;MyClass&gt; results = query.executeList();</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="unique">Unique results</h3>
<div class="paragraph">
<p>If you know that there will only be a single row returned from the SQL query then you can set the query as <em>unique</em>.
Note that the query will return null if the SQL has no results.</p>
</div>
<div class="paragraph">
<p>Sometimes you know that the query can only every return 0 or 1 objects. In this case you can simplify your job by adding</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="comment">// Using traditional JDO Query API</span>
<span class="predefined-type">Query</span> query = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT MY_ID, MY_NAME FROM MYTABLE</span><span class="delimiter">&quot;</span></span>);
query.setClass(MyClass.class);
query.setUnique(<span class="predefined-constant">true</span>);
MyClass obj = (MyClass) query.execute();
<span class="comment">// Using JDO3.2 Query API</span>
<span class="predefined-type">Query</span> query = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT MY_ID, MY_NAME FROM MYTABLE</span><span class="delimiter">&quot;</span></span>);
query.setClass(MyClass.class);
MyClass obj = query.executeUnique();</code></pre>
</div>
</div>
</div>
<div class="sect2">
<h3 id="result_class">Defining a result type</h3>
<div class="paragraph">
<p>If you want to dump each row of the SQL query results into an object of a particular type then you can set the result class.</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="comment">// Using traditional JDO Query API</span>
<span class="predefined-type">Query</span> query = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT MY_ID, MY_NAME FROM MYTABLE</span><span class="delimiter">&quot;</span></span>);
query.setResultClass(MyResultClass.class);
<span class="predefined-type">List</span>&lt;MyResultClass&gt; results = (<span class="predefined-type">List</span>&lt;MyResultClass&gt;) query.execute();
<span class="comment">// Using JDO3.2 Query API</span>
<span class="predefined-type">Query</span> query = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT MY_ID, MY_NAME FROM MYTABLE</span><span class="delimiter">&quot;</span></span>);
<span class="predefined-type">List</span>&lt;MyResultClass&gt; results = query.executeResultList(MyResultClass.class);</code></pre>
</div>
</div>
<div class="paragraph">
<p>The <em>Result Class</em> has to meet certain requirements. These are</p>
</div>
<div class="ulist">
<ul>
<li>
<p>Can be one of Integer, Long, Short, Float, Double, Character, Byte, Boolean, String, java.math.BigInteger, java.math.BigDecimal,
java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, or Object[]</p>
</li>
<li>
<p>Can be a user defined class, that has either a constructor taking arguments of the same type as those returned by the query (in the same order),
or has a public put(Object, Object) method, or public setXXX() methods, or public fields.</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>For example, if we are returning two columns like above, an <em>int</em> and a <em>String</em> then we define our result class like this</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="directive">public</span> <span class="type">class</span> <span class="class">MyResultClass</span>
{
<span class="directive">protected</span> <span class="type">int</span> id = <span class="integer">0</span>;
<span class="directive">protected</span> <span class="predefined-type">String</span> name = <span class="predefined-constant">null</span>;
<span class="directive">public</span> MyResultClass(<span class="type">int</span> id, <span class="predefined-type">String</span> name)
{
<span class="local-variable">this</span>.id = id;
<span class="local-variable">this</span>.name = name;
}
...
}</code></pre>
</div>
</div>
<div class="paragraph">
<p>So here we have a result class using the constructor arguments.
We could equally have provided a class with public fields instead, or provided <em>setXXX</em> methods, or just provide a <em>put</em> method. They all work in the same way.</p>
</div>
</div>
<div class="sect2">
<h3 id="parameters">Parameters</h3>
<div class="paragraph">
<p>In JDO SQL queries can have parameters but must be <em>positional</em>. This means that you do as follows</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="CodeRay highlight"><code data-lang="java"><span class="predefined-type">Query</span> q = pm.newQuery(<span class="string"><span class="delimiter">&quot;</span><span class="content">javax.jdo.query.SQL</span><span class="delimiter">&quot;</span></span>, <span class="string"><span class="delimiter">&quot;</span><span class="content">SELECT col1, col2 FROM MYTABLE WHERE col3 = ? AND col4 = ? and col5 = ?</span><span class="delimiter">&quot;</span></span>);
<span class="predefined-type">List</span> results = q.setParameters(val1, val2, val3).executeList();</code></pre>
</div>
</div>
<div class="paragraph">
<p>So we used traditional JDBC form of parametrisation, using "?".</p>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="col-sm-3">
<nav id="toc" data-spy="affix" data-toggle="toc"></nav>
</div>
</div>
</div>
<footer style="color:#fff; background-color:#222222; padding: 5px;">
<div class="container">
<div class="row" style="margin-top:25px">
<div class="col-sm" align="center">
<a href="https://www.facebook.com/JavaDataObjects" class="externalLink" title="Facebook">Facebook</a>
|
<a href="https://twitter.com/JavaDataObjects" class="externalLink" title="Twitter">Twitter</a>
|
<a href="http://wiki.apache.org/jdo" class="externalLink" title="Wiki">Wiki</a>
|
<a href="http://issues.apache.org/jira/secure/BrowseProject.jspa?id=10630" class="externalLink" title="Issue Tracker">Issue Tracker</a>
|
<a href="mail-lists.html" title="Mailing Lists">Mailing Lists</a>
|
<a href="api31/apidocs/index.html" title="Latest Javadocs">Latest Javadocs</a>
|
<a href="https://privacy.apache.org/policies/privacy-policy-public.html" title="Privacy Policy">Privacy Policy</a>
</div>
</div>
<p class="text-center" style="margin-top:16px">&copy; 2005-2022 Apache Software Foundation. All Rights Reserved.</p>
</div>
</footer>
</body>
</html>