public interface CustomQuerying
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
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
<selectClause>, and the other clause-by-clause replacement features.
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
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
<operationBinding operationType="fetch"> <whereClause> continent = $criteria.continent AND population > $criteria.minPop </whereClause> </operationBinding>
default subclausesgenerated 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>
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().
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.
DataSourceField.customSQLfor an overview.
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:
DataSource.combineCriteria()to add your additional criteria to an existing AdvancedCriteria, wherever this is convenient
OperationBinding.customCriteriaFieldsto prevent the default SQL for this field from being generated
Criterionthat 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.
customSQLclause, 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.
whereClausewould produce different output depending on whether the request criteria included a value for the field
<whereClause>$defaultWhereClause #if ($criteria.someField) AND someDatabaseField = $criteria.someField #end</whereClause>
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).
$rawValuein 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>.
SELECT orderNumber FROM Order
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:
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` if column quoting is enabled for that database (it generally isn't required, since MySQL preserves case by default).