【DataTable】关于实现DataTable后端分页过程中的一些问题总结

刺骨的言语ヽ痛彻心扉 2022-03-22 15:14 1008阅读 0赞

# 【DataTable】关于实现DataTable后端分页过程中的一些问题总结 #

2018年06月21日 12:01:25 [Crayoncxy][] 阅读数：2376

# 一、场景 #

公司新开发的一个web项目，项目中一个功能是从失败交易流水表中按日期查询失败的交易，以列表的形式展示出来。前端列表使用了DataTable，DataTable自带前端分页和后端分页。所谓前端分页就是一次性从数据库中查出所有数据返回给前端，前端自动进行分页。这种处理方式在数据量较小的情况下还可以，当数据量较大（具体数据量没有测试）会导致前端加载数据缓慢卡顿，同时因为后端一次性从数据库查出大量数据放在内存中，会导致内存资源消耗过大而卡顿甚至宕机。为了解决这个瓶颈问题，采用后端分页的形式。后端分页即前端传递当前页码以及当前页显示的数据量给后端，后端只查询当前页要进行展示的数据。从而避开了由于数据量过大而导致的卡顿问题。

## 二、基础代码 ##

DataTable默认的分页机制为前端分页，此处不过多陈述，通过查找资料了解到，后端分页需要将原来的

bServerSide:false

修改为：

bServerSide:true

后端分页对返回的JSON数据格式有要求，具体格式如下：

{"sEcho":"1","iTotalRecords":"0","iTotalDisplayRecords":"0","aaData":[]}

其中sEcho是前端传递的，只需获取然后原数返回即可，iTotalRecords字面理解意思是当前数据表中总的记录数，iTotalDisplayRecords是当前页面要展示的记录数，aaData为返回列表的数据。

这里先展示一版基础代码，也就是我从网上查找资料写的代码，通过基础代码，后边一步一步的发现问题解决问题。

HTML部分：

1.  `</div>`
2.  `<div class="mr-20 ml-20">`
3.  `<table id="trafficMonitorFailed" class="table table-border table-bordered table-hover table-bg table-sort">`
4.  `<thead>`
5.  `<tr class="text-c">`
6.  `<th>交易日期</th>`
7.  `<th>交易时间</th>`
8.  `<th>交易流水号</th>`
9.  `<th>交易唯一标识</th>`
10. `<th>渠道</th>`
11. `<th>交易响应时间</th>`
12. `<th>交易分类</th>`
13. `<th>交易编码</th>`
14. `<th>交易描述</th>`
15. `<th>错误码</th>`
16. `<th>错误描述</th>`
17. `</tr>`
18. `</thead>`
19. `</table>`
20. `</div>`

JS部分：

