Spring MVC with restful DataTables

Up until recently I’ve been using jqGrid for the app I’m working on but I wanted to re-work the UI to make use of twitter bootstrap. I’m not a graphic designer and TB certainly gives you a polished looking app with minimal effort. After some research it seemed that jQuerys DataTables plugin had better UI support for TB than jqGrid. jqGrid is reliant on jQuery UI for its look and feel which as of yet doesn’t have a complete TB theme (though there is work in progress).

If you want to know how to get DataTables displaying in a TB style then the main website has a good walkthrough. It’s really not too difficult. http://datatables.net/blog/Twitter_Bootstrap_2

What was slightly trickier was processing the AJAX requests using Spring MVC. Most of the examples on the site are with PHP where it is quite normal to manually retrieve request parameters. In contrast with Spring MVC the aim is to have a POJO controller and map each parameter on the request to a method parameter. Unfortunately when it comes to sort columns DataTables passes a number to tell you how many columns to sort on and then each column is on the request with an index as part of the parameter name.

You end up needing this kind of thing.

$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
  $sOrder = "ORDER BY ";
  for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
  {
    if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
    {
      $sOrder .= "`".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ". mysql_real_escape_string($_GET['sSortDir_'.$i] ) .", ";
    }
  }
  $sOrder = substr_replace( $sOrder, "", -2 );
  if ( $sOrder == "ORDER BY" )
  {
    $sOrder = "";
  }
}

Now you could do something similar by passing the request object into the method and let your method retrieve the parameters manually but this defeats the object a bit and makes unit testing harder. Instead I decided to create a class to model the DataTables parameters and get Spring to populate it automatically. It’s then possible to pass this single object down through the layers to the place that needs to do the DB query. This makes the parameter lists much shorter and easier to maintain since you can simply add an attribute to the containing object rather than having to visit every method signature.

My object to hold the basic paging criteria is a simple POJO. I’ve made it immutable because you shouldn’t be able to override the inputs that the user has set.

public final class PagingCriteria
{
  private final Integer displayStart;
  private final Integer displaySize;
  private final List<SortField> sortFields;
  private final Integer pageNumber;

  public PagingCriteria(Integer displayStart, Integer displaySize, Integer pageNumber, List<SortField> sortFields)
  {
    this.displayStart = displayStart;
    this.displaySize = displaySize;
    this.pageNumber = pageNumber;
    this.sortFields = sortFields;
  }

  public Integer getDisplayStart() {
    return displayStart;
  }

  public Integer getDisplaySize() {
    return displaySize;
  }

  public List<SortField> getSortFields()
  {
    return Collections.unmodifiableList(sortFields);
  }

  public Integer getPageNumber()
  {
    return pageNumber;
  }

}

I’ve also extracted the concept of a sort field and direction into a further class with an enum representing sort directions.

public final class SortField 
{
  private final String field;
  private final SortDirection direction;
	
  public SortField(String field, SortDirection direction)
  {
    this.field = field;
    this.direction = direction;
  }
	
  public SortField(String field, String direction)
  {
    this.field = field;
    this.direction = SortDirection.valueOfCaseInsensitive(direction);
  }

  public String getField() {
    return field;
  }

  public SortDirection getDirection() {
    return direction;
  }

}
public enum SortDirection 
{
  ASC("asc"), DESC("desc");
	
  private String direction;
  private static final Logger logger = LoggerFactory.getLogger(SortDirection.class);
	
  private SortDirection(String direction)
  {
    this.direction = direction;
  }
	
  public String getDirection()
  {
    return this.direction;
  }
	
  public static SortDirection valueOfCaseInsensitive(String value)
  {
    String valueUpper = value.toUpperCase();
    SortDirection sd = SortDirection.valueOf(valueUpper);
    logger.debug("SortDirection converted "+value+" to "+sd);
    return sd;
  }
}

The PagingCriteria object is passed into my Spring MVC controller something like this.

@Controller
@RequestMapping(value="customers")
public class CustomerController
{

  @Autowired
  private CustomerService customerService;

  @RequestMapping(value="/get", method=RequestMethod.GET)
  public @ResponseBody WebResultSet<Customer> getCustomers(@TableParam PagingCriteria criteria)
  {
    ResultSet<Customer> customers = this.customerService.getCustomers(criteria);
    return ControllerUtils.getWebResultSet(criteria, customers);
  }
...

Ignore the return type for the moment, we’ll come back to that. This is mapping all GET requests to /customers/get to the getCustomers method. It takes in a single parameter for the paging criteria which has been annotated with @TableParam. This is a custom annotation created for populating PagingCriteria with parameters on the request. The annotation class file itself is pretty straightforward.

@Target(ElementType.PARAMETER)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TableParam
{

}

So it’s just a simple marker. We’re using it to annotate method PARAMETER’s and we want to retain this annotation at RUNTIME. Next we need a class that can resolve this annotation and return our PagingCriteria object.

public class TableParamArgumentResolver implements WebArgumentResolver
{

  private static final String S_ECHO = "sEcho";
  private static final String I_DISPLAY_START = "iDisplayStart";
  private static final String I_DISPLAY_LENGTH = "iDisplayLength";
  private static final String I_SORTING_COLS = "iSortingCols";

  private static final String I_SORT_COLS = "iSortCol_";
  private static final String S_SORT_DIR = "sSortDir_";
  private static final String S_DATA_PROP = "mDataProp_";

  public Object resolveArgument(MethodParameter param, NativeWebRequest request) throws Exception
  {
    TableParam tableParamAnnotation = param.getParameterAnnotation(TableParam.class);

    if (tableParamAnnotation!=null)
    {
      HttpServletRequest httpRequest = (HttpServletRequest) request.getNativeRequest();

      String sEcho = httpRequest.getParameter(S_ECHO);
      String sDisplayStart = httpRequest.getParameter(I_DISPLAY_START);
      String sDisplayLength = httpRequest.getParameter(I_DISPLAY_LENGTH);
      String sSortingCols = httpRequest.getParameter(I_SORTING_COLS);

      Integer iEcho = Integer.parseInt(sEcho);
      Integer iDisplayStart = Integer.parseInt(sDisplayStart);
      Integer iDisplayLength = Integer.parseInt(sDisplayLength);
      Integer iSortingCols = Integer.parseInt(sSortingCols);

      List sortFields = new ArrayList();
      for(int colCount=0; colCount<iSortingCols; colCount++)
      {
        String sSortCol = httpRequest.getParameter(I_SORT_COLS+colCount);
        String sSortDir = httpRequest.getParameter(S_SORT_DIR+colCount);
        String sColName = httpRequest.getParameter(S_DATA_PROP+sSortCol);
        sortFields.add(new SortField(sColName, sSortDir));
      }

      PagingCriteria pc = new PagingCriteria(iDisplayStart, iDisplayLength, iEcho, sortFields);

      return pc;
    }

    return WebArgumentResolver.UNRESOLVED;
  }
}

All we do is implement WebArgumentResolver which has a single method resolveArgument. One of the parameters to this method is the request which allows us to retrieve the named request parameters we need. DataTables just passes a column index for sorting but we can use this index to lookup the column name too if mDataProp is used to initialise DataTables.

It’s very important to check first that the annotation being processed is our TableParam and if not to return WebArgumentResolver.UNRESOLVED. The first time through Spring calls each resolver in turn until it finds one that handles that annotation. This is cached and from then on it will always call that resolver for that annotation.

All that’s left now is to register our resolver with spring in the servlet context. This is much easier in Spring 3.1 because of the extra parameters you can now pass to mvc:annotation-driven.

  <mvc:annotation-driven>
    <mvc:argument-resolvers>
      <bean class="org.languagetool.web.utils.TableParamArgumentResolver" />
    </mvc:argument-resolvers>
  </mvc:annotation-driven>

That’s pretty much it. Now when a request comes in to getCustomers(), Spring  sees the @TableParam annotation and calls our custom resolver. This wings around the request picking up all the DataTables parameters and puts them in a PagingCriteria which it then returns. The great thing is once you’ve set this up you can easily reuse it throughout your controllers.

But what about the return type on the controller? If we take another look at the controller method.

@RequestMapping(value="/get", method=RequestMethod.GET)
public @ResponseBody WebResultSet<Customer> getCustomers(@TableParam PagingCriteria criteria)
{
  ResultSet<Customer> customers = this.customerService.getCustomers(criteria);
  return ControllerUtils.getWebResultSet(criteria, customers);
}

There are a couple of things here. The service call is returning a ResultSet object but the controller returns a WebResultSet. This has been annotated with @ResponseBody so if the request object has a request type of JSON then this object will be automatically parsed and returned as JSON. Likewise a content type of XML would return XML assuming the correct dependencies are on the classpath (Jackson and JAXB respectively).

My services are returning a ResultSet object which is similar to PagingCriteria in that it simply wraps the returning data. It’s basically a collection but with extra bits needed to display in the grid such as a record count. The ResultSet holds a collection of DomainObject’s, this is my layer super type for JPA entities which I will be posting about at a later date.

public final class ResultSet<T extends DomainObject>
{
  private final List<T> rows;
  private final Long totalDisplayRecords;
  private final Long totalRecords;

  public ResultSet(List<T> rows, Long totalRecords, Long totalDisplayRecords)
  {
    this.rows = rows;
    this.totalRecords = totalRecords;
    this.totalDisplayRecords = totalDisplayRecords;
  }

  public List<T> getRows()
  {
    return Collections.unmodifiableList(rows);
  }

  public Long getTotalDisplayRecords()
  {
    return totalDisplayRecords;
  }

  public Long getTotalRecords()
  {
    return totalRecords;
  }
}

DataTables itself though will be looking for these parameters to have specific names. It also must have the sEcho parameter from the request sent back. This is a “draw count” which datatables increments each time it makes a request. (Thanks to Sean in the comments for explaining this) Luckily we retrieved sEcho behind the scenes into our PagingCriteria so we use the static ControllerUtils.getWebResultSet() method to build us a DataTables friendly resultset.

public static <T extends DomainObject> WebResultSet<T> getWebResultSet(PagingCriteria pc, ResultSet<T> rs)
{
  return new DataTablesResultSet<T>(pc, rs);
}
public class DataTablesResultSet<T extends DomainObject>  implements WebResultSet<T>
{
  private final Integer sEcho;
  private final Long iTotalRecords;
  private final Long iTotalDisplayRecords;
  private final List<T> aaData;

  public DataTablesResultSet(PagingCriteria pc, ResultSet<T> rs)
  {
    this.sEcho = pc.getPageNumber();
    this.aaData = rs.getRows();
    this.iTotalRecords = rs.getTotalRecords();
    this.iTotalDisplayRecords = rs.getTotalRecords();
  }

  public Integer getsEcho() {
    return sEcho;
  }

  public Long getiTotalRecords() {
    return iTotalRecords;
  }

  public Long getiTotalDisplayRecords() {
    return iTotalDisplayRecords;
  }

  public List<T> getAaData() {
    return Collections.unmodifiableList(aaData);
  }
}

So WebResultSet is simply a marker interface that’s used as a return type on the controllers. We then determine in ControllerUtils.getWebResultSet that it’s a DataTablesResultSet we want. In theory we can now tweak both the inputs and outputs from DataTables or any other grid we choose without having to change the controllers or services.  We’ve essentially removed the controllers dependency on the JavaScript grid implementation.

The next step is to wrap the view layer in custom JSP tags

jqGrid RESTful URL formatter

jQuery seems to have become ubiquitous these days and if you want a scrolling, paging, dynamic grid then jqGrid is an excellent plugin. I’ve been using it in my project with a RESTful JSON web service to return the grid content and I needed a way to format the ID column into a URL. First I had a look at the documentation to see what jqGrid has out of the box.

There is a formatter called ‘link’ or ‘showLink’ but it didn’t quite work as I wanted as it forces you to use a URL of the form site.com/page?id=1. My application is using RESTful URL’s so I wanted something more like site.com/page/1. This left me with a couple of options.

  1. Format the URL on the server and return it from the JSON web service
  2. Create a custom formatter

For me 1 was out of the question because this was a formatting issue that belonged in the view layer. My JSON is being returned as a POJO from a spring controller. The conversion to JSON is handled automatically by Jackson. So you can see I didn’t really want this URL formatting going on in the controller, or worse the model layers.

This is the custom formatter I created for this purpose.

jQuery.extend($.fn.fmatter , {
RESTfulLink : function(cellvalue, options, rowdata)
{
var opts = options.colModel.formatoptions.url;
if(typeof opts == 'string')
{
return '<a href="'+opts + cellvalue+'">'+cellvalue+'</a>';
}else{
return cellvalue;
}
}
});

The first line allows us to create a formatter by extending jqGrid. It is also possible to create a formatter as a standalone function but I can’t see any reason why you would need to do this. The rest is standard JavaScript defining a formatter called RESTfulLink. The parameters are the standard parameters required for a formatter function. This can all be found on the jqGrid wiki.

The interesting part that isn’t mentioned in the documentation, or anywhere else I could find, is how to access a formatter option. This might make more sense once we’ve seen how you use this formatter.

<script>
  jQuery("#grid_id").jqGrid({
  ...
     colModel: [
      ...
     {name:'id', index:'id', width:60, align:"center", editable: true, formatter:'RESTfulLink', formatoptions:{url:'site.com/page/'}},
      ... ]
   ... });

We select the formatter in the same way as any of the standard ones. Note that if we’d done this is a standalone function instead of extending jqGrid then the quotes would have been dropped. E.G. formatter:RESTfulLink. The data we need to create the link has been passed in using the formatoptions object. I’ve created a custom parameter called url but since JavaScript is dynamically typed you could pass in whatever you want.

If you look back at the custom formatter you should see the url parameter being used to construct the URL by appending the contents of the ID column to whatever the URL is. I couldn’t find this documented anywhere so I ended up debugging my function and inspecting the parameters passed in. Otherwise there’s no way I would have guessed it would be options.colModel.formatoptions.url!

So that’s my first proper post, I hope it makes sense. Please leave a comment if you liked it or to suggest an improvement.

Thanks

Ben