cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject svn commit: r1446263 - /cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml
Date Thu, 14 Feb 2013 16:20:08 GMT
Author: aadamchik
Date: Thu Feb 14 16:20:07 2013
New Revision: 1446263

docs: queries > EJBQLQuery, finishing SelectQuery, SQLTemplate


Modified: cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml
--- cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml (original)
+++ cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml Thu Feb 14 16:20:07
@@ -16,37 +16,272 @@
         "object" side in the "object-relational" divide). E.g. SelectQuery is assembled from
a Java
         class of the objects to fetch, a qualifier expression, orderings, etc. - all of this
         expressed in terms of the object model.</para>
-    <para>Native queries describe a desired DB operation as SQL code (SQLTemplate query)
or a reference
-        to a stored procedure (ProcedureQuery), etc. The results of native queries are usually
-        presented as Lists of Maps, with each map representing a row in the DB. They can
-        be converted to objects, however often it takes a considerable effort to do so. Native
-        queries are also less (if at all) portable across databases than object queries.
+    <para>Native queries describe a desired DB operation as SQL code (SQLTemplate query)
or a
+        reference to a stored procedure (ProcedureQuery), etc. The results of native queries
+        usually presented as Lists of Maps, with each map representing a row in the DB (a
term "data
+        row" is often used to describe such a map). They can potentially be converted to
+        however it may take a considerable effort to do so. Native queries are also less
(if at all)
+        portable across databases than object queries. </para>
     <section xml:id="selectquery">
-        <para>SelectQuery is the most commonly used query in user applications. It
returns a list of
-            persistent objects of a certain type specified in the
+        <para>SelectQuery is the most commonly used query in user applications. This
may be the only
+            query you will need in most appplications. It returns a list of persistent objects
of a
+            certain type specified in the
             query:<programlisting language="java">SelectQuery query = new SelectQuery(Artist.class);
 List&lt;Artist> objects = context.performQuery(query);</programlisting>This
             returned all rows in the "ARTIST" table. If the logs were turned on, you might
see the
             following SQL
             printed:<programlisting>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM
 INFO: === returned 5 row. - took 5 ms.</programlisting></para>
-        <para>This SQL was generated by Cayenne from the SelectQuery above. SelectQuery
can use a
+        <para>This SQL was generated by Cayenne from the SelectQuery above. SelectQuery
can have a
             qualifier to select only the data that you care about. Qualifier is simply an
             (Expressions where discussed in the previous chapter). If you only want artists
             name begins with 'Pablo', you might use the following qualifier expression:
-            <programlisting language="java">SelectQuery query = new SelectQuery(Artist.class,
ExpressionFactory.likeExp(Artist.NAME_PROPERTY, "Pablo%"));
+            <programlisting language="java">SelectQuery query = new SelectQuery(Artist.class,

+        ExpressionFactory.likeExp(Artist.NAME_PROPERTY, "Pablo%"));
 List&lt;Artist> objects = context.performQuery(query);</programlisting>The
             SQL will look different this
             time:<programlisting>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM
 [bind: 1->NAME:'Pablo%']
 INFO: === returned 1 row. - took 6 ms.</programlisting></para>
+        <para>SelectQuery allows to append parts of qualifier to
+            self:<programlisting>SelectQuery query = new SelectQuery(Artist.class);
+query.setQualifier(ExpressionFactory.likeExp(Artist.NAME_PROPERTY, "A%"));
+query.andQualifier(ExpressionFactory.greaterExp(Artist.DATE_OF_BIRTH_PROPERTY, someDate));</programlisting></para>
+        <para>To order the results of SelectQuery, one or more Orderings can be applied.
+            were already discussed earlier.
+            E.g.:<programlisting>SelectQuery query = new SelectQuery(Artist.class);
+// create Ordering object explicitly
+query.addOrdering(new Ordering(Artist.DATE_OF_BIRTH_PROPERTY, SortOrder.DESCENDING));
+// or let SelectQuery create it behind the scenes
+query.addOrdering(Artist.NAME_PROPERTY, SortOrder.ASCENDING);</programlisting></para>
+        <para>There's a number of other useful properties in SelectQuery 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 SelectQuery features.</para>
     <section xml:id="ejbqlquery">
+        <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>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>EJBQLQuery query = new EJBQLQuery("select
FROM Artist a");
+List&lt;String> names = context.performQuery(query);</programlisting>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 xml:id="sqltemplate">
-        <title>SQLTemplateQuery</title>
+        <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 custom directives used to customize SQLTemplate parsing
+                integrate with the JDBC layer:<table frame="void">
+                    <caption>cgen optional parameters</caption>
+                    <col width="29%"/>
+                    <col width="26%"/>
+                    <col width="45%"/>
+                    <thead>
+                        <tr>
+                            <th>Directive</th>
+                            <th>Usage</th>
+                            <th>Description</th>
+                        </tr>
+                    </thead>
+                    <tbody>
+                        <tr>
+                            <td><para><code>#bind(value)</code></para>
+                                <para><code>#bind(value jdbcTypeName)</code></para>
+                                <para><code>#bind(value jdbcTypeName scale)</code></para>
+                            </td>
+                            <td>
+                                <para><code>#bind($xyz)</code></para>
+                                <para><code>#bind('str')</code></para>
+                                <para><code>#bind($xyz 'VARCHAR')</code></para>
+                                <para><code>#bind($xyz 'DECIMAL' 2)</code></para>
+                            </td>
+                            <td>
+                                <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
+                                    this or other forms of #bind instead of inserting them
+                                    inline.</para>
+                                <para>A <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
+                                    useful for instance to build IN conditions. </para>
+                                <para>The second parameter - <code>jdbcTypeName</code>
- is a JDBC
+                                    data type of the parameter, as defined in
+                                        <code>java.sql.Types</code>. </para>
+                                <para>A full query example may look like this:  </para>
+                                <para><code>update ARTIST set NAME = #bind($name)
where ID =
+                                        #bind($id)</code></para>
+                            </td>
+                        </tr>
+                        <tr>
+                            <td>
+                                <para><code>#bindEqual(value)</code></para>
+                                <para><code>#bindEqual(value jdbcTypeName)</code></para>
+                                <para><code>#bindEqual(value jdbcTypeName scale)</code></para>
+                            </td>
+                            <td>
+                                <para><code>#bindEqual($xyz)</code></para>
+                                <para><code>#bindEqual('str')</code></para>
+                                <para><code>#bindEqual($xyz 'VARCHAR')</code></para>
+                                <para><code>#bindEqual($xyz 'DECIMAL' 2)</code></para>
+                            </td>
+                            <td>
+                                <para>Same as <code>#bind</code>, but also
includes the "=" sign in
+                                    front of the value binding. E.g.: </para>
+                                <para><code>update ARTIST set NAME = #bind($name)
where ID
+                                        #bindEqual($id)</code></para>
+                                <para>Here we took the previous example and replaced
"ID =
+                                    #bind(..)" with "ID #bindEqual(..)". While it looks like
+                                    clumsy shortcut to eliminate the equal sign, the actual
+                                    why this is useful is that it allows the value to be
+                                    the value is not null, "= ?" is generated, but if it
is, the
+                                    resulting chunk of the SQL would look like "IS NULL"
and will be
+                                    compilant with what the DB expects.</para>
+                            </td>
+                        </tr>
+                        <tr>
+                            <td>
+                                <para><code>#bindNotEqual(value)</code></para>
+                                <para><code>#bindNotEqual(value jdbcTypeName)</code></para>
+                                <para><code>#bindNotEqual(value jdbcTypeName
+                            </td>
+                            <td>
+                                <para><code>#bindNotEqual($xyz)</code></para>
+                                <para><code>#bindNotEqual('str')</code></para>
+                                <para><code>#bindNotEqual($xyz 'VARCHAR')</code></para>
+                                <para><code>#bindNotEqual($xyz 'DECIMAL' 2)</code></para>
+                            </td>
+                            <td>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).</td>
+                        </tr>
+                    </tbody>
+                </table></para>
+        </section>
     <section xml:id="procedurequery">

View raw message