1.  `function dataTableDraw(){`
2.  ` $("#trafficMonitorFailed").dataTable({`
3.  `  bServerSide:true, //开启后端分页`
4.  `  bDestroy: true,         //下边两个属性应该是重载数据相关的 不加在加载数据会弹窗报错 点击确定后显示数据`
5.  `  bRetrieve: true,`
6.  `  bProcessing: true, //显示加载数据时的提示`
7.  `  bInfo:true,  //显示信息 如 当前x页 共x条数据等`
8.  `  bSort:true,  //允许排序`
9.  `  bFilter:true,  //检索、筛选框`
10. `  sAjaxSource: rootUrl + "getTrafficMonitorFailedList", //请求url`
11. `  bLengthChange:true, //支持变更页面显示数据行数`
12. `  sPaginationType: "bootstrap", //翻页风格`
13. `  bPaginate:true,  //显示翻页按钮`
14. `  fnServerData: retrieveData, //执行函数`
15. `  aoColumns:[//列表元素  支持多种属性`
16. `                   { "mData": "tranDate","fnRender":function(data,val){`
17. `        var JSONDate = new Date(val.time); `
18. `        return JSONDate.format('yyyy-MM-dd');`
19. `       },"width":"200px"},`
20. `                   { "mData": "tranTime","fnRender":function(data,val){`
21. `        var JSONTime = new Date(val.time);`
22. `        return JSONTime.format('yyyy-MM-dd HH:mm:ss');`
23. `       }},`
24. `                   { "mData": "seqNo"},`
25. `                   { "mData": "platformId"},`
26. `                   { "mData": "channel"},`
27. `                   { "mData": "costTime"},`
28. `                   { "mData": "reqType"},`
29. `                   { "mData": "reqInterface"},`
30. `                   { "mData": "reqDesc","bSortable":false},  //不允许当前页排序`
31. `                   { "mData": "retCode","bSortable":false},`
32. `                   { "mData": "retMsg","bSortable":false}`
33. `               ],`
34. `      oLanguage: { `
35. `       "sProcessing" : "正在加载中......", `
36. `       "sLengthMenu" : "_MENU_", `
37. `       "sZeroRecords" : "无记录", `
38. `       "sEmptyTable" : "表中无数据存在！", `
39. `       "sInfo" : "当前显示 _START_ 到 _END_ 条，共 _MAX_  条记录", `
40. `       "sInfoEmpty" : "没有数据", `
41. `       "sInfoFiltered" : "数据表中共为 _TOTAL_ 条记录", `
42. `       "sSearch" : " ", `
43. `       "oPaginate" : { `
44. `        "sFirst" : " 首页 ", `
45. `        "sPrevious" : " 上一页 ", `
46. `        "sNext" : " 下一页 ", `
47. `        "sLast" : " 末页 " `
48. `        } `
49. ` } `
50. ` });`
51. ` $(".dataTables_wrapper .dataTables_filter input").attr("placeholder","检索内容");`
52. `}`
53. `//对应上边的回调函数 参数个数不变 名字可改 第一个为请求url  第二个为上送数据 第三个为回调函数`
54. `function retrieveData(sSource,aoData,fnCallback) {`
55. ` var startDate = {`
56. `   "name":"startDate",`
57. `   "value":$("#from").val()`
58. ` }`
59. ` var endDate = {`
60. `   "name":"endDate",`
61. `   "value":$("#to").val()`
62. ` }`
63. ` //我这里按照请求数据的格式增加了自己的查询条件 请求数据格式固定为 name-value的格式 可以使用`
64. ` //alert打印查看 包含了基本的页码、页面数据元素、等信息以及新增的查询条件`
65. ` aoData.push(startDate);`
66. ` aoData.push(endDate);`
67. ` $.ajax({`
68. `     url : sSource,//这个就是请求地址对应sAjaxSource`
69. `     data : {"aoData":JSON.stringify(aoData)},//这个是把datatable的一些基本数据传给后台,比如起始位置,每页显示的行数`
70. `     type : 'post',`
71. `     dataType : 'json',`
72. `     async : false,`
73. `     success : function(result) {`
74. `         fnCallback(result);//把返回的数据传给这个方法就可以了,datatable会自动绑定数据的`
75. `     },`
76. `     error : function(msg) {`
77. `     }`
78. ` });`
79. `}`

后端Controller层：

第一版的基础代码如上，其中有一些如数据Model的代码，不同业务场景Model不同，此处不做列举。

# 三、问题描述/解决 #

## Q1.Cannot read property 'length' of undefined ##

上述代码运行之后，页面加载之后一直显示加载中，F12到调试模式可以看到控制台报错“Cannot read property 'length of undefined'”，而且这个是jquery报的错，可以说是非常恼火了。经过分析，可能是返回的数据格式不对，因此在js的回调函数中进行了修改，将JSON字符串转换成JSON数据的格式，修改后如下：

1.  `$.ajax({`
2.  `url : sSource,//这个就是请求地址对应sAjaxSource`
3.  `data : {"aoData":JSON.stringify(aoData)},//这个是把datatable的一些基本数据传给后台,比如起始位置,每页显示的行数`
4.  `type : 'post',`
5.  `dataType : 'json',`
6.  `async : false,`
7.  `success : function(result) {`
8.  `var ret = eval("(" + result + ")");`
9.  `fnCallback(ret);//把返回的数据传给这个方法就可以了,datatable会自动绑定数据的`
10. `},`
11. `error : function(msg) {`
12. `}`
13. `});`

修改之后运行，此问题解决。

## Q2.DataTables warning (table id = 'trafficMonitorFailed'):Requested unknown parameter '0' from the data source for row 0 ##

上述代码运行之后，页面弹窗报错“DataTables warning (table id ='trafficMonitorFailed'):Requested unknown parameter '0' from the data source for row 0”这个问题诈一看也是一脸蒙蔽，后来发现应该是dataTable版本的问题，需要将数据表中的mData修改为mDataProp,修改之后如下：

