Spring MVC + Hibnerate + jQuery DataTables + Pagination

JQuery datatables (http://www.datatables.net/) always gets all data from server for its build-in pagination, it is not good for huge data, the better approach is to pass current position and page size to server and get data from database batch by batch.

so refer to http://datatables.net/usage/server-side, there are some para we must pass from client to server.

1) on the client side,


oTBExample2 = $("#example2").dataTable({
"bProcesing" : true,
"bServerSide" : true,
"bLenthChange" : false,
"iDisplayLength" : 10,
"sAjaxSource" : "users2.do",
"aoColumns" : [
{
"sTitle" : "User ID",
"mData" : "userid"
},
{
"sTitle" : "Password",
"mData" : "password"
},
{
"sTitle" : "Date & Time",
"mData" : "timeStamp"
},
{
"mData" : "checked",
"fnRender" : function(obj) {
if (obj.aData.checked == "1")
return '<input type="checkbox" name="aCheckBox" class="dt_checked" checked value="'+obj.aData.checked+'\"/>';

return '<input type="checkbox" name="aCheckBox"  class="dt_checked" value="'+obj.aData.checked+'\"/>';
}
},
{
"mData" : null,
"sClass" : "center",
"sDefaultContent" : '<a href="" class="editor_remove" >Delete</a>'
} ],
"fnServerData" : function(sSource, aoData, fnCallback) {
$.ajax({
"dataType" : 'json',
"type" : "GET",
"url" : sSource,
"data" : aoData,
"success" : fnCallback
});
},
"sPaginationType" : "full_numbers"

});// dataTable



<table id="example2" cellpadding="0" cellspacing="0" border="0"
class="display datatable">
<thead>
<tr>
<th width="20%">userid</th>
<th width="20%">password</th>
<th width="20%">time</th>
<th width="10%">selected</th>
<th width="10%">Delete</th>
</tr>
</thead>
<tbody>
</tbody>
</table>


2) on server side,



@RequestMapping(value = "/users2", produces = "application/json")
public @ResponseBody
String showUser(@RequestParam int iDisplayStart,
            @RequestParam int iDisplayLength, @RequestParam int sEcho) {
String method="showUser";
info(method,"para0---"+iDisplayStart);
info(method,"para1---"+iDisplayLength);
info(method,"para2---"+sEcho);
DataTablesTO<Account> dt = new DataTablesTO<Account>();

List<Account> accts = accountService.getAccounts(iDisplayStart,iDisplayLength);
List<Account> accts2 = accountService.getAccounts();
dt.setAaData(accts);  // this is the dataset reponse to client
dt.setiTotalDisplayRecords(accts2.size());  // // the total data in db for datatables to calculate page no. and position
dt.setiTotalRecords(accts2.size());   // the total data in db for datatables to calculate page no.
dt.setsEcho(sEcho);

return toJson(dt);
}


private String toJson(DataTablesTO<?> dt){
ObjectMapper mapper = new ObjectMapper();
try {
return mapper.writeValueAsString(dt);
} catch (JsonProcessingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}

need jackson jar files to support json conversion,

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;


public class DataTablesTO<T> implements java.io.Serializable{

private static final long serialVersionUID = -8220588043068200705L;
private List<T> aaData;
private int sEcho;
private Integer iTotalRecords;
private Integer iTotalDisplayRecords;
...... getter and setter.......
}

3) hibernate query


public List<Account> getAccounts(int currPosition, int pageSize) {

return sessionFactory.getCurrentSession().createQuery("from Account").setMaxResults(pageSize).setFirstResult(currPosition).list();
}


current position which means the first row to be shown on the page in the table.

4) actual result on page

5) this is the request data when call ajax

1. sEcho:
   1
2. iColumns:
   5
3. sColumns:
4. iDisplayStart:
   0
5. iDisplayLength:
   10
6. mDataProp_0:
   userid
7. mDataProp_1:
   password
8. mDataProp_2:
   timeStamp
9. mDataProp_3:
   checked
10. mDataProp_4:
11. sSearch:
12. bRegex:
    false
13. sSearch_0:
14. bRegex_0:
    false
15. bSearchable_0:
    true
16. sSearch_1:
17. bRegex_1:
    false
18. bSearchable_1:
    true
19. sSearch_2:
20. bRegex_2:
    false
21. bSearchable_2:
    true
22. sSearch_3:
23. bRegex_3:
    false
24. bSearchable_3:
    true
25. sSearch_4:
26. bRegex_4:
    false
27. bSearchable_4:
    true
28. iSortCol_0:
    0
29. sSortDir_0:
    asc
30. iSortingCols:
    1
31. bSortable_0:
    true
32. bSortable_1:
    true
33. bSortable_2:
    true
34. bSortable_3:
    true
35. bSortable_4:
    true

6) right now, the datatables does not support order by column, if click the second column to order by it,let's check the request data.

1. sEcho:
   7
2. iColumns:
   5
3. sColumns:
4. iDisplayStart:
   0
5. iDisplayLength:
   10
6. mDataProp_0:
   userid
7. mDataProp_1:
   password
8. mDataProp_2:
   timeStamp
9. mDataProp_3:
   checked
10. mDataProp_4:
11. sSearch:
12. bRegex:
    false
13. sSearch_0:
14. bRegex_0:
    false
15. bSearchable_0:
    true
16. sSearch_1:
17. bRegex_1:
    false
18. bSearchable_1:
    true
19. sSearch_2:
20. bRegex_2:
    false
21. bSearchable_2:
    true
22. sSearch_3:
23. bRegex_3:
    false
24. bSearchable_3:
    true
25. sSearch_4:
26. bRegex_4:
    false
27. bSearchable_4:
    true
28. iSortCol_0:
    1 // means sorted by the second column
29. sSortDir_0:
    asc // means order by asc, if click again, it will change to desc
30. iSortingCols:
    1
31. bSortable_0:
    true
32. bSortable_1:
    true
33. bSortable_2:
    true
34. bSortable_3:
    true
35. bSortable_4:
    true

so, we also need to change the hql as follows

from Account order by ${iSortCol_0}+1 ${sSortDir_0}