如何用mvc和mybatis实现分页查询

一:数据准备:

后端需要准备:

数据总量totalCount,装每页数据的集合List,封装所有数据的pageBean

前端需要传入

:当前页currentPage,每页的大小rows

**需要结合前端数据和后端数据计算

:总页码totalPage

pageBean中拥有的必要属性:

(1)数据总量
private Integer totalCount;
(2)当前页
private Integer currentPage;
(3)每页的大小
private Integer pageSize;
(4)总页码
private Integer totalPage;
(5)装每页数据的集合List
private List pageData;

pageBean中拥有的非必要属性*(封装导航条的数据,适合数据量较大的情况,导航条会比较好看)😗

(导航条个数)
private Integer navCount;
(分页从哪里开始查询)
private Integer index;
(导航条开始和结束的 页码)
private Integer startnNav;
private Integer endNav;

pageBean中有些数据需要在bean中初始化好

(index,startnNav,endNav)
查询从哪里开始
this.index = (this.currentPage -1 ) * this.pageSize;
(导航条开始和结束的 页码)
总页数:

两种情况:
1.如果页码总数小于导航条数,那开始是1,结束是页码总数
2.如果页码总数大于导航条数:分成三种情况(当前页是前面1-6页,最后面4页,中间页码)

if(totalCount % pageSize == 0){ // 整除
            totalPage = totalCount / pageSize;
        }else{
            totalPage = totalCount / pageSize + 1;
        }
//当前页是前面6页
if(currentPage <= 6){
 this.startNav=1;
 this.endNav = 10;
 //当前页是最后4页
}else  if(currentPage >= totalPage -4   && currentPage <= totalPage){
this.startNav =totalPage - this.navCount+ 1
 this.endNav = totalPage;  
 //中间的页码
}else{
// 左边有5个导航条按钮
this.startNav = this.currentPage - navCount/2;
 // 右边有4个导航条按钮
 this.endNav = this.currentPage + navCount / 2 -1;

}

二:后端各层需要做

1.controller层

(1)获取currentPage和rows的参数
(2)调用service查询,获取pageBean对象
(3)用request将pageBean对象转发到前端进行数据展示

 private void pageUser(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        //获取参数,当前页和每页的数量
        String rows = request.getParameter("rows");
        String currentPageStr = request.getParameter("currentPage");
        System.out.println(rows);
        System.out.println(currentPageStr);
        if(rows==null || "".equals(rows)){
            rows="1";
        }
        if(currentPageStr==null || "".equals(currentPageStr)){
            currentPageStr="20";
        }
        Integer currentPage = 0;
        try {
            currentPage = Integer.valueOf(currentPageStr);
            if(currentPage<=0){
                currentPage=1;
            }

        }catch (Exception e){
            currentPage = 1;
        }

        Integer pageSize = 0;
        try {
            pageSize = Integer.valueOf(rows);
        }catch (Exception e){
            pageSize = 10;
        }
        //调用services层
      Pagebean<User> pb= userService.findUserByPage( currentPage,pageSize);

      //回写数据
        request.setAttribute("pb",pb);
        //转发
        request.getRequestDispatcher("/page/user/pageusers02.jsp").forward(request,response);


    }

2.service层(主要工作是组装一个pageBean返回给controller层)

(1)创建一个空的pageBean对象
(2)设置pageSize和currentPage属性
(3)设置总记录数,调用mapper获取总记录数
(4)计算每页的开始:Integer start=(当前页-1)*每页的数量
(5)设置List,调用mapper获取数据
(6)计算总页数

   @Override
    public Pagebean<User> findUserByPage(Integer currentpages, Integer rows) {

        //创建空对象
        Pagebean<User> pb=new Pagebean<User>();

        //调用mapper查询总记录数
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       Integer totalCount= mapper.selectAllCount();
       pb.setTotalCount(totalCount);
       //计算start
        Integer start=null;
        try {
             start=(currentpages-1)*rows;
        }catch (Exception e){
            e.printStackTrace();
        }
        pb.setIndex(start);

        //调用mapper的分页查询,封装List
       List<User> list= mapper.selectList(start,rows);
       pb.setList(list);
       //计算总页码
       Integer totalPage=(totalCount%rows==0)? totalCount/rows:(totalCount/rows+1);
        pb.setTotalPage(totalPage);
        //给rows和currentPage赋值
        if(currentpages>=totalPage){
            currentpages=totalPage;
        }
        pb.setCurrentPage(currentpages);
        pb.setRows(rows);
        return pb;
    }