1.  `{ "mDataProp": "tranDate","fnRender":function(data,val){`
2.  `var JSONDate = new Date(val.time);`
3.  `return JSONDate.format('yyyy-MM-dd');`
4.  `},"width":"200px"},`
5.  `{ "mDataProp": "tranTime","fnRender":function(data,val){`
6.  `var JSONTime = new Date(val.time);`
7.  `return JSONTime.format('yyyy-MM-dd HH:mm:ss');`
8.  `}},`
9.  `{ "mDataProp": "seqNo"},`
10. `{ "mDataProp": "platformId"},`
11. `{ "mDataProp": "channel"},`
12. `{ "mDataProp": "costTime"},`
13. `{ "mDataProp": "reqType"},`
14. `{ "mDataProp": "reqInterface"},`
15. `{ "mDataProp": "reqDesc","bSortable":false},`
16. `{ "mDataProp": "retCode","bSortable":false},`
17. `{ "mDataProp": "retMsg","bSortable":false}`

修改之后运行，问题解决。

## Q3.数据明明很多，DataTable仅显示了一页的数据，并且没有显示其它页的按钮，不可以翻页。 ##

这个问题的意思是，我数据库中有很多数据，F12调试也能看到返回了一页10条数据，并且iTotalRecords为32，按道理应该显示一页数据之后，可以进行翻页，总共显示4页，但是实际的页面却只显示了一页数据，列表下方的翻页处也仅仅有“1”的页签，没有其它的页签，上一页下一页都不能点击。这个问题困扰了我许久，后来私信网上一位大神给出了解决方案。原因是iTotalRecords和iTotalDisplayRecords数据放反了。这个我到现在也没有理解，按照字面意思itotalRecords确实是应该放总查询数据量，iTotalDisplayRecords为当前页要展示的数据量。而实际的使用过程中，这两个数据应该是反了过来。不知道其它人有没有遇到这种情况。

修改代码如下：

1.  `getObj.put("iTotalRecords",trafficMonitorFailed.size());`
2.  `getObj.put("iTotalDisplayRecords",count);`

即将原来两个放置数据统计个数的值互换。运行之后，问题解决，[心心念念][Link 1]的后端分页终于实现了。

## Q4.后端分页实现之后，检索筛选、排序功能失效。 ##

实现了基本的后端分页，点击了几页试了一下，分页效果没问题，但是检索跟排序的功能都没有反应。于是F12开启调试模式，发现点击排序和在检索框输入文字之后，都发起了后台请求，因此可以判定，后端分页的检索跟排序功能需要后端代码实现。实现原理，在前端传递的aoData中，会储存要排序的列、排序的方式以及检索的内容。因此可以通过后端修改SQL的形式完成。

修改之后的Controller代码为：

