Author: aadamchik
Date: Tue Feb 19 17:18:30 2013
New Revision: 1447826
URL: http://svn.apache.org/r1447826
Log:
docs
* NamedQuery
* Custom queries
* java code listings
(cherry picked from commit 57676a27cd6f8feec6d102028a6e8eb0066c7de7)
Modified:
cayenne/main/branches/STABLE-3.1/docs/docbook/cayenne-guide/src/docbkx/queries.xml
Modified:
cayenne/main/branches/STABLE-3.1/docs/docbook/cayenne-guide/src/docbkx/queries.xml
URL:
http://svn.apache.org/viewvc/cayenne/main/branches/STABLE-3.1/docs/docbook/cayenne-guide/src/docbkx/queries.xml?rev=1447826&r1=1447825&r2=1447826&view=diff
==============================================================================
---
cayenne/main/branches/STABLE-3.1/docs/docbook/cayenne-guide/src/docbkx/queries.xml
(original)
+++
cayenne/main/branches/STABLE-3.1/docs/docbook/cayenne-guide/src/docbkx/queries.xml
Tue Feb 19 17:18:30 2013
@@ -31,7 +31,7 @@
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
+ printed:<programlisting language="java">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 have a
qualifier to select only the data that you care about. Qualifier
is simply an Expression
@@ -41,16 +41,16 @@ INFO: === returned 5 row. - took 5 ms.</
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 ?
+ time:<programlisting language="java">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);
+ self:<programlisting language="java">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);
+ E.g.:<programlisting language="java">SelectQuery query = new
SelectQuery(Artist.class);
// create Ordering object explicitly
query.addOrdering(new Ordering(Artist.DATE_OF_BIRTH_PROPERTY,
SortOrder.DESCENDING));
@@ -68,7 +68,7 @@ query.addOrdering(Artist.NAME_PROPERTY,
<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>
+ 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>
@@ -95,7 +95,7 @@ for(Object[] artistWithCount : result) {
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");
+ scalars:<programlisting language="java">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>
@@ -202,7 +202,7 @@ query.setParameters(Collections.singleto
this or other forms of <code>#bind</code> instead of
inserting them
inline.</para>
<para><emphasis role="italic">Semantics:</emphasis></para>
- <programlisting>#bind(value)
+ <programlisting language="java">#bind(value)
#bind(value jdbcType)
#bind(value jdbcType scale)</programlisting>
<para><emphasis role="italic">Arguments:</emphasis>
@@ -225,12 +225,12 @@ query.setParameters(Collections.singleto
</itemizedlist></para>
<para>
<emphasis role="italic"
- >Usage</emphasis>:<programlisting>#bind($xyz)
+ >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>update ARTIST set NAME
= #bind($name) where ID = #bind($id)</programlisting></para>
+ example:</emphasis><programlisting language="java">update
ARTIST set NAME = #bind($name) where ID = #bind($id)</programlisting></para>
</section>
<section>
<title>#bindEqual</title>
@@ -243,37 +243,37 @@ query.setParameters(Collections.singleto
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>#bindEqual(value)
+ <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>#bindEqual($xyz)
+ >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>update ARTIST set NAME
= #bind($name) where ID #bindEqual($id)</programlisting></para>
+ example:</emphasis><programlisting language="java">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>#bindNotEqual(value)
+ <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>#bindNotEqual($xyz)
+ >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>update ARTIST set NAME
= #bind($name) where ID #bindEqual($id)</programlisting></para>
+ example:</emphasis><programlisting language="java">update
ARTIST set NAME = #bind($name) where ID #bindEqual($id)</programlisting></para>
</section>
<section>
<title>#bindObjectEqual</title>
@@ -285,7 +285,7 @@ query.setParameters(Collections.singleto
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>#bindObjectEqual(value columns
idColumns)</programlisting>
+ <programlisting language="java">#bindObjectEqual(value columns
idColumns)</programlisting>
<para><emphasis role="italic">Arguments:</emphasis>
<itemizedlist>
<listitem>
@@ -303,10 +303,10 @@ query.setParameters(Collections.singleto
</itemizedlist></para>
<para>
<emphasis role="italic"
- >Usage</emphasis>:<programlisting>#bindObjectEqual($a
't0.ID' 'ID')
+ >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>String sql = "SELECT *
FROM PAINTING t0 WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER
BY PAINTING_ID";
+ 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 = ....
@@ -317,15 +317,15 @@ select.setParameters(Collections.singlet
<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>#bindObjectNotEqual(value columns
idColumns)</programlisting>
+ <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>#bindObjectNotEqual($a
't0.ID' 'ID')
+ >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>String sql = "SELECT *
FROM PAINTING t0 WHERE #bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' )
ORDER BY PAINTING_ID";
+ 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 = ....
@@ -338,7 +338,7 @@ select.setParameters(Collections.singlet
to create a DataRow (and ultimately - a persistent object)
from an arbitrary
ResultSet.</para>
<para><emphasis role="italic">Semantics:</emphasis></para>
- <programlisting>#result(column)
+ <programlisting language="java">#result(column)
#result(column javaType)
#result(column javaType alias)
#result(column javaType alias dataRowKey)</programlisting>
@@ -373,13 +373,13 @@ select.setParameters(Collections.singlet
</itemizedlist></para>
<para>
<emphasis role="italic"
- >Usage</emphasis>:<programlisting>#result('NAME')
+ >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>SELECT #result('ID'
'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date')
FROM ARTIST</programlisting></para>
+ example:</emphasis><programlisting language="java">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>
@@ -397,12 +397,12 @@ select.setParameters(Collections.singlet
entire WHERE clause should be excluded. chain/chunk allows
to do that.</para>
<para>
<emphasis role="italic"
- >Semantics</emphasis>:<programlisting>#chain(operator) ...
#end
+ >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>#chain('OR' 'WHERE')
+ 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>
@@ -418,7 +418,7 @@ select.setParameters(Collections.singlet
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>SQLTemplate query = new
SQLTemplate(Artist.class, "SELECT * FROM ARTIST");
+ case:<programlisting language="java">SQLTemplate query = new
SQLTemplate(Artist.class, "SELECT * FROM ARTIST");
// List of Artists
List<Artist> artists = context.performQuery(query);</programlisting>Just
@@ -426,7 +426,7 @@ List<Artist> artists = context.perfor
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>String sql = SELECT t0.NAME, COUNT(1)
FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ID = t1.ARTIST_ID) "
+ 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);
@@ -444,7 +444,7 @@ List<DataRow> rows = context.performQ
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>SQLTemplate query = new
SQLTemplate(Painting.class, "SELECT ESTIMATED_PRICE P FROM PAINTING");
+ 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();
@@ -452,7 +452,7 @@ resultDescriptor.addColumnResult("P");
query.setResult(resultDescriptor);
// List of BigDecimals
-List<BigDecimal> prices = context.performQuery(query);
</programlisting><programlisting>SQLTemplate query = new
SQLTemplate(Artist.class, "SELECT t0.ID, t0.NAME, t0.DATE_OF_BIRTH,
COUNT(t1.PAINTING_ID) C " +
+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");
@@ -476,7 +476,7 @@ List<Object[]> data = context.perform
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>String sql = "SELECT distinct "
+ 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'), "
@@ -506,10 +506,10 @@ List<Artist> objects = context.perfor
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>SQLTemplate query = new
SQLTemplate("SELECT * FROM ARTIST");
+ 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>SQLTemplate query = new
SQLTemplate("SELECT * FROM ARTIST");
+ <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
@@ -525,13 +525,13 @@ List objects = context.performQuery(quer
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>ProcedureQuery query = new
ProcedureQuery("my_procedure", Artist.class);
+ 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>// here we do not
bother with root class.
+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");
@@ -544,7 +544,7 @@ QueryResponse response = context.perform
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>ProcedureQuery query = new
ProcedureQuery("my_procedure");
+ list:<programlisting language="java">ProcedureQuery query = new
ProcedureQuery("my_procedure");
QueryResponse response = context.performGenericQuery(query);
// read OUT parameters
@@ -562,8 +562,52 @@ if(!out.isEmpty()) {
</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>
</chapter>
