|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |
public interface CustomQuerying
'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
.
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.
com.smartgwt.client.docs.VelocitySupport
for details.
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>
'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>
$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>
.
SELECT orderNumber FROM Order
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:
orderItem."itemID"
orderItem.$fields.itemID
$qfields.itemID
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).
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
|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |