You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by aa...@apache.org on 2013/02/14 17:20:08 UTC
svn commit: r1446263 -
/cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml
Author: aadamchik
Date: Thu Feb 14 16:20:07 2013
New Revision: 1446263
URL: http://svn.apache.org/r1446263
Log:
docs: queries > EJBQLQuery, finishing SelectQuery, SQLTemplate
Modified:
cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml
Modified: cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml
URL: http://svn.apache.org/viewvc/cayenne/main/trunk/docs/docbook/cayenne-guide/src/docbkx/queries.xml?rev=1446263&r1=1446262&r2=1446263&view=diff
==============================================================================
--- 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 2013
@@ -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 potentially
- 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>
+ <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 (a term "data
+ row" is often used to describe such a map). They can potentially be converted to objects,
+ however it may take a considerable effort to do so. Native queries are also less (if at all)
+ portable across databases than object queries. </para>
<section xml:id="selectquery">
<title>SelectQuery</title>
- <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<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 ARTIST t0
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 Expression
(Expressions where discussed in the previous chapter). If you only want artists whose
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<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 ARTIST t0 WHERE t0.NAME LIKE ?
[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. Ordering
+ 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 and
+ performance optimization. Others are fairly self-explanatory. Please check the API docs
+ for the full extent of the SelectQuery 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 from
+ 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, there
+ are also DELETE and UPDATE varieties available. <note>
+ <para>As of this version of Cayenne, DELETE and UPDATE do not change the state of
+ objects in the ObjectContext. They are run directly against the database
+ instead. </para>
+ </note><programlisting language="java">EJBQLQuery select = new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
+List<Artist> artists = context.performQuery(select);</programlisting><programlisting language="java">EJBQLQuery delete = new EJBQLQuery("delete from Painting");
+context.performGenericQuery(delete);</programlisting><programlisting language="java">EJBQLQuery update = new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
+context.performGenericQuery(update);</programlisting>In
+ most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides you
+ with better compile-time checks. However sometimes you may want a completely scriptable
+ object query. This is when you might prefer EJBQL. A more practical reason for picking
+ EJBQL over SelectQuery though is that the former offers some extra selecting
+ capabilities, namely aggregate functions and
+ subqueries:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
+List<Object[]> result = context.performQuery(query);
+for(Object[] artistWithCount : result) {
+ Artist a = (Artist) artistWithCount[0];
+ int hasPaintings = (Integer) artistWithCount[1];
+}</programlisting>This
+ also demonstrates a previously unseen type of select result - a List of Object[]
+ elements, where each entry in an Object[] is either a DataObject or a scalar, depending
+ on the query SELECT clause. A result can also be a list of
+ scalars:<programlisting>EJBQLQuery query = new EJBQLQuery("select a.name FROM Artist a");
+List<String> names = context.performQuery(query);</programlisting>While
+ Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE
+ clause, and indeed they are very close, there are a few noteable differences:<itemizedlist>
+ <listitem>
+ <para>Null handling: SelectQuery would translate the expressions matching NULL
+ values to the corresponding "X IS NULL" or "X IS NOT NULL" SQL syntax.
+ EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL")
+ syntax to be used, otherwise the generated SQL will look like "X = NULL" (or
+ "X <> NULL"), which will evaluate differently.</para>
+ </listitem>
+ <listitem>
+ <para>Expression Parameters: SelectQuery uses "$" to denote named parameters
+ (e.g. "$myParam"), while EJBQL uses ":" (e.g. ":myParam"). Also EJBQL
+ supports positional parameters denoted by the question mark: "?3".</para>
+ </listitem>
+ </itemizedlist></para>
</section>
<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. It
+ comes handy when the standard ORM concepts are not sufficient for a given query or an
+ update. SQL is too powerful and allows to manipulate data in ways that are not easily
+ described as a graph of related entities. Cayenne acknowledges this fact and provides
+ this facility to execute SQL, mapping the result to objects when possible. Here are
+ examples of selecting and non-selecting
+ SQLTemplates:<programlisting language="java">SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST");
+List<Artist> result = context.performQuery(select);</programlisting><programlisting language="java">SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST");
+QueryResponse response = context.performGenericQuery(update);</programlisting></para>
+ <para>Cayenne doesn't make any attempt to make sense of the SQL semantics, so it doesn't
+ know whether a given query is performing a select or update, etc. It is the the user's
+ decision to run a given query as a selecting or "generic".</para>
+ <para>
+ <note>
+ <para>Any data modifications done to DB as a result of SQLTemplate execution do not
+ change the state of objects in the ObjectContext. So some objects in the context
+ may become stale as a result.</para>
+ </note>
+ </para>
+ <para>Another point to note is that the first argument to the SQLTemplate constructor - the
+ Java class - has the same meaning as in SelectQuery only when the result can be
+ converted to objects (e.g. when this is a selecting query and it is selecting all
+ columns from one table). In this case it denotes the "root" entity of this query result.
+ If the query does not denote a single entity result, this argument is only used for
+ query routing, i.e. determining which database it should be run against. You are free to
+ use any persistent class or even a DataMap instance in such situation. It will work as
+ long as the passed "root" maps to the same database as the current query.</para>
+ <para>To achieve interoperability between mutliple RDBMS a user can specify multiple SQL
+ statements for the same SQLTemplate, each corresponding to a native SQL dialect. A key
+ used to look up the right dialect during execution is a fully qualified class name of
+ the corresponding DbAdapter. If no DB-specific statement is present for a given DB, a
+ default generic statement is used. E.g. in all the examples above a default statement
+ will be used regardless of the runtime database. So in most cases you won't need to
+ explicitly "translate" your SQL to all possible dialects. Here is how this works in
+ practice:<programlisting language="java">SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST");
+
+// For Postgres it would be nice to trim padding of all CHAR columns.
+// Otherwise those will be returned with whitespace on the right.
+// assuming "NAME" is defined as CHAR...
+String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST";
+query.setTemplate(PostgresAdapter.class.getName(), pgSQL);</programlisting></para>
+
+ <section xml:id="sqltemplate-scripting">
+ <title>Scripting SQLTemplate with Velocity</title>
+ <para>The most interesting aspect of SQLTemplate (and the reason why it is called a
+ "template") is that a SQL string is treated by Cayenne as an Apache Velocity
+ template. Before sending it to DB as a PreparedStatement, the String is evaluated in
+ the Velocity context, that does variable substitutions, and performs special
+ callbacks in response to various directives, thus controlling query interaction with
+ the JDBC layer. </para>
+ <para>Check Velocity docs for the syntax details. Here we'll just mention the two main
+ scripting elements - "variables" (that look like <code>$var</code>) and "directives"
+ (that look like <code>#directive(p1 p2 p3)</code>). All built-in Velocity directives
+ are supported. Additionally Cayenne defines a number of its own directives to bind
+ parameters to PreparedStatements and to control the structure of the ResultSet.
+ These directives are described in the following sections.</para>
+ </section>
+ <section xml:id="sqltemplate-parameters">
+ <title>Variable Substitution</title>
+ <para>All variables in the template string are replaced from query
+ parameters:<programlisting language="java">SQLTemplate query = new SQLTemplate(Artist.class, "delete from $tableName");
+query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING"));
+
+// this will generate SQL like this: "delete from mydb.PAINTING"</programlisting>The
+ example above demonstrates the point made earlier in this chapter - even if we don't
+ know upfront which table the query will run against, we can still use a fixed "root"
+ in constructor (<code>Artist.class</code> in this case) , as we are not planning on
+ converting the result to objects.</para>
+ <para>Variable substitution within the text uses "<code>object.toString()</code>" method to replace the
+ variable value. Keep in mind that this may not be appropriate in all situations.
+ E.g. passing a date object in a WHERE clause expression may be converted to a String
+ not understood by the target RDBMS SQL parser. In such cases variable should be wrapped in <code>#bind</code>
+ directive as described below.</para>
+ </section>
+ <section xml:id="sqltemplate-bind-directive">
+ <title>Directives</title>
+ <para>These are the custom directives used to customize SQLTemplate parsing and
+ 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 via
+ 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 is
+ 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 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, "= ?" 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 scale)</code></para>
+ </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>
<section xml:id="procedurequery">
<title>ProcedureQuery</title>