jquery+ajax的查询翻页解决方案

总结下目前项目中用到的翻页解决方案。简要来说，前台利用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

View Code

这里的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     }

View Code

接口实现之后，前台通过实现和接口结构一样的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>&nbsp;" +
                        "<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);
        }
    }
});

View Code

  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 }

View Code

这里的js主要是在jquery上拓展了一个方法（jquery.initPager)，调用时初始化一个option参数，包括绑定数据的元素id，pagedata（初始一般都是从第一页开始，但是需要你指定页码的大小）

包括在翻页时，调用端的回调事件；这里绑定我用的是jquery的tmpl插件，所以需要你指定绑定结果的模板id；还会生成一段翻页按钮的html，pagerBind方法就是在这些html标签上绑定公用的翻页按钮事件，比如翻页调用时页码的处理，第一页需要禁用向前按钮等。需要注意的是，这里的js的pagedata结构需要和接口的pagedata的类结构一样才可以，否则无法配合工作。pagerBind里面会在每个按钮点击之后，调用之前提到的翻页时的回调事件，并把处理过后的pagedata传递过去，这样回调事件就能把数据进而传递到后台的接口中，最后接口根据前台计算出来的翻页信息，对数据库中的数据进行分页。

 

转载于:https://www.cnblogs.com/mixls1234/p/3225026.html