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/
Categories: DatatableJAVA

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.