cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ntimof...@apache.org
Subject cayenne git commit: Update documentation - remove references to Apache Velocity - add some styling
Date Tue, 30 Jan 2018 13:33:30 GMT
Repository: cayenne
Updated Branches:
  refs/heads/master 4517505ba -> cc025e40a


Update documentation
 - remove references to Apache Velocity
 - add some styling


Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo
Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/cc025e40
Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/cc025e40
Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/cc025e40

Branch: refs/heads/master
Commit: cc025e40a8ea81cd290b7dcc4a669d49b8e27640
Parents: 4517505
Author: Nikita Timofeev <stariy95@gmail.com>
Authored: Tue Jan 30 16:33:21 2018 +0300
Committer: Nikita Timofeev <stariy95@gmail.com>
Committed: Tue Jan 30 16:33:21 2018 +0300

----------------------------------------------------------------------
 .../asciidoc/_cayenne-guide/part2/queries.adoc  | 274 ++++++++++++++-----
 1 file changed, 212 insertions(+), 62 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/cayenne/blob/cc025e40/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
----------------------------------------------------------------------
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
index 7a63595..0351940 100644
--- a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
@@ -14,28 +14,45 @@
 
 === Queries
 
-Queries are Java objects used by the application to communicate with the database. Cayenne
knows how to translate queries into SQL statements appropriate for a particular database engine.
Most often queries are used to find objects matching certain criteria, but there are other
types of queries too. E.g. those allowing to run native SQL, call DB stored procedures, etc.
When committing objects, Cayenne itself creates special queries to insert/update/delete rows
in the database.
-
-There is a number of built-in queries in Cayenne, described later in this chapter. Most of
the newer queries use fluent API and can be created and executed as easy-to-read one-liners.
Users can define their own query types to abstract certain DB interactions that for whatever
reason can not be adequately described by the built-in set.
-
-Queries can be roughly categorized as "object" and "native". Object queries (most notably
ObjectSelect, SelectById, and EJBQLQuery) are built with abstractions originating in the object
model (the "object" side in the "object-relational" divide). E.g. ObjectSelect 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.
-
-Native queries describe a desired DB operation as SQL code (SQLSelect, 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.
+Queries are Java objects used by the application to communicate with the database.
+Cayenne knows how to translate queries into SQL statements appropriate for a particular database
engine.
+Most often queries are used to find objects matching certain criteria, but there are other
types of queries too.
+E.g. those allowing to run native SQL, call DB stored procedures, etc. When committing objects,
+Cayenne itself creates special queries to insert/update/delete rows in the database.
+
+There is a number of built-in queries in Cayenne, described later in this chapter.
+Most of the newer queries use fluent API and can be created and executed as easy-to-read
one-liners.
+Users can define their own query types to abstract certain DB interactions
+that for whatever reason can not be adequately described by the built-in set.
+
+Queries can be roughly categorized as "object" and "native".
+Object queries (most notably `ObjectSelect`, `SelectById`, and `EJBQLQuery`)
+are built with abstractions originating in the object model (the "object" side in the "object-relational"
divide).
+E.g. ObjectSelect 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.
+
+Native queries describe a desired DB operation as SQL code (`SQLSelect`, `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.
 [[select]]
 ==== ObjectSelect
 
 ===== Selecting objects
 
-ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported.
+`ObjectSelect` supersedes older `SelectQuery`. `SelectQuery` is still available and supported.
 
-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:
+`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:
 
 [source, java]
 ----
 List<Artist> objects = ObjectSelect.query(Artist.class).select(context);
 ----
 
-This returned all rows in the "ARTIST" table. If the logs were turned on, you might see the
following SQL printed:
+This returned all rows in the _ARTIST_ table. If the logs were turned on, you might see the
following SQL printed:
 
 [source]
 ----
@@ -43,7 +60,11 @@ INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0
 INFO: === returned 5 row. - took 5 ms.
 ----
 
-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:
+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:
 
 [source, java]
 ----
@@ -61,7 +82,7 @@ INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 WHERE t0.NAME
LIKE
 INFO: === returned 1 row. - took 6 ms.
 ----
 
-ObjectSelect allows to assemble qualifier from parts, using "and" and "or" method to chain
then together:
+`ObjectSelect` allows to assemble qualifier from parts, using "and" and "or" method to chain
then together:
 
 [source, java]
 ----
@@ -71,7 +92,7 @@ List<Artist> objects = ObjectSelect.query(Artist.class)
     .select(context);
 ----
 
-To order the results of ObjectSelect, one or more orderings can be applied:
+To order the results of `ObjectSelect`, one or more orderings can be applied:
 
 [source, java]
 ----
@@ -81,7 +102,10 @@ List<Artist> objects = ObjectSelect.query(Artist.class)
     .select(context);
 ----
 
-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.
+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.
 
 ===== Selecting individual columns
 
@@ -104,7 +128,8 @@ List<Object[]> nameAndDate = ObjectSelect
 
 ===== Selecting using aggregate functions
 
-ObjectSelect query supports usage of aggregate functions. Most common variant of aggregation
is selecting count of records, this can be done really easy:
+ObjectSelect query supports usage of aggregate functions.
+Most common variant of aggregation is selecting count of records, this can be done really
easy:
 
 [source, java]
 ----
@@ -146,7 +171,9 @@ ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME
 [[ejbql]]
 ==== EJBQLQuery
 
-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):
+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):
 
 
 [source, java]
