cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ntimof...@apache.org
Subject [2/2] cayenne git commit: CYA_2173 Update fluent query API documentation
Date Fri, 01 Sep 2017 11:20:19 GMT
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 <stariy95@gmail.com>
Authored: Fri Sep 1 14:17:58 2017 +0300
Committer: Nikita Timofeev <stariy95@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&lt;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&lt;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&lt;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 &lt;> 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&lt;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&lt;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&lt;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&lt;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&lt;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&lt;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) &lt; ?
+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&lt;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&lt;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&lt;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&lt;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&lt;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&lt;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


Mime
View raw message