Repository: cayenne Updated Branches: refs/heads/master 4517505ba -> cc025e40a
Update documentation - remove references to Apache Velocity - add some styling Project: http://git-wip-us.apache.org/repos/asf/cayenne/repo Commit: http://git-wip-us.apache.org/repos/asf/cayenne/commit/cc025e40 Tree: http://git-wip-us.apache.org/repos/asf/cayenne/tree/cc025e40 Diff: http://git-wip-us.apache.org/repos/asf/cayenne/diff/cc025e40 Branch: refs/heads/master Commit: cc025e40a8ea81cd290b7dcc4a669d49b8e27640 Parents: 4517505 Author: Nikita Timofeev <stari...@gmail.com> Authored: Tue Jan 30 16:33:21 2018 +0300 Committer: Nikita Timofeev <stari...@gmail.com> Committed: Tue Jan 30 16:33:21 2018 +0300 ---------------------------------------------------------------------- .../asciidoc/_cayenne-guide/part2/queries.adoc | 274 ++++++++++++++----- 1 file changed, 212 insertions(+), 62 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cayenne/blob/cc025e40/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc ---------------------------------------------------------------------- diff --git a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc index 7a63595..0351940 100644 --- a/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc +++ b/docs/asciidoc/cayenne-guide/src/docs/asciidoc/_cayenne-guide/part2/queries.adoc @@ -14,28 +14,45 @@ === Queries -Queries are Java objects used by the application to communicate with the database. Cayenne knows how to translate queries into SQL statements appropriate for a particular database engine. Most often queries are used to find objects matching certain criteria, but there are other types of queries too. E.g. those allowing to run native SQL, call DB stored procedures, etc. When committing objects, Cayenne itself creates special queries to insert/update/delete rows in the database. - -There is a number of built-in queries in Cayenne, described later in this chapter. Most of the newer queries use fluent API and can be created and executed as easy-to-read one-liners. Users can define their own query types to abstract certain DB interactions that for whatever reason can not be adequately described by the built-in set. - -Queries can be roughly categorized as "object" and "native". Object queries (most notably ObjectSelect, SelectById, and EJBQLQuery) are built with abstractions originating in the object model (the "object" side in the "object-relational" divide). E.g. ObjectSelect is assembled from a Java class of the objects to fetch, a qualifier expression, orderings, etc. - all of this expressed in terms of the object model. - -Native queries describe a desired DB operation as SQL code (SQLSelect, SQLTemplate query) or a reference to a stored procedure (ProcedureQuery), etc. The results of native queries are usually presented as Lists of Maps, with each map representing a row in the DB (a term "data row" is often used to describe such a map). They can potentially be converted to objects, however it may take a considerable effort to do so. Native queries are also less (if at all) portable across databases than object queries. +Queries are Java objects used by the application to communicate with the database. +Cayenne knows how to translate queries into SQL statements appropriate for a particular database engine. +Most often queries are used to find objects matching certain criteria, but there are other types of queries too. +E.g. those allowing to run native SQL, call DB stored procedures, etc. When committing objects, +Cayenne itself creates special queries to insert/update/delete rows in the database. + +There is a number of built-in queries in Cayenne, described later in this chapter. +Most of the newer queries use fluent API and can be created and executed as easy-to-read one-liners. +Users can define their own query types to abstract certain DB interactions +that for whatever reason can not be adequately described by the built-in set. + +Queries can be roughly categorized as "object" and "native". +Object queries (most notably `ObjectSelect`, `SelectById`, and `EJBQLQuery`) +are built with abstractions originating in the object model (the "object" side in the "object-relational" divide). +E.g. ObjectSelect is assembled from a Java class of the objects to fetch, +a qualifier expression, orderings, etc. - all of this expressed in terms of the object model. + +Native queries describe a desired DB operation as SQL code (`SQLSelect`, `SQLTemplate` query) +or a reference to a stored procedure (`ProcedureQuery`), etc. +The results of native queries are usually presented as Lists of Maps, +with each map representing a row in the DB (a term "data row" is often used to describe such a map). +They can potentially be converted to objects, however it may take a considerable effort to do so. +Native queries are also less (if at all) portable across databases than object queries. [[select]] ==== ObjectSelect ===== Selecting objects -ObjectSelect supersedes older SelectQuery. SelectQuery is still available and supported. +`ObjectSelect` supersedes older `SelectQuery`. `SelectQuery` is still available and supported. -ObjectSelect is the most commonly used query in Cayenne applications. This may be the only query you will ever need. It returns a list of persistent objects (or data rows) of a certain type specified in the query: +`ObjectSelect` is the most commonly used query in Cayenne applications. This may be the only query you will ever need. +It returns a list of persistent objects (or data rows) of a certain type specified in the query: [source, java] ---- List<Artist> objects = ObjectSelect.query(Artist.class).select(context); ---- -This returned all rows in the "ARTIST" table. If the logs were turned on, you might see the following SQL printed: +This returned all rows in the _ARTIST_ table. If the logs were turned on, you might see the following SQL printed: [source] ---- @@ -43,7 +60,11 @@ INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 INFO: === returned 5 row. - took 5 ms. ---- -This SQL was generated by Cayenne from the ObjectSelect above. ObjectSelect can have a qualifier to select only the data matching specific criteria. Qualifier is simply an Expression (Expressions where discussed in the previous chapter), appended to the query using "where" method. If you only want artists whose name begins with 'Pablo', you might use the following qualifier expression: +This SQL was generated by Cayenne from the `ObjectSelect` above. +`ObjectSelect` can have a qualifier to select only the data matching specific criteria. +Qualifier is simply an Expression (Expressions where discussed in the previous chapter), +appended to the query using "where" method. If you only want artists whose name begins with 'Pablo', +you might use the following qualifier expression: [source, java] ---- @@ -61,7 +82,7 @@ INFO: SELECT t0.DATE_OF_BIRTH, t0.NAME, t0.ID FROM ARTIST t0 WHERE t0.NAME LIKE INFO: === returned 1 row. - took 6 ms. ---- -ObjectSelect allows to assemble qualifier from parts, using "and" and "or" method to chain then together: +`ObjectSelect` allows to assemble qualifier from parts, using "and" and "or" method to chain then together: [source, java] ---- @@ -71,7 +92,7 @@ List<Artist> objects = ObjectSelect.query(Artist.class) .select(context); ---- -To order the results of ObjectSelect, one or more orderings can be applied: +To order the results of `ObjectSelect`, one or more orderings can be applied: [source, java] ---- @@ -81,7 +102,10 @@ List<Artist> objects = ObjectSelect.query(Artist.class) .select(context); ---- -There's a number of other useful methods in ObjectSelect that define what to select and how to optimize database interaction (prefetching, caching, fetch offset and limit, pagination, etc.). Some of them are discussed in separate chapters on caching and performance optimization. Others are fairly self-explanatory. Please check the API docs for the full extent of the ObjectSelect features. +There's a number of other useful methods in `ObjectSelect` that define what to select +and how to optimize database interaction (prefetching, caching, fetch offset and limit, pagination, etc.). +Some of them are discussed in separate chapters on caching and performance optimization. +Others are fairly self-explanatory. Please check the API docs for the full extent of the `ObjectSelect` features. ===== Selecting individual columns @@ -104,7 +128,8 @@ List<Object[]> nameAndDate = ObjectSelect ===== Selecting using aggregate functions -ObjectSelect query supports usage of aggregate functions. Most common variant of aggregation is selecting count of records, this can be done really easy: +ObjectSelect query supports usage of aggregate functions. +Most common variant of aggregation is selecting count of records, this can be done really easy: [source, java] ---- @@ -146,7 +171,9 @@ ORDER BY COUNT(t1.PAINTING_ID) DESC, t0.ARTIST_NAME [[ejbql]] ==== EJBQLQuery -EJBQLQuery was created as a part of an experiment in adopting some of Java Persistence API (JPA) approaches in Cayenne. It is a parameterized object query that is created from query String. A String used to build EJBQLQuery must conform to JPQL (JPA query language): +EJBQLQuery was created as a part of an experiment in adopting some of Java Persistence API (JPA) approaches in Cayenne. +It is a parameterized object query that is created from query String. +A String used to build `EJBQLQuery` must conform to JPQL (JPA query language): [source, java] @@ -154,11 +181,14 @@ EJBQLQuery was created as a part of an experiment in adopting some of Java Persi EJBQLQuery query = new EJBQLQuery("select a FROM Artist a"); ---- -JPQL details can be found in any JPA manual. Here we'll mention only how this fits into Cayenne and what are the differences between EJBQL and other Cayenne queries. +JPQL details can be found in any JPA manual. Here we'll mention only how this fits into Cayenne +and what are the differences between EJBQL and other Cayenne queries. -Although most frequently EJBQLQuery is used as an alternative to SelectQuery, there are also DELETE and UPDATE varieties available. +Although most frequently `EJBQLQuery` is used as an alternative to `SelectQuery`, +there are also DELETE and UPDATE varieties available. -NOTE: As of this version of Cayenne, DELETE and UPDATE do not change the state of objects in the ObjectContext. They are run directly against the database instead. +NOTE: As of this version of Cayenne, DELETE and UPDATE do not change the state of objects in the `ObjectContext`. +They are run directly against the database instead. [source, java] @@ -180,7 +210,11 @@ context.performGenericQuery(update); ---- -In most cases SelectQuery is preferred to EJBQLQuery, as it is API-based, and provides you with better compile-time checks. However sometimes you may want a completely scriptable object query. This is when you might prefer EJBQL. A more practical reason for picking EJBQL over SelectQuery though is that the former offers some extra selecting capabilities, namely aggregate functions and subqueries: +In most cases SelectQuery is preferred to `EJBQLQuery`, as it is API-based, +and provides you with better compile-time checks. However sometimes you may want a completely scriptable object query. +This is when you might prefer EJBQL. +A more practical reason for picking `EJBQL` over `SelectQuery` +though is that the former offers some extra selecting capabilities, namely aggregate functions and subqueries: [source, java] ---- @@ -193,7 +227,9 @@ for(Object[] artistWithCount : result) { ---- -This also demonstrates a previously unseen type of select result - a List of Object[] elements, where each entry in an Object[] is either a DataObject or a scalar, depending on the query SELECT clause. A result can also be a list of scalars: +This also demonstrates a previously unseen type of select result - a List of `Object[]` elements, +where each entry in an Object[] is either a `DataObject` or a scalar, depending on the query SELECT clause. +A result can also be a list of scalars: [source, java] ---- @@ -201,14 +237,18 @@ EJBQLQuery query = new EJBQLQuery("select a.name FROM Artist a"); List<String> names = context.performQuery(query); ---- -EJBQLQuery supports an "IN" clause with three different usage-patterns. The following example would require three individual positional parameters (named parameters could also have been used) to be supplied. +EJBQLQuery supports an "IN" clause with three different usage-patterns. +The following example would require three individual positional parameters +(named parameters could also have been used) to be supplied. [source, java] ---- select p from Painting p where p.paintingTitle in (?1,?2,?3) ---- -The following example requires a single positional parameter to be supplied. The parameter can be any concrete implementation of the java.util.Collection interface such as java.util.List or java.util.Set. +The following example requires a single positional parameter to be supplied. +The parameter can be any concrete implementation of the `java.util.Collection` interface +such as `java.util.List` or `java.util.Set`. [source, java] ---- @@ -222,17 +262,25 @@ The following example is functionally identical to the one prior. select p from Painting p where p.paintingTitle in (?1) ---- -It is xref:evaluete[possible to convert] an xref:expressions[Expression] object used with a xref:select[SelectQuery] to EJBQL. Use the Expression#appendAsEJBQL methods for this purpose. +It is xref:evaluete[possible to convert] an xref:expressions[Expression] object used with a xref:select[SelectQuery] to EJBQL. +Use the Expression#appendAsEJBQL methods for this purpose. -While Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE clause, and indeed they are very close, there are a few noteable differences: +While Cayenne Expressions discussed previously can be thought of as identical to JPQL WHERE clause, +and indeed they are very close, there are a few noteable differences: -- Null handling: SelectQuery would translate the expressions matching NULL values to the corresponding "X IS NULL" or "X IS NOT NULL" SQL syntax. EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL") syntax to be used, otherwise the generated SQL will look like "X = NULL" (or "X <> NULL"), which will evaluate differently. +- Null handling: SelectQuery would translate the expressions matching NULL values to the corresponding "X IS NULL" +or "X IS NOT NULL" SQL syntax. EJBQLQuery on the other hand requires explicit "IS NULL" (or "IS NOT NULL") +syntax to be used, otherwise the generated SQL will look like "X = NULL" (or "X <> NULL"), +which will evaluate differently. -- Expression Parameters: SelectQuery uses "$" to denote named parameters (e.g. "$myParam"), while EJBQL uses ":" (e.g. ":myParam"). Also EJBQL supports positional parameters denoted by the question mark: "?3". +- Expression Parameters: SelectQuery uses "$" to denote named parameters (e.g. "$myParam"), +while EJBQL uses ":" (e.g. ":myParam"). +Also EJBQL supports positional parameters denoted by the question mark: "?3". ===== SelectById -This query allows to search objects by their ID. It's introduced in Cayenne 4.0 and uses new "fluent" API same as `ObjectSelect` query. +This query allows to search objects by their ID. +It's introduced in Cayenne 4.0 and uses new "fluent" API same as `ObjectSelect` query. Here is example of how to use it: @@ -246,7 +294,10 @@ Artist artistWithId1 = SelectById.query(Artist.class, 1) ===== SQLSelect and SQLExec -`SQLSelect` and `SQLExec` are essentially a "fluent" versions of older `SQLTemplate` query. `SQLSelect` can be used (as name suggests) to select custom data in form of entities, separate columns or collection of `DataRow`. `SQLExec` is designed to just execute any raw SQL code (e.g. updates, deletes, DDLs, etc.) This queries support all directives described in xref:sqltemplate[SQLTemplate] section. +`SQLSelect` and `SQLExec` are essentially a "fluent" versions of older `SQLTemplate` query. +`SQLSelect` can be used (as name suggests) to select custom data in form of entities, +separate columns or collection of `DataRow`. `SQLExec` is designed to just execute any raw SQL code +(e.g. updates, deletes, DDLs, etc.) This queries support all directives described in <<SQLTemplate>> section. Here is example of how to use SQLSelect: @@ -274,7 +325,10 @@ int inserted = SQLExec ===== MappedSelect and MappedExec -`MappedSelect` and `MappedExec` is a queries that are just a reference to another queries stored in the DataMap. The actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. Difference between `MappedSelect` and `MappedExec` is (as reflected in their names) whether underlying query intended to select data or just to perform some generic SQL code. +`MappedSelect` and `MappedExec` is a queries that are just a reference to another queries stored in the DataMap. +The actual stored query can be SelectQuery, SQLTemplate, EJBQLQuery, etc. +Difference between `MappedSelect` and `MappedExec` is (as reflected in their names) whether underlying query +intended to select data or just to perform some generic SQL code. NOTE: These queries are "fluent" versions of deprecated `NamedQuery` class. @@ -299,7 +353,12 @@ System.out.println("Rows updated: " + result.firstUpdateCount()); ==== ProcedureCall -Stored procedures are mapped as separate objects in CayenneModeler. `ProcedureCall` provides a way to execute them with a certain set of parameters. This query is a "fluent" version of older `ProcedureQuery`. Just like with `SQLTemplate`, the outcome of a procedure can be anything - a single result set, multiple result sets, some data modification (returned as an update count), or a combination of these. So use root class to get a single result set, and use only procedure name for anything else: +Stored procedures are mapped as separate objects in CayenneModeler. +`ProcedureCall` provides a way to execute them with a certain set of parameters. +This query is a "fluent" version of older `ProcedureQuery`. Just like with `SQLTemplate`, +the outcome of a procedure can be anything - a single result set, multiple result sets, +some data modification (returned as an update count), or a combination of these. +So use root class to get a single result set, and use only procedure name for anything else: [source, java] @@ -321,7 +380,9 @@ ProcedureResult result = ProcedureCall.query("my_procedure") .call(); ---- -A stored procedure can return data back to the application as result sets or via OUT parameters. To simplify the processing of the query output, QueryResponse treats OUT parameters as if it was a separate result set. For stored procedures declaref any OUT or INOUT parameters, `ProcedureResult` have convenient utility method to get them: +A stored procedure can return data back to the application as result sets or via OUT parameters. +To simplify the processing of the query output, QueryResponse treats OUT parameters as if it was a separate result set. +For stored procedures declaref any OUT or INOUT parameters, `ProcedureResult` have convenient utility method to get them: [source, java] ---- @@ -332,13 +393,20 @@ ProcedureResult result = ProcedureCall.query("my_procedure") Object out = result.getOutParam("out_param"); ---- -There maybe a situation when a stored procedure handles its own transactions, but an application is configured to use Cayenne-managed transactions. This is obviously conflicting and undesirable behavior. In this case ProcedureQueries should be executed explicitly wrapped in an "external" Transaction. This is one of the few cases when a user should worry about transactions at all. See Transactions section for more details. +There maybe a situation when a stored procedure handles its own transactions, +but an application is configured to use Cayenne-managed transactions. +This is obviously conflicting and undesirable behavior. +In this case ProcedureQueries should be executed explicitly wrapped in an "external" Transaction. +This is one of the few cases when a user should worry about transactions at all. +See Transactions section for more details. ==== Custom Queries -If a user needs some extra functionality not addressed by the existing set of Cayenne queries, he can write his own. The only requirement is to implement `org.apache.cayenne.query.Query` interface. The easiest way to go about it is to subclass some of the base queries in Cayenne. +If a user needs some extra functionality not addressed by the existing set of Cayenne queries, he can write his own. +The only requirement is to implement `org.apache.cayenne.query.Query` interface. +The easiest way to go about it is to subclass some of the base queries in Cayenne. -E.g. to do something directly in the JDBC layer, you might subclass AbstractQuery: +E.g. to do something directly in the JDBC layer, you might subclass `AbstractQuery`: [source, java] ---- @@ -358,7 +426,7 @@ public class MyQuery extends AbstractQuery { } ---- -To delegate the actual query execution to a standard Cayenne query, you may subclass IndirectQuery: +To delegate the actual query execution to a standard Cayenne query, you may subclass `IndirectQuery`: [source, java] @@ -378,11 +446,16 @@ public class MyDelegatingQuery extends IndirectQuery { } ---- -In fact many internal Cayenne queries are IndirectQueries, delegating to SelectQuery or SQLTemplate after some preprocessing. +In fact many internal Cayenne queries are `IndirectQueries`, delegating to `SelectQuery` or `SQLTemplate` + after some preprocessing. [[sqltemplate]] ==== SQLTemplate -SQLTemplate is a query that allows to run native SQL from a Cayenne application. It comes handy when the standard ORM concepts are not sufficient for a given query or an update. SQL is too powerful and allows to manipulate data in ways that are not easily described as a graph of related entities. Cayenne acknowledges this fact and provides this facility to execute SQL, mapping the result to objects when possible. Here are examples of selecting and non-selecting SQLTemplates: +SQLTemplate is a query that allows to run native SQL from a Cayenne application. +It comes handy when the standard ORM concepts are not sufficient for a given query or an update. +SQL is too powerful and allows to manipulate data in ways that are not easily described as a graph of related entities. +Cayenne acknowledges this fact and provides this facility to execute SQL, mapping the result to objects when possible. +Here are examples of selecting and non-selecting SQLTemplates: [source, java] @@ -397,13 +470,28 @@ SQLTemplate update = new SQLTemplate(Artist.class, "delete from ARTIST"); QueryResponse response = context.performGenericQuery(update); ---- -Cayenne doesn't make any attempt to make sense of the SQL semantics, so it doesn't know whether a given query is performing a select or update, etc. It is the the user's decision to run a given query as a selecting or "generic". +Cayenne doesn't make any attempt to make sense of the SQL semantics, +so it doesn't know whether a given query is performing a select or update, etc. +It is the the user's decision to run a given query as a selecting or "generic". -NOTE: Any data modifications done to DB as a result of SQLTemplate execution do not change the state of objects in the ObjectContext. So some objects in the context may become stale as a result. +NOTE: Any data modifications done to DB as a result of `SQLTemplate` execution do not change the state +of objects in the ObjectContext. So some objects in the context may become stale as a result. -Another point to note is that the first argument to the SQLTemplate constructor - the Java class - has the same meaning as in SelectQuery only when the result can be converted to objects (e.g. when this is a selecting query and it is selecting all columns from one table). In this case it denotes the "root" entity of this query result. If the query does not denote a single entity result, this argument is only used for query routing, i.e. determining which database it should be run against. You are free to use any persistent class or even a DataMap instance in such situation. It will work as long as the passed "root" maps to the same database as the current query. +Another point to note is that the first argument to the `SQLTemplate` constructor - the Java class - +has the same meaning as in SelectQuery only when the result can be converted to objects +(e.g. when this is a selecting query and it is selecting all columns from one table). +In this case it denotes the "root" entity of this query result. If the query does not denote a single entity result, +this argument is only used for query routing, i.e. determining which database it should be run against. +You are free to use any persistent class or even a DataMap instance in such situation. +It will work as long as the passed "root" maps to the same database as the current query. -To achieve interoperability between mutliple RDBMS a user can specify multiple SQL statements for the same SQLTemplate, each corresponding to a native SQL dialect. A key used to look up the right dialect during execution is a fully qualified class name of the corresponding DbAdapter. If no DB-specific statement is present for a given DB, a default generic statement is used. E.g. in all the examples above a default statement will be used regardless of the runtime database. So in most cases you won't need to explicitly "translate" your SQL to all possible dialects. Here is how this works in practice: +To achieve interoperability between multiple RDBMS a user can specify multiple SQL statements +for the same `SQLTemplate`, each corresponding to a native SQL dialect. +A key used to look up the right dialect during execution is a fully qualified class name of the corresponding `DbAdapter`. +If no DB-specific statement is present for a given DB, a default generic statement is used. +E.g. in all the examples above a default statement will be used regardless of the runtime database. +So in most cases you won't need to explicitly "translate" your SQL to all possible dialects. +Here is how this works in practice: [source, java] ---- @@ -416,11 +504,17 @@ String pgSQL = "SELECT ARTIST_ID, RTRIM(NAME), DATE_OF_BIRTH FROM ARTIST"; query.setTemplate(PostgresAdapter.class.getName(), pgSQL); ---- -===== Scripting SQLTemplate with Velocity +===== Scripting SQLTemplate with templates -The most interesting aspect of SQLTemplate (and the reason why it is called a "template") is that a SQL string is treated by Cayenne as an Apache Velocity template. Before sending it to DB as a PreparedStatement, the String is evaluated in the Velocity context, that does variable substitutions, and performs special callbacks in response to various directives, thus controlling query interaction with the JDBC layer. +The most interesting aspect of SQLTemplate (and the reason why it is called a "template") +is that a SQL string is treated by Cayenne as a template. Before sending it to DB as a PreparedStatement, +the String is evaluated, that does variable substitutions, and performs special callbacks +in response to various directives, thus controlling query interaction with the JDBC layer. -Check Velocity docs for the syntax details. Here we'll just mention the two main scripting elements - "variables" (that look like `$var`) and "directives" (that look like `#directive(p1 p2 p3)`). All built-in Velocity directives are supported. Additionally Cayenne defines a number of its own directives to bind parameters to PreparedStatements and to control the structure of the ResultSet. These directives are described in the following sections. +Here we'll describe the two main scripting elements - "variables" (that look like `$var`) +and "directives" (that look like `#directive(p1 p2 p3)`). +Cayenne defines a number of directives to bind parameters to `PreparedStatement` +and to control the structure of the `ResultSet`. These directives are described in the following sections. ===== Variable Substitution @@ -436,9 +530,15 @@ query.setParameters(Collections.singletonMap("tableName", "mydb.PAINTING")); // this will generate SQL like this: "delete from mydb.PAINTING" ---- -The example above demonstrates the point made earlier in this chapter - even if we don't know upfront which table the query will run against, we can still use a fixed "root" in constructor (`Artist.class` in this case) , as we are not planning on converting the result to objects. +The example above demonstrates the point made earlier in this chapter - +even if we don't know upfront which table the query will run against, +we can still use a fixed "root" in constructor (`Artist.class` in this case), +as we are not planning on converting the result to objects. -Variable substitution within the text uses `"object.toString()"` method to replace the variable value. Keep in mind that this may not be appropriate in all situations. E.g. passing a date object in a WHERE clause expression may be converted to a String not understood by the target RDBMS SQL parser. In such cases variable should be wrapped in `#bind` directive as described below. +Variable substitution within the text uses `object.toString()` method to replace the variable value. +Keep in mind that this may not be appropriate in all situations. +E.g. passing a date object in a WHERE clause expression may be converted to a String not understood by the target RDBMS SQL parser. +In such cases variable should be wrapped in `#bind` directive as described below. [[directives]] ===== Directives @@ -447,7 +547,10 @@ These are the Cayenne directives used to customize SQLTemplate parsing and integ ====== #bind -Creates a PreparedStatement positional parameter in place of the directive, binding the value to it before statement execution. `#bind` is allowed in places where a "?" would be allowed in a PreparedStatement. And in such places it almost always makes sense to pass objects to the template via this or other forms of #bind instead of inserting them inline. +Creates a PreparedStatement positional parameter in place of the directive, binding the value to it before statement execution. +`#bind` is allowed in places where a "?" would be allowed in a PreparedStatement. +And in such places it almost always makes sense to pass objects to the template via this +or other forms of `#bind` instead of inserting them inline. Semantics: @@ -460,7 +563,9 @@ Semantics: Arguments: -- `value` - can either be a char constant or a variable that is resolved from the query parameters. Note that the variable can be a collection, that will be automatically expanded into a list of individual value bindings. This is useful for instance to build IN conditions. +- `value` - can either be a char constant or a variable that is resolved from the query parameters. +Note that the variable can be a collection, that will be automatically expanded into a list of individual value bindings. +This is useful for instance to build IN conditions. - `jdbcType` - is a JDBC data type of the parameter as defined in `java.sql.Types`. @@ -486,7 +591,12 @@ update ARTIST set NAME = #bind($name) where ID = #bind($id) ====== #bindEqual -Same as #bind, but also includes the "=" sign in front of the value binding. Look at the example below - we took the #bind example and replaced `"ID = #bind(..)"` with `"ID #bindEqual(..)"`. While it looks like a clumsy shortcut to eliminate the equal sign, the actual reason why this is useful is that it allows the value to be null. If the value is not null, `"= ?"` is generated, but if it is, the resulting chunk of the SQL would look like `"IS NULL"` and will be compilant with what the DB expects. +Same as #bind, but also includes the "=" sign in front of the value binding. +Look at the example below - we took the #bind example and replaced `"ID = #bind(..)"` with `"ID #bindEqual(..)"`. +While it looks like a clumsy shortcut to eliminate the equal sign, +the actual reason why this is useful is that it allows the value to be null. +If the value is not null, `"= ?"` is generated, but if it is, the resulting chunk of the SQL would look like `"IS NULL"` +and will be compilant with what the DB expects. Semantics: @@ -554,7 +664,11 @@ update ARTIST set NAME = #bind($name) where ID #bindEqual($id) ====== #bindObjectEqual -It can be tricky to use a Persistent object or an ObjectId in a binding, especially for tables with compound primary keys. This directive helps to handle such binding. It maps columns in the query to the names of Persistent object ID columns, extracts ID values from the object, and generates SQL like "COL1 = ? AND COL2 = ? ..." , binding positional parameters to ID values. It can also correctly handle null object. Also notice how we are specifying a Velocity array for multi-column PK. +It can be tricky to use a Persistent object or an ObjectId in a binding, +especially for tables with compound primary keys. This directive helps to handle such binding. +It maps columns in the query to the names of Persistent object ID columns, extracts ID values from the object, +and generates SQL like "COL1 = ? AND COL2 = ? ..." , binding positional parameters to ID values. +It can also correctly handle null object. Also notice how we are specifying an array for multi-column PK. Semantics: @@ -624,7 +738,9 @@ select.setParameters(Collections.singletonMap("a", a)); ====== #result -Renders a column in SELECT clause of a query and maps it to a key in the result DataRow. Also ensures the value read is of the correct type. This allows to create a DataRow (and ultimately - a persistent object) from an arbitrary ResultSet. +Renders a column in SELECT clause of a query and maps it to a key in the result DataRow. +Also ensures the value read is of the correct type. This allows to create a DataRow (and ultimately - a persistent object) +from an arbitrary ResultSet. Semantics: @@ -640,11 +756,16 @@ Arguments: - `column` - the name of the column to render in SQL SELECT clause. -- `javaType` - a fully-qualified Java class name for a given result column. For simplicity most common Java types used in JDBC can be specified without a package. These include all numeric types, primitives, String, SQL dates, BigDecimal and BigInteger. So `"#result('A' 'String')"`, `"#result('B' 'java.lang.String')"` and `"#result('C' 'int')"` are all valid +- `javaType` - a fully-qualified Java class name for a given result column. +For simplicity most common Java types used in JDBC can be specified without a package. +These include all numeric types, primitives, String, SQL dates, BigDecimal and BigInteger. +So `"#result('A' 'String')"`, `"#result('B' 'java.lang.String')"` and `"#result('C' 'int')"` are all valid - `alias` - specifies both the SQL alias of the column and the value key in the DataRow. If omitted, "column" value is used. -- `dataRowKey` - needed if SQL 'alias' is not appropriate as a DataRow key on the Cayenne side. One common case when this happens is when a DataRow retrieved from a query is mapped using joint prefetch keys (see below). In this case DataRow must use database path expressions for joint column keys, and their format is incompatible with most databases alias format. +- `dataRowKey` - needed if SQL 'alias' is not appropriate as a DataRow key on the Cayenne side. +One common case when this happens is when a DataRow retrieved from a query is mapped using joint prefetch keys (see below). +In this case DataRow must use database path expressions for joint column keys, and their format is incompatible with most databases alias format. Usage: @@ -669,7 +790,9 @@ NOTE: For advanced features you may look at the <<Apache Velocity extension>> ====== Mapping SQLTemplate Results -Here we'll discuss how to convert the data selected via SQLTemplate to some useable format, compatible with other query results. It can either be very simple or very complex, depending on the structure of the SQL, JDBC driver nature and the desired result structure. This section presents various tips and tricks dealing with result mapping. +Here we'll discuss how to convert the data selected via SQLTemplate to some useable format, compatible with other query results. +It can either be very simple or very complex, depending on the structure of the SQL, +JDBC driver nature and the desired result structure. This section presents various tips and tricks dealing with result mapping. By default SQLTemplate is expected to return a List of Persistent objects of its root type. This is the simple case: @@ -681,7 +804,9 @@ SQLTemplate query = new SQLTemplate(Artist.class, "SELECT * FROM ARTIST"); List<Artist> artists = context.performQuery(query); ---- -Just like SelectQuery, SQLTemplate can fetch DataRows. In fact DataRows option is very useful with SQLTemplate, as the result type most often than not does not represent a Cayenne entity, but instead may be some aggregated report or any other data whose object structure is opaque to Cayenne: +Just like SelectQuery, SQLTemplate can fetch DataRows. In fact DataRows option is very useful with SQLTemplate, +as the result type most often than not does not represent a Cayenne entity, +but instead may be some aggregated report or any other data whose object structure is opaque to Cayenne: [source, Java] ---- @@ -696,9 +821,16 @@ query.setFetchingDataRows(true); List<DataRow> rows = context.performQuery(query); ---- -In the example above, even though the query root is Artist. the result is a list of artist names with painting counts (as mentioned before in such case "root" is only used to find the DB to fetch against, but has no bearning on the result). The DataRows here are the most appropriate and desired result type. +In the example above, even though the query root is Artist. +The result is a list of artist names with painting counts (as mentioned before in such case "root" +is only used to find the DB to fetch against, but has no bearning on the result). +The DataRows here are the most appropriate and desired result type. -In a more advanced case you may decide to fetch a list of scalars or a list of Object[] with each array entry being either an entity or a scalar. You probably won't be doing this too often and it requires quite a lot of work to setup, but if you want your SQLTemplate to return results similar to EJBQLQuery, it is doable using SQLResult as described below: +In a more advanced case you may decide to fetch a list of scalars or a list of `Object[]` +with each array entry being either an entity or a scalar. +You probably won't be doing this too often and it requires quite a lot of work to setup, +but if you want your `SQLTemplate` to return results similar to `EJBQLQuery`, +it is doable using `SQLResult` as described below: [source, Java] ---- @@ -734,7 +866,11 @@ query.setResult(resultDescriptor); List<Object[]> data = context.performQuery(query); ---- -Another trick related to mapping result sets is making Cayenne recognize prefetched entities in the result set. This emulates "joint" prefetching of SelectQuery, and is achieved by special column naming. Columns belonging to the "root" entity of the query should use unqualified names corresponding to the root DbEntity columns. For each related entity column names must be prefixed with relationship name and a dot (e.g. "toArtist.ID"). Column naming can be controlled with "#result" directive: +Another trick related to mapping result sets is making Cayenne recognize prefetched entities in the result set. +This emulates "joint" prefetching of `SelectQuery`, and is achieved by special column naming. +Columns belonging to the "root" entity of the query should use unqualified names corresponding to the root `DbEntity` columns. +For each related entity column names must be prefixed with relationship name and a dot (e.g. "toArtist.ID"). +Column naming can be controlled with `#result` directive: [source, Java] ---- @@ -754,9 +890,20 @@ q.addPrefetch(Artist.PAINTINGS_PROPERTY) List<Artist> objects = context.performQuery(query); ---- -And the final tip deals with capitalization of the DataRow keys. Queries like `"SELECT * FROM..."` and even `"SELECT COLUMN1, COLUMN2, ... FROM ..."` can sometimes result in Cayenne exceptions on attempts to convert fetched DataRows to objects. Essentially any query that is not using a `#result` directive to describe the result set is prone to this problem, as different databases may produce different capitalization of the java.sql.ResultSet columns. +And the final tip deals with capitalization of the DataRow keys. Queries like `"SELECT * FROM..."` +and even `"SELECT COLUMN1, COLUMN2, ... FROM ..."` can sometimes result in Cayenne exceptions +on attempts to convert fetched DataRows to objects. +Essentially any query that is not using a `#result` directive to describe the result set is prone to this problem, +as different databases may produce different capitalization of the `java.sql.ResultSet` columns. -The most universal way to address this issue is to describe each column explicitly in the SQLTemplate via `#result`, e.g.: `"SELECT #result('column1'), #result('column2'), .."`. However this quickly becomes impractical for tables with lots of columns. For such cases Cayenne provides a shortcut based on the fact that an ORM mapping usually follows some naming convention for the column names. Simply put, for case-insensitive databases developers normally use either all lowercase or all uppercase column names. Here is the API that takes advantage of that user knowledge and forces Cayenne to follow a given naming convention for the DataRow keys (this is also available as a dropdown in the Modeler): +The most universal way to address this issue is to describe each column explicitly in the SQLTemplate via `#result`, +e.g.: `"SELECT #result('column1'), #result('column2'), .."`. +However this quickly becomes impractical for tables with lots of columns. +For such cases Cayenne provides a shortcut based on the fact that an ORM mapping usually follows some naming convention +for the column names. Simply put, for case-insensitive databases developers +normally use either all lowercase or all uppercase column names. +Here is the API that takes advantage of that user knowledge and forces Cayenne to follow +a given naming convention for the DataRow keys (this is also available as a dropdown in the Modeler): [source, Java] ---- @@ -774,7 +921,10 @@ query.setColumnNamesCapitalization(CapsStrategy.UPPER); List objects = context.performQuery(query); ---- -None of this affects the generated SQL, but the resulting DataRows are using correct capitalization. Note that you probably shouldn't bother with this unless you are getting CayenneRuntimeExceptions when fetching with SQLTemplate. +None of this affects the generated SQL, but the resulting DataRows are using correct capitalization. + +NOTE: You probably shouldn't bother with this unless you are getting +`CayenneRuntimeExceptions` when fetching with `SQLTemplate`.
