Overview
In this blog, we will see how to configure Server-Side Pagination in jQuery Datatable.
To activate Server-Side pagination you need to pass "serverSide": true
in DataTable configuration
$('#example').DataTable( {
"processing": true, // shows loading image while fetching data
"serverSide": true, // activates server side pagination
"ajax": {
url: "user" // API
},
"columns": [
{ "data": "lastName" },
{ "data": "firstName" },
{ "data": "email" }
]
});
Datatable appends a few parameters in request call whether it’s POST/GET
Few of these request parameters are
// append Sort Order for column order[0][column]
order: asc
// append start index of Row
start: 20
// appends number of rows should be returned from Server Side.
length: 10
Similarly, Datatable also expects a few parameters in response to load table correctly.
// unique ID which can be retrived from request
"draw": 3
// total number of records
"recordsTotal": 57
// total number of filtered recods
"recordsFiltered": 57
At Server-Side there will be changes to support Pagination. Here we are using Spring Web but the logic remains the same.
Controller
@GetMapping("/user")
public UsersDTO getCurrentUser(@RequestParam("start") int start,
@RequestParam("length") int length,
@RequestParam("draw") int draw,
@RequestParam("order[0][column]") int sortColIndex,
@RequestParam("order[0][dir]") String order,
@RequestParam("columns[0][data]") String col0DataAttrName) {
// To handle Sorting
// sortColIndex => which column index is being sorted
// order => asc or desc
// col0DataAttrName => can be used to pass column name dymanically in DBQuery
int totalUsers = userRepository.totalUsers();
UsersDTO usersDto = new UsersDTO();
usersDto.setData(userRepository.findUserWithPage(start, length));
usersDto.setRecordsFiltered(totalUsers);
usersDto.setRecordsTotal(totalUsers); // Needed to show Pagination in Datatable
usersDto.setDraw(draw);
return usersDto;
}
DB Service
@Query(value="SELECT * FROM USERS LIMIT ?,?", nativeQuery = true)
List<User> findUserWithPage(int start, int length);
@Query(value="SELECT count(*) FROM USERS", nativeQuery = true)
int totalUsers();
You can download a working Model of the above demo here or see it on Github
You can also see a live demo here.
To Run
- Unzip/Clone
- Inside Project folder run “mvn clean install”
- mvn spring-boot:run
- Browse to http://localhost:8080/
2 Comments
peter · December 11, 2019 at 7:54 pm
Good post. Thank you very much. I am looking this solution.
Jyoti · August 29, 2020 at 12:55 am
How to make columns[0][data] dynamic. I have 4 columns title, name, age, dob. I want to send them dynamically. columns[0][data] take title everytime.