Sorting a Dojo DataGrid with the Play! Framework

The dojox.grid.DataGrid widget from the Dojo Javascript toolkit will make a RESTful call in response to the clicking of a DataGrid's header.  If you're using the Play! Framework, form a JPA query from the querystring and call the Controller's renderJSON() method.  Watch for SQL injection attacks on the ORDER BY clause.

The dojox.grid.DataGrid component displays a spreadsheet-like control in a web page.  DataGrid is data-aware which means that it's backed by a model component called a Store.  As changes are made to the DataGrid, the Store is also modified which results in a call to server-side processing.

A Dojo DataGrid

One type of a Store is the dojox.data.JsonRestStore.  This backs up a DataGrid with a RESTful web service.  In the Play! Framework, the RESTful web service is implemented with a Controller method that uses the convenience method renderJSON(), possibly enhanced with custom behavior.

Hooking Up the Store

In Dojo, the JsonRestStore is created in a Javascript with a target and an idAttribute as follows

var step_store =dojox.data.JsonRestStore({
   target: "/steps/${params.proc_id}",
   idAttribute: "step_id"
});


The Store 'step_store' is connected to  the DataGrid component like this in a following declaration.

var grid4 = new dojox.grid.DataGrid({
   store: step_store,
   rowSelector: '30px',
   structure: layout4,
   autoHeight: true
   },
   document.createElement('div'));


The target URL is constructed using a 'proc_id' which is a parent identifier for a Step.  A Step is an entity with fields Display Number, Description, Title, Text, and step_id.  step_id is the primary key.

In the Play! routes file, /steps/{proc_id} is mapped to a controller method listSteps() as follows.

GET /steps/{proc_id}/ Application.listSteps


Implementing the Store

The listSteps() method is a Controller method that will call a JPA query and return the results using the inherited renderJSON().  For this particular example, renderJSON() is supplemented with a custom JsonSerializer.

Controller Implementation of JsonRestStore

When the page first loads, listSteps() is called unparameterized.  When the user selects a header on the DataGrid on which to sort ("#", "Step Title", etc.), listSteps() is called with a special QUERY_STRING.  In this second case, the QUERY_STRING will include a sort parameter of the form ?sort(+display_seq_nb) for an ascending sort on the field 'display_seq_nb'.

The QUERY_STRING is retrieved by the Play! variable 'request.querystring'.  Several Commons Lang StringUtils methods are called to parse the QUERY_String.  If the sort() argument wasn't provided, the default sort on display_seq_nb is enacted.

Security

Although I could have formed an ORDER BY statement like "ORDER BY step_title_tx ASC" from a sort parameter like "sort(+step_title_tx)", I'm using another structure to prevent a SQL Injection attack that could return details about the underlying SQL query.  The attack I'm referring involves repeated calls like ?sort(7) to eventually guess the number of columns in the SQL query.  The map used in the Controller method replaces programming logic.

The dojox.grid.DataGrid widget is used for editing a block of data from within a web page.  The widget is data aware which means that it's backed by a Store, in this case a collection of RESTful web service calls.  When building the basic GET call, add in functionality to handle Dojo's ?sort(fieldname) query string.  Watch for SQL injection attacks when forming JPQL from Strings.