Repository: cayenne Updated Branches: refs/heads/master 62ba8fb28 -> d166a6abd
http://git-wip-us.apache.org/repos/asf/cayenne/blob/d166a6ab/docs/docbook/cayenne-guide/src/docbkx/queries.xml ---------------------------------------------------------------------- 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 License. --> -<chapter xmlns="http://docbook.org/ns/docbook"; xmlns:xlink="http://www.w3.org/1999/xlink"; +<chapter xmlns="http://docbook.org/ns/docbook"; xmlns:xi="http://www.w3.org/2001/XInclude"; version="5.0" xml:id="queries"> <title>Queries</title> <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 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>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<Artist> objects = ObjectSelect.query(Artist.class).select(context);</programlisting>This - returned all rows in the "ARTIST" table. If the logs were turned on, you might see the - following SQL - printed:<screen>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 -INFO: === returned 5 row. - took 5 ms.</screen></para> - <para>This SQL was generated by Cayenne from the ObjectSelect above. ObjectSelect can have a - qualifier to select only the data matching specific criteria. Qualifier is simply an - Expression (Expressions where discussed in the previous chapter), appended to the query - using "where" method. If you only want artists whose name begins with 'Pablo', you might - use the following qualifier expression: - <programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class) - .where(Artist.NAME.like("Pablo%")) - .select(context);</programlisting>The - SQL will look different this - time:<screen>INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 WHERE t0.NAME LIKE ? -[bind: 1->NAME:'Pablo%'] -INFO: === returned 1 row. - took 6 ms.</screen></para> - <para>ObjectSelect allows to assemble qualifier from parts, using "and" and "or" method to - chain then - together:<programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class) - .where(Artist.NAME.like("A%")) - .and(Artist.DATE_OF_BIRTH.gt(someDate) - .select(context);</programlisting></para> - <para>To order the results of ObjectSelect, one or more orderings can be - applied:<programlisting language="java">List<Artist> objects = ObjectSelect.query(Artist.class) - .addOrderBy(Artist.DATE_OF_BIRTH.desc()) - .addOrderBy(Artist.NAME.asc()) - .select(context);</programlisting></para> - <para>There's a number of other useful methods in ObjectSelect that define what to select - and how to optimize database interaction (prefetching, caching, fetch offset and limit, - pagination, etc.). Some of them are discussed in separate chapters on caching and - performance optimization. Others are fairly self-explanatory. Please check the API docs - for the full extent of the ObjectSelect features.</para> - </section> - <section xml:id="ejbqlquery"> - <title>EJBQLQuery</title> - <para>EJBQLQuery was created as a part of an experiment in adopting some of Java Persistence - API (JPA) approaches in Cayenne. It is a parameterized object query that is created from - query String. A String used to build EJBQLQuery must conform to JPQL (JPA query - language):<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");</programlisting></para> - <para>JPQL details can be found in any JPA manual. Here we'll mention only how this fits - into Cayenne and what are the differences between EJBQL and other Cayenne - queries.</para> - <para>Although most frequently EJBQLQuery is used as an alternative to SelectQuery, there - are also DELETE and UPDATE varieties available. <note> - <para>As of this version of Cayenne, DELETE and UPDATE do not change the state of - objects in the ObjectContext. They are run directly against the database - instead. </para> - </note><programlisting language="java">EJBQLQuery select = new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'"); -List<Artist> artists = context.performQuery(select);</programlisting><programlisting language="java">EJBQLQuery delete = new EJBQLQuery("delete from Painting"); -context.performGenericQuery(delete);</programlisting><programlisting language="java">EJBQLQuery update = new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'"); -context.performGenericQuery(update);</programlisting>In - most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides you - with better compile-time checks. However sometimes you may want a completely scriptable - object query. This is when you might prefer EJBQL. A more practical reason for picking - EJBQL over SelectQuery though is that the former offers some extra selecting - capabilities, namely aggregate functions and - subqueries:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a"); -List<Object[]> result = context.performQuery(query); -for(Object[] artistWithCount : result) { - Artist a = (Artist) artistWithCount[0]; - int hasPaintings = (Integer) artistWithCount[1]; -}</programlisting>This - also demonstrates a previously unseen type of select result - a List of Object[] - elements, where each entry in an Object[] is either a DataObject or a scalar, depending - on the query SELECT clause. A result can also be a list of - scalars:<programlisting language="java">EJBQLQuery query = new EJBQLQuery("select a.name FROM Artist a"); -List<String> names = context.performQuery(query);</programlisting> - EJBQLQuery supports an "IN" clause with three different usage-patterns. The following - example would require three individual positional parameters (named - parameters could also have been used) to be supplied. - - <programlisting language="sql">select p from Painting p where p.paintingTitle in (?1,?2,?3)</programlisting> - - The following example requires a single positional parameter to be supplied. The - parameter can be any concrete implementation of the java.util.Collection interface such as - java.util.List or java.util.Set. - - <programlisting language="sql">select p from Painting p where p.paintingTitle in ?1</programlisting> - - The following example is functionally identical to the one prior. - - <programlisting language="sql">select p from Painting p where p.paintingTitle in (?1)</programlisting> - - </para> - <para> - It is <link linkend="expressions-to-ejbql">possible to convert</link> - an <link linkend="expressions">Expression</link> - object used with a <link linkend="selectquery">SelectQuery</link> - to EJBQL. Use the Expression#appendAsEJBQL methods for this purpose. - </para> - <para> - While - Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE - clause, and indeed they are very close, there are a few noteable differences: - <itemizedlist> - <listitem> - <para>Null handling: SelectQuery would translate the expressions matching NULL - values to the corresponding "X IS NULL" or "X IS NOT NULL" SQL syntax. - EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL") - syntax to be used, otherwise the generated SQL will look like "X = NULL" (or - "X <> NULL"), which will evaluate differently.</para> - </listitem> - <listitem> - <para>Expression Parameters: SelectQuery uses "$" to denote named parameters - (e.g. "$myParam"), while EJBQL uses ":" (e.g. ":myParam"). Also EJBQL - supports positional parameters denoted by the question mark: "?3".</para> - </listitem> - </itemizedlist> - </para> - </section> - <section xml:id="sqltemplate"> - <title>SQLTemplate</title> - <para>SQLTemplate is a query that allows to run native SQL from a Cayenne application. It - comes handy when the standard ORM concepts are not sufficient for a given query or an - update. SQL is too powerful and allows to manipulate data in ways that are not easily - described as a graph of related entities. Cayenne acknowledges this fact and provides - this facility to execute SQL, mapping the result to objects when possible. Here are - examples of selecting and non-selecting - SQLTemplates:<programlisting language="java">SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST"); -List<Artist> result = context.performQuery(select);</programlisting><programlisting language="java">SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST"); -QueryResponse response = context.performGenericQuery(update);</programlisting></para> - <para>Cayenne doesn't make any attempt to make sense of the SQL semantics, so it doesn't - know whether a given query is performing a select or update, etc. It is the the user's - decision to run a given query as a selecting or "generic".</para> - <para> - <note> - <para>Any data modifications done to DB as a result of SQLTemplate execution do not - change the state of objects in the ObjectContext. So some objects in the context - may become stale as a result.</para> - </note> - </para> - <para>Another point to note is that the first argument to the SQLTemplate constructor - the - Java class - has the same meaning as in SelectQuery only when the result can be - converted to objects (e.g. when this is a selecting query and it is selecting all - columns from one table). In this case it denotes the "root" entity of this query result. - If the query does not denote a single entity result, this argument is only used for - query routing, i.e. determining which database it should be run against. You are free to - use any persistent class or even a DataMap instance in such situation. It will work as - long as the passed "root" maps to the same database as the current query.</para> - <para>To achieve interoperability between mutliple RDBMS a user can specify multiple SQL - statements for the same SQLTemplate, each corresponding to a native SQL dialect. A key - used to look up the right dialect during execution is a fully qualified class name of - the corresponding DbAdapter. If no DB-specific statement is present for a given DB, a - default generic statement is used. E.g. in all the examples above a default statement - will be used regardless of the runtime database. So in most cases you won't need to - explicitly "translate" your SQL to all possible dialects. Here is how this works in - practice:<programlisting language="java">SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST"); - -// For Postgres it would be nice to trim padding of all CHAR columns. -// Otherwise those will be returned with whitespace on the right. -// assuming "NAME" is defined as CHAR... -String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST"; -query.setTemplate(PostgresAdapter.class.getName(), pgSQL);</programlisting></para> - - <section xml:id="sqltemplate-scripting"> - <title>Scripting SQLTemplate with Velocity</title> - <para>The most interesting aspect of SQLTemplate (and the reason why it is called a - "template") is that a SQL string is treated by Cayenne as an Apache Velocity - template. Before sending it to DB as a PreparedStatement, the String is evaluated in - the Velocity context, that does variable substitutions, and performs special - callbacks in response to various directives, thus controlling query interaction with - the JDBC layer. </para> - <para>Check Velocity docs for the syntax details. Here we'll just mention the two main - scripting elements - "variables" (that look like <code>$var</code>) and "directives" - (that look like <code>#directive(p1 p2 p3)</code>). All built-in Velocity directives - are supported. Additionally Cayenne defines a number of its own directives to bind - parameters to PreparedStatements and to control the structure of the ResultSet. - These directives are described in the following sections.</para> - </section> - <section xml:id="sqltemplate-parameters"> - <title>Variable Substitution</title> - <para>All variables in the template string are replaced from query - parameters:<programlisting language="java">SQLTemplate query = new SQLTemplate(Artist.class, "delete from $tableName"); -query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING")); - -// this will generate SQL like this: "delete from mydb.PAINTING"</programlisting>The - example above demonstrates the point made earlier in this chapter - even if we don't - know upfront which table the query will run against, we can still use a fixed "root" - in constructor (<code>Artist.class</code> in this case) , as we are not planning on - converting the result to objects.</para> - <para>Variable substitution within the text uses "<code>object.toString()</code>" method to replace the - variable value. Keep in mind that this may not be appropriate in all situations. - E.g. passing a date object in a WHERE clause expression may be converted to a String - not understood by the target RDBMS SQL parser. In such cases variable should be wrapped in <code>#bind</code> - directive as described below.</para> - </section> - <section xml:id="sqltemplate-bind-directive"> - <title>Directives</title> - <para>These are the Cayenne directives used to customize SQLTemplate parsing and - integrate it with the JDBC layer: </para> - <section> - <title>#bind</title> - <para>Creates a PreparedStatement positional parameter in place of the directive, - binding the value to it before statement execution. <code>#bind</code> is - allowed in places where a "?" would be allowed in a PreparedStatement. And in - such places it almost always makes sense to pass objects to the template via - this or other forms of <code>#bind</code> instead of inserting them - inline.</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#bind(value) -#bind(value jdbcType) -#bind(value jdbcType scale)</programlisting> - <para><emphasis role="italic">Arguments:</emphasis> - <itemizedlist> - <listitem> - <para><code>value</code> - can either be a char constant or a variable - that is resolved from the query parameters. Note that the variable - can be a collection, that will be automatically expanded into a list - of individual value bindings. This is useful for instance to build - IN conditions. </para> - </listitem> - <listitem> - <para><code>jdbcType</code> - is a JDBC data type of the parameter as - defined in <code>java.sql.Types</code>.</para> - </listitem> - <listitem> - <para><code>scale</code> - An optional scale of the numeric value. Same - as "scale" in PreparedStatement.</para> - </listitem> - </itemizedlist></para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#bind($xyz) -#bind('str') -#bind($xyz 'VARCHAR') -#bind($xyz 'DECIMAL' 2)</programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID = #bind($id)</programlisting></para> - </section> - <section> - <title>#bindEqual</title> - <para>Same as #bind, but also includes the "=" sign in front of the value binding. - Look at the example below - we took the #bind example and replaced "<code>ID = - #bind(..)</code>" with "<code>ID #bindEqual(..)</code>". While it looks like - a clumsy shortcut to eliminate the equal sign, the actual reason why this is - useful is that it allows the value to be null. If the value is not null, - "<code>= ?</code>" is generated, but if it is, the resulting chunk of the - SQL would look like "<code>IS NULL</code>" and will be compilant with what the - DB expects.</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#bindEqual(value) -#bindEqual(value jdbcType) -#bindEqual(value jdbcType scale)</programlisting> - <para><emphasis role="italic">Arguments: (same as #bind)</emphasis> - </para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#bindEqual($xyz) -#bindEqual('str') -#bindEqual($xyz 'VARCHAR') -#bindEqual($xyz 'DECIMAL' 2)</programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID #bindEqual($id)</programlisting></para> - </section> - <section> - <title>#bindNotEqual</title> - <para>This directive deals with the same issue as <code>#bindEqual</code> above, - only it generates "not equal" in front of the value (or IS NOT NULL).</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#bindNotEqual(value) -#bindNotEqual(value jdbcType) -#bindNotEqual(value jdbcType scale)</programlisting> - <para><emphasis role="italic">Arguments: (same as #bind)</emphasis></para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#bindNotEqual($xyz) -#bindNotEqual('str') -#bindNotEqual($xyz 'VARCHAR') -#bindNotEqual($xyz 'DECIMAL' 2)</programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="sql">update ARTIST set NAME = #bind($name) where ID #bindEqual($id)</programlisting></para> - </section> - <section> - <title>#bindObjectEqual</title> - <para>It can be tricky to use a Persistent object or an ObjectId in a binding, - especially for tables with compound primary keys. This directive helps to handle - such binding. It maps columns in the query to the names of Persistent object ID - columns, extracts ID values from the object, and generates SQL like "COL1 = ? - AND COL2 = ? ..." , binding positional parameters to ID values. It can also - correctly handle null object. Also notice how we are specifying a Velocity array - for multi-column PK.</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#bindObjectEqual(value columns idColumns)</programlisting> - <para><emphasis role="italic">Arguments:</emphasis> - <itemizedlist> - <listitem> - <para><code>value</code> - must be a variable that is resolved from the - query parameters to a Persistent or ObjectId.</para> - </listitem> - <listitem> - <para><code>columns</code> - the names of the columns to generate in the - SQL.</para> - </listitem> - <listitem> - <para><code>idColumn</code> - the names of the ID columns for a given - entity. Must match the order of "columns" to match against.</para> - </listitem> - </itemizedlist></para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#bindObjectEqual($a 't0.ID' 'ID') -#bindObjectEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])</programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="java">String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID"; -SQLTemplate select = new SQLTemplate(Artist.class, sql); - -Artist a = .... -select.setParameters(Collections.singletonMap("a", a)); </programlisting></para> - </section> - <section> - <title>#bindObjectNotEqual</title> - <para>Same as #bindObjectEqual above, only generates "not equal" operator for value - comparison (or IS NOT NULL).</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#bindObjectNotEqual(value columns idColumns)</programlisting> - <para><emphasis role="italic">Arguments: (same as #bindObjectEqual)</emphasis> - </para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#bindObjectNotEqual($a 't0.ID' 'ID') -#bindObjectNotEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])</programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="java">String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID"; -SQLTemplate select = new SQLTemplate(Artist.class, sql); - -Artist a = .... -select.setParameters(Collections.singletonMap("a", a)); </programlisting></para> - </section> - <section> - <title>#result</title> - <para>Renders a column in SELECT clause of a query and maps it to a key in the - result DataRow. Also ensures the value read is of the correct type. This allows - to create a DataRow (and ultimately - a persistent object) from an arbitrary - ResultSet.</para> - <para><emphasis role="italic">Semantics:</emphasis></para> - <programlisting language="java">#result(column) -#result(column javaType) -#result(column javaType alias) -#result(column javaType alias dataRowKey)</programlisting> - <para><emphasis role="italic">Arguments:</emphasis> - <itemizedlist> - <listitem> - <para><code>column</code> - the name of the column to render in SQL - SELECT clause.</para> - </listitem> - <listitem> - <para><code>javaType</code> - a fully-qualified Java class name for a - given result column. For simplicity most common Java types used in - JDBC can be specified without a package. These include all numeric - types, primitives, String, SQL dates, BigDecimal and BigInteger. So - "<code>#result('A' 'String')</code>", "<code>#result('B' - 'java.lang.String')</code>" and "<code>#result('C' - 'int')</code>" are all valid</para> - </listitem> - <listitem> - <para><code>alias</code> - specifies both the SQL alias of the column - and the value key in the DataRow. If omitted, "column" value is - used.</para> - </listitem> - <listitem> - <para><code>dataRowKey</code> - needed if SQL 'alias' is not appropriate - as a DataRow key on the Cayenne side. One common case when this - happens is when a DataRow retrieved from a query is mapped using - joint prefetch keys (see below). In this case DataRow must use - database path expressions for joint column keys, and their format is - incompatible with most databases alias format. </para> - </listitem> - </itemizedlist></para> - <para> - <emphasis role="italic" - >Usage</emphasis>:<programlisting language="java">#result('NAME') -#result('DATE_OF_BIRTH' 'java.util.Date') -#result('DOB' 'java.util.Date' 'DATE_OF_BIRTH') -#result('DOB' 'java.util.Date' '' 'artist.DATE_OF_BIRTH') -#result('SALARY' 'float') </programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="sql">SELECT #result('ID' 'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date') FROM ARTIST</programlisting></para> - </section> - <section> - <title>#chain and #chunk</title> - - <para><code>#chain</code> and <code>#chunk</code> directives are used for - conditional inclusion of SQL code. They are used together with - <code>#chain</code> wrapping multiple <code>#chunks</code>. A chunk - evaluates its parameter expression and if it is NULL suppresses rendering of the - enclosed SQL block. A chain renders its prefix and its chunks joined by the - operator. If all the chunks are suppressed, the chain itself is suppressed. This - allows to work with otherwise hard to script SQL semantics. E.g. a WHERE clause - can contain multiple conditions joined with AND or OR. Application code would - like to exclude a condition if its right-hand parameter is not present (similar - to Expression pruning discussed above). If all conditions are excluded, the - entire WHERE clause should be excluded. chain/chunk allows to do that.</para> - <para> - <emphasis role="italic" - >Semantics</emphasis>:<programlisting language="java">#chain(operator) ... #end -#chain(operator prefix) ... #end -#chunk() ... #end -#chunk(param) ... #end </programlisting></para> - <para><emphasis role="italic">Full - example:</emphasis><programlisting language="java">#chain('OR' 'WHERE') - #chunk($name) NAME LIKE #bind($name) #end - #chunk($id) ARTIST_ID > #bind($id) #end -#end" </programlisting></para> - - </section> - </section> - <section> - <title>Mapping SQLTemplate Results</title> - <para>Here we'll discuss how to convert the data selected via SQLTemplate to some - useable format, compatible with other query results. It can either be very simple or - very complex, depending on the structure of the SQL, JDBC driver nature and the - desired result structure. This section presents various tips and tricks dealing with - result mapping. </para> - <para>By default SQLTemplate is expected to return a List of Persistent objects of its - root type. This is the simple - case:<programlisting language="java">SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST"); - -// List of Artists -List<Artist> artists = context.performQuery(query);</programlisting>Just - like SelectQuery, SQLTemplate can fetch DataRows. In fact DataRows option is very - useful with SQLTemplate, as the result type most often than not does not represent a - Cayenne entity, but instead may be some aggregated report or any other data whose - object structure is opaque to - Cayenne:<programlisting language="java">String sql = "SELECT t0.NAME, COUNT(1) FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) " - + "GROUP BY t0.NAME ORDER BY COUNT(1)"; -SQLTemplate query = new SQLTemplate(Artist.class, sql); - -// ensure we are fetching DataRows -query.setFetchingDataRows(true); - -// List of DataRow -List<DataRow> rows = context.performQuery(query);</programlisting>In - the example above, even though the query root is Artist. the result is a list of - artist names with painting counts (as mentioned before in such case "root" is only - used to find the DB to fetch against, but has no bearning on the result). The - DataRows here are the most appropriate and desired result type.</para> - <para>In a more advanced case you may decide to fetch a list of scalars or a list of - Object[] with each array entry being either an entity or a scalar. You probably - won't be doing this too often and it requires quite a lot of work to setup, but if - you want your SQLTemplate to return results similar to EJBQLQuery, it is doable - using SQLResult as described - below:<programlisting language="java">SQLTemplate query = new SQLTemplate(Painting.class, "SELECT ESTIMATED_PRICE P FROM PAINTING"); - -// let Cayenne know that result is a scalar -SQLResult resultDescriptor = new SQLResult(); -resultDescriptor.addColumnResult("P"); -query.setResult(resultDescriptor); - -// List of BigDecimals -List<BigDecimal> prices = context.performQuery(query); </programlisting><programlisting language="java">SQLTemplate query = new SQLTemplate(Artist.class, "SELECT t0.ID, t0.NAME, t0.DATE_OF_BIRTH, COUNT(t1.PAINTING_ID) C " + - "FROM ARTIST t0 LEFT JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) " + - "GROUP BY t0.ID, t0.NAME, t0.DATE_OF_BIRTH"); - -// let Cayenne know that result is a mix of Artist objects and the count of their paintings -EntityResult artistResult = new EntityResult(Artist.class); -artistResult.addDbField(Artist.ID_PK_COLUMN, "ARTIST_ID"); -artistResult.addObjectField(Artist.NAME_PROPERTY, "NAME"); -artistResult.addObjectField(Artist.DATE_OF_BIRTH_PROPERTY, "DATE_OF_BIRTH"); - -SQLResult resultDescriptor = new SQLResult(); -resultDescriptor.addEntityResult(artistResult); -resultDescriptor.addColumnResult("C"); -query.setResult(resultDescriptor); - -// List of Object[] -List<Object[]> data = context.performQuery(query);</programlisting></para> - <para>Another trick related to mapping result sets is making Cayenne recognize - prefetched entities in the result set. This emulates "joint" prefetching of - SelectQuery, and is achieved by special column naming. Columns belonging to the - "root" entity of the query should use unqualified names corresponding to the root - DbEntity columns. For each related entity column names must be prefixed with - relationship name and a dot (e.g. "toArtist.ID"). Column naming can be controlled - with "#result" - directive:<programlisting language="java">String sql = "SELECT distinct " - + "#result('t1.ESTIMATED_PRICE' 'BigDecimal' '' 'paintings.ESTIMATED_PRICE'), " - + "#result('t1.PAINTING_TITLE' 'String' '' 'paintings.PAINTING_TITLE'), " - + "#result('t1.GALLERY_ID' 'int' '' 'paintings.GALLERY_ID'), " - + "#result('t1.ID' 'int' '' 'paintings.ID'), " - + "#result('NAME' 'String'), " - + "#result('DATE_OF_BIRTH' 'java.util.Date'), " - + "#result('t0.ID' 'int' '' 'ID') " - + "FROM ARTIST t0, PAINTING t1 " - + "WHERE t0.ID = t1.ARTIST_ID"; - -SQLTemplate q = new SQLTemplate(Artist.class, sql); -q.addPrefetch(Artist.PAINTINGS_PROPERTY) -List<Artist> objects = context.performQuery(query);</programlisting></para> - <para>And the final tip deals with capitalization of the DataRow keys. Queries like - "<code>SELECT * FROM...</code>" and even "<code>SELECT COLUMN1, COLUMN2, ... - FROM ...</code>" can sometimes result in Cayenne exceptions on attempts to - convert fetched DataRows to objects. Essentially any query that is not using a - <code>#result</code> directive to describe the result set is prone to this - problem, as different databases may produce different capitalization of the - java.sql.ResultSet columns. </para> - <para>The most universal way to address this issue is to describe each column explicitly - in the SQLTemplate via <code>#result</code>, e.g.: "<code>SELECT #result('column1'), - #result('column2'), ..</code>". However this quickly becomes impractical for - tables with lots of columns. For such cases Cayenne provides a shortcut based on the - fact that an ORM mapping usually follows some naming convention for the column - names. Simply put, for case-insensitive databases developers normally use either all - lowercase or all uppercase column names. Here is the API that takes advantage of - that user knowledge and forces Cayenne to follow a given naming convention for the - DataRow keys (this is also available as a dropdown in the - Modeler):<programlisting language="java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST"); -query.setColumnNamesCapitalization(CapsStrategy.LOWER); -List objects = context.performQuery(query);</programlisting></para> - <para>or<programlisting language="java">SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST"); -query.setColumnNamesCapitalization(CapsStrategy.UPPER); -List objects = context.performQuery(query); </programlisting></para> - <para>None of this affects the generated SQL, but the resulting DataRows are using - correct capitalization. Note that you probably shouldn't bother with this unless you - are getting CayenneRuntimeExceptions when fetching with SQLTemplate.</para> - </section> - </section> - <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, mutliple - result sets, some data modification (returned as an update count), or a combination of - these. So use "performQuery" to get a single result set, and use "performGenericQuery" - for anything - else:<programlisting language="java">ProcedureQuery query = new ProcedureQuery("my_procedure", Artist.class); - -// Set "IN" parameter values -query.addParam("p1", "abc"); -query.addParam("p2", 3000); - -List<Artist> result = context.performQuery(query);</programlisting><programlisting language="java">// here we do not bother with root class. -// Procedure name gives us needed routing information -ProcedureQuery query = new ProcedureQuery("my_procedure"); - -query.addParam("p1", "abc"); -query.addParam("p2", 3000); - -QueryResponse response = context.performGenericQuery(query); </programlisting></para> - <para>A stored procedure can return data back to the application as result sets or via OUT - parameters. To simplify the processing of the query output, QueryResponse treats OUT - parameters as if it was a separate result set. If a stored procedure declares any OUT or - INOUT parameters, QueryResponse will contain their returned values in the very first - result - list:<programlisting language="java">ProcedureQuery query = new ProcedureQuery("my_procedure"); -QueryResponse response = context.performGenericQuery(query); - -// read OUT parameters -List out = response.firstList(); - -if(!out.isEmpty()) { - Map outParameterValues = (Map) outList.get(0); -}</programlisting></para> - <para>There maybe a situation when a stored procedure handles its own transactions, but an - application is configured to use Cayenne-managed transactions. This is obviously - conflicting and undesirable behavior. In this case ProcedureQueries should be executed - explicitly wrapped in an "external" Transaction. This is one of the few cases when a - user should worry about transactions at all. See Transactions section for more - details.</para> - </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 matter - - the API for calling them is the same - via a - NamedQuery:<programlisting language="java">String[] keys = new String[] {"loginid", "password"}; -Object[] values = new String[] {"joe", "secret"}; - -NamedQuery query = new NamedQuery("Login", keys, values); - -List<User> matchingUsers = context.performQuery(query); </programlisting></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 - } - }; - } -}</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> + <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"/>--> </chapter>