3.mapper层

拥有两个方法:
(1)查询数据库总量的方法
(2)利用分页查询数据

接口中的方法

  //查询总和
    Integer selectAllCount();

    //分页查询
    List<User> selectList(@Param("start") Integer start, @Param("rows") Integer rows);

xml中的sql

<mapper>
<!--    查询总数-->
    <select id="selectAllCount" resultType="Integer">
        select count(1) from t_user
    </select>
<!--    分页查询-->
    <select id="selectList" resultType="User">
        select * from t_user limit #{start},#{rows}
    </select>
</mapper>

三:前端展示

1. 把dataTable的htmlcopy过来

  2. 表格的数据展示

<div class="wrapper wrapper-content animated fadeInRight">
    <div class="row">
        <div class="col-sm-12">
            <div class="ibox float-e-margins">
                <table class="table table-bordered">
                    <tr>
                        <th>编号</th>
                        <th>用户名</th>
                        <th>真实姓名</th>
                        <th>电话</th>
                        <th>邮箱</th>
                        <th>qq</th>
                        <th>注册时间</th>
                        <th>操作</th>
                    </tr>
                    <c:forEach var="u" items="${pb.list}">
                        <tr>
                            <td>${u.id}</td>
                            <td>${u.username}</td>
                            <td>${u.realname}</td>
                            <td>${u.phone}</td>
                            <td>${u.email}</td>
                            <td>${u.qq}</td>
                            <td>${u.regtime}</td>

                            <td>
                                <a href="/pms/user?action=findOne&&id=${u.id}">修改</a>

                                <a href="javascript:void(0)" onclick='delUser(this,${u.id},"${u.username}")'>删除</a>
                            </td>
                        </tr>
                    </c:forEach>
                </table>
            </div>
        </div>
    </div>
</div>

 3. 导航条数据实现

 <div class="row">
                    <div class="col-sm-6">
                        <div class="dataTables_info" id="DataTables_Table_0_info" role="alert" aria-live="polite"
                             aria-relevant="all">显示 ${pb.startNav} 到 ${pb.endNav}项，共 ${pb.totalPage} 项
                        </div>
                    </div>

                    <div class="col-sm-6">
                        <div class="dataTables_paginate paging_simple_numbers" id="DataTables_Table_0_paginate">
                            <ul class="pagination">
                                <c:if test="${pb.currentPage==1}">
                                    <li class="paginate_button previous disabled" aria-controls="DataTables_Table_0"
                                        tabindex="0" id="DataTables_Table_0_previous">
                                        <a href="#">上一页</a>
                                    </li>
                                </c:if>
                                <c:if test="${pb.currentPage!=1}">
                                    <li class="paginate_button previous " aria-controls="DataTables_Table_0"
                                        tabindex="0" id="DataTables_Table_0_previous">
                                        <a href="${pageContext.request.contextPath}/user?action=pageUser&currentPage=${pb.currentPage-1}&rows=20">上一页</a>
                                    </li>
                                </c:if>

                                <c:forEach var="index" begin="${pb.startNav}" end="${pb.endNav}">

                                    <%--                                             高亮 active--%>
                                    <c:if test="${index == pb.currentPage}">
                                        <li class="paginate_button active" aria-controls="DataTables_Table_0"
                                            tabindex="0">
                                            <a href="javascript:void(0)">${index}</a>
                                        </li>
                                    </c:if>

                                    <c:if test="${index != pb.currentPage}">
                                        <li class="paginate_button" aria-controls="DataTables_Table_0"
                                            tabindex="0">
                                            <a href="${pageContext.request.contextPath}/user?action=pageUser&currentPage=${index}&rows=20">${index}</a>
                                        </li>
                                    </c:if>

                                </c:forEach>

                                <c:if test="${pb.currentPage==pb.totalPage}">
                                    <li class="paginate_button next" aria-controls="DataTables_Table_0" tabindex="0"
                                        id="DataTables_Table_0_next"><a
                                            href="#">下一页</a>
                                    </li>
                                </c:if>
                                <c:if test="${pb.currentPage!=pb.totalPage}">
                                    <li class="paginate_button next" aria-controls="DataTables_Table_0" tabindex="0"
                                        id="DataTables_Table_0_next"><a
                                            href="${pageContext.request.contextPath}/user?action=pageUser&currentPage=${pb.currentPage+1}&rows=20">下一页</a>
                                    </li>
                                </c:if>

                            </ul>
                        </div>
                    </div>
                </div>
            </div>

        </div>

效果展示: