Infinite drawing of database table rows
Infinite drawing of database table rows
algorihtmyc
Posts: 4Questions: 1Answers: 0
I try to figure out how Jquery DataTable plugin work with Spring Web App. I need to make my pagination on the server side, since I have lot of rows in a real project I am working. I followed this tutorial : https://medium.com/@gustavo.ponce.ch/spring-boot-jquery-datatables-a2e816e2b5e9
which is shared on the homepage of this current website (datatables.net).
I tried changing the Ajax call by adding the serverSide:true and processing: true. The table now passed in serverside mode with no problem and each and every click on the page makes an Ajax call towards the controller. But the table keeps drawing endless pages with always same range(16 rows in my employee database table) of information and I don't understand why. Any help will be highly appreciated thanks a lot!
Screenshots of the view webapp (Look right bottom page number: 1,2 until ...)
Example using ServerSide mode with dummy data:
My datatable.js:
$(document).ready(function() {
var table = $('#employeesTable').DataTable({
"processing" : true,
"serverSide" : true,
"sAjaxSource" : "/employees",
"sAjaxDataProp" : "",
"order" : [ [ 0, "asc" ] ],
"aoColumns" : [ {
"mData" : "id"
}, {
"mData" : "name"
}, {
"mData" : "lastName"
}, {
"mData" : "email"
}, {
"mData" : "phone"
} ]
})
});
EmployeeRestController class:
@RestController
public class EmployeeRestController {
@Autowired
private EmployeeService employeeService;
@RequestMapping(path = "/employees", method = RequestMethod.GET)
public List<Employee> getAllEmployees() {
return employeeService.getAllEmployees();
}
@RequestMapping(value = "/employee/{id}", method = RequestMethod.GET)
public Employee getEmployeeById(@PathVariable("id") long id) {
return employeeService.getEmployeeById(id);
}
}
HTML snippet of body:
<body>
<h1>Employees Table</h1>
<table id="employeesTable" class="display">
<!-- Header Table -->
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<!-- Footer Table -->
<tfoot>
<tr>
<th>Id</th>
<th>Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</tfoot>
</table>
</body>
JSON response when clicking on pages NOTE the 16 rows is normal because they are all the same but have different ID this is just dummy data that was inserted in my table employees in database using H2:
[
{
"id":1,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":2,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":3,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":4,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":5,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":6,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":7,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":8,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":9,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":10,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":11,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":12,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":13,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":14,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":15,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
},
{
"id":16,
"name":"Bob",
"lastName":"Marley",
"email":"one@love.com",
"phone":"6483748590"
}
]
Answers
The response from the server does not contain the information that DataTables needs for server-side processing. The server-side processing manual page details the parameter that are required. You must include the number of records with and without the filter and the server must also return the records for the requested page.
Server-side processing is typically only needed if you have tens of thousands or more rows.
Allan
Thanks for your reply I really do struggle to find the solution. What I showed you is just a dump project for me understanding how the datatables serverside do work. In the real project I am working on for an organisation I do have to track sent emails and invoices to customers, I have to display this in a table I have more than sixty thousand rows in one among other tables. I honestly did have a look but I have difficulty to understand , i find the manual not that well explained. However i will keep trying until i find the solution thanks for the support.
Did you try asking the author of the Spring tutorial?
Hi dear, Yes I did i left a comment (response) on his post...
I'd suggest having a look at this example. Have a look at the JSON response from the server (it is available in the "Ajax" tab below the demo table) and note how it has the parameters that are mentioned in the documentation:
You must have those parameters if you are going to use server-side processing. They are not optional. The values will obviously be different, but they must be present.
The documentation I linked to before details exactly what each parameter is used for.
Allan
Thanks Allan for the answer but how to implicitely set those parameters to get the result it is explained in the example?
I tried changing to this in my Ajax Call but i still get the same Json Response as Showed in the post:
No, because it is utterly dependent upon the server-side script you are using. You mentioned you are using Spring - that's not a framework I've ever worked with, so I can't tell you how to write a program for it I'm afraid. All I can say is that the server must respond with the parameters that are detailed in the manual.
Exactly how you get those values - I have no idea in Spring! The manual explains what they are and what data you need in order to be able to generate the value (e.g. a simple COUNT(*) to get the total number of records).
Allan
Hi, i hope you're great!
Im dev. an app like this, with the same Controller and Rest that u have, but my Employee Model have anothers Att. like Experiencie And Department (there are Relational tables, and models).
My problem with this its that i cant show the registries of my BD in my DataTable. ..
But, in the Navigator Log it show that recivieved and AJAX Response from Employees ... Employees its a RestController where im consult the List of Employees.. (employeeServices.getAll()))