JS Datatables插件server-side方式处理分页的示例代码段

Author: Kagula

测试日期：2017-02-07

测试环境：

[1]Chrome 54.x

[2]Metronic 4.7（这是我们JS代码依赖的模板库）

正文

html代码段

                            <div class="portlet-body">                                <div class="table-scrollable" style="padding: 16pt">                                    <table class="display" id="mainTable">                                        <thead>                                        <tr>                                            <th> # </th>                                            <th> 报警类型  </th>                                            <th> 报警时间 </th>                                            <th> 处理人员 </th>                                        </tr>                                        </thead>                                        <tbody>                                        </tbody>                                    </table>                                </div>                            </div>
JS代码段
        var lang = {            "sPRocessing": "处理中...",            "sLengthMenu": "每页 _MENU_ 条记录",            "sZeroRecords": "没有匹配结果",            "sInfo": "当前显示第 _START_ 至第 _END_ 条记录，共 _TOTAL_ 条记录。",            "sInfoEmpty": "当前显示第 0 至 0 条记录，共 0 条记录",            "sInfoFiltered": "(由 _MAX_ 项结果过滤)",            "sInfoPostFix": "",            "sSearch": "搜索:",            "sUrl": "",            "sEmptyTable": "表中数据为空",            "sLoadingRecords": "载入中...",            "sInfoThousands": ",",            "oPaginate": {                "sFirst": "首页",                "sPrevious": "上页",                "sNext": "下页",                "sLast": "末页",                "sJump": "跳转"            },            "oAria": {                "sSortAscending": ": 以升序排列此列",                "sSortDescending": ": 以降序排列此列"            }        };        myTable = $('#mainTable').dataTable( {            language:lang,  //中文提示            //"lengthChange": false,//不让用户选择每页item数量            "aLengthMenu": [[7, 25, 50, -1], [7, 25, 50, "全部"]],//让用户选择页面记录数量            "iDisplayLength": 7, //默认页面中的记录数量。            "bFilter": true, //过滤功能            //"bSort": false, //禁用排序            "aoColumnDefs": [ { "bSortable": false, "aTargets": [ 0 ] }],//设置第一列不可排序            //"aaSorting": [[ 0, "desc" ]], //设置第一列默认降序            "serverSide" : true,//服务端进行分页处理            "Ajax" : {                data : function(d) {                    console.log('custom request');                    //默认会有                    //start，表示要从第几行记录开始显示，例如0表示要取第一行记录开始的页面。                    //length，表示要取的记录数量，即页面大小。                    //[order][0][column]参数为0表示第一列，1表示第二列，[order][0][dir]参数为asc，表示要求服务端返回所指向列升序的表。                    //接住Shift可以选择多列排序，会添加下面的键值对请求                    //[order][1][column]参数为2表示第三列，3表示第四列，[order][1][dir]参数为asc，表示要求服务端返回所指向列升序的表。                    //达到多列排序的目的。                    //search[regex]的默认值为"false"，search[value]里存放的是待搜索字符串，如果没有东西要Search则里面是空字符串。                    return $.extend({}, d, {                        'data' : '{}'//自己增加个data请求参数                    })                },                dataSrc: function (json) {                    console.log('process response data from server side before display.');                    return json.data;                },                url : "/queryWarningInfo.do",                type : "POST",                crossDomain: true            },            columns: [                { "data": "idWarningInfo","width":24 },//这里的宽度指的是尽可能小的宽度                { "data": "warningType",                    render : function(data, type,row) {                    if (data == '0') {                        return "<span class=/"label label-sm label-warning/">入侵</span>";                    } else if(data == '1') {                        return "<span class=/"label label-sm label-danger/">设备故障</span>";                    } else if(data == '2') {                        return "设备离线";                    }                    return '未知';                    },                    "width":96                },                { "data": "eventTime" },                { "data": "StaffName","width":32 }            ]        } );    }    $('#mainTable tbody').on( 'click', 'tr', function () {        if ( $(this).hasClass('selected') ) {            $(this).removeClass('selected');        }        else {            myTable.$('tr.selected').removeClass('selected');            $(this).addClass('selected');            //取选中行的信息            var aData = myTable.fnGetData(this); // get datarow            if (null != aData)  // null if we clicked on title row            {                console.log(aData.idWarningInfo);            }        }    } );
服务端返回的JSON字符串
{    "code": "0",    "data": [        {            "StaffName": "kagula",            "deviceIndex": "0",            "eventTime": "2017-01-21 07:01:37",            "idWarningInfo": "1",            "portIndex": "0",            "warningType": "0"        },        {            "StaffName": "kagula",            "deviceIndex": "0",            "eventTime": "2017-01-21 07:01:48",            "idWarningInfo": "2",            "portIndex": "1",            "warningType": "0"        },        {            "StaffName": "kagula",            "deviceIndex": "1",            "eventTime": "2017-01-21 07:02:16",            "idWarningInfo": "3",            "portIndex": "0",            "warningType": "0"        },        {            "StaffName": "kagula",            "deviceIndex": "1",            "eventTime": "2017-01-21 07:02:16",            "idWarningInfo": "4",            "portIndex": "1",            "warningType": "0"        },        {            "StaffName": "",            "deviceIndex": "1",            "eventTime": "2017-02-04 01:03:33",            "idWarningInfo": "5",            "portIndex": "1",            "warningType": "0"        },        {            "StaffName": "",            "deviceIndex": "1",            "eventTime": "2017-02-04 05:46:49",            "idWarningInfo": "6",            "portIndex": "1",            "warningType": "1"        },        {            "StaffName": "",            "deviceIndex": "1",            "eventTime": "2017-02-04 05:46:59",            "idWarningInfo": "7",            "portIndex": "1",            "warningType": "2"        }    ],    "iTotalDisplayRecords": "100",    "iTotalRecords": "100",    "message": "success"}
HTML Render效果
C++辅助代码
代码段一
	class CJSDataTablesPluginReqData{	public:		CJSDataTablesPluginReqData(std::map<std::string, std::string> &mapReqArgs,			std::vector<std::string> &vecDBFieldName);//在DB中的字段名,数量同前端表中显示的列		//返回适合SQLite的SQL子句		std::string getWhereClause() { return _clauseWhere; }		std::string getOrderbyClause() { return _clauSEOrderby; }		std::string getLimitClause() { return _clauseLimit; }		bool isOK() {			if (_clauseOrderby.empty() == false && _clauseLimit.empty() == false)			{				return true;			}			return false;		}	private:		std::string _clauseWhere;		std::string _clauseOrderby;		std::string _clauseLimit;	};
代码段二
	CJSDataTablesPluginReqData::CJSDataTablesPluginReqData(std::map<std::string, std::string> &mapReqArgs,		std::vector<std::string> &vecDBFieldName)	{		try		{			//order by			//最多支持16个字段			char szBuf[1024];			std::string fieldName;			std::string fieldDir;			StringBuilder<char> sbClauseOrderby;			int index;			for (int i = 0; i < 16; i++)			{				memset(szBuf, sizeof(szBuf), 0);				sprintf_s(szBuf, "order[%d][column]", i);				if (mapReqArgs.find(szBuf) == mapReqArgs.end())					break;				index = boost::lexical_cast<int>(mapReqArgs[szBuf]);				if (index >= vecDBFieldName.size())					break;				fieldName = vecDBFieldName[index];				memset(szBuf, sizeof(szBuf), 0);				sprintf_s(szBuf, "order[%d][dir]", i);				if (mapReqArgs.find(szBuf) == mapReqArgs.end())					break;				fieldDir = mapReqArgs[szBuf];				if (_clauseOrderby.empty())				{					sbClauseOrderby.Append(" order by ");				}				else				{					sbClauseOrderby.Append(",");				}//if				sbClauseOrderby.Append(fieldName);				sbClauseOrderby.Append(" ");				sbClauseOrderby.Append(fieldDir);			}//for			_clauseOrderby = sbClauseOrderby.ToString();			//where clause			_clauseWhere = [&mapReqArgs,vecDBFieldName]()->std::string{				std::string search_value;				if (mapReqArgs.find("search[value]") == mapReqArgs.end())					return "";				search_value = mapReqArgs["search[value]"];				if (search_value.empty())					return "";				StringBuilder<char> sbClauseWhere;				for (int i = 0; i < vecDBFieldName.size(); i++)				{					if (i == 0)					{						sbClauseWhere.Append("where (");					}					else					{						sbClauseWhere.Append(" or ");					}					sbClauseWhere.Append(vecDBFieldName[i]);					sbClauseWhere.Append(" like '%");					sbClauseWhere.Append(search_value);					sbClauseWhere.Append("%'");				}				sbClauseWhere.Append(")");				return sbClauseWhere.ToString();			}();			//limit clause			int limit;			int offset;			if (mapReqArgs.find("start") == mapReqArgs.end())				return;			if (mapReqArgs.find("length") == mapReqArgs.end())				return;			offset = boost::lexical_cast<int>(mapReqArgs["start"]);			limit = boost::lexical_cast<int>(mapReqArgs["length"]);			if (limit <= 0)				return;			memset(szBuf, sizeof(szBuf), 0);			sprintf_s(szBuf, "limit %d offset %d", limit, offset);			_clauseLimit = szBuf;		}		catch (boost::bad_lexical_cast &e)		{					}//catch	}//function
 
参考资料
https://datatables.net/