sql 分页加载数据

首先，我们需要写一条sql语句，每次查询多少条数据

<!-- 分页 -->
    <select id="articlePageList" resultType="Article">
        SELECT
        a.id AS "id",
        a.category_id AS "category.id",
        a.title AS "title",
        a.create_date AS "createDate"     // 上面这四个是我们需要的查出来的参数
        FROM cms_article a      
        WHERE
        a.category_id = #{cid}  // cid:传入的参数条件
        AND a.del_flag = 0
        ORDER BY a.weight DESC, a.create_date DESC
        LIMIT #{pageNo}, #{pageSize}  // pageNo:当前页   pageSize:每页显示多少条数据
    </select>

另外，我们还需要一条sql语句来查询相关条件的总条数

 <!--查询文章总条数 count-->
    <select id="totalCount" resultType="Article">
        SELECT *
        FROM cms_article a
        WHERE
        a.category_id = #{cid}
        AND  a.del_flag = 0
    </select>

接下来，我们需要去dao层

    // 查询条数
    public List<Article> totalCount(String cid);
    // 根据栏目id 当前页 条数 查询文章列表
    public List<Article> articlePageList(@Param("pageNo") int pageNo, @Param("pageSize") int pageSize, @Param("cid") String cid);

service层

    // 查询条数
    public List<Article> totalCount(String cid) {
        return articleDao.pageCount(cid);
    }

    // 根据栏目id 当前页 条数 查询文章列表
    public List<Article> articlePageList(int pageNo, int pageSize, String cid) {
        // 计算当前页 
        pageNo = pageNo - 1;
        pageNo = pageNo * pageSize;
        return articleDao.articlePageList(pageNo, pageSize, cid);
    }

    // 返回一个文章总数
    public int totalCounts(String cid) {
        // 文章总条数
        List<Article> articleCount = pageCount(cid);
        int totalCount = articleCount.size();
        return totalCounts;
    }

下面我是用接口来写的，在前段用ajax直接调用这个接口

    @RequestMapping(value = "findArticleList")
    @ResponseBody
    public Map<String, Object> findArticleList(String cid, String pageNo) {
        Map<String, Object> articleListMap = new HashMap<String, Object>();
        // 每页文章条数
        String pageSize = "10";
        List<Article> articlePage = articleService.articlePageList(Integer.parseInt(pageNo), Integer.parseInt(pageSize), cid);
        articleListMap.put("articlePage", articlePage);
        // 文章页数
        int totalCount = articleService.totalCount(cid);
        articleListMap.put("totalCount", totalCount);
        
        return articleListMap;
    }

我把ajax调用接口单独写在一个js文件

function articleListApi(cid, pageNo) {

    $.ajax({
        url: artlistUrl,
        type: 'post',
        dataType: 'json',
        data: {
            // 这里是将参数传到后台
            cid: cid,
            pageNo: pageNo,
        },
        success: function (data) {
            $("#artLis").html("");
            var list = data["articlePage"];
            var totalCount = data["totalCount"];
            getCount(totalCount);
            if (list == "undefined" || list == null || list == "") {
                $("#pageDiv").hide();
            } else {
                for (var i = 0; i < list.length; i++) {
                    var month = list[i].createDate.substring(5, 7);
                    var day = list[i].createDate.substring(8, 10);
                    $("#artLis").append("<li><a href='articleDetail?cid=" + list[i].category.id + "&id=" + list[i].id + "'><span>" + list[i].title + "</span><span class='fr'>" + month + "月" + day + "日</span></a></li>");
                }
                $("#pageDiv").show();
            }
        }
    });


}
// 从接口方法获取数据总数的方法
function getCount(totalCount) {
    pageClik(totalCount);
}

下面这里是在jsp页面写的调用方法

// 这个是我放在页面的一个隐藏标签，用来接收链接跳转过来的ID，也就是cid，需要把这个参数拿到传给后台接口
<input id="liCid" type="hidden" value="${param.cid}"/>
        <%-- 分页插件--%>
         // maxshowpageitem="5"  这个参数为显示的页码数  pagelistcount="10"   这个参数为每页显示多少条数据
        <div id="pageDiv">
            <div>
                <ul class="page" maxshowpageitem="5" pagelistcount="10" id="page"></ul>
            </div>
        </div>
---------------------------------------------------------------------
// 接口调用的链接地址
var artlistUrl = "${ctxf}/api/article/findArticleList";
$(document).ready(function () {
        // 拿到隐藏标签的值
        var cid = $("#liCid").val();
        // 第一次加载默认第一页
        var pageNo = 1;
        articleListApi(cid, pageNo);
    });

这个为分页插件的调用方法，也是写在jsp页面

<script type="text/javascript">
    var pageNo = 1;
    function pageClik(totalCount) {
        var cid = $("#liCid").val();
        var GG = {
            "kk": function (mm) {
              // 这里的 mm 为点击的第几页
                pageNo = mm;
            articleListApi(cid, pageNo);
            }
        }
        $("#page").initPage(totalCount, pageNo, GG.kk);
    }
</script>

下面直接贴出 分页的css文件，和分页的封装方法

.page {
    list-style: none;
}

.page > li {
    float: left;
    padding: 5px 10px;
    cursor: pointer;
}

.page .pageItem {
    border: solid thin #DDDDDD;
    margin: 5px;
}

.page .pageItemActive {
    border: solid thin #0099FF;
    margin: 5px;
    background-color: #0099FF;
    color: white;
}

.page .pageItem:hover {
    border: solid thin #0099FF;
    background-color: #0099FF;
    color: white;
}

.page .pageItemDisable {
    border: solid thin #DDDDDD;
    margin: 5px;
    background-color: #DDDDDD;
}

/**
 * Created by lhs on 2017/9/6.
 */
$.fn.extend({
    "initPage": function (listCount, currentPage, fun) {
        var maxshowpageitem = $(this).attr("maxshowpageitem");
        if (maxshowpageitem != null && maxshowpageitem > 0 && maxshowpageitem != "") {
            page.maxshowpageitem = maxshowpageitem;
        }
        var pagelistcount = $(this).attr("pagelistcount");
        if (pagelistcount != null && pagelistcount > 0 && pagelistcount != "") {
            page.pagelistcount = pagelistcount;
        }

        var pageId = $(this).attr("id");
        page.pageId = pageId;
        if (listCount < 0) {
            listCount = 0;
        }
        if (currentPage <= 0) {
            currentPage = 1;
        }
        page.setPageListCount(pageId, listCount, currentPage, fun);

    }
});
var page = {
    "maxshowpageitem": 5,//最多显示的页码个数
    "pagelistcount": 10,//每一页显示的内容条数
    /**
     * 初始化分页界面
     * @param listCount 列表总量
     */
    "initWithUl": function (pageId, listCount, currentPage) {

        var pageCount = 1;
        if (listCount > 0) {
            var pageCount = listCount % page.pagelistcount > 0 ? parseInt(listCount / page.pagelistcount) + 1 : parseInt(listCount / page.pagelistcount);
        }
        var appendStr = page.getPageListModel(pageCount, currentPage);
        $("#" + pageId).html(appendStr);
    },
    /**
     * 设置列表总量和当前页码
     * @param listCount 列表总量
     * @param currentPage 当前页码
     */
    "setPageListCount": function (pageId, listCount, currentPage, fun) {
        listCount = parseInt(listCount);
        currentPage = parseInt(currentPage);
        page.initWithUl(pageId, listCount, currentPage);
        page.initPageEvent(pageId, listCount, fun);

    },
    "initPageEvent": function (pageId, listCount, fun) {
        $("#" + pageId + ">li[class='pageItem']").on("click", function () {
            if (typeof fun == "function") {
                fun($(this).attr("page-data"));
            }
            page.setPageListCount(pageId, listCount, $(this).attr("page-data"), fun);
        });
    },
    "getPageListModel": function (pageCount, currentPage) {
        var prePage = currentPage - 1;
        var nextPage = currentPage + 1;
        var prePageClass = "pageItem";
        var nextPageClass = "pageItem";
        if (prePage <= 0) {
            prePageClass = "pageItemDisable";
        }
        if (nextPage > pageCount) {
            nextPageClass = "pageItemDisable";
        }
        var appendStr = "";
        appendStr += "<li class='" + prePageClass + "' page-data='1' page-rel='firstpage'>首页</li>";
        appendStr += "<li class='" + prePageClass + "' page-data='" + prePage + "' page-rel='prepage'><上一页</li>";
        var miniPageNumber = 1;
        if (currentPage - parseInt(page.maxshowpageitem / 2) > 0 && currentPage + parseInt(page.maxshowpageitem / 2) <= pageCount) {
            miniPageNumber = currentPage - parseInt(page.maxshowpageitem / 2);
        } else if (currentPage - parseInt(page.maxshowpageitem / 2) > 0 && currentPage + parseInt(page.maxshowpageitem / 2) > pageCount) {
            miniPageNumber = pageCount - page.maxshowpageitem + 1;
            if (miniPageNumber <= 0) {
                miniPageNumber = 1;
            }
        }
        var showPageNum = parseInt(page.maxshowpageitem);
        if (pageCount < showPageNum) {
            showPageNum = pageCount
        }
        for (var i = 0; i < showPageNum; i++) {
            var pageNumber = miniPageNumber++;
            var itemPageClass = "pageItem";
            if (pageNumber == currentPage) {
                itemPageClass = "pageItemActive";
            }

            appendStr += "<li class='" + itemPageClass + "' page-data='" + pageNumber + "' page-rel='itempage'>" + pageNumber + "</li>";
        }
        appendStr += "<li class='" + nextPageClass + "' page-data='" + nextPage + "' page-rel='nextpage'>下一页></li>";
        appendStr += "<li class='" + nextPageClass + "' page-data='" + pageCount + "' page-rel='lastpage'>尾页</li>";
        return appendStr;

    }
}