基于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>