1.  `@RequestMapping(value="/page/getTrafficMonitorFailedList",method=RequestMethod.POST)`
2.  `@ResponseBody`
3.  `public String getTrafficMonitorFailedList(String aoData){`
4.  `List<TrafficMonitorFailed> trafficMonitorFailed = new ArrayList<TrafficMonitorFailed>();`
5.  `JSONArray jsonarray=(JSONArray) JSONArray.fromObject(aoData);//json格式化用的是fastjson`
6.  `if(jsonarray == null||jsonarray.size() ==0){`
7.  `return WebFactory.createErrorResponse("错误的查询条件");`
8.  `}`
9.  `String startDate = formatDate.format(new Date());`
10. `String endDate = formatDate.format(new Date());`
11. `String sEcho = null;`
12. `int iDisplayStart = 0; // 起始索引`
13. `int iDisplayLength = 10; // 每页显示的行数`
14. `int orderColumn = 0;//默认排序列`
15. `String orderDir = "asc";//默认排序方式为升序`
16. `String sSearch = "";//默认检索内容`
17. `int count = 0;`
18. `for (int i = 0; i < jsonarray.size(); i++) {`
19. `JSONObject obj = (JSONObject) jsonarray.get(i);`
20. `if (obj.get("name").equals("sEcho"))`
21. `sEcho = obj.get("value").toString();`
22.  
23. `if (obj.get("name").equals("iDisplayStart"))`
24. `iDisplayStart =Integer.parseInt(obj.get("value").toString());`
25.  
26. `if (obj.get("name").equals("iDisplayLength"))`
27. `iDisplayLength = Integer.parseInt(obj.get("value").toString());`
28.  
29. `if (obj.get("name").equals("startDate"))`
30. `startDate = obj.get("value").toString();`
31.  
32. `if (obj.get("name").equals("endDate"))`
33. `endDate = obj.get("value").toString();`
34.  
35. `if (obj.get("name").equals("iSortCol_0"))`
36. `orderColumn = Integer.parseInt(obj.get("value").toString());`
37.  
38. `if (obj.get("name").equals("sSortDir_0"))`
39. `orderDir = obj.get("value").toString();`
40.  
41. `if (obj.get("name").equals("sSearch"))`
42. `sSearch = obj.get("value").toString();`
43. `}`
44.  
45. `if("".equals(sSearch)||sSearch == null){`
46. `trafficMonitorFailed = this.trafficMonitorFailedService.getTrafficMonitorFailedList(startDate, endDate, iDisplayStart, iDisplayLength, orderColumn, orderDir);`
47. `count = this.trafficMonitorFailedService.getTrafficMonitorFailedListCount(startDate, endDate).getTotal();`
48. `}else{`
49. `trafficMonitorFailed = this.trafficMonitorFailedService.getTrafficMonitorFailedListSearch(startDate, endDate, iDisplayStart, iDisplayLength, orderColumn, orderDir,sSearch);`
50. `count = this.trafficMonitorFailedService.getTrafficMonitorFailedListCountSearch(startDate, endDate,sSearch).getTotal();`
51. `}`
52. `JSONObject getObj = new JSONObject();`
53. `getObj.put("sEcho", sEcho);// DataTable前台必须要的`
54. `getObj.put("iTotalRecords",trafficMonitorFailed.size());//显示的行数,这个要和上面写的一样`
55. `getObj.put("iTotalDisplayRecords",count);//总行数`
56. `getObj.put("aaData", trafficMonitorFailed);//把查到数据装入aaData,要以JSON格式返回`
57. `return getObj.toString();`
58.  
59. `}`

这里前端传递的orderColumn是int类型，标记的是列的序号，SQL语句中可以判断序号然后ORDER BY指定的列，orderDir传递的是排序方式有两种一种是ASC另一种是DESC。sSearch传递的是检索框的内容，这里为了避免没有检索的情况还使用like语句模糊查找而影响效率，没有想到其它办法，单单的使用了判断sSearch如果为空则不使用like语句查找。多说一句，分页查询使用的sql语句我这里使用了limit a，b的形式，从a+1位置查，查b条数据。这里忘了贴SQL语句，大概的形式就是select xxx from xxx where xxx order by xxx asc/desc limit a,b 这种，如果后期数据量大了出现瓶颈再继续优化。

## Q5.给DataTable增加横向滚动条 ##

为了美观，我想保证每一条数据都在一行上显示，不进行换行。然后就会出现数据过长，一页显示不下的情况，因此需要增加滚动条。网上查到不同版本使用的形式不同，我这里是使用如下形式

sScrollX:"100%"

还有另一种方式是将“100%”改为true的形式。增加滚动条还需要设置文本框的数据处于一行显示而不是自动换行。

修改html代码如下：

1.  `</div>`
2.  `<div class="mr-20 ml-20">`
3.  `<table id="trafficMonitorFailed" class="table table-border table-bordered table-hover table-bg table-sort" style="white-space:nowrap">`
4.  `<thead>`
5.  `<tr class="text-c">`
6.  `<th>交易日期</th>`
7.  `<th>交易时间</th>`
8.  `<th>交易流水号</th>`
9.  `<th>交易唯一标识</th>`
10. `<th>渠道</th>`
11. `<th>交易响应时间</th>`
12. `<th>交易分类</th>`
13. `<th>交易编码</th>`
14. `<th>交易描述</th>`
15. `<th>错误码</th>`
16. `<th>错误描述</th>`
17. `</tr>`
18. `</thead>`
19. `</table>`
20. `</div>`

即增加了样式：

style="white-space:nowrap"

至此，后端分页的功能全部实现。如有不足，还望指正。

[Crayoncxy]: https://me.csdn.net/chenxyt
[Link 1]: https://www.baidu.com/s?wd=%E5%BF%83%E5%BF%83%E5%BF%B5%E5%BF%B5&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd