Server Side Pagination using jQuery Datatable

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/