DataTables server-side pagination

Question

I have working Spring REST app with client side pagination, default by DataTables and everything works. Now i need to change it to server-side pagination i have problem, because have no idea how to get information from DataTables what page number client want to see. I can't find anything usefull in DT manual.

Answer 1

When you say Datatable I assume you are talking about DataTables jQuery plugin.

To activate serverside pagination you need to pass

"serverSide": true,

like this:

$('#example').DataTable( {
    "processing": true,
    "serverSide": true,
    "ajax": "/your_url"
});

Doing the above will activate your server-side pagination. But you need to do few changes on the server-side too. Let's see them step by step.

1. What happens when you mark serverSide as true

DataTables plugin adds custom parameters to the AJAX call with information like

order:  asc
start:  20
length: 10

// and many more.

You can inspect this demo link and see the parameters passed in request when you click the next page button.

2. Similarly, DataTables plugin expects some fields in response in order to preserve pagination logic.

"draw": 3,             // unique ID
"recordsTotal": 57,    // total number of records
"recordsFiltered": 57  // total number of filtered records

You can inspect this demo link and see response data this time.

3. Now changes are on serverside in your API

You need to add these parameters as queryParam for GET and attr in POST call in your controller API:

order: asc
start: 20
length: 10

4. Service Layer Changes - DB query

In-Service Layer where you get details from a database.

You need to get the total number of records and in search query pass a LIMIT clause LIMIT 10, 10 in case of MySQL.

E.g.:

SELECT * FROM User LIMIT 20,10;

Use start and length to calculate the next set of records.

It can be trickier but if you understand and implement properly it's fun.

Read more in detail here which also contains code and live demo.

Answer 2

Please see the sample about DataTables server-side processing: https://datatables.net/examples/server_side/simple.html

After change page, you can capture the request to server as following format: https://.../server_processing.php?draw=3&columns...&order=0&dir=asc&start=20&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1534436781912

That means DataTable requests page number 3 (draw=3), order by first column ascending, ....

At server side (REST), you can get page number by (for example) request.getParameter("draw")