com.smartgwt.client.docs
Interface CustomQuerying


public interface CustomQuerying

Custom Querying Overview

The SmartGWT server provides a number of ways to let you customize the SQL or Hibernate query it generates to fetch data from or update your database. You can provide full custom queries in either 'SQL' or 'HQL', or you can replace individual parts of the query ('the WHERE clause', for example) while letting SmartGWT generate the rest. Full custom queries provide complete flexibility, but they cannot be used for automatic data paging; if you use a full custom query, all of its data will be returned to the client in one shot. This may, of course, be perfectly reasonable, depending on the number of rows involved. However, if you need automatic paging support, you should try to implement your customizations by replacing pieces of the query.

Query customization is done per OperationBinding, so it is entirely feasible to provide multiple custom queries for the same 'operation type' on a given DataSource.

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 or HQL code using the Velocity template language. You can refer to container variables $criteria and $values in your queries or clause snippets, and SmartGWT will insert the appropriate values. A simple 'whereClause' example:

<whereClause>continent = $critieria.continent AND population > $criteria.minPop</whereClause>

Please see com.smartgwt.client.docs.VelocitySupport for full details of accessing SmartGWT Server context variables using Velocity.

Other template variables

In addition to the Velocity template variables described above, we also provide a number of template variables containing generally-useful values. Please see com.smartgwt.client.docs.VelocitySupport for details.

Using the default clauses

You also have access to the com.smartgwt.client..DefaultQueryClause generated by SmartGWT. You can use these in full custom queries to allow a certain part of the query code to be generated:

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

You can also use them, with care, as a foundation for your own additions:

SELECT $defaultSelectClause, foo, bar FROM $defaultTableClause WHERE ($defaultWhereClause) AND baz > $criteria.baz.

You can also use them within query snippets in the various subclause properties:

<selectClause>$defaultSelectClause, foo, bar</selectClause>

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>
 

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 com.smartgwt.client.docs.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 SmartGWT 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 These differences are one barrier to writing SQL that is portable from one database product to another. There are two ways to work around them.

Firstly, you can simply accept the database's default behavior. So, your table would contain a column called "orderNumber" on MySQL, "ORDERNUMBER" on Oracle and "ordernumber" on PostgreSQL (note that you can still use mixed-case DataSource field names; SmartGWT will map DataSource field "orderNumber" to Oracle column "ORDERNUMBER" transparently). This is the simplest 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 will 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:

The first of these is not portable. It will work fine in HSQL and Oracle, but will fail with a syntax error in MySQL, amongst others, 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:
com.smartgwt.client.docs.serverds.OperationBinding#selectClause, com.smartgwt.client.docs.serverds.OperationBinding#tableClause, com.smartgwt.client.docs.serverds.OperationBinding#whereClause, com.smartgwt.client.docs.serverds.OperationBinding#groupClause, com.smartgwt.client.docs.serverds.OperationBinding#orderClause, com.smartgwt.client.docs.serverds.OperationBinding#valuesClause, com.smartgwt.client.docs.serverds.OperationBinding#customSQL, com.smartgwt.client.docs.serverds.OperationBinding#customHQL, com.smartgwt.client.docs.serverds.OperationBinding#customValueFields, com.smartgwt.client.docs.serverds.OperationBinding#customCriteriaFields, com.smartgwt.client.docs.serverds.OperationBinding#excludeCriteriaFields, com.smartgwt.client.docs.serverds.OperationBinding#useForCacheSync, com.smartgwt.client.docs.serverds.OperationBinding#cacheSyncOperation, com.smartgwt.client.docs.serverds.OperationBinding#canSyncCache, com.smartgwt.client.data.OperationBinding#getSqlType, com.smartgwt.client..DefaultQueryClause, com.smartgwt.client..SQLType