You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by nt...@apache.org on 2017/09/01 11:20:19 UTC
[2/2] cayenne git commit: CYA_2173 Update fluent query API
documentation
CYA_2173 Update fluent query API documentation
Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo
Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/d166a6ab
Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/d166a6ab
Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/d166a6ab
Branch: refs/heads/master
Commit: d166a6abd76c1a295741d95ba7177f275451e613
Parents: 62ba8fb
Author: Nikita Timofeev <st...@gmail.com>
Authored: Fri Sep 1 14:17:58 2017 +0300
Committer: Nikita Timofeev <st...@gmail.com>
Committed: Fri Sep 1 14:17:58 2017 +0300
----------------------------------------------------------------------
.../cayenne-guide/src/docbkx/queries-custom.xml | 57 ++
.../cayenne-guide/src/docbkx/queries-ejbql.xml | 94 +++
.../cayenne-guide/src/docbkx/queries-mapped.xml | 44 ++
.../src/docbkx/queries-namedquery.xml | 35 +
.../src/docbkx/queries-procedure.xml | 67 ++
.../src/docbkx/queries-procedurecall.xml | 61 ++
.../cayenne-guide/src/docbkx/queries-select.xml | 111 ++++
.../src/docbkx/queries-selectbyid.xml | 33 +
.../src/docbkx/queries-sqlselect.xml | 45 ++
.../src/docbkx/queries-sqltemplate.xml | 420 ++++++++++++
.../cayenne-guide/src/docbkx/queries.xml | 632 +------------------
11 files changed, 981 insertions(+), 618 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-custom.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-custom.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-custom.xml
new file mode 100644
index 0000000..a8f00ea
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-custom.xml
@@ -0,0 +1,57 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="custom-queries">
+ <title>Custom Queries</title>
+ <para>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. </para>
+ <para>E.g. to do something directly in the JDBC layer, you might subclass
+ AbstractQuery:<programlisting language="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
+ }
+ };
+ }
+}</programlisting></para>
+ <para>To delegate the actual query execution to a standard Cayenne query, you may subclass
+ IndirectQuery:<programlisting language="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
+ }
+}</programlisting></para>
+ <para>In fact many internal Cayenne queries are IndirectQueries, delegating to SelectQuery
+ or SQLTemplate after some preprocessing.</para>
+ </section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-ejbql.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-ejbql.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-ejbql.xml
new file mode 100644
index 0000000..4c0afff
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-ejbql.xml
@@ -0,0 +1,94 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to you under the Apache License, Version
+ 2.0 (the "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0 Unless required by
+ applicable law or agreed to in writing, software distributed under the
+ License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
+ CONDITIONS OF ANY KIND, either express or implied. See the License for
+ the specific language governing permissions and limitations under the
+ License.
+-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="ejbqlquery">
+ <title>EJBQLQuery</title>
+ <para>EJBQLQuery was created as a part of an experiment in adopting some of Java Persistence
+ API (JPA) approaches in Cayenne. It is a parameterized object query that is created from
+ query String. A String used to build EJBQLQuery must conform to JPQL (JPA query
+ language):<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");</programlisting></para>
+ <para>JPQL details can be found in any JPA manual. Here we'll mention only how this fits
+ into Cayenne and what are the differences between EJBQL and other Cayenne
+ queries.</para>
+ <para>Although most frequently EJBQLQuery is used as an alternative to SelectQuery, there
+ are also DELETE and UPDATE varieties available. <note>
+ <para>As of this version of Cayenne, DELETE and UPDATE do not change the state of
+ objects in the ObjectContext. They are run directly against the database
+ instead. </para>
+ </note><programlisting language="java">EJBQLQuery select = new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
+List<Artist> artists = context.performQuery(select);</programlisting><programlisting language="java">EJBQLQuery delete = new EJBQLQuery("delete from Painting");
+context.performGenericQuery(delete);</programlisting><programlisting language="java">EJBQLQuery update = new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
+context.performGenericQuery(update);</programlisting>In
+ most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides you
+ with better compile-time checks. However sometimes you may want a completely scriptable
+ object query. This is when you might prefer EJBQL. A more practical reason for picking
+ EJBQL over SelectQuery though is that the former offers some extra selecting
+ capabilities, namely aggregate functions and
+ subqueries:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
+List<Object[]> result = context.performQuery(query);
+for(Object[] artistWithCount : result) {
+ Artist a = (Artist) artistWithCount[0];
+ int hasPaintings = (Integer) artistWithCount[1];
+}</programlisting>This
+ also demonstrates a previously unseen type of select result - a List of Object[]
+ elements, where each entry in an Object[] is either a DataObject or a scalar, depending
+ on the query SELECT clause. A result can also be a list of
+ scalars:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a.name FROM Artist a");
+List<String> names = context.performQuery(query);</programlisting>
+
+ EJBQLQuery supports an "IN" clause with three different usage-patterns. The following
+ example would require three individual positional parameters (named
+ parameters could also have been used) to be supplied.
+
+ <programlisting language="sql">select p from Painting p where p.paintingTitle in (?1,?2,?3)</programlisting>
+
+ The following example requires a single positional parameter to be supplied. The
+ parameter can be any concrete implementation of the java.util.Collection interface such as
+ java.util.List or java.util.Set.
+
+ <programlisting language="sql">select p from Painting p where p.paintingTitle in ?1</programlisting>
+
+ The following example is functionally identical to the one prior.
+
+ <programlisting language="sql">select p from Painting p where p.paintingTitle in (?1)</programlisting>
+
+ </para>
+ <para>
+ It is <link linkend="expressions-to-ejbql">possible to convert</link>
+ an <link linkend="expressions">Expression</link>
+ object used with a <link linkend="selectquery">SelectQuery</link>
+ to EJBQL. Use the Expression#appendAsEJBQL methods for this purpose.
+ </para>
+ <para>
+ While
+ Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE
+ clause, and indeed they are very close, there are a few noteable differences:
+ <itemizedlist>
+ <listitem>
+ <para>Null handling: SelectQuery would translate the expressions matching NULL
+ values to the corresponding "X IS NULL" or "X IS NOT NULL" SQL syntax.
+ EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL")
+ syntax to be used, otherwise the generated SQL will look like "X = NULL" (or
+ "X <> NULL"), which will evaluate differently.</para>
+ </listitem>
+ <listitem>
+ <para>Expression Parameters: SelectQuery uses "$" to denote named parameters
+ (e.g. "$myParam"), while EJBQL uses ":" (e.g. ":myParam"). Also EJBQL
+ supports positional parameters denoted by the question mark: "?3".</para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-mapped.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-mapped.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-mapped.xml
new file mode 100644
index 0000000..b80e828
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-mapped.xml
@@ -0,0 +1,44 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="mappedqueries">
+ <title>MappedSelect and MappedExec</title>
+ <para>
+ <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.
+ <note>
+ <para>This queries are "fluent" versions of deprecated <code>NamedQuery</code> class.</para>
+ </note>
+ </para>
+ <para>
+ Here is example of how to use <code>MappedSelect</code>:
+ <programlisting language="java"><![CDATA[List<Artist> results = MappedSelect.query("artistsByName", Artist.class)
+ .param("name", "Picasso")
+ .select(context);]]></programlisting>
+ </para>
+ <para>
+ And here is example of <code>MappedExec</code>:
+ <programlisting language="java"><![CDATA[QueryResult result = MappedExec.query("updateQuery")
+ .param("var", "value")
+ .execute(context);
+System.out.println("Rows updated: " + result.firstUpdateCount());]]></programlisting>
+ </para>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-namedquery.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-namedquery.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-namedquery.xml
new file mode 100644
index 0000000..2287413
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-namedquery.xml
@@ -0,0 +1,35 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="namedquery">
+ <title>NamedQuery</title>
+ <para>NamedQuery is a query that is a reference to another query stored in the DataMap. The
+ actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. It doesn't matter
+ - the API for calling them is the same - via a
+ NamedQuery:<programlisting language="java">String[] keys = new String[] {"loginid", "password"};
+Object[] values = new String[] {"joe", "secret"};
+
+NamedQuery query = new NamedQuery("Login", keys, values);
+
+List<User> matchingUsers = context.performQuery(query); </programlisting>
+ <warning>
+ <para>This query is deprecated in favor of <code>MappedSelect</code> and <code>MappedExec</code></para>
+ </warning>
+ </para>
+ </section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-procedure.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-procedure.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-procedure.xml
new file mode 100644
index 0000000..dea2ce8
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-procedure.xml
@@ -0,0 +1,67 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="procedurequery">
+ <title>ProcedureQuery</title>
+ <para>Stored procedures are mapped as separate objects in CayenneModeler. ProcedureQuery
+ provides a way to execute them with a certain set of parameters. Just like with
+ SQLTemplate, the outcome of a procedure can be anything - a single result set, mutliple
+ result sets, some data modification (returned as an update count), or a combination of
+ these. So use "performQuery" to get a single result set, and use "performGenericQuery"
+ for anything
+ else:<programlisting language="java">ProcedureQuery query = new ProcedureQuery("my_procedure", Artist.class);
+
+// Set "IN" parameter values
+query.addParam("p1", "abc");
+query.addParam("p2", 3000);
+
+List<Artist> result = context.performQuery(query);</programlisting><programlisting language="java">// here we do not bother with root class.
+// Procedure name gives us needed routing information
+ProcedureQuery query = new ProcedureQuery("my_procedure");
+
+query.addParam("p1", "abc");
+query.addParam("p2", 3000);
+
+QueryResponse response = context.performGenericQuery(query); </programlisting></para>
+ <para>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. If a stored procedure declares any OUT or
+ INOUT parameters, QueryResponse will contain their returned values in the very first
+ result
+ list:<programlisting language="java">ProcedureQuery query = new ProcedureQuery("my_procedure");
+QueryResponse response = context.performGenericQuery(query);
+
+// read OUT parameters
+List out = response.firstList();
+
+if(!out.isEmpty()) {
+ Map outParameterValues = (Map) outList.get(0);
+}</programlisting></para>
+ <para>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.</para>
+ <para>
+ <warning>
+ <para>This query is superseded by <code>ProcedureCall</code> and generally shouldn't be used.</para>
+ </warning>
+ </para>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-procedurecall.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-procedurecall.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-procedurecall.xml
new file mode 100644
index 0000000..394a21e
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-procedurecall.xml
@@ -0,0 +1,61 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="procedurecallquery">
+ <title>ProcedureCall</title>
+ <para>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 set, and use only procedure name
+ for anything else:
+ <programlisting language="java"><![CDATA[List<Artist> result = ProcedureCall.query("my_procedure", Artist.class)
+ .param("p1", "abc")
+ .param("p2", 3000)
+ .call(context)
+ .firstList();]]></programlisting>
+
+ <programlisting language="java"><![CDATA[// 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();]]></programlisting>
+ </para>
+ <para>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:
+ <programlisting language="java">ProcedureResult result = ProcedureCall.query("my_procedure")
+ .call(context);
+
+// read OUT parameters
+Object out = result.getOutParam("out_param");
+</programlisting>
+ </para>
+ <para>
+ 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.
+ </para>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-select.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-select.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-select.xml
new file mode 100644
index 0000000..87a265a
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-select.xml
@@ -0,0 +1,111 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to you under the Apache License, Version
+ 2.0 (the "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0 Unless required by
+ applicable law or agreed to in writing, software distributed under the
+ License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
+ CONDITIONS OF ANY KIND, either express or implied. See the License for
+ the specific language governing permissions and limitations under the
+ License.
+-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="selectquery">
+ <title>ObjectSelect</title>
+ <section>
+ <title>Selecting objects</title>
+ <para>
+ <emphasis role="italic">ObjectSelect supersedes older SelectQuery. SelectQuery is still
+ available and supported. </emphasis>
+ </para>
+ <para>ObjectSelect 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:
+ <programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class).select(context);</programlisting>
+ This returned all rows in the "ARTIST" table. If the logs were turned on, you might see the
+ following SQL printed:
+ <screen>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0
+INFO: === returned 5 row. - took 5 ms.</screen>
+ </para>
+ <para>This SQL was generated by Cayenne from the ObjectSelect above. ObjectSelect can have a
+ qualifier to select only the data matching specific criteria. Qualifier is simply an
+ Expression (Expressions where discussed in the previous chapter), appended to the query
+ using "where" method. If you only want artists whose name begins with 'Pablo', you might
+ use the following qualifier expression:
+ <programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class)
+ .where(Artist.NAME.like("Pablo%"))
+ .select(context);</programlisting>
+ The SQL will look different this time:
+ <screen>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 WHERE t0.NAME LIKE ?
+[bind: 1->NAME:'Pablo%']
+INFO: === returned 1 row. - took 6 ms.</screen>
+ </para>
+ <para>ObjectSelect allows to assemble qualifier from parts, using "and" and "or" method to
+ chain then together:
+ <programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class)
+ .where(Artist.NAME.like("A%"))
+ .and(Artist.DATE_OF_BIRTH.gt(someDate)
+ .select(context);</programlisting>
+ </para>
+ <para>To order the results of ObjectSelect, one or more orderings can be applied:
+ <programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class)
+ .addOrderBy(Artist.DATE_OF_BIRTH.desc())
+ .addOrderBy(Artist.NAME.asc())
+ .select(context);</programlisting>
+ </para>
+ <para>There's a number of other useful methods in ObjectSelect that define what to select
+ and how to optimize database interaction (prefetching, caching, fetch offset and limit,
+ pagination, etc.). Some of them are discussed in separate chapters on caching and
+ performance optimization. Others are fairly self-explanatory. Please check the API docs
+ for the full extent of the ObjectSelect features.
+ </para>
+ </section>
+ <section>
+ <title>Selecting individual columns</title>
+ <para>
+ <code>ObjectSelect</code> query can be used to fetch individual properties of objects via
+ type-safe API:
+ <programlisting language="java"><![CDATA[List<String> names = ObjectSelect.columnQuery(Artist.class, Artist.ARTIST_NAME)
+ .select(context);]]></programlisting>
+ And here is example of selecting several properties, note that result will be <code>Object[]</code>:
+ <programlisting language="java"><![CDATA[List<Object[]> nameAndDate = ObjectSelect
+ .columnQuery(Artist.class, Artist.ARTIST_NAME, Artist.DATE_OF_BIRTH)
+ .select(context);]]></programlisting>
+ </para>
+ </section>
+ <section>
+ <title>Selecting using aggregate functions</title>
+ <para>
+ ObjectSelect query supports usage of aggregate functions.
+ Most common variant of aggregation is selecting count of records, this can be done really easy:
+ <programlisting language="java"><![CDATA[long count = ObjectSelect.query(Artist.class).selectCount(context);]]></programlisting>
+ But you can use aggregates in more cases, even combine selecting individual properties and aggregates:
+ <programlisting language="java"><![CDATA[// this is artificial property signaling that we want to get full object
+Property<Artist> artistProperty = Property.createSelf(Artist.class);
+
+List<Object[]> artistAndPaintingCount = ObjectSelect.columnQuery(Artist.class, artistProperty, Artist.PAINTING_ARRAY.count())
+ .where(Artist.ARTIST_NAME.like("a%"))
+ .having(Artist.PAINTING_ARRAY.count().lt(5L))
+ .orderBy(Artist.PAINTING_ARRAY.count().desc(), Artist.ARTIST_NAME.asc())
+ .select(context);
+
+for(Object[] next : artistAndPaintingCount) {
+ Artist artist = (Artist)next[0];
+ long paintings = (Long)next[1];
+ System.out.println(artist.getArtistName() + " have " + paintings + " paintings");
+}]]></programlisting>
+ Here is generated <code>SQL</code> for this query:
+ <programlisting language="sql">SELECT DISTINCT t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID, COUNT(t1.PAINTING_ID)
+FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
+WHERE t0.ARTIST_NAME LIKE ?
+GROUP BY t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH
+HAVING COUNT(t1.PAINTING_ID) < ?
+ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME</programlisting>
+ </para>
+ </section>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-selectbyid.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-selectbyid.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-selectbyid.xml
new file mode 100644
index 0000000..59940f9
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-selectbyid.xml
@@ -0,0 +1,33 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="selectbyid">
+ <title>SelectById</title>
+ <para>
+ This query allows to search objects by their ID.
+ It's introduced in Cayenne 4.0 and uses new "fluent" API same as <code>ObjectSelect</code> query.
+ </para>
+ <para>
+ Here is example of how to use it:
+ <programlisting language="java"><![CDATA[Artist artistWithId1 = SelectById.query(Artist.class, 1)
+ .prefetch(Artist.PAINTING_ARRAY.joint())
+ .localCache()
+ .selectOne(context);]]></programlisting>
+ </para>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-sqlselect.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-sqlselect.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-sqlselect.xml
new file mode 100644
index 0000000..20be436
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-sqlselect.xml
@@ -0,0 +1,45 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ ~ Licensed to the Apache Software Foundation (ASF) under one
+ ~ or more contributor license agreements. See the NOTICE file
+ ~ distributed with this work for additional information
+ ~ regarding copyright ownership. The ASF licenses this file
+ ~ to you under the Apache License, Version 2.0 (the
+ ~ "License"); you may not use this file except in compliance
+ ~ with the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing,
+ ~ software distributed under the License is distributed on an
+ ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ ~ KIND, either express or implied. See the License for the
+ ~ specific language governing permissions and limitations
+ ~ under the License.
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~-->
+<section xmlns="http://docbook.org/ns/docbook" xmlns:xlink="http://www.w3.org/1999/xlink" xml:id="sqlselect">
+ <title>SQLSelect and SQLExec</title>
+ <para>
+ <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 or collection of <code>DataRow</code>.
+ <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 <link linkend="sqltemplate">SQLTemplate</link> section.
+ </para>
+ <para>
+ Here is example of how to use <code>SQLSelect</code>:
+ <programlisting language="java"><![CDATA[SQLSelect<Painting> q1 = SQLSelect
+ .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE #bind($title)")
+ .params("title", "painting%")
+ .upperColumnNames()
+ .localCache()
+ .limit(100)
+ .select(context);]]></programlisting>
+ </para>
+ <para>
+ And here is example of how to use <code>SQLExec</code>:
+ <programlisting language="java"><![CDATA[int inserted = SQLExec
+ .query("INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (#bind($id), #bind($name))")
+ .paramsArray(55, "Picasso")
+ .update(context);]]></programlisting>
+ </para>
+</section>
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries-sqltemplate.xml
----------------------------------------------------------------------
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries-sqltemplate.xml b/docs/docbook/cayenne-guide/src/docbkx/queries-sqltemplate.xml
new file mode 100644
index 0000000..4d0b21c
--- /dev/null
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries-sqltemplate.xml
@@ -0,0 +1,420 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to you under the Apache License, Version
+ 2.0 (the "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0 Unless required by
+ applicable law or agreed to in writing, software distributed under the
+ License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
+ CONDITIONS OF ANY KIND, either express or implied. See the License for
+ the specific language governing permissions and limitations under the
+ License.
+-->
+<section xmlns="http://docbook.org/ns/docbook" xml:id="sqltemplate">
+ <title>SQLTemplate</title>
+ <para>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:<programlisting language="java">SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST");
+List<Artist> result = context.performQuery(select);</programlisting><programlisting language="java">SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST");
+QueryResponse response = context.performGenericQuery(update);</programlisting></para>
+ <para>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".</para>
+ <para>
+ <note>
+ <para>Any data modifications done to DB as a result of SQLTemplate execution do not
+ change the state of objects in the ObjectContext. So some objects in the context
+ may become stale as a result.</para>
+ </note>
+ </para>
+ <para>Another point to note is that the first argument to the SQLTemplate 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 should be run against. You are free to
+ use any persistent class or even a DataMap instance in such situation. It will work as
+ long as the passed "root" maps to the same database as the current query.</para>
+ <para>To achieve interoperability between mutliple RDBMS a user can specify multiple SQL
+ statements for the same SQLTemplate, 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 DbAdapter. 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 runtime database. So in most cases you won't need to
+ explicitly "translate" your SQL to all possible dialects. Here is how this works in
+ practice:<programlisting language="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);</programlisting></para>
+
+ <section xml:id="sqltemplate-scripting">
+ <title>Scripting SQLTemplate with Velocity</title>
+ <para>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 an Apache Velocity
+ template. Before sending it to DB as a PreparedStatement, the String is evaluated in
+ the Velocity context, that does variable substitutions, and performs special
+ callbacks in response to various directives, thus controlling query interaction with
+ the JDBC layer. </para>
+ <para>Check Velocity docs for the syntax details. Here we'll just mention the two main
+ scripting elements - "variables" (that look like <code>$var</code>) and "directives"
+ (that look like <code>#directive(p1 p2 p3)</code>). All built-in Velocity directives
+ are supported. Additionally Cayenne defines a number of its own directives to bind
+ parameters to PreparedStatements and to control the structure of the ResultSet.
+ These directives are described in the following sections.</para>
+ </section>
+ <section xml:id="sqltemplate-parameters">
+ <title>Variable Substitution</title>
+ <para>All variables in the template string are replaced from query
+ parameters:<programlisting language="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"</programlisting>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.</para>
+ <para>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.</para>
+ </section>
+ <section xml:id="sqltemplate-bind-directive">
+ <title>Directives</title>
+ <para>These are the Cayenne directives used to customize SQLTemplate parsing and
+ integrate it with the JDBC layer: </para>
+ <section>
+ <title>#bind</title>
+ <para>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.</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#bind(value)
+#bind(value jdbcType)
+#bind(value jdbcType scale)</programlisting>
+ <para><emphasis role="italic">Arguments:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para><code>value</code> - can either be a char constant or a variable
+ that is resolved from the query parameters. Note that the variable
+ can be a collection, that will be automatically expanded into a list
+ of individual value bindings. This is useful for instance to build
+ IN conditions. </para>
+ </listitem>
+ <listitem>
+ <para><code>jdbcType</code> - is a JDBC data type of the parameter as
+ defined in <code>java.sql.Types</code>.</para>
+ </listitem>
+ <listitem>
+ <para><code>scale</code> - An optional scale of the numeric value. Same
+ as "scale" in PreparedStatement.</para>
+ </listitem>
+ </itemizedlist></para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#bind($xyz)
+#bind('str')
+#bind($xyz 'VARCHAR')
+#bind($xyz 'DECIMAL' 2)</programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID = #bind($id)</programlisting></para>
+ </section>
+ <section>
+ <title>#bindEqual</title>
+ <para>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>IS NULL</code>" and will be compilant with what the
+ DB expects.</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#bindEqual(value)
+#bindEqual(value jdbcType)
+#bindEqual(value jdbcType scale)</programlisting>
+ <para><emphasis role="italic">Arguments: (same as #bind)</emphasis>
+ </para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#bindEqual($xyz)
+#bindEqual('str')
+#bindEqual($xyz 'VARCHAR')
+#bindEqual($xyz 'DECIMAL' 2)</programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID #bindEqual($id)</programlisting></para>
+ </section>
+ <section>
+ <title>#bindNotEqual</title>
+ <para>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).</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#bindNotEqual(value)
+#bindNotEqual(value jdbcType)
+#bindNotEqual(value jdbcType scale)</programlisting>
+ <para><emphasis role="italic">Arguments: (same as #bind)</emphasis></para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#bindNotEqual($xyz)
+#bindNotEqual('str')
+#bindNotEqual($xyz 'VARCHAR')
+#bindNotEqual($xyz 'DECIMAL' 2)</programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID #bindEqual($id)</programlisting></para>
+ </section>
+ <section>
+ <title>#bindObjectEqual</title>
+ <para>It can be tricky to use a Persistent object or an ObjectId in a binding,
+ especially for tables with compound primary keys. This directive helps to handle
+ such binding. It maps columns in the query to the names of Persistent object ID
+ columns, extracts ID values from the object, and generates SQL like "COL1 = ?
+ AND COL2 = ? ..." , binding positional parameters to ID values. It can also
+ correctly handle null object. Also notice how we are specifying a Velocity array
+ for multi-column PK.</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#bindObjectEqual(value columns idColumns)</programlisting>
+ <para><emphasis role="italic">Arguments:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para><code>value</code> - must be a variable that is resolved from the
+ query parameters to a Persistent or ObjectId.</para>
+ </listitem>
+ <listitem>
+ <para><code>columns</code> - the names of the columns to generate in the
+ SQL.</para>
+ </listitem>
+ <listitem>
+ <para><code>idColumn</code> - the names of the ID columns for a given
+ entity. Must match the order of "columns" to match against.</para>
+ </listitem>
+ </itemizedlist></para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#bindObjectEqual($a 't0.ID' 'ID')
+#bindObjectEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])</programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="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);
+
+Artist a = ....
+select.setParameters(Collections.singletonMap("a", a)); </programlisting></para>
+ </section>
+ <section>
+ <title>#bindObjectNotEqual</title>
+ <para>Same as #bindObjectEqual above, only generates "not equal" operator for value
+ comparison (or IS NOT NULL).</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#bindObjectNotEqual(value columns idColumns)</programlisting>
+ <para><emphasis role="italic">Arguments: (same as #bindObjectEqual)</emphasis>
+ </para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#bindObjectNotEqual($a 't0.ID' 'ID')
+#bindObjectNotEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])</programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="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);
+
+Artist a = ....
+select.setParameters(Collections.singletonMap("a", a)); </programlisting></para>
+ </section>
+ <section>
+ <title>#result</title>
+ <para>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.</para>
+ <para><emphasis role="italic">Semantics:</emphasis></para>
+ <programlisting language="java">#result(column)
+#result(column javaType)
+#result(column javaType alias)
+#result(column javaType alias dataRowKey)</programlisting>
+ <para><emphasis role="italic">Arguments:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para><code>column</code> - the name of the column to render in SQL
+ SELECT clause.</para>
+ </listitem>
+ <listitem>
+ <para><code>javaType</code> - a fully-qualified Java class name for a
+ given result column. For simplicity most common Java types used in
+ JDBC can be specified without a package. These include all numeric
+ types, primitives, String, SQL dates, BigDecimal and BigInteger. So
+ "<code>#result('A' 'String')</code>", "<code>#result('B'
+ 'java.lang.String')</code>" and "<code>#result('C'
+ 'int')</code>" are all valid</para>
+ </listitem>
+ <listitem>
+ <para><code>alias</code> - specifies both the SQL alias of the column
+ and the value key in the DataRow. If omitted, "column" value is
+ used.</para>
+ </listitem>
+ <listitem>
+ <para><code>dataRowKey</code> - needed if SQL 'alias' is not appropriate
+ as a DataRow key on the Cayenne side. One common case when this
+ happens is when a DataRow retrieved from a query is mapped using
+ joint prefetch keys (see below). In this case DataRow must use
+ database path expressions for joint column keys, and their format is
+ incompatible with most databases alias format. </para>
+ </listitem>
+ </itemizedlist></para>
+ <para>
+ <emphasis role="italic"
+ >Usage</emphasis>:<programlisting language="java">#result('NAME')
+#result('DATE_OF_BIRTH' 'java.util.Date')
+#result('DOB' 'java.util.Date' 'DATE_OF_BIRTH')
+#result('DOB' 'java.util.Date' '' 'artist.DATE_OF_BIRTH')
+#result('SALARY' 'float') </programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="sql">SELECT #result('ID' 'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date') FROM ARTIST</programlisting></para>
+ </section>
+ <section>
+ <title>#chain and #chunk</title>
+
+ <para><code>#chain</code> and <code>#chunk</code> directives are used for
+ conditional inclusion of SQL code. They are used together with
+ <code>#chain</code> wrapping multiple <code>#chunks</code>. A chunk
+ evaluates its parameter expression and if it is NULL suppresses rendering of the
+ enclosed SQL block. A chain renders its prefix and its chunks joined by the
+ operator. If all the chunks are suppressed, the chain itself is suppressed. This
+ allows to work with otherwise hard to script SQL semantics. E.g. a WHERE clause
+ can contain multiple conditions joined with AND or OR. Application code would
+ like to exclude a condition if its right-hand parameter is not present (similar
+ to Expression pruning discussed above). If all conditions are excluded, the
+ entire WHERE clause should be excluded. chain/chunk allows to do that.</para>
+ <para>
+ <emphasis role="italic"
+ >Semantics</emphasis>:<programlisting language="java">#chain(operator) ... #end
+#chain(operator prefix) ... #end
+#chunk() ... #end
+#chunk(param) ... #end </programlisting></para>
+ <para><emphasis role="italic">Full
+ example:</emphasis><programlisting language="java">#chain('OR' 'WHERE')
+ #chunk($name) NAME LIKE #bind($name) #end
+ #chunk($id) ARTIST_ID > #bind($id) #end
+#end" </programlisting></para>
+
+ </section>
+ </section>
+ <section>
+ <title>Mapping SQLTemplate Results</title>
+ <para>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. </para>
+ <para>By default SQLTemplate is expected to return a List of Persistent objects of its
+ root type. This is the simple
+ case:<programlisting language="java">SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");
+
+// List of Artists
+List<Artist> artists = context.performQuery(query);</programlisting>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:<programlisting language="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);
+
+// ensure we are fetching DataRows
+query.setFetchingDataRows(true);
+
+// List of DataRow
+List<DataRow> rows = context.performQuery(query);</programlisting>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.</para>
+ <para>In a more advanced case you may decide to fetch a list of scalars or a list of
+ Object[] 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 SQLTemplate to return results similar to EJBQLQuery, it is doable
+ using SQLResult as described
+ below:<programlisting language="java">SQLTemplate query = new SQLTemplate(Painting.class, "SELECT ESTIMATED_PRICE P FROM PAINTING");
+
+// let Cayenne know that result is a scalar
+SQLResult resultDescriptor = new SQLResult();
+resultDescriptor.addColumnResult("P");
+query.setResult(resultDescriptor);
+
+// List of BigDecimals
+List<BigDecimal> prices = context.performQuery(query); </programlisting><programlisting language="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);</programlisting></para>
+ <para>Another trick related to mapping result sets is making Cayenne recognize
+ prefetched entities in the result set. This emulates "joint" prefetching of
+ SelectQuery, and is achieved by special column naming. Columns belonging to the
+ "root" entity of the query should use unqualified names corresponding to the root
+ DbEntity columns. For each related entity column names must be prefixed with
+ relationship name and a dot (e.g. "toArtist.ID"). Column naming can be controlled
+ with "#result"
+ directive:<programlisting language="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);</programlisting></para>
+ <para>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
+ java.sql.ResultSet columns. </para>
+ <para>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 either all
+ lowercase or all uppercase column names. Here is the API that takes advantage of
+ that user knowledge and forces Cayenne to follow a given naming convention for the
+ DataRow keys (this is also available as a dropdown in the
+ Modeler):<programlisting language="java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
+query.setColumnNamesCapitalization(CapsStrategy.LOWER);
+List objects = context.performQuery(query);</programlisting></para>
+ <para>or<programlisting language="java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
+query.setColumnNamesCapitalization(CapsStrategy.UPPER);
+List objects = context.performQuery(query); </programlisting></para>
+ <para>None of this affects the generated SQL, but the resulting DataRows are using
+ correct capitalization. Note that you probably shouldn't bother with this unless you
+ are getting CayenneRuntimeExceptions when fetching with SQLTemplate.</para>
+ </section>
+ </section>
\ No newline at end of file