总结下目前项目中用到的翻页解决方案。简要来说,前台利用js、jquery技术实现页面的绑定显示,和后台的数据交互,前台部分还进行了进一步的封装。后台用sqlserver的row_number来实现分页。
sql 部分。
1 ;with sql as ( 2 select a.id,a.tstamp 3 from system_history_log a inner join system_login b on a.id=b.id 4 where a.id=>100000 5 ), 6 row as ( 7 select sql.*,row_number() over(order by sql.tstamp desc) as rowNum from sql 8 ) 9 select row.*,cr.cou from row inner join (select count(1) cou from row) cr on 1=1 10 where rowNum BETWEEN 1 AND 15 order by row.tstamp
这里的sql一般放在wcf的接口中,通过定义的类结构,通常是query类似的名字,里面定义需要查询过滤的条件,这里比较重要的是pageData的定义,通过它来对row的行号进行定义。
这个pagedata一般可以这样定义:
1 public class PageData 2 { 3 public int StartNum { get; set; } 4 5 public int EndNum { get; set; } 6 7 public int MaxCount { get; set; } 8 9 public int PageSize { get; set; } 10 11 public int PageNum { get; set; } 12 }
接口实现之后,前台通过实现和接口结构一样的json数据,来实现功能。
jQuery.fn.extend({ initPager: function (initOption) { /// <summary> /// 在指定div上面,根据pagedata为其绑定翻页事件、当前页码信息. /// </summary> /// <param name="initOption" type="Json"> /// pageData: required.接口返回数据的pagedata,包含当前翻页的信息; /// onPageTurning: required.当翻页时,绑定数据的回调方法; /// pagerTemplate: 模板id.传空则会自动生成模板; /// </param> try { var result = initOption.pageData; var container = $(this).prop("id"); var template = initOption.pagerTemplate; var dataBindingEvent = $.GetType(initOption.onPageTurning) == "function" ? initOption.onPageTurning : function () { alert("function is not defined");}; var pageSize = checkParameter(initOption.pageData) == true ? initOption.pageData.PageSize : 25; var ranNum = rnd(); var isAppend = $("#" + container).data("isAppend"); if (isAppend == null || !isAppend) { if (!checkParameter(initOption.pagerTemplate)) { var noneDiv = $(this).after("<div class='none' id='noneDiv" + ranNum + "'>" + "<div id='PagerTemplate" + ranNum + "'>" + "<em class='tab_pagination_nav fr'>" + "<a id='lkbStart" + ranNum + "' href='javascript:void(0)'>Start</a> " + "<a id='lkbPrevious" + ranNum + "' href='javascript:void(0)'>Previous</a>(${StartNum}-${EndNum}/ ${MaxCount})" + "<a id='lkbNext" + ranNum + "' href='javascript:void(0)'>Next</a>" + "<a id='lkbEnd" + ranNum + "' href='javascript:void(0)'>End</a></em></div>" + "</div>"); template = "PagerTemplate" + ranNum; $("#" + container).data("rnd", ranNum); $("#" + container).data("isAppend", true); } else { ranNum = 0; } } else { if (!checkParameter(initOption.pagerTemplate)) { ranNum = $("#" + container).data("rnd"); template = "PagerTemplate" + $("#" + container).data("rnd"); } else { ranNum = 0; } } pagerBind(result, container, template, dataBindingEvent, pageSize, ranNum); } catch (e) { alert(e); } } });
1 function pagerBind(result, container, template, dataBindingEvent, pageSize, ranNum) { 2 var isDisabled = false; 3 var container = $("#" + container); 4 $(container).empty(); 5 6 if (result.EndNum > result.MaxCount) { 7 result.EndNum = result.MaxCount; 8 isDisabled = true; 9 } 10 RenderTemplatefunction(container, "#" + template, result); 11 var tDiv = $(container); 12 13 var lkbStart = ""; 14 var lkbPrevious = ""; 15 var lkbNext = ""; 16 var lkbEnd = ""; 17 if (ranNum == 0) { 18 lkbStart = tDiv.find("#lkbStart"); 19 lkbPrevious = tDiv.find("#lkbPrevious"); 20 lkbNext = tDiv.find("#lkbNext"); 21 lkbEnd = tDiv.find("#lkbEnd"); 22 } 23 else { 24 lkbStart = tDiv.find("#lkbStart" + ranNum); 25 lkbPrevious = tDiv.find("#lkbPrevious" + ranNum); 26 lkbNext = tDiv.find("#lkbNext" + ranNum); 27 lkbEnd = tDiv.find("#lkbEnd" + ranNum); 28 } 29 30 if (result.EndNum == result.MaxCount) { 31 if (result.MaxCount > result.PageSize) { 32 lkbStart.disabledSuper("disabled", false); 33 lkbPrevious.disabledSuper("disabled", false); 34 lkbNext.disabledSuper("disabled", true); 35 lkbEnd.disabledSuper("disabled", true); 36 } else { 37 lkbStart.disabledSuper("disabled", true); 38 lkbPrevious.disabledSuper("disabled", true); 39 lkbNext.disabledSuper("disabled", true); 40 lkbEnd.disabledSuper("disabled", true); 41 } 42 } 43 44 else if (result.StartNum == 1) { 45 $(lkbStart).disabledSuper("disabled", true); 46 $(lkbPrevious).disabledSuper("disabled", true); 47 $(lkbNext).disabledSuper("disabled", false); 48 $(lkbEnd).disabledSuper("disabled", false); 49 } 50 51 else { 52 $(lkbStart).disabledSuper("disabled", false); 53 $(lkbPrevious).disabledSuper("disabled", false); 54 $(lkbNext).disabledSuper("disabled", false); 55 $(lkbEnd).disabledSuper("disabled", false); 56 } 57 58 59 if (result.StartNum == 0) { 60 $(lkbStart).disabledSuper("disabled", true); 61 $(lkbPrevious).disabledSuper("disabled", true); 62 $(lkbNext).disabledSuper("disabled", true); 63 $(lkbEnd).disabledSuper("disabled", true); 64 } 65 66 if (isDisabled) { 67 $(lkbStart).disabledSuper("disabled", true); 68 $(lkbPrevious).disabledSuper("disabled", true); 69 $(lkbNext).disabledSuper("disabled", true); 70 $(lkbEnd).disabledSuper("disabled", true); 71 } 72 lkbStart.clickLoginCallBack(function () { 73 result.StartNum = 1; 74 result.PageNum = 1; 75 result.EndNum = result.MaxCount > pageSize ? pageSize : result.MaxCount; 76 77 dataBindingEvent(result); 78 }); 79 80 lkbPrevious.clickLoginCallBack(function () { 81 result.PageNum -= 1; 82 result.StartNum = (result.PageNum - 1) * pageSize + 1; 83 result.EndNum = (result.PageNum - 1) * pageSize + pageSize; 84 85 dataBindingEvent(result); 86 }); 87 88 89 90 lkbNext.clickLoginCallBack(function () { 91 result.PageNum += 1; 92 result.StartNum = (result.PageNum - 1) * pageSize + 1; 93 result.EndNum = result.PageNum * pageSize > result.MaxCount ? result.MaxCount : result.PageNum * pageSize; 94 95 dataBindingEvent(result); 96 }); 97 98 lkbEnd.clickLoginCallBack(function () { 99 result.PageNum = result.MaxCount % pageSize > 0 ? parseInt(result.MaxCount / pageSize) + 1 : parseInt(result.MaxCount / pageSize); 100 result.StartNum = parseInt(result.MaxCount / pageSize) * pageSize + 1; 101 result.EndNum = result.MaxCount; 102 103 dataBindingEvent(result); 104 }); 105 }
这里的js主要是在jquery上拓展了一个方法(jquery.initPager),调用时初始化一个option参数,包括绑定数据的元素id,pagedata(初始一般都是从第一页开始,但是需要你指定页码的大小)
包括在翻页时,调用端的回调事件;这里绑定我用的是jquery的tmpl插件,所以需要你指定绑定结果的模板id;还会生成一段翻页按钮的html,pagerBind方法就是在这些html标签上绑定公用的翻页按钮事件,比如翻页调用时页码的处理,第一页需要禁用向前按钮等。需要注意的是,这里的js的pagedata结构需要和接口的pagedata的类结构一样才可以,否则无法配合工作。pagerBind里面会在每个按钮点击之后,调用之前提到的翻页时的回调事件,并把处理过后的pagedata传递过去,这样回调事件就能把数据进而传递到后台的接口中,最后接口根据前台计算出来的翻页信息,对数据库中的数据进行分页。
何时结束黑梦,迎接艳阳天?