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.

For DataSources with serverType set to "sql", "hibernate" or "jpa", you can provide full custom queries via OperationBinding.customSQL.
Hibernate dataSources also support custom HQL queries via OperationBinding.customHQL, and JPA dataSources also support custom JQL queries via OperationBinding.customJQL.

For DataSources with serverType set to "sql" you can replace individual parts of the query (the WHERE clause, for example) while letting Smart GWT generate the rest. The default SQL clauses are available as variables ($defaultWhereClause, $defaultOrderClause, etc).
This feature is not available for JPA or Hibernate dataSources.

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.

Query customization is done per OperationBinding, so you can create multiple customized queries per DataSource or even for the same operation type.

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:

   <operationBinding operationType="fetch">
      <whereClause>
          continent = $criteria.continent AND population > $criteria.minPop
      </whereClause>
   </operationBinding>
  

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

In SQL dataSources, 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:

  <customSQL>
     SELECT foo, bar FROM $defaultTableClause 
         WHERE baz > $criteria.baz
  </customSQL>
  

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>
  

Server Summaries & SQL Templating

When using the Server Summaries feature to get aggregated results, there are additional automatically generated and replaceable clauses groupClause and groupWhereClause. See the "SQL Templating & Aggregation" section of the Server Summaries overview for more information.

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 evaluated 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().

Note that you can also retrieve either individual SQL clauses or entire SQL queries via SQLDataSource.getSQLClause(), which ensures that you never have to repeat any SQL that the framework can generate, even for a heavily customized query in which parts of the SQL are generated by your own custom Java code. You can even create a new DSRequest server-side and call getSQLClause() to retrieve the SQL that such a request would generate, which might be used as a sub-select in a larger overall query.

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
• use DataSource.combineCriteria() to add your additional criteria to an existing AdvancedCriteria, wherever this is convenient
• list the fieldName in OperationBinding.customCriteriaFields to prevent the default SQL for this field from being generated
• use $advancedCriteria in your customized SQL to access the value

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:

     <operationBinding operationType="remove">
         <customSQL>call deleteOrder($criteria.orderNo)</customSQL>
     </operationBinding>
  

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:

<whereClause>$defaultWhereClause #if ($criteria.someField) AND someDatabaseField = $criteria.someField #end</whereClause>

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 this
  WHERE 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:

<whereClause>($defaultWhereClause) AND confidential = '0'</whereClause>.

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 $qfields are 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).

Automatically generated table aliases

Some Smartclient features use SQL table aliases in the generated SQL when generating multiple SQL joins to the same SQL table. When using SQL Templating, it's sometimes necessary to know the names of the aliases in the generated SQL. Smartclient generates consistent and predictable SQL table aliases, so they can be safely relied on when used in SQL Templating.

Generated aliases depend on the way DataSources declare their relationship and on the context this relationship is used in, including usages where aliases are not generated at all. The relationship between two directly related DataSources has two sides:

• the original "from" DataSource, which is on "this side" of relation
• the related "to" DataSource, which is on the "other side" of relation.

This also applies to the DataSource which might be in the middle of the relations chain, for example in case of indirect inclusion. For any relation in the established chain of relations there's always "from" and "to" DataSources. All rules below are described from the "from" DataSource point of view and do apply to any relation.

Also, if in a chain of relations there's relation that requires an alias, then all subsequent relations will be forced to use an alias and use the prior generated aliases as a prefix. This results in a chain of aliases concatenated by underscore symbol: "<alias1>_<alias2>_<alias3>". For more details on such complex scenarios see DataSourceField.includeVia and DataSourceField.otherFKs features, but general rules described here are always respected.

For the DataSource joined via DataSourceField.foreignKey declared on "this side" of relation the SQL table alias will be:

• DataSourceField.relatedTableAlias if it is set (always forces alias usage)
• DataSourceField.name of the field with foreignKey set if it is referenced by the DataSourceField.includeVia feature, or if the DataSource is in the middle of the relations chain and the alias has already started to build up
• absent (no alias will be used) if none of the above is true, i.e. SQL table will be referenced directly by the DataSource.tableName in generated SQL

For the DataSource joined via the DataSourceField.foreignKey declared on the "other side" of relation or via the DataSourceField.otherFKs the SQL table alias will always consists of two parts, like "<field>_<datasource>", where:

• "<field>" is the DataSourceField.name (or DataSourceField.relatedTableAlias if set) of the field the relation is based on, which can be:
  - the "from" DataSource field that is linked by the "to" DataSource field with the foreignKey on the "other side" of the relation OR
  - the "from" DataSource field with the DataSourceField.otherFKs if one of the otherFKs sets was used to establish the relation
• <datasource>" is referenced by the "to" DataSource ID (or DataSource.relatedTableAlias if set)

Note that databases have their limitations for the length of identifier names. We limit table aliases according to the current database as described in SQL Settings overview (search for aliasLengthLimit settings). If the generated table alias would exceed the length limit, we instead use a generated and unpredictable value like "a123". To avoid hitting this limit:

• use relatively short strings for relatedTableAlias
• for rare, multi-step inclusion scenarios where multiple field names are used as a prefix, if field names are very long in order to match database column names, you can use a shorter field name and use DataSourceField.nativeName to specify the underlying column name (this is demonstrated in DataSourceField.includeVia samples - search for "moneyTransferPreviousId")

Auto-generated joins & customized SQL

The Smartclient SQL engine generally seeks to reduce the amount of auto generated SQL joins to the bare minimum required to perform the requested query. Features like DSRequest.outputs and ServerSummaries may directly or indirectly reduce the amount of requested fields, which allows SQL engine to optimize the SQL query by fetching only fields requested and auto generating only SQL joins required for those fields avoiding joining unnecessary tables. Although if a field requiring joins would not be requested, but would be present in Criteria, SQL engine would still auto generate required joins.

There's a possibility to use customized SQL together with auto generated joins, for example includeFrom + customSelectExpression usage when SQL joins would be generated according to the includeFrom attribute, but still the customSelectExpression would be used in the select clause. In the example below "currencySymbol" could be the only requested field and SQL query would have auto-joined currency table using "source" alias and customSelectExpression in select clause:

  <field name="currencyId" foreignKey="currency.id" relatedTableAlias="source"/>
  <field name="currencySymbol" type="text"
           customSelectExpression="CONCAT(source.code,' - ',source.symbol)"
           includeFrom="currency.symbol" />