Interface CustomQuerying

public interface CustomQuerying

Custom Querying Overview

This feature, called "SQL Templating", is available with Power or better licenses
only. See smartclient.com/product for details.

The Smart GWT server provides a number of ways to let you customize the SQL, JPA or Hibernate
query it generates to fetch data from or update your database. You can provide full
custom queries in either SQL, JQL or
HQL, or you can replace
individual parts of the query
(the WHERE clause, for
example) while letting
Smart GWT generate the rest.

Full custom queries specified via <customSQL> provide complete flexibility, but
they cannot be used for automatic data paging; if you use a full custom query, all data
returned by the query will be delivered to the client, which may be inefficient.
To retain automatic data paging, implement your customizations by replacing just specific
clauses of the query, via <whereClause>,
<selectClause>,
and the other clause-by-clause
replacement features.

Using criteria and values

Whether using full custom queries or individual clauses, your code has access to the
criteria values submitted with the operation; if this is an "add" or "update" operation,
it also has access to the new field values sent from the client.

Fields are accessed in your SQL, JQL or HQL code using the Velocity template language. You
can refer to container variables $criteria and $values in your queries or
clause snippets, and Smart GWT will insert the appropriate values. A simple
whereClause example:

In addition to the $criteria and $values Velocity template variables described above, we
also provide a number of template variables containing generally-useful values. Please see
VelocitySupport for details.

Using the default clauses

You also have access to the default
subclauses generated by
Smart GWT. You can use these in full custom queries to allow a certain part of the query
code to be generated:

You can also use them within individual clauses in order to customize a clause without
losing default SQL generation:

<whereClause>
($defaultWhereClause) AND foo > 5
</whereClause>

Mixing SQL Templating and custom Java Logic

You can use both SQL Templating and custom Java logic added via DMI in the same
operationBinding. Your DMI method is called before SQL is generated, and the SQL template
will be evalauted and the actual SQL operation performed only when you call
dsRequest.execute(). Or, you can use a custom DataSource
that extends SQLDataSource; your overrides of, eg, executeFetch()
will be called, and the SQL operation performed only when you call, eg,
super.executeFetch(dsRequest)

This allows you to modify the criteria or values on the DSRequest, which will change the
values retrieved by $criteria and $values when the SQL Template is evaluated. You can also
add entirely new information to the Velocity context used to evaluate the template, via
the server-side API DSRequest.addToTemplateContext().

User-defined snippets

Using the aforementioned addToTemplateContext() method, it is possible to
create your own library of SQL snippets and reuse them in your customized DataSources. You
do this by adding your text to the template context as a snippet and referencing it in your
custom SQL. Objects added as snippets are automatically passed through Velocity evaluation
immediately before the main evaluation, so they can contain variable references and those
references will be correctly resolved. For example, consider this simple custom DataSource
Java and .ds.xml file:

public class MyCustomDS extends SQLDataSource {
public DSResponse executeFetch(DSRequest req) throws Exception {
// Add a clause to restrict the fetch. In real life this would probably be
// read from a file rather than being hard-coded into a program
req.addToTemplateContext("mySnippet", "continent = $criteria.currentContinent", true);
return super.executeFetch(req);
}
}
<operationBinding operationType="fetch"/>
<whereClause>$defaultWhereClause AND $mySnippet</whereClause>
</operationBinding>

Note that the snippet refers to the $criteria context variable. If this were
added to the template context as a regular context variable, the generated SQL would contain
the literal text "$criteria.currentContinent". However, if is added to the context as a
snippet (third parameter is true), the variable reference will be correctly
resolved and quoted, leading to generated SQL like:

... WHERE someOtherField = 27 AND continent = 'Europe'

Excluding fields from SQL Generation

In some cases you pass a value to the server which is intended to be used by custom Java
logic and should not cause SQL to be generated. To prevent all SQL from being generated for
a particular field, set customSQL="true" on that field.

Any field for which SQL will ever be generated must be declared in a DataSource. It's
common to have a field which is only used in one or two operationBindings - in this case,
set customSQL="true" on the field, and use OperationBinding.customFields
to cause
specific operationBindings to generate SQL for the field, while all others ignore it.

In other cases you want to hand-write SQL for a particular field for a specific
operationBinding. You can set OperationBinding.excludeCriteriaFields to
exclude fields from SQL generation for the whereClause of a specific operationBinding.

Field-level SQL Customization

An individual field can configured with custom expressions to be used in different SQL
statements for all operationBindings - see DataSourceField.customSQL for an
overview.

Using AdvancedCriteria

The above examples involving $criteria assume that the submitted criteria are simple
Criteria and not AdvancedCriteria, a more sophisticated
criteria format in which
different search operators can be specified per field and criteria can be nested.

The special variable $advancedCriteria provides simplified access to the AdvancedCriteria
structure: $advancedCriteria.fieldName will return the criteria value specified for a
given fieldName, regardless of where it's present in the AdvancedCriteria.

This makes it straightforward to add an additional criteria value to AdvancedCriteria that
you want to use only in the SQL template:

make a simple Criteria object representing the fieldName and value name you need to have
available in the SQL template

Java API dsRequest.getCriteriaValue() is equivalent to accessing $advancedCriteria in a SQL
Template. Also note, if a given fieldName appears more than once in AdvancedCriteria,
$advancedCriteria.fieldName will return the value for the first Criterion that
uses the fieldName, as found by depth-first search.

NOTE: $advancedCriteria falls back to simple criteria values if the current criteria object
is not an AdvancedCriteria. This means that you can safely use $advancedCriteria
in circumstances where you cannot predict in advance whether your server code will be handed
a simple criteria or an AdvancedCriteria.

Stored procedures

It is possible to include templated calls to SQL stored procedures in a
customSQL clause, for the
ultimate in flexibility. For
example, the deletion of an order might require a number of actions: deletion of the order
record itself, messages sent to other systems (data warehousing, maybe, or a central accounts
system running on a mainframe), an event log written, and so on. You could write a stored
procedure to do all this, and then invoke it with a customSQL clause:

When calling stored procedures this way, be sure that the <customSQL> operates like a
normal SQL statement, so that it can be called via normal JDBC calls. For
operationType="fetch", the JDBC API PreparedStatement.executeQuery() is called and expects a
ResultSet returned. For "update", "add" and "remove" operationTypes,
PreparedStatement.executeUpdate() is called and expects an integer return value (number of
rows affected). If your stored procedure uses in/out parameters, returns something other
than a ResultSet or affected row count, or in some other way is incompatble with the
standard JDBC methods described above, you may need to add code to adjust the return values
to what JDBC expects. You can do this by either putting code into the <customSQL>
block directly, or by adding a second stored procedure that transforms the outputs of the
first procedure.

Named Queries

With JPA and Hibernate datasources it is possible to use named queries. The query should be
declared using the Java Persistence API or Hibernate API and then referenced in the datasource
definition on an operationBinding. Named queries support value substitution in order to build
dynamic queries. For more information see OperationBinding.namedQuery.

Velocity Template Language conditional logic

When writing a customized SQL clause for an operation, it is commonly desirable to be
able to include conditional logic - for example only modifying a where clause if a
certain criteria value is present. Velocity template language conditional statements
can be embedded directly into your template code to achieve this. For example the following
whereClause would produce different output depending on whether the
request criteria included a value for the field someField:

If criteria.someField was not present in the request, the generated
SQL statement would simply use the default where clause -- otherwise
AND someDatabaseField = [some value] would be appended to it (where
[some value] was picked up from the value of someField on
the request criteria object).

Custom queries are safe

Custom queries are protected from
SQL injection attacks, because anything coming from the client is quoted and escaped
in accordance with the syntax of the underlying database before use (though see the warning
about using $rawValue in the article on VelocitySupport).
So, in a typical SQL injection attack an attacker might enter his User ID as 123' OR '1' = '1

in the hope that this will generate a query
with a where clause like thisWHERE userID = '123' OR '1' = '1'

which would of course return every row. With Smart GWT custom queries, this does not happen;
the client-provided string is escaped, and the resultant clause would look like this: WHERE userID = '123'' OR ''1'' = ''1'

This clause only returns those records where the userID column contains the literal value that
the user typed: 123' OR '1' = '1

Further, custom queries can be protected from buggy or ad-hoc client requests because the
query is specified on the server. For example you could add a custom where clause, as shown
in the above section on default clauses, to ensure that certain records are never seen by
the client. For instance:

Column case-sensitivity issues

Different database products have different rules concerning case-sensitivity in column
names. Consider the following query:

SELECT orderNumber FROM Order

MySQL and Microsoft SQL Server are not case-sensitive with regard to column names, so
this query will work whether the column is called "orderNumber" or "ORDERNUMBER" or any
other variation.

Oracle, HSQLDB and DB2 default to upper-case column names. Therefore, this query will
fail if the column is actually called "orderNumber"; it will only work if the underlying
column name is "ORDERNUMBER"

PostgreSQL and Informix default to lower-case column names, so this query will fail
unless the underlying column name is actually "ordernumber"

Note that these differences only apply in a practical sense if the underlying database
table was created with quoted column names. If you create your tables without quoting
column names, the database creates the columns using its own preferred defaults, which
is what it will also use when it encounters an unquoted column name in a query. Behind
the scenes, the differences are still there - your column will be called "ORDERNUMBER"
on Oracle and "ordernumber" on PostgreSQL - but that wouldn't be apparent unless you went
looking for it: the example query would work unchanged with both products, and you would
be able to use whatever mixture of case you like in your DataSource field names
(Smart GWT will map DataSource field "orderNumber" to Oracle column "ORDERNUMBER"
transparently). This is the recommended approach.

If you can't, or don't want to, accept the database default - if you are working with an
existing schema, for example - then you will need to quote column names in your queries.
Unfortunately, the way you do this also differs by database product, so quoting a column
name correctly in one database's syntax may mean that the query cannot be ported to a
different database without change.

To help with this case, we provide two extra container variables that you can use.
$fields contains the names of all the fields in your DataSource, but quoted in
accordance with the column-quoting rules of the target database. $qfields also
contains a list of field names, but in this case each one is qualified with its table
name.

As an example of how to use $fields and $qfields, consider a DataSource with
a field called "itemID", bound to a column also called "itemID", and a tableName property
of "orderItem". Here are three ways to write a selectClause
for a custom SQL query that returns that field:

orderItem."itemID"

orderItem.$fields.itemID

$qfields.itemID

The first of these is not portable. It will work fine in HSQL and Oracle, but will fail
with a syntax error in MySQL because you quote a field name with backticks in MySQL, not
quote marks.

The usages via $fields and $qfieldsare portable. The second line,
when targeting Oracle, will be translated to orderItem."itemID"; when targeting
MySQL, it will be translated to orderItem.itemID, or
orderItem.`itemID`
if column quoting is enabled for that database (it generally isn't required, since MySQL
preserves case by default).