com.smartgwt.client.docs

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.

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

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>
  

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
• 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).

See Also:

DefaultQueryClause, SQLType, OperationBinding.selectClause, OperationBinding.tableClause, OperationBinding.ansiJoinClause, OperationBinding.whereClause, OperationBinding.groupClause, OperationBinding.orderClause, OperationBinding.valuesClause, OperationBinding.customSQL, OperationBinding.customHQL, OperationBinding.customJQL, OperationBinding.namedQuery, OperationBinding.customFields, OperationBinding.customValueFields, OperationBinding.customCriteriaFields, OperationBinding.excludeCriteriaFields, OperationBinding.useForCacheSync, OperationBinding.cacheSyncOperation, OperationBinding.canSyncCache, OperationBinding.makeKeysAvailable, OperationBinding.sqlType