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 08:24:47 UTC
[cayenne] branch STABLE-4.1 updated: splitting queries.adoc into
smaller more manageable files
This is an automated email from the ASF dual-hosted git repository.
aadamchik pushed a commit to branch STABLE-4.1
in repository https://gitbox.apache.org/repos/asf/cayenne.git
The following commit(s) were added to refs/heads/STABLE-4.1 by this push:
new 5bb2b1c splitting queries.adoc into smaller more manageable files
5bb2b1c is described below
commit 5bb2b1cfd13dd95efaf4bb2401de65bd40d3c081
Author: Andrus Adamchik <an...@objectstyle.com>
AuthorDate: Sat Nov 2 10:59:08 2019 +0300
splitting queries.adoc into smaller more manageable files
---
.../asciidoc/_cayenne-guide/part2/queries.adoc | 946 +--------------------
.../_cayenne-guide/part2/queries/custom.adoc | 61 ++
.../_cayenne-guide/part2/queries/ejbql.adoc | 117 +++
.../_cayenne-guide/part2/queries/mapped.adoc | 41 +
.../_cayenne-guide/part2/queries/objectselect.adoc | 145 ++++
.../_cayenne-guide/part2/queries/procedure.adoc | 61 ++
.../_cayenne-guide/part2/queries/selectbyid.adoc | 28 +
.../asciidoc/_cayenne-guide/part2/queries/sql.adoc | 61 ++
.../{queries.adoc => queries/sqltemplate.adoc} | 480 -----------
9 files changed, 522 insertions(+), 1418 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 b6b6ff0..ef61984 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
@@ -37,949 +37,19 @@ 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
-
-NOTE: ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported, but will be deprecated
-in the future.
-
-`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:
-
-[source]
-----
-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:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .where(Artist.NAME.like("Pablo%"))
- .select(context);
-----
-
-The SQL will look different this time:
-
-[source]
-----
-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.
-----
-
-`ObjectSelect` allows to assemble qualifier from parts, using "and" and "or" method to chain then together:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .where(Artist.NAME.like("A%"))
- .and(Artist.DATE_OF_BIRTH.gt(someDate)
- .select(context);
-----
-
-To order the results of `ObjectSelect`, one or more orderings can be applied:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .orderBy(Artist.DATE_OF_BIRTH.desc())
- .orderBy(Artist.NAME.asc())
- .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.
-
-===== Selecting individual columns
-
-`ObjectSelect` query can be used to fetch individual properties of objects via type-safe API:
-
-[source, java]
-----
-List<String> names = ObjectSelect
- .columnQuery(Artist.class, Artist.ARTIST_NAME)
- .select(context);
-----
-
-And here is an example of selecting several properties. The result is a list of `Object[]`:
-
-[source, java]
-----
-List<Object[]> nameAndDate = ObjectSelect
- .columnQuery(Artist.class, Artist.ARTIST_NAME, Artist.DATE_OF_BIRTH)
- .select(context);
-----
-
-===== 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:
-
-[source, java]
-----
-long count = ObjectSelect.query(Artist.class).selectCount(context);
-----
-
-But you can use aggregates in more cases, even combine selecting individual properties and aggregates:
-
-[source, java]
-----
-// this is artificial property signaling that we want to get full object
-Property<Artist> artistProperty = Property.createSelf(Artist.class);
-
-List<Object[]> artistAndPaintingCount = ObjectSelect.columnQuery(Artist.class, artistProperty, Artist.PAINTING_ARRAY.count())
- .where(Artist.ARTIST_NAME.like("a%"))
- .having(Artist.PAINTING_ARRAY.count().lt(5L))
- .orderBy(Artist.PAINTING_ARRAY.count().desc(), Artist.ARTIST_NAME.asc())
- .select(context);
-
-for(Object[] next : artistAndPaintingCount) {
- Artist artist = (Artist)next[0];
- long paintings = (Long)next[1];
- System.out.println(artist.getArtistName() + " have " + paintings + " paintings");
-}
-----
-
-Here is generated `SQL` for this query:
-
-[source, SQL]
-----
-SELECT DISTINCT t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID, COUNT(t1.PAINTING_ID)
-FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
-WHERE t0.ARTIST_NAME LIKE ?
-GROUP BY t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH
-HAVING COUNT(t1.PAINTING_ID) < ?
-ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME
-----
-
-[[ejbql]]
-==== EJBQLQuery
-
-NOTE: As soon as all of the `EJBQLQuery` capabilities become available in `ObjectSelect`, we are planning to
-deprecate `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 follows JPQL
-(JPA Query Language) syntax:
-
-[source, java]
-----
-EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");
-----
-
-JPQL details can be found in any JPA manual. Here we'll focus on 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 ObjectSelect, there are also DELETE and UPDATE
-varieties available.
-
-NOTE: DELETE and UPDATE do not change the state of objects in the ObjectContext. They are run directly against the
-database instead.
-
-[source, java]
-----
-EJBQLQuery select =
- new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
-List<Artist> artists = context.performQuery(select);
-----
-
-[source, java]
-----
-EJBQLQuery delete = new EJBQLQuery("delete from Painting");
-context.performGenericQuery(delete);
-----
-
-[source, java]
-----
-EJBQLQuery update =
- new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
-context.performGenericQuery(update);
-----
-
-In most cases `ObjectSelect` 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 `ObjectSelect` though is that the former offers a few extra capabilities,
-such as subqueries.
-
-Just like `ObjectSelect` `EJBQLQuery` can return a List of Object[] elements, where each entry in an array is either a
-DataObject or a scalar, depending on the query SELECT clause.
-[source, java]
-----
-EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
-List<Object[]> result = context.performQuery(query);
-for(Object[] artistWithCount : result) {
- Artist a = (Artist) artistWithCount[0];
- int hasPaintings = (Integer) artistWithCount[1];
-}
-----
-
-A result can also be a list of scalars:
-[source, java]
-----
-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.
-
-[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`.
-
-[source, java]
-----
-select p from Painting p where p.paintingTitle in ?1
-----
-
-The following example is functionally identical to the one prior.
-
-[source, java]
-----
-select p from Painting p where p.paintingTitle in (?1)
-----
-
-It is possible to convert an xref:expressions[Expression] object used with a xref:select[ObjectSelect] 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 notable 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.
-
-- 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.
-
-Here is example of how to use it:
-
-[source, java]
-----
-Artist artistWithId1 = SelectById.query(Artist.class, 1)
- .prefetch(Artist.PAINTING_ARRAY.joint())
- .localCache()
- .selectOne(context);
-----
-
-===== 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.
-
-Here is example of how to use SQLSelect:
-
-[source, java]
-----
-// Selecting objects
-List<Painting> paintings = SQLSelect
- .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE #bind($title)")
- .params("title", "painting%")
- .upperColumnNames()
- .localCache()
- .limit(100)
- .select(context);
-
-// Selecting scalar values
-List<String> paintingNames = SQLSelect
- .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE ESTIMATED_PRICE > #bind($price)")
- .params("price", 100000)
- .select(context);
-
-// Selecting DataRow with predefined types
-List<DataRow> result = SQLSelect
- .dataRowQuery("SELECT * FROM ARTIST_CT", 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)
- .select(context);
-----
-
-
-And here is 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")
- .update(context);
-----
-
-===== 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.
-
-NOTE: These queries are "fluent" versions of deprecated `NamedQuery` class.
-
-Here is example of how to use `MappedSelect`:
-
-[source, java]
-----
-List<Artist> results = MappedSelect.query("artistsByName", Artist.class)
- .param("name", "Picasso")
- .select(context);
-----
-
-And here is example of `MappedExec`:
-
-[source, java]
-----
-QueryResult result = MappedExec.query("updateQuery")
- .param("var", "value")
- .execute(context);
-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:
-
-
-[source, java]
-----
-List<Artist> result = ProcedureCall.query("my_procedure", Artist.class)
- .param("p1", "abc")
- .param("p2", 3000)
- .call(context)
- .firstList();
-----
-
-[source, java]
-----
-// here we do not bother with root class.
-// Procedure name gives us needed routing information
-ProcedureResult result = ProcedureCall.query("my_procedure")
- .param("p1", "abc")
- .param("p2", 3000)
- .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:
-
-[source, java]
-----
-ProcedureResult result = ProcedureCall.query("my_procedure")
- .call(context);
-
-// read OUT parameters
-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.
-
-==== 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.
-
-E.g. to do something directly in the JDBC layer, you might subclass `AbstractQuery`:
-
-[source, 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
- }
- };
- }
-}
-----
-
-To delegate the actual query execution to a standard Cayenne query, you may subclass `IndirectQuery`:
-
-
-[source, 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
- }
-}
-----
-
-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:
-
-
-[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`.
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+include::queries/objectselect.adoc[]
+include::queries/ejbql.adoc[]
+include::queries/selectbyid.adoc[]
+include::queries/sql.adoc[]
+include::queries/mapped.adoc[]
+include::queries/procedure.adoc[]
+include::queries/custom.adoc[]
+include::queries/sqltemplate.adoc[]
\ No newline at end of file
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/custom.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/custom.adoc
new file mode 100644
index 0000000..d7cf762
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/custom.adoc
@@ -0,0 +1,61 @@
+// 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.
+==== 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.
+
+E.g. to do something directly in the JDBC layer, you might subclass `AbstractQuery`:
+
+[source, 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
+ }
+ };
+ }
+}
+----
+
+To delegate the actual query execution to a standard Cayenne query, you may subclass `IndirectQuery`:
+
+
+[source, 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
+ }
+}
+----
+
+In fact many internal Cayenne queries are `IndirectQueries`, delegating to `SelectQuery` or `SQLTemplate`
+after some preprocessing.
\ No newline at end of file
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/ejbql.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/ejbql.adoc
new file mode 100644
index 0000000..ee1f362
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/ejbql.adoc
@@ -0,0 +1,117 @@
+// 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.
+[[ejbql]]
+==== EJBQLQuery
+
+NOTE: As soon as all of the `EJBQLQuery` capabilities become available in `ObjectSelect`, we are planning to
+deprecate `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 follows JPQL
+(JPA Query Language) syntax:
+
+[source, java]
+----
+EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");
+----
+
+JPQL details can be found in any JPA manual. Here we'll focus on 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 ObjectSelect, there are also DELETE and UPDATE
+varieties available.
+
+NOTE: DELETE and UPDATE do not change the state of objects in the ObjectContext. They are run directly against the
+database instead.
+
+[source, java]
+----
+EJBQLQuery select =
+ new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
+List<Artist> artists = context.performQuery(select);
+----
+
+[source, java]
+----
+EJBQLQuery delete = new EJBQLQuery("delete from Painting");
+context.performGenericQuery(delete);
+----
+
+[source, java]
+----
+EJBQLQuery update =
+ new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
+context.performGenericQuery(update);
+----
+
+In most cases `ObjectSelect` 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 `ObjectSelect` though is that the former offers a few extra capabilities,
+such as subqueries.
+
+Just like `ObjectSelect` `EJBQLQuery` can return a List of Object[] elements, where each entry in an array is either a
+DataObject or a scalar, depending on the query SELECT clause.
+[source, java]
+----
+EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
+List<Object[]> result = context.performQuery(query);
+for(Object[] artistWithCount : result) {
+ Artist a = (Artist) artistWithCount[0];
+ int hasPaintings = (Integer) artistWithCount[1];
+}
+----
+
+A result can also be a list of scalars:
+[source, java]
+----
+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.
+
+[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`.
+
+[source, java]
+----
+select p from Painting p where p.paintingTitle in ?1
+----
+
+The following example is functionally identical to the one prior.
+
+[source, java]
+----
+select p from Painting p where p.paintingTitle in (?1)
+----
+
+It is possible to convert an xref:expressions[Expression] object used with a xref:select[ObjectSelect] 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 notable 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.
+
+- 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".
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/mapped.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/mapped.adoc
new file mode 100644
index 0000000..da21add
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/mapped.adoc
@@ -0,0 +1,41 @@
+// 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.
+
+==== 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.
+
+NOTE: These queries are "fluent" versions of deprecated `NamedQuery` class.
+
+Here is example of how to use `MappedSelect`:
+
+[source, java]
+----
+List<Artist> results = MappedSelect.query("artistsByName", Artist.class)
+ .param("name", "Picasso")
+ .select(context);
+----
+
+And here is example of `MappedExec`:
+
+[source, java]
+----
+QueryResult result = MappedExec.query("updateQuery")
+ .param("var", "value")
+ .execute(context);
+System.out.println("Rows updated: " + result.firstUpdateCount());
+----
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
new file mode 100644
index 0000000..9330a78
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/objectselect.adoc
@@ -0,0 +1,145 @@
+// 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.
+[[select]]
+==== ObjectSelect
+
+===== Selecting objects
+
+NOTE: ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported, but will be deprecated
+in the future.
+
+`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:
+
+[source]
+----
+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:
+
+[source, java]
+----
+List<Artist> objects = ObjectSelect.query(Artist.class)
+ .where(Artist.NAME.like("Pablo%"))
+ .select(context);
+----
+
+The SQL will look different this time:
+
+[source]
+----
+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.
+----
+
+`ObjectSelect` allows to assemble qualifier from parts, using "and" and "or" method to chain then together:
+
+[source, java]
+----
+List<Artist> objects = ObjectSelect.query(Artist.class)
+ .where(Artist.NAME.like("A%"))
+ .and(Artist.DATE_OF_BIRTH.gt(someDate)
+ .select(context);
+----
+
+To order the results of `ObjectSelect`, one or more orderings can be applied:
+
+[source, java]
+----
+List<Artist> objects = ObjectSelect.query(Artist.class)
+ .orderBy(Artist.DATE_OF_BIRTH.desc())
+ .orderBy(Artist.NAME.asc())
+ .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.
+
+===== Selecting individual columns
+
+`ObjectSelect` query can be used to fetch individual properties of objects via type-safe API:
+
+[source, java]
+----
+List<String> names = ObjectSelect
+ .columnQuery(Artist.class, Artist.ARTIST_NAME)
+ .select(context);
+----
+
+And here is an example of selecting several properties. The result is a list of `Object[]`:
+
+[source, java]
+----
+List<Object[]> nameAndDate = ObjectSelect
+ .columnQuery(Artist.class, Artist.ARTIST_NAME, Artist.DATE_OF_BIRTH)
+ .select(context);
+----
+
+===== 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:
+
+[source, java]
+----
+long count = ObjectSelect.query(Artist.class).selectCount(context);
+----
+
+But you can use aggregates in more cases, even combine selecting individual properties and aggregates:
+
+[source, java]
+----
+// this is artificial property signaling that we want to get full object
+Property<Artist> artistProperty = Property.createSelf(Artist.class);
+
+List<Object[]> artistAndPaintingCount = ObjectSelect.columnQuery(Artist.class, artistProperty, Artist.PAINTING_ARRAY.count())
+ .where(Artist.ARTIST_NAME.like("a%"))
+ .having(Artist.PAINTING_ARRAY.count().lt(5L))
+ .orderBy(Artist.PAINTING_ARRAY.count().desc(), Artist.ARTIST_NAME.asc())
+ .select(context);
+
+for(Object[] next : artistAndPaintingCount) {
+ Artist artist = (Artist)next[0];
+ long paintings = (Long)next[1];
+ System.out.println(artist.getArtistName() + " have " + paintings + " paintings");
+}
+----
+
+Here is generated `SQL` for this query:
+
+[source, SQL]
+----
+SELECT DISTINCT t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID, COUNT(t1.PAINTING_ID)
+FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
+WHERE t0.ARTIST_NAME LIKE ?
+GROUP BY t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH
+HAVING COUNT(t1.PAINTING_ID) < ?
+ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME
+----
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/procedure.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/procedure.adoc
new file mode 100644
index 0000000..223dbf0
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/procedure.adoc
@@ -0,0 +1,61 @@
+// 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.
+==== 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:
+
+
+[source, java]
+----
+List<Artist> result = ProcedureCall.query("my_procedure", Artist.class)
+ .param("p1", "abc")
+ .param("p2", 3000)
+ .call(context)
+ .firstList();
+----
+
+[source, java]
+----
+// here we do not bother with root class.
+// Procedure name gives us needed routing information
+ProcedureResult result = ProcedureCall.query("my_procedure")
+ .param("p1", "abc")
+ .param("p2", 3000)
+ .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:
+
+[source, java]
+----
+ProcedureResult result = ProcedureCall.query("my_procedure")
+ .call(context);
+
+// read OUT parameters
+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.
diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/selectbyid.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/selectbyid.adoc
new file mode 100644
index 0000000..c6f4135
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/selectbyid.adoc
@@ -0,0 +1,28 @@
+// 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.
+
+==== 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.
+
+Here is example of how to use it:
+
+[source, java]
+----
+Artist artistWithId1 = SelectById.query(Artist.class, 1)
+ .prefetch(Artist.PAINTING_ARRAY.joint())
+ .localCache()
+ .selectOne(context);
+----
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
new file mode 100644
index 0000000..a41da76
--- /dev/null
+++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sql.adoc
@@ -0,0 +1,61 @@
+// 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.
+==== 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.
+
+Here is example of how to use SQLSelect:
+
+[source, java]
+----
+// Selecting objects
+List<Painting> paintings = SQLSelect
+ .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE #bind($title)")
+ .params("title", "painting%")
+ .upperColumnNames()
+ .localCache()
+ .limit(100)
+ .select(context);
+
+// Selecting scalar values
+List<String> paintingNames = SQLSelect
+ .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE ESTIMATED_PRICE > #bind($price)")
+ .params("price", 100000)
+ .select(context);
+
+// Selecting DataRow with predefined types
+List<DataRow> result = SQLSelect
+ .dataRowQuery("SELECT * FROM ARTIST_CT", 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)
+ .select(context);
+----
+
+
+And here is 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")
+ .update(context);
+----
\ No newline at end of file
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/sqltemplate.adoc
similarity index 53%
copy from docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc
copy to docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries/sqltemplate.adoc
index b6b6ff0..1a063c7 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/sqltemplate.adoc
@@ -11,460 +11,6 @@
// CONDITIONS OF ANY KIND, either express or implied. See the License for
// the specific language governing permissions and limitations under the
// License.
-
-=== 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
-
-NOTE: ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported, but will be deprecated
-in the future.
-
-`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:
-
-[source]
-----
-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:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .where(Artist.NAME.like("Pablo%"))
- .select(context);
-----
-
-The SQL will look different this time:
-
-[source]
-----
-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.
-----
-
-`ObjectSelect` allows to assemble qualifier from parts, using "and" and "or" method to chain then together:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .where(Artist.NAME.like("A%"))
- .and(Artist.DATE_OF_BIRTH.gt(someDate)
- .select(context);
-----
-
-To order the results of `ObjectSelect`, one or more orderings can be applied:
-
-[source, java]
-----
-List<Artist> objects = ObjectSelect.query(Artist.class)
- .orderBy(Artist.DATE_OF_BIRTH.desc())
- .orderBy(Artist.NAME.asc())
- .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.
-
-===== Selecting individual columns
-
-`ObjectSelect` query can be used to fetch individual properties of objects via type-safe API:
-
-[source, java]
-----
-List<String> names = ObjectSelect
- .columnQuery(Artist.class, Artist.ARTIST_NAME)
- .select(context);
-----
-
-And here is an example of selecting several properties. The result is a list of `Object[]`:
-
-[source, java]
-----
-List<Object[]> nameAndDate = ObjectSelect
- .columnQuery(Artist.class, Artist.ARTIST_NAME, Artist.DATE_OF_BIRTH)
- .select(context);
-----
-
-===== 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:
-
-[source, java]
-----
-long count = ObjectSelect.query(Artist.class).selectCount(context);
-----
-
-But you can use aggregates in more cases, even combine selecting individual properties and aggregates:
-
-[source, java]
-----
-// this is artificial property signaling that we want to get full object
-Property<Artist> artistProperty = Property.createSelf(Artist.class);
-
-List<Object[]> artistAndPaintingCount = ObjectSelect.columnQuery(Artist.class, artistProperty, Artist.PAINTING_ARRAY.count())
- .where(Artist.ARTIST_NAME.like("a%"))
- .having(Artist.PAINTING_ARRAY.count().lt(5L))
- .orderBy(Artist.PAINTING_ARRAY.count().desc(), Artist.ARTIST_NAME.asc())
- .select(context);
-
-for(Object[] next : artistAndPaintingCount) {
- Artist artist = (Artist)next[0];
- long paintings = (Long)next[1];
- System.out.println(artist.getArtistName() + " have " + paintings + " paintings");
-}
-----
-
-Here is generated `SQL` for this query:
-
-[source, SQL]
-----
-SELECT DISTINCT t0.ARTIST_NAME, t0.DATE_OF_BIRTH, t0.ARTIST_ID, COUNT(t1.PAINTING_ID)
-FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID)
-WHERE t0.ARTIST_NAME LIKE ?
-GROUP BY t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH
-HAVING COUNT(t1.PAINTING_ID) < ?
-ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME
-----
-
-[[ejbql]]
-==== EJBQLQuery
-
-NOTE: As soon as all of the `EJBQLQuery` capabilities become available in `ObjectSelect`, we are planning to
-deprecate `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 follows JPQL
-(JPA Query Language) syntax:
-
-[source, java]
-----
-EJBQLQuery query = new EJBQLQuery("select a FROM Artist a");
-----
-
-JPQL details can be found in any JPA manual. Here we'll focus on 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 ObjectSelect, there are also DELETE and UPDATE
-varieties available.
-
-NOTE: DELETE and UPDATE do not change the state of objects in the ObjectContext. They are run directly against the
-database instead.
-
-[source, java]
-----
-EJBQLQuery select =
- new EJBQLQuery("select a FROM Artist a WHERE a.name = 'Salvador Dali'");
-List<Artist> artists = context.performQuery(select);
-----
-
-[source, java]
-----
-EJBQLQuery delete = new EJBQLQuery("delete from Painting");
-context.performGenericQuery(delete);
-----
-
-[source, java]
-----
-EJBQLQuery update =
- new EJBQLQuery("UPDATE Painting AS p SET p.name = 'P2' WHERE p.name = 'P1'");
-context.performGenericQuery(update);
-----
-
-In most cases `ObjectSelect` 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 `ObjectSelect` though is that the former offers a few extra capabilities,
-such as subqueries.
-
-Just like `ObjectSelect` `EJBQLQuery` can return a List of Object[] elements, where each entry in an array is either a
-DataObject or a scalar, depending on the query SELECT clause.
-[source, java]
-----
-EJBQLQuery query = new EJBQLQuery("select a, COUNT(p) FROM Artist a JOIN a.paintings p GROUP BY a");
-List<Object[]> result = context.performQuery(query);
-for(Object[] artistWithCount : result) {
- Artist a = (Artist) artistWithCount[0];
- int hasPaintings = (Integer) artistWithCount[1];
-}
-----
-
-A result can also be a list of scalars:
-[source, java]
-----
-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.
-
-[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`.
-
-[source, java]
-----
-select p from Painting p where p.paintingTitle in ?1
-----
-
-The following example is functionally identical to the one prior.
-
-[source, java]
-----
-select p from Painting p where p.paintingTitle in (?1)
-----
-
-It is possible to convert an xref:expressions[Expression] object used with a xref:select[ObjectSelect] 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 notable 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.
-
-- 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.
-
-Here is example of how to use it:
-
-[source, java]
-----
-Artist artistWithId1 = SelectById.query(Artist.class, 1)
- .prefetch(Artist.PAINTING_ARRAY.joint())
- .localCache()
- .selectOne(context);
-----
-
-===== 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.
-
-Here is example of how to use SQLSelect:
-
-[source, java]
-----
-// Selecting objects
-List<Painting> paintings = SQLSelect
- .query(Painting.class, "SELECT * FROM PAINTING WHERE PAINTING_TITLE LIKE #bind($title)")
- .params("title", "painting%")
- .upperColumnNames()
- .localCache()
- .limit(100)
- .select(context);
-
-// Selecting scalar values
-List<String> paintingNames = SQLSelect
- .scalarQuery(String.class, "SELECT PAINTING_TITLE FROM PAINTING WHERE ESTIMATED_PRICE > #bind($price)")
- .params("price", 100000)
- .select(context);
-
-// Selecting DataRow with predefined types
-List<DataRow> result = SQLSelect
- .dataRowQuery("SELECT * FROM ARTIST_CT", 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)
- .select(context);
-----
-
-
-And here is 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")
- .update(context);
-----
-
-===== 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.
-
-NOTE: These queries are "fluent" versions of deprecated `NamedQuery` class.
-
-Here is example of how to use `MappedSelect`:
-
-[source, java]
-----
-List<Artist> results = MappedSelect.query("artistsByName", Artist.class)
- .param("name", "Picasso")
- .select(context);
-----
-
-And here is example of `MappedExec`:
-
-[source, java]
-----
-QueryResult result = MappedExec.query("updateQuery")
- .param("var", "value")
- .execute(context);
-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:
-
-
-[source, java]
-----
-List<Artist> result = ProcedureCall.query("my_procedure", Artist.class)
- .param("p1", "abc")
- .param("p2", 3000)
- .call(context)
- .firstList();
-----
-
-[source, java]
-----
-// here we do not bother with root class.
-// Procedure name gives us needed routing information
-ProcedureResult result = ProcedureCall.query("my_procedure")
- .param("p1", "abc")
- .param("p2", 3000)
- .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:
-
-[source, java]
-----
-ProcedureResult result = ProcedureCall.query("my_procedure")
- .call(context);
-
-// read OUT parameters
-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.
-
-==== 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.
-
-E.g. to do something directly in the JDBC layer, you might subclass `AbstractQuery`:
-
-[source, 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
- }
- };
- }
-}
-----
-
-To delegate the actual query execution to a standard Cayenne query, you may subclass `IndirectQuery`:
-
-
-[source, 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
- }
-}
-----
-
-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.
@@ -957,29 +503,3 @@ None of this affects the generated SQL, but the resulting DataRows are using cor
NOTE: You probably shouldn't bother with this unless you are getting
`CayenneRuntimeExceptions` when fetching with `SQLTemplate`.
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-