@@ -154,11 +181,14 @@ EJBQLQuery was created as a part of an experiment in adopting some of
Java Persi
 EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");
 ----
 
-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.
+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.
 
-Although most frequently EJBQLQuery is used as an alternative to SelectQuery, there are also
DELETE and UPDATE varieties available.
+Although most frequently `EJBQLQuery` is used as an alternative to `SelectQuery`,
+there are also DELETE and UPDATE varieties available.
 
-NOTE: 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.
+NOTE: 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.
 
 
 [source, java]
@@ -180,7 +210,11 @@ context.performGenericQuery(update);
 ----
 
 
-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:
+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:
 
 [source, java]
 ----
@@ -193,7 +227,9 @@ for(Object[] artistWithCount : result) {
 ----
 
 
-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:
+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:
 
 [source, java]
 ----
@@ -201,14 +237,18 @@ EJBQLQuery query = new EJBQLQuery("select a.name FROM Artist a");
 List<String> names = context.performQuery(query);
 ----
 
-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.
+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.
 
 [source, java]
 ----
 select p from Painting p where p.paintingTitle in (?1,?2,?3)
 ----
 
-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.
+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`.
 
 [source, java]
 ----
@@ -222,17 +262,25 @@ The following example is functionally identical to the one prior.
 select p from Painting p where p.paintingTitle in (?1)
 ----
 
-It is xref:evaluete[possible to convert] an xref:expressions[Expression] object used with
a xref:select[SelectQuery] to EJBQL. Use the Expression#appendAsEJBQL methods for this purpose.
+It is xref:evaluete[possible to convert] an xref:expressions[Expression] object used with
a xref:select[SelectQuery] to EJBQL.
+Use the Expression#appendAsEJBQL methods for this purpose.
 
-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:
+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:
 
-- 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.
+- 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.
 
-- 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".
+- 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".
 
 ===== SelectById
 
-This query allows to search objects by their ID. It's introduced in Cayenne 4.0 and uses
new "fluent" API same as `ObjectSelect` query.
+This query allows to search objects by their ID.
+It's introduced in Cayenne 4.0 and uses new "fluent" API same as `ObjectSelect` query.
 
 Here is example of how to use it:
 
@@ -246,7 +294,10 @@ Artist artistWithId1 = SelectById.query(Artist.class, 1)
 
 ===== SQLSelect and SQLExec
 
-`SQLSelect` and `SQLExec` are essentially a "fluent" versions of older `SQLTemplate` query.
`SQLSelect` can be used (as name suggests) to select custom data in form of entities, separate
columns or collection of `DataRow`. `SQLExec` is designed to just execute any raw SQL code
(e.g. updates, deletes, DDLs, etc.) This queries support all directives described in xref:sqltemplate[SQLTemplate]
section.
+`SQLSelect` and `SQLExec` are essentially a "fluent" versions of older `SQLTemplate` query.
+`SQLSelect` can be used (as name suggests) to select custom data in form of entities,
+separate columns or collection of `DataRow`. `SQLExec` is designed to just execute any raw
SQL code
+(e.g. updates, deletes, DDLs, etc.) This queries support all directives described in <<SQLTemplate>>
section.
 
 Here is example of how to use SQLSelect:
 
@@ -274,7 +325,10 @@ int inserted = SQLExec
 
 ===== MappedSelect and MappedExec
 
-`MappedSelect` and `MappedExec` is a queries that are just a reference to another queries
stored in the DataMap. The actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery,
etc. Difference between `MappedSelect` and `MappedExec` is (as reflected in their names) whether
underlying query intended to select data or just to perform some generic SQL code.
+`MappedSelect` and `MappedExec` is a queries that are just a reference to another queries
stored in the DataMap.
+The actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc.
+Difference between `MappedSelect` and `MappedExec` is (as reflected in their names) whether
underlying query
+intended to select data or just to perform some generic SQL code.
 
 NOTE: These queries are "fluent" versions of deprecated `NamedQuery` class.
 
@@ -299,7 +353,12 @@ System.out.println("Rows updated: " + result.firstUpdateCount());
 
 ==== ProcedureCall
 
-Stored procedures are mapped as separate objects in CayenneModeler. `ProcedureCall` provides
a way to execute them with a certain set of parameters. This query is a "fluent" version of
older `ProcedureQuery`. Just like with `SQLTemplate`, the outcome of a procedure can be anything
- a single result set, multiple result sets, some data modification (returned as an update
count), or a combination of these. So use root class to get a single result set, and use only
procedure name for anything else:
+Stored procedures are mapped as separate objects in CayenneModeler.
+`ProcedureCall` provides a way to execute them with a certain set of parameters.
+This query is a "fluent" version of older `ProcedureQuery`. Just like with `SQLTemplate`,
+the outcome of a procedure can be anything - a single result set, multiple result sets,
+some data modification (returned as an update count), or a combination of these.
+So use root class to get a single result set, and use only procedure name for anything else:
 
 
 [source, java]
@@ -321,7 +380,9 @@ ProcedureResult result = ProcedureCall.query("my_procedure")
     .call();
 ----
 
-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. For stored procedures declaref any OUT or INOUT parameters,
`ProcedureResult` have convenient utility method to get them:
+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.
+For stored procedures declaref any OUT or INOUT parameters, `ProcedureResult` have convenient
utility method to get them:
 
 [source, java]
 ----
@@ -332,13 +393,20 @@ ProcedureResult result = ProcedureCall.query("my_procedure")
 Object out = result.getOutParam("out_param");
 ----
 
-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.
+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.
 
 ==== Custom Queries
 
-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 `org.apache.cayenne.query.Query`
interface. The easiest way to go about it is to subclass some of the base queries in Cayenne.
+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 `org.apache.cayenne.query.Query` interface.
+The easiest way to go about it is to subclass some of the base queries in Cayenne.
 
-E.g. to do something directly in the JDBC layer, you might subclass AbstractQuery:
+E.g. to do something directly in the JDBC layer, you might subclass `AbstractQuery`:
 
 [source, java]
 ----
@@ -358,7 +426,7 @@ public class MyQuery extends AbstractQuery {
 }
 ----
 
-To delegate the actual query execution to a standard Cayenne query, you may subclass IndirectQuery:
+To delegate the actual query execution to a standard Cayenne query, you may subclass `IndirectQuery`:
 
 
 [source, java]
@@ -378,11 +446,16 @@ public class MyDelegatingQuery extends IndirectQuery {
 }
 ----
 
-In fact many internal Cayenne queries are IndirectQueries, delegating to SelectQuery or SQLTemplate
after some preprocessing.
+In fact many internal Cayenne queries are `IndirectQueries`, delegating to `SelectQuery`
or `SQLTemplate`
+ after some preprocessing.
 
 [[sqltemplate]]
 ==== SQLTemplate
-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:
+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:
 
 
 [source, java]
@@ -397,13 +470,28 @@ SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST");
 QueryResponse response = context.performGenericQuery(update);
 ----
 
-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".
+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".
 
-NOTE: 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.
+NOTE: 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.
 
-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.
+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.
 
-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:
+To achieve interoperability between multiple 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:
 
 [source, java]
 ----
@@ -416,11 +504,17 @@ String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST";
 query.setTemplate(PostgresAdapter.class.getName(), pgSQL);
 ----
 
-===== Scripting SQLTemplate with Velocity
+===== Scripting SQLTemplate with templates
 
-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.
+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 a template. Before sending it to DB as a PreparedStatement,
+the String is evaluated, that does variable substitutions, and performs special callbacks
+in response to various directives, thus controlling query interaction with the JDBC layer.
 
-Check Velocity docs for the syntax details. Here we'll just mention the two main scripting
elements - "variables" (that look like `$var`) and "directives" (that look like `#directive(p1
p2 p3)`). 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.
+Here we'll describe the two main scripting elements - "variables" (that look like `$var`)
+and "directives" (that look like `#directive(p1 p2 p3)`).
+Cayenne defines a number of directives to bind parameters to `PreparedStatement`
+and to control the structure of the `ResultSet`. These directives are described in the following
sections.
 
 
 ===== Variable Substitution
