You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by aa...@apache.org on 2019/11/02 09:27:07 UTC
[cayenne-website] branch master updated: 4.1 docs update...
removing SQLTemplate mentions
This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/cayenne-website.git
The following commit(s) were added to refs/heads/master by this push:
new aa234a5 4.1 docs update... removing SQLTemplate mentions
aa234a5 is described below
commit aa234a545718087920ae46779ec1e0a0a284a5ff
Author: Andrus Adamchik <an...@objectstyle.com>
AuthorDate: Sat Nov 2 12:27:01 2019 +0300
4.1 docs update... removing SQLTemplate mentions
---
src/main/site/content/docs/4.1/cayenne-guide.html | 540 +++++++---------------
1 file changed, 172 insertions(+), 368 deletions(-)
diff --git a/src/main/site/content/docs/4.1/cayenne-guide.html b/src/main/site/content/docs/4.1/cayenne-guide.html
index 6a2b5d1..9f4ff53 100644
--- a/src/main/site/content/docs/4.1/cayenne-guide.html
+++ b/src/main/site/content/docs/4.1/cayenne-guide.html
@@ -2070,25 +2070,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>
@@ -2359,17 +2359,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)
@@ -2377,215 +2376,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>
@@ -2594,28 +2413,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>
@@ -2660,7 +2475,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>
@@ -2698,7 +2513,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>
@@ -2729,7 +2544,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>
@@ -2770,18 +2585,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>
@@ -2808,18 +2623,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>
@@ -2874,155 +2699,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">