This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/cayenne-website.git
The following commit(s) were added to refs/heads/asf-site by this push:
new aeea820 4.1 query docs refresh
aeea820 is described below
commit aeea8206a1a36dcfc231f499b4c0050b8b238d72
Author: Andrus Adamchik <and...@objectstyle.com>
AuthorDate: Sat Nov 2 12:28:13 2019 +0300
4.1 query docs refresh
---
docs/4.1/cayenne-guide/index.html | 540 ++++++++++++--------------------------
1 file changed, 172 insertions(+), 368 deletions(-)
diff --git a/docs/4.1/cayenne-guide/index.html
b/docs/4.1/cayenne-guide/index.html
index da27ecd..6898d5c 100644
--- a/docs/4.1/cayenne-guide/index.html
+++ b/docs/4.1/cayenne-guide/index.html
@@ -2245,25 +2245,25 @@ o.orderList(list);</code></pre>
<p>There is a number of built-in queries in Cayenne, described later in
this chapter. Most of the newer queries use fluent API and can be created and
executed as easy-to-read one-liners. Users can define their own query types to
abstract certain DB interactions that for whatever reason can not be adequately
described by the built-in set.</p>
</div>
<div class="paragraph">
- <p>Queries can be roughly categorized as "object" and "native". Object
queries (most notably <code>ObjectSelect</code>, <code>SelectById</code>, and
<code>EJBQLQuery</code>) are built with abstractions originating in the object
model (the "object" side in the "object-relational" divide). E.g. ObjectSelect
is assembled from a Java class of the objects to fetch, a qualifier expression,
orderings, etc. - all of this expressed in terms of the object model.</p>
+ <p>Queries can be roughly categorized as "object" and "native". Object
queries (most notably <code>ObjectSelect</code>, <code>SelectById</code>, and
<code>EJBQLQuery</code>) are built with abstractions originating in the object
model (the "object" side in the "object-relational" divide). E.g.
<code>ObjectSelect</code> consists of a Java class of objects to fetch, a
qualifier expression, orderings, etc. - all of this expressed in terms of the
object model.</p>
</div>
<div class="paragraph">
- <p>Native queries describe a desired DB operation as SQL code
(<code>SQLSelect</code>, <code>SQLTemplate</code> query) or a reference to a
stored procedure (<code>ProcedureQuery</code>), etc. The results of native
queries are usually presented as Lists of Maps, with each map representing a
row in the DB (a term "data row" is often used to describe such a map). They
can potentially be converted to objects, however it may take a considerable
effort to do so. Native queries are also les [...]
+ <p>Native queries describe a desired DB operation using SQL
(<code>SQLSelect</code>, <code>SQLExec</code> query), a reference to a stored
procedure (<code>ProcedureQuery</code>), etc. The results of native queries are
lists of scalars, lists of <code>Object[]</code> or lists of maps (a term "data
row" is often used to describe such a map). Some of them can potentially be
converted to persistent objects (though usually with considerable effort).
Native queries are less (if at all) por [...]
</div>
<div class="sect3">
<h4 id="select"><a class="anchor" href="#select"></a>ObjectSelect</h4>
+ <div class="admonitionblock note">
+ <table>
+ <tbody>
+ <tr>
+ <td class="icon"> <i class="fa fa-info-circle fa-2x" title="Note"></i>
</td>
+ <td class="content"> ObjectSelect supersedes older SelectQuery.
SelectQuery is still available and supported, but will be deprecated in the
future. </td>
+ </tr>
+ </tbody>
+ </table>
+ </div>
<div class="sect4">
<h5 id="selecting-objects"><a class="anchor"
href="#selecting-objects"></a>Selecting objects</h5>
- <div class="admonitionblock note">
- <table>
- <tbody>
- <tr>
- <td class="icon"> <i class="fa fa-info-circle fa-2x"
title="Note"></i> </td>
- <td class="content"> ObjectSelect supersedes older SelectQuery.
SelectQuery is still available and supported, but will be deprecated in the
future. </td>
- </tr>
- </tbody>
- </table>
- </div>
<div class="paragraph">
<p><code>ObjectSelect</code> is the most commonly used query in Cayenne
applications. This may be the only query you will ever need. It returns a list
of persistent objects (or data rows) of a certain type specified in the
query:</p>
</div>
@@ -2534,17 +2534,16 @@ List<String> names =
context.performQuery(query);</code></pre>
<div class="sect3">
<h4 id="sqlselect-and-sqlexec"><a class="anchor"
href="#sqlselect-and-sqlexec"></a>SQLSelect and SQLExec</h4>
<div class="paragraph">
- <p><code>SQLSelect</code> and <code>SQLExec</code> are essentially a
"fluent" versions of older <code>SQLTemplate</code> query.
<code>SQLSelect</code> can be used (as name suggests) to select custom data in
form of entities, separate columns, collection of <code>DataRow</code> or
Object[]. <code>SQLExec</code> is designed to just execute any raw SQL code
(e.g. updates, deletes, DDLs, etc.) This queries support all directives
described in <a href="#sqltemplate">SQLTemplate</a> sectio [...]
+ <p>SQL is very powerful and allows to manipulate data in ways that can
not always be described as a graph of related entities. Cayenne acknowledges
this fact and provides a facility to execute SQL, sometimes allowing to map
results back to persistent objects. <code>SQLSelect</code> and
<code>SQLExec</code> are a pair of queries that allow to run native SQL.
<code>SQLSelect</code> can be used (as name suggests) to select custom data in
form of entities, separate columns, collection o [...]
</div>
<div class="paragraph">
- <p>Here is example of how to use SQLSelect:</p>
+ <p>Both queries support advanced SQL templating, with variable
substitution and special directives as described <a href="#sqlscripting">in the
next chapter</a>. Here we’ll just provide a few simple examples:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlight"><code class="language-java java"
data-lang="java">// Selecting objects
List<Painting> paintings = SQLSelect
- .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE
#bind($title)")
- .params("title", "painting%")
+ .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE
'A%'")
.upperColumnNames()
.localCache()
.limit(100)
@@ -2552,215 +2551,35 @@ List<Painting> paintings = SQLSelect
// Selecting scalar values
List<String> paintingNames = SQLSelect
- .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE
ESTIMATED_PRICE > #bind($price)")
- .params("price", 100000)
+ .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE
ESTIMATED_PRICE > 100000")
.select(context);
// Selecting DataRow with predefined types
List<DataRow> result = SQLSelect
- .dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class,
LocalDateTime.class)
+ .dataRowQuery("SELECT * FROM ARTIST", Integer.class, String.class,
LocalDateTime.class)
.select(context);
// Selecting Object[] with predefined types
List<Object[]> result = SQLSelect
- .scalarQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class,
LocalDateTime.class)
+ .scalarQuery("SELECT * FROM ARTIST", Integer.class, String.class,
LocalDateTime.class)
.select(context);</code></pre>
</div>
</div>
<div class="paragraph">
- <p>And here is example of how to use <code>SQLExec</code>:</p>
+ <p>And here is an example of how to use <code>SQLExec</code>:</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlight"><code class="language-java java"
data-lang="java">int inserted = SQLExec
- .query("INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (#bind($id),
#bind($name))")
- .paramsArray(55, "Picasso")
+ .query("INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (55,
'Picasso')")
.update(context);</code></pre>
</div>
</div>
</div>
<div class="sect3">
- <h4 id="mappedselect-and-mappedexec"><a class="anchor"
href="#mappedselect-and-mappedexec"></a>MappedSelect and MappedExec</h4>
- <div class="paragraph">
- <p><code>MappedSelect</code> and <code>MappedExec</code> is a queries
that are just a reference to another queries stored in the DataMap. The actual
stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. Difference
between <code>MappedSelect</code> and <code>MappedExec</code> is (as reflected
in their names) whether underlying query intended to select data or just to
perform some generic SQL code.</p>
- </div>
- <div class="admonitionblock note">
- <table>
- <tbody>
- <tr>
- <td class="icon"> <i class="fa fa-info-circle fa-2x" title="Note"></i>
</td>
- <td class="content"> These queries are "fluent" versions of deprecated
<code>NamedQuery</code> class. </td>
- </tr>
- </tbody>
- </table>
- </div>
- <div class="paragraph">
- <p>Here is example of how to use <code>MappedSelect</code>:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">List<Artist> results =
MappedSelect.query("artistsByName", Artist.class)
- .param("name", "Picasso")
- .select(context);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>And here is example of <code>MappedExec</code>:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">QueryResult result = MappedExec.query("updateQuery")
- .param("var", "value")
- .execute(context);
-System.out.println("Rows updated: " + result.firstUpdateCount());</code></pre>
- </div>
- </div>
- </div>
- <div class="sect3">
- <h4 id="procedurecall"><a class="anchor"
href="#procedurecall"></a>ProcedureCall</h4>
- <div class="paragraph">
- <p>Stored procedures are mapped as separate objects in CayenneModeler.
<code>ProcedureCall</code> provides a way to execute them with a certain set of
parameters. This query is a "fluent" version of older
<code>ProcedureQuery</code>. Just like with <code>SQLTemplate</code>, the
outcome of a procedure can be anything - a single result set, multiple result
sets, some data modification (returned as an update count), or a combination of
these. So use root class to get a single result se [...]
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">List<Artist> result =
ProcedureCall.query("my_procedure", Artist.class)
- .param("p1", "abc")
- .param("p2", 3000)
- .call(context)
- .firstList();</code></pre>
- </div>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">// here we do not bother with root class.
-// Procedure name gives us needed routing information
-ProcedureResult result = ProcedureCall.query("my_procedure")
- .param("p1", "abc")
- .param("p2", 3000)
- .call();</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>A stored procedure can return data back to the application as result
sets or via OUT parameters. To simplify the processing of the query output,
QueryResponse treats OUT parameters as if it was a separate result set. For
stored procedures declaref any OUT or INOUT parameters,
<code>ProcedureResult</code> have convenient utility method to get them:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">ProcedureResult result = ProcedureCall.query("my_procedure")
- .call(context);
-
-// read OUT parameters
-Object out = result.getOutParam("out_param");</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>There maybe a situation when a stored procedure handles its own
transactions, but an application is configured to use Cayenne-managed
transactions. This is obviously conflicting and undesirable behavior. In this
case ProcedureQueries should be executed explicitly wrapped in an "external"
Transaction. This is one of the few cases when a user should worry about
transactions at all. See Transactions section for more details.</p>
- </div>
- </div>
- <div class="sect3">
- <h4 id="custom-queries"><a class="anchor"
href="#custom-queries"></a>Custom Queries</h4>
- <div class="paragraph">
- <p>If a user needs some extra functionality not addressed by the existing
set of Cayenne queries, he can write his own. The only requirement is to
implement <code>org.apache.cayenne.query.Query</code> interface. The easiest
way to go about it is to subclass some of the base queries in Cayenne.</p>
- </div>
- <div class="paragraph">
- <p>E.g. to do something directly in the JDBC layer, you might subclass
<code>AbstractQuery</code>:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">public class MyQuery extends AbstractQuery {
-
- @Override
- public SQLAction createSQLAction(SQLActionVisitor visitor) {
- return new SQLAction() {
-
- @Override
- public void performAction(Connection connection, OperationObserver
observer) throws SQLException, Exception {
- // 1. do some JDBC work using provided connection...
- // 2. push results back to Cayenne via OperationObserver
- }
- };
- }
-}</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>To delegate the actual query execution to a standard Cayenne query,
you may subclass <code>IndirectQuery</code>:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">public class MyDelegatingQuery extends IndirectQuery {
-
- @Override
- protected Query createReplacementQuery(EntityResolver resolver) {
- SQLTemplate delegate = new SQLTemplate(SomeClass.class,
generateRawSQL());
- delegate.setFetchingDataRows(true);
- return delegate;
- }
-
- protected String generateRawSQL() {
- // build some SQL string
- }
-}</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>In fact many internal Cayenne queries are
<code>IndirectQueries</code>, delegating to <code>SelectQuery</code> or
<code>SQLTemplate</code> after some preprocessing.</p>
- </div>
- </div>
- <div class="sect3">
- <h4 id="sqltemplate"><a class="anchor"
href="#sqltemplate"></a>SQLTemplate</h4>
- <div class="paragraph">
- <p>SQLTemplate is a query that allows to run native SQL from a Cayenne
application. It comes handy when the standard ORM concepts are not sufficient
for a given query or an update. SQL is too powerful and allows to manipulate
data in ways that are not easily described as a graph of related entities.
Cayenne acknowledges this fact and provides this facility to execute SQL,
mapping the result to objects when possible. Here are examples of selecting and
non-selecting SQLTemplates:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">SQLTemplate select = new SQLTemplate(Artist.class, "select *
from ARTIST");
-List<Artist> result = context.performQuery(select);</code></pre>
- </div>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">SQLTemplate update = new SQLTemplate(Artist.class, "delete
from ARTIST");
-QueryResponse response = context.performGenericQuery(update);</code></pre>
- </div>
- </div>
+ <h4 id="sqlscripting"><a class="anchor" href="#sqlscripting"></a>Scripting
SQL Queries</h4>
<div class="paragraph">
- <p>Cayenne doesn’t make any attempt to make sense of the SQL semantics,
so it doesn’t know whether a given query is performing a select or update, etc.
It is the the user’s decision to run a given query as a selecting or
"generic".</p>
- </div>
- <div class="admonitionblock note">
- <table>
- <tbody>
- <tr>
- <td class="icon"> <i class="fa fa-info-circle fa-2x" title="Note"></i>
</td>
- <td class="content"> Any data modifications done to DB as a result of
<code>SQLTemplate</code> execution do not change the state of objects in the
ObjectContext. So some objects in the context may become stale as a result.
</td>
- </tr>
- </tbody>
- </table>
- </div>
- <div class="paragraph">
- <p>Another point to note is that the first argument to the
<code>SQLTemplate</code> constructor - the Java class - has the same meaning as
in SelectQuery only when the result can be converted to objects (e.g. when this
is a selecting query and it is selecting all columns from one table). In this
case it denotes the "root" entity of this query result. If the query does not
denote a single entity result, this argument is only used for query routing,
i.e. determining which database it [...]
- </div>
- <div class="paragraph">
- <p>To achieve interoperability between multiple RDBMS a user can specify
multiple SQL statements for the same <code>SQLTemplate</code>, each
corresponding to a native SQL dialect. A key used to look up the right dialect
during execution is a fully qualified class name of the corresponding
<code>DbAdapter</code>. If no DB-specific statement is present for a given DB,
a default generic statement is used. E.g. in all the examples above a default
statement will be used regardless of the [...]
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">SQLTemplate select = new SQLTemplate(Artist.class, "select *
from ARTIST");
-
-// For Postgres it would be nice to trim padding of all CHAR columns.
-// Otherwise those will be returned with whitespace on the right.
-// assuming "NAME" is defined as CHAR...
-String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST";
-query.setTemplate(PostgresAdapter.class.getName(), pgSQL);</code></pre>
- </div>
- </div>
- <div class="sect4">
- <h5 id="scripting-sqltemplate-with-templates"><a class="anchor"
href="#scripting-sqltemplate-with-templates"></a>Scripting SQLTemplate with
templates</h5>
- <div class="paragraph">
- <p>The most interesting aspect of SQLTemplate (and the reason why it is
called a "template") is that a SQL string is treated by Cayenne as a template.
Before sending it to DB as a PreparedStatement, the String is evaluated, that
does variable substitutions, and performs special callbacks in response to
various directives, thus controlling query interaction with the JDBC layer.</p>
- </div>
- <div class="paragraph">
- <p>Here we’ll describe the two main scripting elements - "variables"
(that look like <code>$var</code>) and "directives" (that look like
<code>#directive(p1 p2 p3)</code>). Cayenne defines a number of directives to
bind parameters to <code>PreparedStatement</code> and to control the structure
of the <code>ResultSet</code>. These directives are described in the following
sections.</p>
- </div>
+ <p>A powerful feature of <code>SQLSelect</code> and <code>SQLExec</code>
is that a SQL string is treated by Cayenne as a dynamic template. Before
sending it to DB as a PreparedStatement, the String is evaluated, resolving the
dynamic parts. The two main scripting elements are "variables" (that look like
<code>$var</code>) and "directives" (that look like <code>#directive(p1 p2
p3)</code>). In the discussion below we’ll use both selecting and updating
examples, as scripting works the [...]
</div>
<div class="sect4">
<h5 id="variable-substitution"><a class="anchor"
href="#variable-substitution"></a>Variable Substitution</h5>
@@ -2769,28 +2588,24 @@ query.setTemplate(PostgresAdapter.class.getName(),
pgSQL);</code></pre>
</div>
<div class="listingblock">
<div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">SQLTemplate query = new SQLTemplate(Artist.class, "delete from
$tableName");
-query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING"));
-
-// this will generate SQL like this: "delete from mydb.PAINTING"</code></pre>
+ <pre class="highlight"><code class="language-java java"
data-lang="java">// this will generate SQL like this: "delete from
mydb.PAINTING"
+SQLExec query = SQLExec.query("delete from $tableName")
+ .params("mydb.PAINTING");</code></pre>
</div>
</div>
<div class="paragraph">
- <p>The example above demonstrates the point made earlier in this chapter
- even if we don’t know upfront which table the query will run against, we can
still use a fixed "root" in constructor (<code>Artist.class</code> in this
case), as we are not planning on converting the result to objects.</p>
- </div>
- <div class="paragraph">
- <p>Variable substitution within the text uses
<code>object.toString()</code> method to replace the variable value. Keep in
mind that this may not be appropriate in all situations. E.g. passing a date
object in a WHERE clause expression may be converted to a String not understood
by the target RDBMS SQL parser. In such cases variable should be wrapped in
<code>#bind</code> directive as described below.</p>
+ <p>Variable substitution within the text uses
<code>object.toString()</code> method to replace the variable value. This may
not be appropriate in all situations. E.g. passing a date object in a
<code>WHERE</code> clause expression may be converted to a String not
understood by the target DB SQL parser. In such cases variable should be
wrapped in <code>#bind</code> directive as described below.</p>
</div>
</div>
<div class="sect4">
<h5 id="directives"><a class="anchor"
href="#directives"></a>Directives</h5>
<div class="paragraph">
- <p>These are the Cayenne directives used to customize SQLTemplate
parsing and integrate it with the JDBC layer:</p>
+ <p>"Directives" look like <code>#directive(p1 p2 p3)</code> (notice the
absence of comma between the arguments). The folliwng directives are supported
in SQL templates:</p>
</div>
<div class="sect5">
<h6 id="bind"><a class="anchor" href="#bind"></a>#bind</h6>
<div class="paragraph">
- <p>Creates a PreparedStatement positional parameter in place of the
directive, binding the value to it before statement execution.
<code>#bind</code> is allowed in places where a "?" would be allowed in a
PreparedStatement. And in such places it almost always makes sense to pass
objects to the template via this or other forms of <code>#bind</code> instead
of inserting them inline.</p>
+ <p>Creates a <code>PreparedStatement</code> positional parameter in
place of the directive, binding the value to it before statement execution.
<code>#bind</code> is allowed in places where a "?" would be allowed in a
PreparedStatement. And in such places it almost always makes sense to pass
objects to the template via some flavor of <code>#bind</code> instead of
inserting them inline.</p>
</div>
<div class="paragraph">
<p>Semantics:</p>
@@ -2835,7 +2650,7 @@ query.setParameters(Collections.singletonMap("tableName",
"mydb.PAINTING"));
<div class="sect5">
<h6 id="bindequal"><a class="anchor"
href="#bindequal"></a>#bindEqual</h6>
<div class="paragraph">
- <p>Same as #bind, but also includes the "=" sign in front of the value
binding. Look at the example below - we took the #bind example and replaced
<code>"ID = #bind(..)"</code> with <code>"ID #bindEqual(..)"</code>. While it
looks like a clumsy shortcut to eliminate the equal sign, the actual reason why
this is useful is that it allows the value to be null. If the value is not
null, <code>"= ?"</code> is generated, but if it is, the resulting chunk of the
SQL would look like <code [...]
+ <p>Same as <code>#bind</code>, but also includes the "=" sign in front
of the value binding. Look at the example below - we took the #bind example and
replaced <code>"ID = #bind(..)"</code> with <code>"ID #bindEqual(..)"</code>.
Motivation for this directive is to handle NULL SQL syntax. If the value is not
null, <code>= ?</code> is generated, but if it is, the resulting SQL would look
like <code>IS NULL</code>, which is compliant with what the DB expects.</p>
</div>
<div class="paragraph">
<p>Semantics:</p>
@@ -2873,7 +2688,7 @@ query.setParameters(Collections.singletonMap("tableName",
"mydb.PAINTING"));
<div class="sect5">
<h6 id="bindnotequal"><a class="anchor"
href="#bindnotequal"></a>#bindNotEqual</h6>
<div class="paragraph">
- <p>This directive deals with the same issue as <code>#bindEqual</code>
above, only it generates "not equal" in front of the value (or IS NOT
NULL).</p>
+ <p>This directive deals with the same issue as <code>#bindEqual</code>
above, only it generates "!=" in front of the value (or <code>IS NOT
NULL</code>).</p>
</div>
<div class="paragraph">
<p>Semantics:</p>
@@ -2904,7 +2719,7 @@ query.setParameters(Collections.singletonMap("tableName",
"mydb.PAINTING"));
</div>
<div class="listingblock">
<div class="content">
- <pre class="highlight"><code class="language-SQL SQL"
data-lang="SQL">update ARTIST set NAME = #bind($name) where ID
#bindEqual($id)</code></pre>
+ <pre class="highlight"><code class="language-SQL SQL"
data-lang="SQL">update ARTIST set NAME = #bind($name) where ID
#bindNotEqual($id)</code></pre>
</div>
</div>
</div>
@@ -2945,18 +2760,18 @@
query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING"));
</div>
<div class="listingblock">
<div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">String sql = "SELECT * FROM PAINTING t0 WHERE
#bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID";
-SQLTemplate select = new SQLTemplate(Artist.class, sql);
+ <pre class="highlight"><code class="language-java java"
data-lang="java">String sql = "SELECT * FROM PAINTING t0 WHERE
#bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' )";
+Artist artistParam = ...;
-Artist a = ....
-select.setParameters(Collections.singletonMap("a", a));</code></pre>
+SQLSelect select = SQLSelect.query(Painting.class, sql)
+ .params("a", artistParam);</code></pre>
</div>
</div>
</div>
<div class="sect5">
<h6 id="bindobjectnotequal"><a class="anchor"
href="#bindobjectnotequal"></a>#bindObjectNotEqual</h6>
<div class="paragraph">
- <p>Same as #bindObjectEqual above, only generates "not equal" operator
for value comparison (or IS NOT NULL).</p>
+ <p>Same as <code>#bindObjectEqual</code> above, only generates
<code>!=</code> operator for value comparison (or <code>IS NOT
NULL</code>).</p>
</div>
<div class="paragraph">
<p>Semantics:</p>
@@ -2983,18 +2798,28 @@ select.setParameters(Collections.singletonMap("a",
a));</code></pre>
</div>
<div class="listingblock">
<div class="content">
- <pre class="highlight"><code class="language-java java"
data-lang="java">String sql = "SELECT * FROM PAINTING t0 WHERE
#bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID";
-SQLTemplate select = new SQLTemplate(Artist.class, sql);
+ <pre class="highlight"><code class="language-java java"
data-lang="java">String sql = "SELECT * FROM PAINTING t0 WHERE
#bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' )";
+Artist artistParam = ...;
-Artist a = ....
-select.setParameters(Collections.singletonMap("a", a));</code></pre>
+SQLSelect select = SQLSelect.query(Painting.class, sql)
+ .params("a", artistParam);</code></pre>
</div>
</div>
</div>
<div class="sect5">
<h6 id="result"><a class="anchor" href="#result"></a>#result</h6>
<div class="paragraph">
- <p>Renders a column in SELECT clause of a query and maps it to a key in
the result DataRow. Also ensures the value read is of the correct type. This
allows to create a DataRow (and ultimately - a persistent object) from an
arbitrary ResultSet.</p>
+ <p>Used around a column in <code>SELECT</code> clause to define the
type conversion of the column value (e.g. it may force a conversion from
Integer to Long) and/or define column name in the result (useful when fetching
objects or DataRows).</p>
+ </div>
+ <div class="admonitionblock note">
+ <table>
+ <tbody>
+ <tr>
+ <td class="icon"> <i class="fa fa-info-circle fa-2x"
title="Note"></i> </td>
+ <td class="content"> You don’t have to use <code>#result</code> for
any given query if the default data types and column names coming from the
query suit your needs. But if you do, you have to provide <code>#result</code>
for every single result column, otherwise such column will be ignored. </td>
+ </tr>
+ </tbody>
+ </table>
</div>
<div class="paragraph">
<p>Semantics:</p>
@@ -3049,155 +2874,134 @@ select.setParameters(Collections.singletonMap("a",
a));</code></pre>
</table>
</div>
</div>
- <div class="sect5">
- <h6 id="mapping-sqltemplate-results"><a class="anchor"
href="#mapping-sqltemplate-results"></a>Mapping SQLTemplate Results</h6>
- <div class="paragraph">
- <p>Here we’ll discuss how to convert the data selected via SQLTemplate
to some useable format, compatible with other query results. It can either be
very simple or very complex, depending on the structure of the SQL, JDBC driver
nature and the desired result structure. This section presents various tips and
tricks dealing with result mapping.</p>
- </div>
- <div class="paragraph">
- <p>By default SQLTemplate is expected to return a List of Persistent
objects of its root type. This is the simple case:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">SQLTemplate query = new SQLTemplate(Artist.class, "SELECT *
FROM ARTIST");
-
-// List of Artists
-List<Artist> artists = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>Just like SelectQuery, SQLTemplate can fetch DataRows. In fact
DataRows option is very useful with SQLTemplate, as the result type most often
than not does not represent a Cayenne entity, but instead may be some
aggregated report or any other data whose object structure is opaque to
Cayenne:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">String sql = "SELECT t0.NAME, COUNT(1) FROM ARTIST t0 JOIN
PAINTING t1 ON (t0.ID = t1.ARTIST_ID) "
- + "GROUP BY t0.NAME ORDER BY COUNT(1)";
-SQLTemplate query = new SQLTemplate(Artist.class, sql);
+ </div>
+ </div>
+ <div class="sect3">
+ <h4 id="mappedselect-and-mappedexec"><a class="anchor"
href="#mappedselect-and-mappedexec"></a>MappedSelect and MappedExec</h4>
+ <div class="paragraph">
+ <p><code>MappedSelect</code> and <code>MappedExec</code> is a queries
that are just a reference to another queries stored in the DataMap. The actual
stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. Difference
between <code>MappedSelect</code> and <code>MappedExec</code> is (as reflected
in their names) whether underlying query intended to select data or just to
perform some generic SQL code.</p>
+ </div>
+ <div class="admonitionblock note">
+ <table>
+ <tbody>
+ <tr>
+ <td class="icon"> <i class="fa fa-info-circle fa-2x" title="Note"></i>
</td>
+ <td class="content"> These queries are "fluent" versions of deprecated
<code>NamedQuery</code> class. </td>
+ </tr>
+ </tbody>
+ </table>
+ </div>
+ <div class="paragraph">
+ <p>Here is example of how to use <code>MappedSelect</code>:</p>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">List<Artist> results =
MappedSelect.query("artistsByName", Artist.class)
+ .param("name", "Picasso")
+ .select(context);</code></pre>
+ </div>
+ </div>
+ <div class="paragraph">
+ <p>And here is example of <code>MappedExec</code>:</p>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">QueryResult result = MappedExec.query("updateQuery")
+ .param("var", "value")
+ .execute(context);
+System.out.println("Rows updated: " + result.firstUpdateCount());</code></pre>
+ </div>
+ </div>
+ </div>
+ <div class="sect3">
+ <h4 id="procedurecall"><a class="anchor"
href="#procedurecall"></a>ProcedureCall</h4>
+ <div class="paragraph">
+ <p>Stored procedures are mapped as separate objects in CayenneModeler.
<code>ProcedureCall</code> provides a way to execute them with a certain set of
parameters. This query is a "fluent" version of older
<code>ProcedureQuery</code>. Just like with <code>SQLTemplate</code>, the
outcome of a procedure can be anything - a single result set, multiple result
sets, some data modification (returned as an update count), or a combination of
these. So use root class to get a single result se [...]
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">List<Artist> result =
ProcedureCall.query("my_procedure", Artist.class)
+ .param("p1", "abc")
+ .param("p2", 3000)
+ .call(context)
+ .firstList();</code></pre>
+ </div>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">// here we do not bother with root class.
+// Procedure name gives us needed routing information
+ProcedureResult result = ProcedureCall.query("my_procedure")
+ .param("p1", "abc")
+ .param("p2", 3000)
+ .call();</code></pre>
+ </div>
+ </div>
+ <div class="paragraph">
+ <p>A stored procedure can return data back to the application as result
sets or via OUT parameters. To simplify the processing of the query output,
QueryResponse treats OUT parameters as if it was a separate result set. For
stored procedures declaref any OUT or INOUT parameters,
<code>ProcedureResult</code> have convenient utility method to get them:</p>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">ProcedureResult result = ProcedureCall.query("my_procedure")
+ .call(context);
-// ensure we are fetching DataRows
-query.setFetchingDataRows(true);
+// read OUT parameters
+Object out = result.getOutParam("out_param");</code></pre>
+ </div>
+ </div>
+ <div class="paragraph">
+ <p>There maybe a situation when a stored procedure handles its own
transactions, but an application is configured to use Cayenne-managed
transactions. This is obviously conflicting and undesirable behavior. In this
case ProcedureQueries should be executed explicitly wrapped in an "external"
Transaction. This is one of the few cases when a user should worry about
transactions at all. See Transactions section for more details.</p>
+ </div>
+ </div>
+ <div class="sect3">
+ <h4 id="custom-queries"><a class="anchor"
href="#custom-queries"></a>Custom Queries</h4>
+ <div class="paragraph">
+ <p>If a user needs some extra functionality not addressed by the existing
set of Cayenne queries, he can write his own. The only requirement is to
implement <code>org.apache.cayenne.query.Query</code> interface. The easiest
way to go about it is to subclass some of the base queries in Cayenne.</p>
+ </div>
+ <div class="paragraph">
+ <p>E.g. to do something directly in the JDBC layer, you might subclass
<code>AbstractQuery</code>:</p>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">public class MyQuery extends AbstractQuery {
-// List of DataRow
-List<DataRow> rows = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>In the example above, even though the query root is Artist. The
result is a list of artist names with painting counts (as mentioned before in
such case "root" is only used to find the DB to fetch against, but has no
bearning on the result). The DataRows here are the most appropriate and desired
result type.</p>
- </div>
- <div class="paragraph">
- <p>In a more advanced case you may decide to fetch a list of scalars or
a list of <code>Object[]</code> with each array entry being either an entity or
a scalar. You probably won’t be doing this too often and it requires quite a
lot of work to setup, but if you want your <code>SQLTemplate</code> to return
results similar to <code>EJBQLQuery</code>, it is doable using
<code>SQLResult</code> as described below:</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">SQLTemplate query = new SQLTemplate(Painting.class, "SELECT
ESTIMATED_PRICE P FROM PAINTING");
+ @Override
+ public SQLAction createSQLAction(SQLActionVisitor visitor) {
+ return new SQLAction() {
-// let Cayenne know that result is a scalar
-SQLResult resultDescriptor = new SQLResult();
-resultDescriptor.addColumnResult("P");
-query.setResult(resultDescriptor);
+ @Override
+ public void performAction(Connection connection, OperationObserver
observer) throws SQLException, Exception {
+ // 1. do some JDBC work using provided connection...
+ // 2. push results back to Cayenne via OperationObserver
+ }
+ };
+ }
+}</code></pre>
+ </div>
+ </div>
+ <div class="paragraph">
+ <p>To delegate the actual query execution to a standard Cayenne query,
you may subclass <code>IndirectQuery</code>:</p>
+ </div>
+ <div class="listingblock">
+ <div class="content">
+ <pre class="highlight"><code class="language-java java"
data-lang="java">public class MyDelegatingQuery extends IndirectQuery {
-// List of BigDecimals
-List<BigDecimal> prices = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">SQLTemplate query = new SQLTemplate(Artist.class, "SELECT
t0.ID, t0.NAME, t0.DATE_OF_BIRTH, COUNT(t1.PAINTING_ID) C " +
- "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) " +
- "GROUP BY t0.ID, t0.NAME, t0.DATE_OF_BIRTH");
-
-// let Cayenne know that result is a mix of Artist objects and the count of
their paintings
-EntityResult artistResult = new EntityResult(Artist.class);
-artistResult.addDbField(Artist.ID_PK_COLUMN, "ARTIST_ID");
-artistResult.addObjectField(Artist.NAME_PROPERTY, "NAME");
-artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, "DATE_OF_BIRTH");
-
-SQLResult resultDescriptor = new SQLResult();
-resultDescriptor.addEntityResult(artistResult);
-resultDescriptor.addColumnResult("C");
-query.setResult(resultDescriptor);
-
-// List of Object[]
-List<Object[]> data = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>You can fetch list of scalars, list of Object[] or list of DataRow
with predefined result column types or using default types.</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">// Selecting Object[] with predefined types
-SQLTemplate q3 = new SQLTemplate(Artist.class, "SELECT ARTIST_ID, ARTIST_NAME
FROM ARTIST");
- q3.setResultColumnsTypes(Double.class, String.class);
- q3.setUseScalar(true);
-List<Object[]> result = context.performQuery(q3);
+ @Override
+ protected Query createReplacementQuery(EntityResolver resolver) {
+ SQLTemplate delegate = new SQLTemplate(SomeClass.class,
generateRawSQL());
+ delegate.setFetchingDataRows(true);
+ return delegate;
+ }
-// Selecting DataRow with predefined types
-SQLTemplate q3 = new SQLTemplate(Artist.class, "SELECT ARTIST_ID, ARTIST_NAME
FROM ARTIST");
- q3.setResultColumnsTypes(Double.class, String.class);
- q3.setFetchingDataRows(true);
-List<DataRow> result = context.performQuery(q3);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>Another trick related to mapping result sets is making Cayenne
recognize prefetched entities in the result set. This emulates "joint"
prefetching of <code>SelectQuery</code>, and is achieved by special column
naming. Columns belonging to the "root" entity of the query should use
unqualified names corresponding to the root <code>DbEntity</code> columns. For
each related entity column names must be prefixed with relationship name and a
dot (e.g. "toArtist.ID"). Column naming can [...]
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">String sql = "SELECT distinct "
- + "#result('t1.ESTIMATED_PRICE' 'BigDecimal' ''
'paintings.ESTIMATED_PRICE'), "
- + "#result('t1.PAINTING_TITLE' 'String' '' 'paintings.PAINTING_TITLE'), "
- + "#result('t1.GALLERY_ID' 'int' '' 'paintings.GALLERY_ID'), "
- + "#result('t1.ID' 'int' '' 'paintings.ID'), "
- + "#result('NAME' 'String'), "
- + "#result('DATE_OF_BIRTH' 'java.util.Date'), "
- + "#result('t0.ID' 'int' '' 'ID') "
- + "FROM ARTIST t0, PAINTING t1 "
- + "WHERE t0.ID = t1.ARTIST_ID";
-
-SQLTemplate q = new SQLTemplate(Artist.class, sql);
-q.addPrefetch(Artist.PAINTINGS_PROPERTY)
-List<Artist> objects = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>And the final tip deals with capitalization of the DataRow keys.
Queries like <code>"SELECT * FROM…"</code> and even <code>"SELECT COLUMN1,
COLUMN2, … FROM …"</code> can sometimes result in Cayenne exceptions on
attempts to convert fetched DataRows to objects. Essentially any query that is
not using a <code>#result</code> directive to describe the result set is prone
to this problem, as different databases may produce different capitalization of
the <code>java.sql.ResultSet< [...]
- </div>
- <div class="paragraph">
- <p>The most universal way to address this issue is to describe each
column explicitly in the SQLTemplate via <code>#result</code>, e.g.:
<code>"SELECT #result('column1'), #result('column2'), .."</code>. However this
quickly becomes impractical for tables with lots of columns. For such cases
Cayenne provides a shortcut based on the fact that an ORM mapping usually
follows some naming convention for the column names. Simply put, for
case-insensitive databases developers normally use [...]
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
-query.setColumnNamesCapitalization(CapsStrategy.LOWER);
-List objects = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>or</p>
- </div>
- <div class="listingblock">
- <div class="content">
- <pre class="highlight"><code class="language-Java Java"
data-lang="Java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
-query.setColumnNamesCapitalization(CapsStrategy.UPPER);
-List objects = context.performQuery(query);</code></pre>
- </div>
- </div>
- <div class="paragraph">
- <p>None of this affects the generated SQL, but the resulting DataRows
are using correct capitalization.</p>
- </div>
- <div class="admonitionblock note">
- <table>
- <tbody>
- <tr>
- <td class="icon"> <i class="fa fa-info-circle fa-2x"
title="Note"></i> </td>
- <td class="content"> You probably shouldn’t bother with this unless
you are getting <code>CayenneRuntimeExceptions</code> when fetching with
<code>SQLTemplate</code>. </td>
- </tr>
- </tbody>
- </table>
- </div>
+ protected String generateRawSQL() {
+ // build some SQL string
+ }
+}</code></pre>
</div>
</div>
+ <div class="paragraph">
+ <p>In fact many internal Cayenne queries are
<code>IndirectQueries</code>, delegating to <code>SelectQuery</code> or
<code>SQLTemplate</code> after some preprocessing.</p>
+ </div>
</div>
</div>
<div class="sect2">
