JS Datatables插件server-side方式處理分頁的示例代碼段

2024-04-27 15:10:06
字體：大中小
來源：轉載
供稿：網友
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/