Play! Framework 2.0 + DataTables + Server-side processing

This post will describe how to set up a Play! Framework 2.0(.2) Java project with DataTables and server-side processing. The source code used in this example can be found on github.

Create a new project

Create a new Play! application and choose Create a simple Java application. In this example we will call our app dtapp.

$ play new dtapp

Download needed files

We are going to use DataTables with the jQuery style, so we need to download those packages.

1. Download DataTables (this post uses version 1.9.2)

  • Copy DataTables-1.9.2/media/css/demo_table_jui.css to dtapp/public/stylesheets/
  • Copy¬†DataTables-1.9.2/media/js/jquery.dataTables.min.js to dtapp/public/javascripts/

2. Download a jQuery UI (this post uses version 1.8.21). You can choose what theme you like, this post will use UI lightness.

  • Copy jquery-ui/js/jquery-1.7.2.min.js to dtapp/public/javascripts/ (delete the old jquery-1.7.1.min.js)
  • Copy jquery-ui/js/jquery-ui-1.8.21.custom.min.js to dtapp/public/javascripts/
  • Copy query-ui/css/ui-lightness to dtapp/public/stylesheets/

3. Modify main.scala.html to include the new files:

@(title: String)(content: Html)

<!DOCTYPE html>

<html>
 <head>
 <title>@title</title>
 <link rel="stylesheet" media="screen" href="@routes.Assets.at("stylesheets/demo_table_jui.css")">
 <link rel="stylesheet" media="screen" href="@routes.Assets.at("stylesheets/ui-lightness/jquery-ui-1.8.21.custom.css")">
 <link rel="shortcut icon" type="image/png" href="@routes.Assets.at("images/favicon.png")">
 <script src="@routes.Assets.at("javascripts/jquery-1.7.2.min.js")" type="text/javascript"></script>
 <script src="@routes.Assets.at("javascripts/jquery-ui-1.8.21.custom.min.js")" type="text/javascript"></script>
 <script src="@routes.Assets.at("javascripts/jquery.dataTables.min.js")" type="text/javascript"></script>
 </head>
 <body>
 @content
 </body>
</html>

Create the Model

Our table will display information about contacts, so we need to create a Contact model. Create a file dtapp/app/models/Contact.java with the following content:

package models;

import java.util.*;
import javax.persistence.*;

import play.api.libs.Crypto;
import play.db.ebean.*;
import play.data.format.*;
import play.data.validation.*;

import play.Logger;

import com.avaje.ebean.*;

@Entity
public class Contact extends Model {

  @Id
  public Long id;

  @Constraints.Required
  public String name;

  public String title;
  public String email;

  public static Model.Finder<Long,Contact> find = new Model.Finder(Long.class, Contact.class);

  public static List<Contact> findAll() {
    return find.all();
  }

  public String toString() {
    return name;
  }
}

Create the view

Add a HTML-table and the needed javascript to index.scala.html. The javascript-snippet initializes DataTable and sets so that it will use server-side processing and jQuery UI. Note the sAjaxSource setting, that points to an application route.

@(message: String)

@main("Play!ing with DataTables") {

<script type="text/javascript">
  /* Table initialisation */
  $(document).ready(function() {
    $('#contacts_table').dataTable( {
      "bProcessing": true,
      "aaSorting": [[ 0, "asc" ]],
      "bServerSide": true,
      "bJQueryUI": true,
      "sPaginationType": "full_numbers",
      "sAjaxSource": "@routes.Application.list()"
    });
  });
</script>

<h2>Play!ing with DataTables</h2>

<table id="contacts_table" class='display'>
  <thead>
    <tr>
      <th>Name</th>
      <th>Title</th>
      <th>E-mail</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Row 1 Data 1</td>
      <td>Row 1 Data 2</td>
      <td>etc</td>
    </tr>
  </tbody>
</table>
}

Set up the route
Now we need to set up a route for the Ajax-call. Add the following line to your dtapp/conf/routes:

GET /search controllers.Application.list()

Set up the controller

This is where the tricky part is. DataTables with server-side processing sends a long query-string to the Application.list(). The query-string contains information like search-string, sorting, pagination etc. We need to get the query-string, find out what it wants, get the data it wants from the database and return it. DataTables expects Application.list() to give back a JSON-string containing the needed information to be displayed. The real magic here is EBeans Page<T>, a class that will help us to easily get the data we need.