@@ -436,9 +530,15 @@ query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING"));
 // this will generate SQL like this: "delete from mydb.PAINTING"
 ----
 
-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
(`Artist.class` in this case) , as we are not planning on converting the result to objects.
+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 (`Artist.class` in this case),
+as we are not planning on converting the result to objects.
 
-Variable substitution within the text uses `"object.toString()"` 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 `#bind` directive as described
below.
+Variable substitution within the text uses `object.toString()` 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 `#bind` directive as described below.
 
 [[directives]]
 ===== Directives
@@ -447,7 +547,10 @@ These are the Cayenne directives used to customize SQLTemplate parsing
and integ
 
 ====== #bind
 
-Creates a PreparedStatement positional parameter in place of the directive, binding the value
to it before statement execution. `#bind` 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.
+Creates a PreparedStatement positional parameter in place of the directive, binding the value
to it before statement execution.
+`#bind` 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.
 
 Semantics:
 
@@ -460,7 +563,9 @@ Semantics:
 
 Arguments:
 
-- `value` - 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.
+- `value` - 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.
 
 - `jdbcType` - is a JDBC data type of the parameter as defined in `java.sql.Types`.
 
@@ -486,7 +591,12 @@ update ARTIST set NAME = #bind($name) where ID = #bind($id)
 
 ====== #bindEqual
 
-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 `"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.
+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 `"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.
 
 Semantics:
 
@@ -554,7 +664,11 @@ update ARTIST set NAME = #bind($name) where ID #bindEqual($id)
 
 ====== #bindObjectEqual
 
-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.
+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 an array for
multi-column PK.
 
 Semantics:
 
@@ -624,7 +738,9 @@ select.setParameters(Collections.singletonMap("a", a));
 
 ====== #result
 
-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.
+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.
 
 Semantics:
 
@@ -640,11 +756,16 @@ Arguments:
 
 - `column` - the name of the column to render in SQL SELECT clause.
 
-- `javaType` - 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 `"#result('A'
'String')"`, `"#result('B' 'java.lang.String')"` and `"#result('C' 'int')"` are all valid
+- `javaType` - 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 `"#result('A' 'String')"`, `"#result('B' 'java.lang.String')"` and `"#result('C' 'int')"`
are all valid
 
 - `alias` - specifies both the SQL alias of the column and the value key in the DataRow.
If omitted, "column" value is used.
 
-- `dataRowKey` - 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.
+- `dataRowKey` - 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.
 
 Usage:
 
@@ -669,7 +790,9 @@ NOTE: For advanced features you may look at the <<Apache Velocity
extension>>
 
 ====== Mapping SQLTemplate Results
 
-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.
+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.
 
 By default SQLTemplate is expected to return a List of Persistent objects of its root type.
This is the simple case:
 
@@ -681,7 +804,9 @@ SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");
 List<Artist> artists = context.performQuery(query);
 ----
 
-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:
+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:
 
 [source, Java]
 ----
@@ -696,9 +821,16 @@ query.setFetchingDataRows(true);
 List<DataRow> rows = context.performQuery(query);
 ----
 
-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.
+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.
 
-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:
+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:
 
 [source, Java]
 ----
@@ -734,7 +866,11 @@ query.setResult(resultDescriptor);
 List<Object[]> data = context.performQuery(query);
 ----
 
-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:
+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:
 
 [source, Java]
 ----
@@ -754,9 +890,20 @@ q.addPrefetch(Artist.PAINTINGS_PROPERTY)
 List<Artist> objects = context.performQuery(query);
 ----
 
-And the final tip deals with capitalization of the DataRow keys. Queries like `"SELECT *
FROM..."` and even `"SELECT COLUMN1, COLUMN2, ... FROM ..."` can sometimes result in Cayenne
exceptions on attempts to convert fetched DataRows to objects. Essentially any query that
is not using a `#result` directive to describe the result set is prone to this problem, as
different databases may produce different capitalization of the java.sql.ResultSet columns.
+And the final tip deals with capitalization of the DataRow keys. Queries like `"SELECT *
FROM..."`
+and even `"SELECT COLUMN1, COLUMN2, ... FROM ..."` can sometimes result in Cayenne exceptions
+on attempts to convert fetched DataRows to objects.
+Essentially any query that is not using a `#result` directive to describe the result set
is prone to this problem,
+as different databases may produce different capitalization of the `java.sql.ResultSet` columns.
 
-The most universal way to address this issue is to describe each column explicitly in the
SQLTemplate via `#result`, e.g.: `"SELECT #result('column1'), #result('column2'), .."`. 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):
+The most universal way to address this issue is to describe each column explicitly in the
SQLTemplate via `#result`,
+e.g.: `"SELECT #result('column1'), #result('column2'), .."`.
+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):
 
 [source, Java]
 ----
@@ -774,7 +921,10 @@ query.setColumnNamesCapitalization(CapsStrategy.UPPER);
 List objects = context.performQuery(query);
 ----
 
-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.
+None of this affects the generated SQL, but the resulting DataRows are using correct capitalization.
+
+NOTE: You probably shouldn't bother with this unless you are getting
+`CayenneRuntimeExceptions` when fetching with `SQLTemplate`.
 
 
 


Mime
View raw message