Java – JQuery DataTables with ServerSide Processing in Spring (with Java and Thymeleaf)

datatablesjavajqueryspring-bootthymeleaf

I have a Spring Boot application using Java and Thymeleaf. In it, I have a page with a JQuery DataTable. This table has thousands of rows in it, and so while I currently just put it all on the page and let JQuery handle it entirely, I want to switch to using ServerSide processing for the paging, sorting, etc.

I am set up to do this on the back-end, where I can pass a start and end row number and sort information dynamically to my SQL query. However, my issue is that I just can't figure out exactly how DataTables is trying to notify me via controller when the user clicks the 'next page' button, or a sort button. Where/how can my controller pick up this information, so that I can plug it into my SQL query and return what is necessary?

So, lets say I have an example object, like a "Person".

public class Person {

    private static String PERSON_IMAGE = "<img th:src=\"@{/images/personimage.png}\" alt=\"icon\"/>";
    private static String PERSON_IMAGE_2 = "<img th:src=\"@{/images/personimage2.png}\" alt=\"icon\"/>";

    private String name;
    private String socialSecurity;
    private String birthdate;
    private String gender;
    private String personImage;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSocialSecurity() {
        return socialSecurity;
    }

    public void setSocialSecurity(String socialSecurity) {
        this.socialSecurity = socialSecurity;
    }

    public String getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(String birthdate) {
        this.birthdate = birthdate;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getPersonImage() {
        if(null != birthdate) {
            return PERSON_IMAGE;
        } else {
            return PERSON_IMAGE_2;
        }

    }
}

So, on my Thymeleaf HTML page, I used to have the following: (Version 1)

<table id="myDataTable" class="dataTable display compact">
    <thead>
        <tr>
            <th>Name</th>
            <th>Social Security</th>                                        
            <th>Birthdate</th>
            <th>Gender</th>
        </tr>
    </thead>
    <tbody>
        <tr th:each="person : ${peopleList}">
            <td th:attr="data-order=${person.name}"><a th:href="|javascript:openPersonDetail('${person.socialSecurity}');|"><span th:text="${person.name}">name</span></a></td>
            <td th:text="${person.socialSecurity}">socialSecurity</td>                      
            <td class="dt-body-center" th:text="${person.birthdate}">birthdate</td>
            <td class="dt-body-center" th:text="${person.gender}">gender</td>
        </tr>
    </tbody>
</table>

My page controller was very simple, and just passed in a full list of people, as follows: (Version 1)

@GetMapping("/ApplicationName/MyDataTablePage")
public String myDataTablePage(Model model) {

    SearchCriteria searchCriteria = new SearchCriteria();
    searchCriteria.setOrderByString("name");
    searchCriteria.setUsePaging(false);
    searchCriteria.setFIRST_NUMBER(null);
    searchCriteria.setLAST_NUMBER(null);

    model.addAttribute("peopleList", myMapperService.getPeople(searchCriteria)); //returns an ArrayList<Person>

    return "/pages/MyDataTablePage";
}

And finally, my Javascript used to have the following: (Version 1)

$(document).ready(function() {
    $('#myDataTable').DataTable({
        "destroy" : true,
        "scrollY" : 300,
        "scrollCollapse" : true,
        "paging" : true,
        "autoWidth" : true,
        "ordering" : true,
        "searching" : false,
        "order" : [ [ 0, 'asc' ] ],
        "pageLength" : 20,
        "lengthChange" : false,
        "pagingType" : "full_numbers",
        "dom" : '<"top"ip>rt<"bottom"fl><"clear">'
    });
});

This worked, but I wanted to switch the DataTable to use serverside processing, and so the first step was to (1) change the main page controller to stop appending the list of People to the model, (2) to create a new controller which would return my ArrayList as JSON for the DataTable to call by itself, (3) to change the html of the main page to no longer provide any tbody tag to the DataTable, and (4) to change the javascript where the table is created to put the data in by itself.

=============================================================

That led me to version 2 of the following elements.

My changed Thymeleaf HTML page: (Version 2)

<table id="myDataTable" class="dataTable display compact">
    <thead>
        <tr>
            <th>Name</th>
            <th>Social Security</th>                                        
            <th>Birthdate</th>
            <th>Gender</th>
        </tr>
    </thead>
</table>

My changed page controller: (Version 2)

@GetMapping("/ApplicationName/MyDataTablePage")
public String myDataTablePage(Model model) {

    return "/pages/MyDataTablePage";
}

My new DataTables controller:

@RequestMapping(path = "/ApplicationName/Data/Person", method = RequestMethod.GET, produces = "application/json")
@ResponseBody
public List<Person> getPersonData() {
    System.out.println("I was called!");

    SearchCriteria searchCriteria = new SearchCriteria();
    searchCriteria.setOrderByString("name");
    searchCriteria.setUsePaging(false);
    searchCriteria.setFIRST_NUMBER(null);
    searchCriteria.setLAST_NUMBER(null);

    return myMapperService.getPeople(searchCriteria); //returns an ArrayList<Person>
}

My changed (much more complicated) Javascript: (Version 2)

$(document).ready(function () {
     $('#myDataTable').DataTable({ 
         'destroy' : true,
         'serverSide' : true,
         'sAjaxSource': '/ApplicationName/Data/Person',
         'sAjaxDataProp': '',
         'order': [ [ 0, 'asc' ] ],
         'columns': 
         [ 
            {  'data': 'name',
                'render': function(data, type, row, meta){ 
                    if(type === 'display'){ 
                        data = '<a href="javascript:openPersonDetail(&apos;'+ row.socialSecurity +'&apos;);">' + data + '</a>' 
                    }  
                    return data; 
                } 
            } ,
            { 'data': 'socialSecurity'} ,
            { 'data': 'birthdate'} ,
            { 'data': 'gender'} 
         ],
         'scrollY' : 300,
         'scrollCollapse' : true,
         'paging' : true,
         'autoWidth' : true,
         'ordering' : true,
         'searching' : false,
         'pageLength' : 20,
         'lengthChange' : false,
         'pagingType' : 'full_numbers',
         'dom' : '<"top"ip>rt<"bottom"fl><"clear">' 
     }); 
 });

=============================================================

And that works, too. Especially if I remove the "'serverSide' : true," configuration – the DataTable works exactly like it used to before I made the above changes, but now it calls my new Data JSON controller instead of using a model attribute.

Problem:

However, with the "'serverSide' : true," configuration added to the DataTable, each time I go to a new 'page' of the table, it just recalls my "/Clinic/Detail/Data/Patient" controller each time.

But, I need DataTables to pass my controller information about what page the user has selected, or what column the user tried to sort on. I also need to pass the table (dynamically) the total number of rows there are (count) so that DataTables can properly set up the page numbers for the user.

Is DataTables sending some additional parameters to my controller that I can pick up? Something like a @PathVariable("whatever")?

I feel like I am so close to getting this working, but I'm stuck at this point.

I would really appreciate any help!

Best Answer

I found 2 solutions:

  1. Look at this post: Spring Boot + Bootstrap + Thymeleaf Datatable
<script>
    $('#example').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "/employees",
            "type": "POST",
            "dataType": "json",
            "contentType": "application/json",
            "data": function (d) {
                return JSON.stringify(d);
            }
        },
        "columns": [
            {"data": "name", "width": "20%"},
            {"data": "position","width": "20%"},
            {"data": "office", "width": "20%"},
            {"data": "start_date", "width": "20%"},
            {"data": "salary", "width": "20%"}
        ]
    });
</script>
  1. Look at this github project: spring-data-jpa-datatables