Your dtapp/app/controllers/Application.java should look like this:

package controllers;

import play.*;
import play.libs.Json;
import play.mvc.*;
import play.data.*;

import models.*;
import views.html.*;

import java.util.*;

import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.node.ArrayNode;
import org.codehaus.jackson.node.ObjectNode;

import com.avaje.ebean.Expr;
import com.avaje.ebean.Page;

public class Application extends Controller {

  public static Result index() {
    return ok(index.render("Your new application is ready."));
  }

  public static Result list() {
    /**
     * Get needed params
     */
    Map<String, String[]> params = request().queryString();

    Integer iTotalRecords = Contact.find.findRowCount();
    String filter = params.get("sSearch")[0];
    Integer pageSize = Integer.valueOf(params.get("iDisplayLength")[0]);
    Integer page = Integer.valueOf(params.get("iDisplayStart")[0]) / pageSize;

    /**
     * Get sorting order and column
     */
    String sortBy = "name";
    String order = params.get("sSortDir_0")[0];

    switch(Integer.valueOf(params.get("iSortCol_0")[0])) {
      case 0 : sortBy = "name"; break;
      case 1 : sortBy = "title"; break;
      case 2 : sortBy = "email"; break;
    }

    /**
     * Get page to show from database
     * It is important to set setFetchAhead to false, since it doesn't benefit a stateless application at all.
     */
    Page<Contact> contactsPage = Contact.find.where(
      Expr.or(
        Expr.ilike("name", "%"+filter+"%"),
        Expr.or(
          Expr.ilike("title", "%"+filter+"%"),
          Expr.ilike("email", "%"+filter+"%")
        )
      )
    )
    .orderBy(sortBy + " " + order + ", id " + order)
    .findPagingList(pageSize).setFetchAhead(false)
    .getPage(page);

    Integer iTotalDisplayRecords = contactsPage.getTotalRowCount();

    /**
     * Construct the JSON to return
     */
    ObjectNode result = Json.newObject();

    result.put("sEcho", Integer.valueOf(params.get("sEcho")[0]));
    result.put("iTotalRecords", iTotalRecords);
    result.put("iTotalDisplayRecords", iTotalDisplayRecords);

    ArrayNode an = result.putArray("aaData");

    for(Contact c : contactsPage.getList()) {
      ObjectNode row = Json.newObject();
      row.put("0", c.name);
      row.put("1", c.title);
      row.put("2", c.email);
      an.add(row);
    }

    return ok(result);
 }
}

Set up the sample data

Now we just need to create some sample data so that we can try out our shiny new table. Download initial-data.yml and place it in your dtapp/conf/. Then create dtapp/app/Global.java, that will import the sample data, with the following content:

import play.*;
import play.libs.*;
import java.util.*;
import com.avaje.ebean.*;
import models.*;
import java.util.concurrent.*;

public class Global extends GlobalSettings {

  @Override
  public void onStart(Application app) {

    /**
     * Here we load the initial data into the database
     */
    if(Ebean.find(Contact.class).findRowCount() == 0) {
      Map<String,List<Object>> all = (Map<String,List<Object>>)Yaml.load("initial-data.yml");
      Ebean.save(all.get("contacts"));
    }
  }
}

The last thing you have to do is enable H2 database for your application. Uncomment the following lines in dtapp/conf/application.conf:

db.default.driver=org.h2.Driver
db.default.url="jdbc:h2:mem:play"
ebean.default="models.*"

Ready to run!

You should now be able to run your application. DataTables will only fetch the needed data. Try searching for different terms, pagination etc. The table should update directly. Try adding more sample data to the yaml-file to see how it behaves. Try bringing front your web browsers Inspector and see what query and data is sent.

Enjoy!

5 Responses to Play! Framework 2.0 + DataTables + Server-side processing

  1. pichulines says:

    thanks, very useful!

  2. Mustafa DUMLUPINAR says:

    really good!

  3. Camilo says:

    Really Good,

    I Hope it’s not difficult to integrate it with
    twitter bootstrap.

    thanks

  4. Morgan Dawe says:

    Great Tutorial. I have found this site via google search while trying to solve an issue of mine using .makeEditable() on my datatable. My model/view are set up about the same, the only difference is my data source. Instead i am using a scala for loop to iterate thru entries in my models find helper on page load. I am doing it this way because a dynamic data source always seams to hide the model entry’s id property when updating (id is always empty in my post). Any suggestions?