You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by aa...@apache.org on 2019/11/02 09:17:25 UTC
[cayenne] branch master updated: SQL query docs reorg:
This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/cayenne.git
The following commit(s) were added to refs/heads/master by this push:
new 203ca11 SQL query docs reorg:
203ca11 is described below
commit 203ca11ac0cdab74c39dac0a4b564f4c017650fd
Author: Andrus Adamchik <an...@objectstyle.com>
AuthorDate: Sat Nov 2 11:52:13 2019 +0300
SQL query docs reorg:
* removing chapter on SQLTemplate
* rewriting scrioting guide to apply to SQLSelect/SQLExec
* various minor edits
---
.../asciidoc/_cayenne-guide/part2/queries.adoc | 41 +-
.../_cayenne-guide/part2/queries/objectselect.adoc | 4 +-
.../asciidoc/_cayenne-guide/part2/queries/sql.adoc | 29 +-
.../_cayenne-guide/part2/queries/sqlscripting.adoc | 282 ++++++++++++
.../_cayenne-guide/part2/queries/sqltemplate.adoc | 505 ---------------------
5 files changed, 306 insertions(+), 555 deletions(-)
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 fd776e3..f997834 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
@@ -28,15 +28,14 @@ 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.
+E.g. `ObjectSelect` consists of a Java class of 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.
+Native queries describe a desired DB operation using SQL (`SQLSelect`, `SQLExec` query), a reference to a stored
+procedure (`ProcedureQuery`), etc. The results of native queries are lists of scalars, lists of `Object[]` or lists of
+maps (a term "data row" is often used to describe such a map). Some of them can potentially be converted to persistent
+objects (though usually with considerable effort). Native queries are less (if at all) portable across databases
+than object queries.
include::queries/objectselect.adoc[]
@@ -45,6 +44,8 @@ include::queries/selectbyid.adoc[]
include::queries/sql.adoc[]
+include::queries/sqlscripting.adoc[]
+
include::queries/mapped.adoc[]
include::queries/procedure.adoc[]
@@ -53,27 +54,3 @@ include::queries/ejbql.adoc[]
include::queries/custom.adoc[]
-include::queries/sqltemplate.adoc[]
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/objectselect.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/objectselect.adoc
index 9163ad8..b45141a 100644
--- a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/objectselect.adoc
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/objectselect.adoc
@@ -14,10 +14,10 @@
[[select]]
==== ObjectSelect
-===== Selecting objects
-
NOTE: `ObjectSelect` supersedes older `SelectQuery`. `SelectQuery` is deprecated since 4.2.
+===== Selecting objects
+
`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:
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
index dc14ba6..58429ce 100644
--- a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
@@ -13,20 +13,20 @@
// License.
==== 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, collection of `DataRow` or Object[]. `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. Also you can predefine
-result type of columns.
+SQL is very powerful and allows to manipulate data in ways that can not always be described as a graph of related entities.
+Cayenne acknowledges this fact and provides a facility to execute SQL, sometimes allowing to map results back to
+persistent objects. `SQLSelect` and `SQLExec` are a pair of queries that allow to run native SQL. `SQLSelect` can be
+used (as name suggests) to select custom data in form of entities, separate columns, collection of `DataRow` or Object[].
+`SQLExec` is designed to execute any raw SQL code (e.g. updates, deletes, DDLs, etc.).
-Here is example of how to use SQLSelect:
+Both queries support advanced SQL templating, with variable substitution and special directives as described
+<<sqlscripting,in the next chapter>>. Here we'll just provide a few simple examples:
[source, java]
----
// Selecting objects
List<Painting> paintings = SQLSelect
- .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE #bind($title)")
- .params("title", "painting%")
+ .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE 'A%'")
.upperColumnNames()
.localCache()
.limit(100)
@@ -34,28 +34,25 @@ List<Painting> paintings = SQLSelect
// Selecting scalar values
List<String> paintingNames = SQLSelect
- .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE ESTIMATED_PRICE > #bind($price)")
- .params("price", 100000)
+ .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE ESTIMATED_PRICE > 100000")
.select(context);
// Selecting DataRow with predefined types
List<DataRow> result = SQLSelect
- .dataRowQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
+ .dataRowQuery("SELECT * FROM ARTIST", Integer.class, String.class, LocalDateTime.class)
.select(context);
// Selecting Object[] with predefined types
List<Object[]> result = SQLSelect
- .scalarQuery("SELECT * FROM ARTIST_CT", Integer.class, String.class, LocalDateTime.class)
+ .scalarQuery("SELECT * FROM ARTIST", Integer.class, String.class, LocalDateTime.class)
.select(context);
----
-
-And here is example of how to use `SQLExec`:
+And here is an example of how to use `SQLExec`:
[source, java]
----
int inserted = SQLExec
- .query("INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (#bind($id), #bind($name))")
- .paramsArray(55, "Picasso")
+ .query("INSERT INTO ARTIST (ARTIST_ID, ARTIST_NAME) VALUES (55, 'Picasso')")
.update(context);
----
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqlscripting.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqlscripting.adoc
new file mode 100644
index 0000000..80ad70d
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqlscripting.adoc
@@ -0,0 +1,282 @@
+// Licensed to the Apache Software Foundation (ASF) under one or more
+// contributor license agreements. See the NOTICE file distributed with
+// this work for additional information regarding copyright ownership.
+// The ASF licenses this file to you under the Apache License, Version
+// 2.0 (the "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0 Unless required by
+// applicable law or agreed to in writing, software distributed under the
+// License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
+// CONDITIONS OF ANY KIND, either express or implied. See the License for
+// the specific language governing permissions and limitations under the
+// License.
+[#sqlscripting]
+==== Scripting SQL Queries
+
+A powerful feature of `SQLSelect` and `SQLExec` is that a SQL string is treated by Cayenne as a dynamic template. Before
+sending it to DB as a PreparedStatement, the String is evaluated, resolving the dynamic parts. The two main scripting
+elements are "variables" (that look like `$var`) and "directives" (that look like `#directive(p1 p2 p3)`). In the discussion
+below we'll use both selecting and updating examples, as scripting works the same way for both `SQLSelect` and `SQLExec`.
+
+===== Variable Substitution
+
+All variables in the template string are replaced from query parameters:
+
+[source, java]
+----
+// this will generate SQL like this: "delete from mydb.PAINTING"
+SQLExec query = SQLExec.query("delete from $tableName")
+ .params("mydb.PAINTING");
+----
+
+Variable substitution within the text uses `object.toString()` method to replace the variable value.
+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 DB SQL parser. In such cases variable should be wrapped in
+`#bind` directive as described below.
+
+[#directives]
+===== Directives
+
+"Directives" look like `#directive(p1 p2 p3)` (notice the absence of comma between the arguments). The folliwng
+directives are supported in SQL templates:
+
+====== #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 some flavor of `#bind` instead of
+inserting them inline.
+
+Semantics:
+
+[source]
+----
+#bind(value)
+#bind(value jdbcType)
+#bind(value jdbcType scale)
+----
+
+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.
+
+- `jdbcType` - is a JDBC data type of the parameter as defined in `java.sql.Types`.
+
+- `scale` - An optional scale of the numeric value. Same as "scale" in PreparedStatement.
+
+Usage:
+
+[source]
+----
+#bind($xyz)
+#bind('str')
+#bind($xyz 'VARCHAR')
+#bind($xyz 'DECIMAL' 2)
+----
+
+Full example:
+
+[source, SQL]
+----
+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(..)"`.
+Motivation for this directive is to handle NULL SQL syntax. If the value is not null, `= ?` is generated, but if it is,
+the resulting SQL would look like `IS NULL`, which is compliant with what the DB expects.
+
+Semantics:
+
+[source]
+----
+#bindEqual(value)
+#bindEqual(value jdbcType)
+#bindEqual(value jdbcType scale)
+----
+
+Arguments: (same as #bind)
+
+Usage:
+
+[source]
+----
+#bindEqual($xyz)
+#bindEqual('str')
+#bindEqual($xyz 'VARCHAR')
+#bindEqual($xyz 'DECIMAL' 2)
+----
+
+Full example:
+
+[source, SQL]
+----
+update ARTIST set NAME = #bind($name) where ID #bindEqual($id)
+----
+
+====== #bindNotEqual
+
+This directive deals with the same issue as `#bindEqual` above, only it generates "!=" in front of the value (or `IS NOT NULL`).
+
+Semantics:
+
+[source]
+----
+#bindNotEqual(value)
+#bindNotEqual(value jdbcType)
+#bindNotEqual(value jdbcType scale)
+----
+
+Arguments: (same as #bind)
+
+Usage:
+
+[source]
+----
+#bindNotEqual($xyz)
+#bindNotEqual('str')
+#bindNotEqual($xyz 'VARCHAR')
+#bindNotEqual($xyz 'DECIMAL' 2)
+----
+
+Full example:
+
+[source, SQL]
+----
+update ARTIST set NAME = #bind($name) where ID #bindNotEqual($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 an array for
+multi-column PK.
+
+Semantics:
+
+[source]
+----
+#bindObjectEqual(value columns idColumns)
+----
+
+Arguments:
+
+- `value` - must be a variable that is resolved from the query parameters to a Persistent or ObjectId.
+
+- `columns` - the names of the columns to generate in the SQL.
+
+- `idColumn` - the names of the ID columns for a given entity. Must match the order of "columns" to match against.
+
+Usage:
+
+[source]
+----
+#bindObjectEqual($a 't0.ID' 'ID')
+#bindObjectEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])
+----
+
+Full example:
+
+[source, java]
+----
+String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' )";
+Artist artistParam = ...;
+
+SQLSelect select = SQLSelect.query(Painting.class, sql)
+ .params("a", artistParam);
+----
+
+====== #bindObjectNotEqual
+
+Same as `#bindObjectEqual` above, only generates `!=` operator for value comparison (or `IS NOT NULL`).
+
+Semantics:
+
+[source]
+----
+#bindObjectNotEqual(value columns idColumns)
+----
+
+Arguments: (same as #bindObjectEqual)
+
+Usage:
+
+[source]
+----
+#bindObjectNotEqual($a 't0.ID' 'ID')
+#bindObjectNotEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])
+----
+
+Full example:
+
+[source, java]
+----
+String sql = "SELECT * FROM PAINTING t0 WHERE #bindObjectNotEqual($a 't0.ARTIST_ID' 'ARTIST_ID' )";
+Artist artistParam = ...;
+
+SQLSelect select = SQLSelect.query(Painting.class, sql)
+ .params("a", artistParam);
+----
+
+====== #result
+
+Used around a column in `SELECT` clause to define the type conversion of the column value (e.g. it may force a conversion
+from Integer to Long) and/or define column name in the result (useful when fetching objects or DataRows).
+
+NOTE: You don't have to use `#result` for any given query if the default data types and column names coming from the
+query suit your needs. But if you do, you have to provide `#result` for every single result column, otherwise such column
+will be ignored.
+
+Semantics:
+
+[source]
+----
+#result(column)
+#result(column javaType)
+#result(column javaType alias)
+#result(column javaType alias dataRowKey)
+----
+
+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
+
+- `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.
+
+Usage:
+
+[source]
+----
+#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')
+----
+
+Full example:
+
+[source, SQL]
+----
+SELECT #result('ID' 'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date') FROM ARTIST
+----
+
+NOTE: For advanced features you may look at the <<Apache Velocity extension>>
+
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqltemplate.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqltemplate.adoc
deleted file mode 100644
index a7f5dc9..0000000
--- a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqltemplate.adoc
+++ /dev/null
@@ -1,505 +0,0 @@
-// Licensed to the Apache Software Foundation (ASF) under one or more
-// contributor license agreements. See the NOTICE file distributed with
-// this work for additional information regarding copyright ownership.
-// The ASF licenses this file to you under the Apache License, Version
-// 2.0 (the "License"); you may not use this file except in compliance
-// with the License. You may obtain a copy of the License at
-//
-// http://www.apache.org/licenses/LICENSE-2.0 Unless required by
-// applicable law or agreed to in writing, software distributed under the
-// License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR
-// CONDITIONS OF ANY KIND, either express or implied. See the License for
-// the specific language governing permissions and limitations under the
-// License.
-[[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:
-
-
-[source, java]
-----
-SQLTemplate select = new SQLTemplate(Artist.class, "select * from ARTIST");
-List<Artist> result = context.performQuery(select);
-----
-
-[source, java]
-----
-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".
-
-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.
-
-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]
-----
-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);
-----
-
-===== 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 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.
-
-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
-
-All variables in the template string are replaced from query parameters:
-
-
-[source, 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"
-----
-
-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.
-
-[[directives]]
-===== Directives
-
-These are the Cayenne directives used to customize SQLTemplate parsing and integrate it with the JDBC layer:
-
-====== #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.
-
-Semantics:
-
-[source]
-----
-#bind(value)
-#bind(value jdbcType)
-#bind(value jdbcType scale)
-----
-
-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.
-
-- `jdbcType` - is a JDBC data type of the parameter as defined in `java.sql.Types`.
-
-- `scale` - An optional scale of the numeric value. Same as "scale" in PreparedStatement.
-
-Usage:
-
-[source]
-----
-#bind($xyz)
-#bind('str')
-#bind($xyz 'VARCHAR')
-#bind($xyz 'DECIMAL' 2)
-----
-
-Full example:
-
-[source, SQL]
-----
-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.
-
-Semantics:
-
-
-[source]
-----
-#bindEqual(value)
-#bindEqual(value jdbcType)
-#bindEqual(value jdbcType scale)
-----
-
-Arguments: (same as #bind)
-
-Usage:
-
-[source]
-----
-#bindEqual($xyz)
-#bindEqual('str')
-#bindEqual($xyz 'VARCHAR')
-#bindEqual($xyz 'DECIMAL' 2)
-----
-
-
-Full example:
-
-
-[source, SQL]
-----
-update ARTIST set NAME = #bind($name) where ID #bindEqual($id)
-----
-
-====== #bindNotEqual
-
-This directive deals with the same issue as `#bindEqual` above, only it generates "not equal" in front of the value (or IS NOT NULL).
-
-Semantics:
-
-[source]
-----
-#bindNotEqual(value)
-#bindNotEqual(value jdbcType)
-#bindNotEqual(value jdbcType scale)
-----
-
-Arguments: (same as #bind)
-
-Usage:
-
-[source]
-----
-#bindNotEqual($xyz)
-#bindNotEqual('str')
-#bindNotEqual($xyz 'VARCHAR')
-#bindNotEqual($xyz 'DECIMAL' 2)
-----
-
-Full example:
-
-
-[source, SQL]
-----
-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 an array for multi-column PK.
-
-Semantics:
-
-[source]
-----
-#bindObjectEqual(value columns idColumns)
-----
-
-Arguments:
-
-- `value` - must be a variable that is resolved from the query parameters to a Persistent or ObjectId.
-
-- `columns` - the names of the columns to generate in the SQL.
-
-- `idColumn` - the names of the ID columns for a given entity. Must match the order of "columns" to match against.
-
-Usage:
-
-[source]
-----
-#bindObjectEqual($a 't0.ID' 'ID')
-#bindObjectEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])
-----
-
-Full example:
-
-[source, 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));
-----
-
-====== #bindObjectNotEqual
-
-Same as #bindObjectEqual above, only generates "not equal" operator for value comparison (or IS NOT NULL).
-
-Semantics:
-
-[source]
-----
-#bindObjectNotEqual(value columns idColumns)
-----
-
-Arguments: (same as #bindObjectEqual)
-
-Usage:
-
-[source]
-----
-#bindObjectNotEqual($a 't0.ID' 'ID')
-#bindObjectNotEqual($b ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'])
-----
-
-Full example:
-
-[source, 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));
-----
-
-====== #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.
-
-Semantics:
-
-[source]
-----
-#result(column)
-#result(column javaType)
-#result(column javaType alias)
-#result(column javaType alias dataRowKey)
-----
-
-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
-
-- `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.
-
-Usage:
-
-[source]
-----
-#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')
-----
-
-Full example:
-
-
-[source, SQL]
-----
-SELECT #result('ID' 'int'), #result('NAME' 'String'), #result('DATE_OF_BIRTH' 'java.util.Date') FROM ARTIST
-----
-
-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.
-
-By default SQLTemplate is expected to return a List of Persistent objects of its root type. This is the simple case:
-
-[source, Java]
-----
-SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST");
-
-// List of Artists
-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:
-
-[source, 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);
-----
-
-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:
-
-[source, 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);
-----
-
-[source, 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);
-----
-
-You can fetch list of scalars, list of Object[] or list of DataRow with predefined result column types or using default types.
-[source, Java]
-----
-// Selecting Object[] with predefined types
-SQLTemplate q3 = new SQLTemplate(Artist.class, "SELECT ARTIST_ID, ARTIST_NAME FROM ARTIST");
- q3.setResultColumnsTypes(Double.class, String.class);
- q3.setUseScalar(true);
-List<Object[]> result = context.performQuery(q3);
-
-// Selecting DataRow with predefined types
-SQLTemplate q3 = new SQLTemplate(Artist.class, "SELECT ARTIST_ID, ARTIST_NAME FROM ARTIST");
- q3.setResultColumnsTypes(Double.class, String.class);
- q3.setFetchingDataRows(true);
-List<DataRow> result = context.performQuery(q3);
-----
-
-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]
-----
-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);
-----
-
-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):
-
-[source, Java]
-----
-SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
-query.setColumnNamesCapitalization(CapsStrategy.LOWER);
-List objects = context.performQuery(query);
-----
-
-or
-
-[source, Java]
-----
-SQLTemplate query = new SQLTemplate("SELECT * FROM ARTIST");
-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: You probably shouldn't bother with this unless you are getting
-`CayenneRuntimeExceptions` when fetching with `SQLTemplate`.
\ No newline at end of file