Ajax DataTables with the Play framework 1.2

This article is a follow-on to Integrating Play framework with jQuery DataTables, which describes how to integrate DataTables with a Play framework 1.2 web application. This time, we show how to load the table data from the server using Ajax, rather than decorating an existing (populated) HTML table.

Architectural considerations

In the case that generating HTML on the server for the entire data set is a problem, the more complex alternative is to use Ajax to fetch the data in separate HTTP requests that do not fetch the entire data set. For example, each request could fetch only data for the 15 rows that are currently displayed.

However, before launching into what could be a premature optimisation, it is worth considering the numbers. This article uses the example of a table of time zones in two languages: this has four columns and 1216 rows, resulting in a 1.1 MB HTML document. This almost certainly too big. However, the generated HTML contains a lot of white-space; if you use a copy of the CRUD #{table} tag - ${play.path}/modules/crud/app/views/tags/crud/table.html - and remove the leading white-space on each line as well as line breaks inside table cells, then the HTML document size drops to 160 KB, which is not ‘too big’ in many cases.

Loading the entire table from a single Ajax request improves this situation further in two ways. First, the data is loaded from a separate asynchronous HTTP, which can happen partly in parallel, resulting in a faster overall page load. Second, the total size of the data is less: 2 KB of HTML and 69 KB of JSON - less than half the previous total. Again, this is still without paging, where you load even less data at a time, in separate requests.

Serve the table data in JSON format

At this point all of the functionality works. However, the CRUD application now serves the entire set of model data objects as an HTML document in one go, which means a single large data transfer. If you do not want to do this then you can take advantage of DataTables’ support for Ajax loading, so that you only load one or two pages of data at a time.

The first step is to make the table data available in JSON format, for Ajax calls. To do this, add a new crud-datatables/app/controllers/CrudJson.java controller to your Play module:

package controllers;

import java.lang.reflect.Field;
import java.util.List;

import play.Logger;
import play.db.Model;


public class CrudJson extends CRUD {

   /**
   * Controller method for DataTables, based on
   * {@link CRUD#list(int, String, String, String, String)}
   * with unused request parameters.
   */
   public static void listJson(int page, String search, String searchFields,
      String orderBy, String order) {
     ObjectType type = ObjectType.get(getControllerClass());
     notFoundIfNull(type);
     if (page < 1) {
      page = 1;
     }
     final List<Model> objects = type.findPage(page, search, searchFields,
      orderBy, order, (String) request.args.get("where"));
     renderJSON(new DataTablesSource(type, objects));
   }

   /**
   * JSON wrapper for a list of model objects, for use by DataTables.
   */
   private static class DataTablesSource {

      private final String[][] aaData;
      private Long iTotalRecords; // TODO: set to total records, before filtering
      private Long iTotalDisplayRecords; // TODO: set to total records, after filtering
      private Long sEcho; // TODO: set to pass-through request parameter

      public DataTablesSource(final ObjectType type, final List objects) {

         this.aaData = new String[objects.size()][type.getFields().size()];
         for (int column = 0; column < type.getFields().size(); column++) {
            final ObjectType.ObjectField objectField = type.getFields().get(column);
            for (int rowIndex = 0; rowIndex < objects.size(); rowIndex++) {
               try {
                  final Field field = type.entityClass.getField(objectField.name);
                  final String value = field.get(objects.get(rowIndex)).toString()
                  aaData[rowIndex][column] = value;
               }
               catch (Exception e) {
                  Logger.error("Could not read field " + objectField.name);
               }
            }
         }
      }
   }
}

To use the new controller, add a corresponding route for each CRUD controller in crud-datatables/conf/routes

#{crud.types}
GET /${type.controllerName}.json ${type.controllerClass.name.substring(12).replace('$','')}.listJson
#{/crud.types}

… and change the example application’s controller to use the new JSON controller as its super-class:

package controllers;

public class LocalisedTimeZones extends CrudJson {

}

The JSON-formatted data is now available from http://localhost:9000/localisedtimezones.json

Load the table from Ajax requests

To change the view template to load this JSON data we need to do two things: change the DataTables configuration, and remove the generated HTML table in crud-datatables-example/app/views/CRUD/list.html. First, replace the DataTables initialisation script:

<script type="text/javascript" charset="utf-8">
  $(document).ready(function() {
     $('#crudListTable table').dataTable( {
        "sAjaxSource": '${type.controllerName}.json',
        aoColumns: [
           #{list items:type.fields*.name, as:'field'}
              { sTitle: "${field}" },
           #{/list}
        ]
     });
  });
</script>

The call to the DataTables jQuery plug-in now includes two options: sAjaxSource specifies the relative URL of the JSON data, and aoColumns provides the column names, for which we use a Play template tag to generate a list of field names from the CRUD type.

Next, replace the crud.table template tag with an empty HTML table, which DataTables will populate:

<div id="crudListTable">
   <table></table>
</div>

The table now loads from a single Ajax request.

Add paging, sorting and search to the Ajax requests

To make paging, sorting and search work, we have to parse the relevant parameters from the Ajax HTTP request that data tables sends, and set the appropriate parameters in the call to the CRUD.ObjectType.findPage method. The relevant DataTables parameters, described in the server-side processing documentation, are:

iDisplayStart
iDisplayLength
iSortingCols
iSortCol_0
sSortDir_0

The last two sorting parameters’ names end with the zero-based column index. DataTables supports multi-column sorting, and sends additional ‘column’ and ‘direction’ request parameters whose name ends with an index for each sort column, e.g. iSortCol_1 and iSortCol_1 for a second sort column.

To parse these parameters into the form that the CRUD module needs, we can use a new inner class in the crud-datatables/app/controllers/CrudJson.java controller:

public static class DataTablesParameters {
  private final ObjectType type;
  public int page;
  public Integer pageSize;
  public String search;
  public String orderBy;
  public String order;

  public DataTablesParameters(final ObjectType type, final Request request) {
     this.type = type;

     // Paging
     final Integer startRow = request.params.get("iDisplayStart", Integer.class);
     this.pageSize = request.params.get("iDisplayLength", Integer.class);
     this.page = startRow == null ? 1 : (startRow / this.pageSize) + 1;

     // Sorting (first sort column only).
     final Integer sortingColumns = request.params.get("iSortingCols", Integer.class);
     if (sortingColumns != null) {
        final int sortColumnIndex = request.params.get("iSortCol_0", Integer.class);
        final String sortDirection = request.params.get("sSortDir_0");
        this.orderBy = type.getFields().get(sortColumnIndex).name;
        this.order = sortDirection == null ? null : sortDirection.toUpperCase();
     }

     // Search
     this.search = request.params.get("sSearch");
  }
}

We can now change the controller’s listJson method:

public static void listJson() {
   ObjectType type = ObjectType.get(getControllerClass());
   notFoundIfNull(type);

   final DataTablesParameters dtp = new DataTablesParameters(type, request);

   // Hack: set the page size for the whole application.
   if (dtp.pageSize != null) {
      Play.configuration.setProperty("crud.pageSize", String.valueOf(dtp.pageSize));
   }

   final Long totalCount = type.count(null, null, null);
   final List<Model> objects = type.findPage(dtp.page, dtp.search, null, dtp.orderBy, dtp.order, null);
   renderJSON(new DataTablesSource(type, totalCount, objects));
}

Note that we pass a null to the findPage method’s searchField argument, which means that the search string will search all fields, which is what we want.

Unfortunately, the CRUD.ObjectType.findPage method does not have a parameter for the page size, so this code includes a nasty hack to change the Play configuration’s crud.pageSize parameter. You do not want to do this for multiple users.

Finally, although DataTables supports multi-column sorting, we are only getting the first sort column because the findPage method’s orderBy and order arguments only work for a single column.

The fix for the last two issues is to construct your own JPA query, based on the CRUD.ObjectType.findPage method’s implementation. This is left as an exercise for the reader.

Next steps

The above examples are enough to get an idea of how you can integrate Play with DataTables’ JavaScript API. However, you can take this further by supporting additional DataTables features, such as:

multi-column sorting (mentioned above)
individual column filtering - ‘advanced search’
pipelining data to fetch more than one page at a time, to reduce the number of Ajax requests.