基于JSP的mysql分页显示

基于JSP的mysql分页显示

1.开发环境

本案例的开发环境为：mysql + idea + jdk1.8 + Tomcat9

2.目录结构

3.数据库的表设计

4.代码

4.1 pageBean

package JavaBean;

import java.util.List;

public class pageBean {

    private int pageNumber;
    private int pageSize;
    private int totalCount;
    private int totalPage;

    private List<Object> list;

    public void SetPageSizeAndTotalCount(int pageSize,int totalCount)
    {
        this.pageSize=pageSize;
        this.totalCount=totalCount;
        totalPage= (int)Math.ceil((double)totalCount/pageSize);
    }

    public int getPageNumber() {
        return pageNumber;
    }

    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<Object> getList() {
        return list;
    }

    public void setList(List<Object> list) {
        this.list = list;
    }
}

4.2 userDao

public class userDao {
    //所有用户数量
    public int selectUserCount() throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDs());
        String sql = "select count(*) from user";
        return r.query(sql, new ScalarHandler<Long>()).intValue();
    }
    //分页显示用户信息
    public List selectUserList(int pageNo, int pageSize) throws SQLException {
        QueryRunner r = new QueryRunner(DBUtil.getDs());
        String sql = "select * from user limit ?,?";
        return r.query(sql, new BeanListHandler<UserBean>(UserBean.class), (pageNo-1)*pageSize,pageSize );
    }
}

4.3 UserService

public class UserService {
    private static userDao userDao = new userDao();

    public static pageBean getUserPage(int pageNumber) {
        pageBean p = new pageBean();
        p.setPageNumber(pageNumber);
        int pageSize = 3;
        int totalCount = 0;
        try {
            totalCount = userDao.selectUserCount();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        p.SetPageSizeAndTotalCount(pageSize, totalCount);
        List list=null;
        try {
            list = userDao.selectUserList( pageNumber, pageSize);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        p.setList(list);
        return p;
    }
}

4.4 UserListServlet

@WebServlet(name = "UserListServlet",urlPatterns = "/UserListServlet")
public class UserListServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        int pageNumber = 1;
        if(request.getParameter("pageNumber") != null) {
            try {
                pageNumber=Integer.parseInt(request.getParameter("pageNumber") ) ;
            }
            catch (Exception e)
            {
            }
        }
        if(pageNumber<=0)
            pageNumber=1;
        pageBean p = UserService.getUserPage(pageNumber);
        if(p.getTotalPage()==0)
        {
            p.setTotalPage(1);
            p.setPageNumber(1);
        }
        else {
            if(pageNumber>=p.getTotalPage()+1)
            {
                p = UserService.getUserPage(pageNumber);
            }
        }
        request.setAttribute("p", p);
        request.getRequestDispatcher("/userList.jsp").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request,response);
    }
}

4.4 page.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<div style='text-align:center;'>
    <a class='btn btn-info'   <c:if test="${p.pageNumber==1 }">disabled</c:if>  <c:if test="${p.pageNumber!=1 }">href="${param.url }?pageNumber=1${param.param }"</c:if>>首页</a>
    <a class='btn btn-info' <c:if test="${p.pageNumber==1 }">disabled</c:if> <c:if test="${p.pageNumber!=1 }">href="${pageContext.request.contextPath }${param.url }?pageNumber=${p.pageNumber-1}${param.param }"</c:if>>上一页</a>
    <h3 style='display:inline;'>[${p.pageNumber }/${p.totalPage }]</h3>
    <h3 style='display:inline;'>[${p.totalCount }]</h3>
    <a class='btn btn-info' <c:if test="${p.totalPage==0 || p.pageNumber==p.totalPage }">disabled</c:if> <c:if test="${p.pageNumber!=p.totalPage }">href="${param.url }?pageNumber=${p.pageNumber+1}${param.param }"</c:if>>下一页</a>
    <a class='btn btn-info' <c:if test="${p.totalPage==0 || p.pageNumber==p.totalPage }">disabled</c:if> <c:if test="${p.pageNumber!=p.totalPage }">href="${param.url }?pageNumber=${p.totalPage}${param.param }"</c:if>>尾页</a>
    <input type='text' class='form-control' style='display:inline;width:60px;' value=''/><a class='btn btn-info' href='javascript:void(0);' onclick='location.href="${param.url }?pageNumber="+(this.previousSibling.value)+"${param.param }"'>GO</a>
</div>

4.5 userList.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
    <style type="text/css">
        tr{
            text-align: center;
        }
        td,th{
            width: 300px;
        }
    </style>
</head>
<body>
    <div align="center">
    <table>
    <tr>
        <th>用户名</th>
        <th>密码</th>
        <th>邮箱</th>
        <th>操作</th>
    </tr>
    <c:forEach items="${p.list}" var="u">
        <tr>
            <td><p>${u.name }</p></td>
            <td><p>${u.password }</p></td>
            <td><p>${u.email }</p></td>
            <td>
                <a>重置密码</a>
                <a>修改</a>
                <a>删除</a>
            </td>
        </tr>
    </c:forEach>
    </table>
    </div>
    <div style="margin-top: 100px;">
        <jsp:include page="/page.jsp">
            <jsp:param value="/UserListServlet" name="url"/>
        </jsp:include>
    </div>
</body>
</html>

5.运行效果