Overview

Play CMS provides API to create pageable sortable tables using datatable. Such functionality could be implemented using following steps:

1. Implement your own DatatableService
2. Create page template
3. Initialize datatable in JavaScript

For this tutorial, let's assume we have the following entity:

public class Car {

    @Id
    @GeneratedValue
    private Long id;
    private String model;
    @Column(unique = true)
    private String registrationId;
    private BigDecimal price;
    @Temporal(TemporalType.TIMESTAMP)
    private Date buyDate;

    //Getters and setters are skipped
}

Implement own DatatableService

Play CMS provides a datatable request handler in form of a template methodAbstractDatatableService#getDatatable(Http.Request request) => JsonNode. All you need is to extend your service from AbstractDatatableService class and implement template placeholder methods:

long getTotalSize();
TypedQuery<T> findByKeywords(String keywords, String orderByField, String orderByDirection);
List<String> getSortableFields();
List<Function<T, String>> getTableFields();

Here's an example: CarDatatableService.java

import ch.insign.cms.services.AbstractDatatableService;
import models.Car;
import play.db.jpa.JPAApi;

import javax.inject.Inject;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.List;
import java.util.function.Function;

public class CarDatatableService extends AbstractDatatableService<Car>  {

    private final String[] SORTABLE_FIELDS = {"id", "model", "registrationId", "price", "buyDate"};
    private final String[] SEARCHABLE_FIELDS = {"model", "registrationId", "price"};

    private final JPAApi jpaApi;

    @Inject
    public CarDatatableService(JPAApi jpaApi) {
        this.jpaApi = jpaApi;
    }

    // Total count of entities in table
    @Override
    protected long getTotalSize() {
        CriteriaBuilder builder = jpaApi.em().getCriteriaBuilder();
        CriteriaQuery<Long> query = builder.createQuery(Long.class);
        Root cars = query.from(Car.class);

        query.select(builder.count(cars));

        return jpaApi.em().createQuery(query).getSingleResult();
    }

    @Override
    protected TypedQuery<Car> findByKeywords(String keywords, String orderByField, String orderByDirection) {
        CriteriaBuilder builder = jpaApi.em().getCriteriaBuilder();
        CriteriaQuery<Car> query = builder.createQuery(Car.class);
        Root<Car> cars = query.from(Car.class);

        Predicate[] conditions = Arrays.stream(keywords.split(" "))
                .map(String::trim)
                .map(String::toLowerCase)
                .flatMap(keyword -> Arrays.stream(SEARCHABLE_FIELDS)
                .map(field -> builder.like(builder.lower(cars.get(field)), "%" + keyword + "%")))
                .map(builder::or)
                .toArray(Predicate[]::new);

        query.where(conditions);

        if ("asc".equals(orderByDirection)) {
            query.orderBy(builder.asc(cars.get(orderByField)));
        } else {
            query.orderBy(builder.desc(cars.get(orderByField)));
        }

        return jpaApi.em().createQuery(query.select(cars));
    }

    // List of fields which could be used for sorting
    @Override
    protected List getSortableFields() {
        return Arrays.asList(SORTABLE_FIELDS);
    }

    // List of string representations of the entity fields
    @Override
    protected List<Function<Car, String>> getTableFields() {
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");

    return Arrays.asList(
            car -> String.valueOf(car.getId()),
            car -> car.getModel(),
            car -> car.getRegistrationId(),
            car -> car.getPrice().toString(),
            car -> formatter.format(car.getBuyDate())
        );
    }
}

Here the findByKeywords method is more involved, so let's take a closer look at it.
It receives 3 parameters:

• String keywords - this is user's search query. The search result should include the records with at least one match of at least one of the keywords.
• String orderByField - the field name to sort search results on. The field name must be one of the values returned by getSortableFields().
• String orderByDirection - the sort order direction, it can either be 'asc' or 'desc'.

You're free to implement the search query accordingly to your preferences as long as you're conforming to the findByKeywords contract.
In the example above we've used JPA's CriteriaBuilder to compose a search query. The keywords are split by space and converted to wildcard patterns for a SQL LIKE comparison operator.
Next, we define result sorting. For our example we take field name as is, but for more complex situations when sorting by column from joined table you may need to do some additional processing.
Finally, we return the TypedQuery generated by the JPA Criteria API.

Now service is ready to use. You can inject it into one of the controllers and define new route. Let's assume that new route will be available as controllers.routes.CarController.datatable().

Create page template

Once the route is set up it's time to build a view template for the backend page.

views/car/list.scala.html:

@()

@import ch.insign.cms.views.html.admin.shared.layout

@pageFooter = {
    @* JS that would initialize datatable *@
    <script src="@{ch.insign.cms.views.html.tags._asset("backend/js/car_list.js")}" type="text/javascript"></script>
}

@layout(new ch.insign.cms.views.admin.utils.AdminContext, "", "", null, pageFooter) {
    <div class="row">
        <div class="col-md-12">
            <div class="portlet">
                <div class="portlet-title">
                    <div class="caption">
                        <i class="fa fa-car"></i> Car list
                    </div>
                </div>

                <div class="portlet-body">
                    <table class="table table-striped table-bordered table-hover" id="carList" data-url="@controllers.routes.CarController.datatable()">
                        <thead>
                            <tr>
                                <th>Id</th>
                                <th>Model</th>
                                <th>Registration Id</th>
                                <th>Price</th>
                                <th>Buy Date</th>
                            </tr>
                        </thead>
                        <tbody></tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
}

As you can see we don't define any logic inside template. Datatable uses AJAX to obtain data from server. Setting up datatable would be completed in the next step.

Initialize datatable in JavaScript

Now it's time to write car_list.js:

/* global jsconfig */

$(function () {
    // sanity check, we really won't to run this code if datatable plugin is missing
    if (!jQuery().dataTable) {
        return;
    }

    // Set up data url
    jsconfig.datatables.sAjaxSource = $('#carList').data("url");

    var carList = $('#carList').dataTable(jsconfig.datatables);

    // modify table search input
    $('#carList_wrapper .dataTables_filter').addClass("form-group pull-left");
    $('#carList_wrapper .dataTables_length select').addClass("form-control input-small ");
    $('#carList_wrapper .dataTables_length select').css({display: "block"});
    $('#carList_wrapper .dataTables_length').addClass("pull-right");

    $('.input_search')
        .append('<div class="input-group">' +
                ' <input type="text" id="searchQuery" aria-controls="carList" class="form-control" placeholder="Search...">' +
                ' <span class="input-group-btn">' +
                ' <button id="searchBtn" class="btn btn-primary"><i class="fa fa-search"></i></button>' +
                ' </span>' +
                '</div>');

    // add search field submission handlers
    $('#searchBtn').click(function () {
        carList.fnFilter($('#searchQuery').val());
    });

    $("#searchQuery").keyup(function (event) {
        if (event.keyCode == 13) {
            carList.fnFilter($('#searchQuery').val());
        }
    });
});

In this snippet we initialize the datatable on the element with id='carList', and then we add a custom search bar. Note, that for configuration we use jsconfig.datatables object. It available on all CMS pages and contains default settings for datatable. So, only thing you need to do before creating datatable is to define data URL.

When you launch your application you will get following screen: