cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject [01/10] cayenne git commit: CYA_2173 Update fluent query API documentation
Date Tue, 26 Sep 2017 08:20:22 GMT
Repository: cayenne
Updated Branches:
  refs/heads/STABLE-4.0 25f3ba07e -> d5a181fff
diff --git a/docs/docbook/cayenne-guide/src/docbkx/queries.xml b/docs/docbook/cayenne-guide/src/docbkx/queries.xml
index 37254d4..28b2993 100644
--- a/docs/docbook/cayenne-guide/src/docbkx/queries.xml
+++ b/docs/docbook/cayenne-guide/src/docbkx/queries.xml
@@ -14,7 +14,7 @@
     the specific language governing permissions and limitations under the
-<chapter xmlns="" xmlns:xlink=""
+<chapter xmlns="" xmlns:xi=""
     version="5.0" xml:id="queries">
     <para>Queries are Java objects used by the application to communicate with the
database. Cayenne
@@ -38,622 +38,18 @@
         "data row" is often used to describe such a map). They can potentially be converted
         objects, however it may take a considerable effort to do so. Native queries are also
         (if at all) portable across databases than object queries. </para>
-    <section xml:id="selectquery">
-        <title>ObjectSelect</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 =
-            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
-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
-            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("Pablo%"))
-        .select(context);</programlisting>The
-            SQL will look different this
-            time:<screen>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST
-[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("A%"))
-        .and(
-        .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
-            performance optimization. Others are fairly self-explanatory. Please check the
API docs
-            for the full extent of the ObjectSelect features.</para>
-    </section>
-    <section 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
-            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,
-            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 = '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 = 'P2' WHERE = 'P1'");
-            most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides
-            with better compile-time checks. However sometimes you may want a completely
-            object query. This is when you might prefer EJBQL. A more practical reason for
-            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];
-            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,
-            on the query SELECT clause. A result can also be a list of
-            scalars:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select FROM Artist a");
-List&lt;String> names = context.performQuery(query);</programlisting>
-            EJBQLQuery supports an "IN" clause with three different usage-patterns.  The
-            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.
-            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
-            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
-                        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
-                        (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>
-    <section xml:id="sqltemplate">
-        <title>SQLTemplate</title>
-        <para>SQLTemplate is a query that allows to run native SQL from a Cayenne application.
-            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
-            described as a graph of related entities. Cayenne acknowledges this fact and
-            this facility to execute SQL, mapping the result to objects when possible. Here
-            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
-            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
-            converted to objects (e.g. when this is a selecting query and it is selecting
-            columns from one table). In this case it denotes the "root" entity of this query
-            If the query does not denote a single entity result, this argument is only used
-            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
-            explicitly "translate" your SQL to all possible dialects. Here is how this works
-            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...
-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
-                the Velocity context, that does variable substitutions, and performs special
-                callbacks in response to various directives, thus controlling query interaction
-                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
-                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
-                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>
-                    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
-                    this or other forms of <code>#bind</code> instead of inserting
-                    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
-                                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($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($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>
-                    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($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
-                    correctly handle null object. Also notice how we are specifying a Velocity
-                    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
-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' )
-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
-                    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
-                                types, primitives, String, SQL dates, BigDecimal and BigInteger.
-                                    "<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
-                                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
-                                happens is when a DataRow retrieved from a query is mapped
-                                joint prefetch keys (see below). In this case DataRow must
-                                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
-            </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.
-                    allows to work with otherwise hard to script SQL semantics. E.g. a WHERE
-                    can contain multiple conditions joined with AND or OR. Application code
-                    like to exclude a condition if its right-hand parameter is not present
-                    to Expression pruning discussed above). If all conditions are excluded,
-                    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'
-	#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
-                desired result structure. This section presents various tips and tricks dealing
-                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,
-// 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
-                object structure is opaque to
-                Cayenne:<programlisting language="java">String sql = "SELECT t0.NAME,
-SQLTemplate query = new SQLTemplate(Artist.class, sql);
-// ensure we are fetching DataRows
-// 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).
-                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
-                using SQLResult as described
-                below:<programlisting language="java">SQLTemplate query = new SQLTemplate(Painting.class,
-// let Cayenne know that result is a scalar
-SQLResult resultDescriptor = new SQLResult();
-// 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,
-      "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();
-// 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
-                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);
-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
-                    FROM ...</code>" can sometimes result in Cayenne exceptions on
attempts to
-                convert fetched DataRows to objects. Essentially any query that is not using
-                    <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('column2'), ..</code>". However this quickly becomes impractical
-                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
-                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
-List objects = context.performQuery(query);</programlisting></para>
-            <para>or<programlisting language="java">SQLTemplate query = new SQLTemplate("SELECT
-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>
-    <section 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,
-            result sets, some data modification (returned as an update count), or a combination
-            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",
-// 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
-            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
-            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);
-        <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
-            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>
-    <section 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
-            - 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></para>
-    </section>
-    <section 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
-            }
-        };
-    }
-        <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
-    }
-        <para>In fact many internal Cayenne queries are IndirectQueries, delegating
to SelectQuery
-            or SQLTemplate after some preprocessing.</para>
-    </section>
+    <xi:include href="queries-select.xml"/>
+    <xi:include href="queries-ejbql.xml"/>
+    <xi:include href="queries-selectbyid.xml"/>
+    <xi:include href="queries-sqlselect.xml"/>
+    <xi:include href="queries-mapped.xml"/>
+    <xi:include href="queries-procedurecall.xml"/>
+    <xi:include href="queries-custom.xml"/>
+    <xi:include href="queries-sqltemplate.xml"/>
+    <!-- ProcedureQuery is not deprecated as of 4.0 but newer ProcedureCall is preferred
+    <!--<xi:include href="queries-procedure.xml"/>-->
+    <!-- NamedQuery deprecated since 4.0 -->
+    <!--<xi:include href="queries-namedquery.xml"/>-->

View raw message