MySQL实现分页

最新推荐文章于 2022-07-22 17:27:34 发布

qq_40302790

最新推荐文章于 2022-07-22 17:27:34 发布

阅读量181

点赞数 2

CC 4.0 BY-SA版权

分类专栏：数据库 Java 前端 Web 文章标签：分页

本文链接：https://blog.csdn.net/qq_40302790/article/details/99638350

Java 同时被 3 个专栏收录

19 篇文章

订阅专栏

Web

14 篇文章

订阅专栏

数据库

9 篇文章

订阅专栏

本文详细介绍了如何使用MySQL的LIMIT语句实现数据分页查询，包括编写Java实体类、分页类、服务类以及Servlet处理，最后展示了如何在JSP页面上实现分页导航和数据展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

效果：
在这里插入图片描述
sql语句：select * from tablename limit m,n （从第m+1条数据开始，显示n条数据）
所以实现分页的sql语句可表示为：select * from tablename limit 每页显示条数*(当前页-1),每页显示的条数
MySQL数据表中的数据（部分）：
在这里插入图片描述
实体类：

public class Person {
	private Integer id;
	private String name;
	private String gender;
	private Integer age;
	private String province;
	//省略其他
}

分页的类：

public class SplitPage<T> {
	private int currentPage = 1;// 当前页
	private int size = 10;// 每页显示多少数据
	private int totalPage;// 总共多少页
	private int dataCount;// 总共多少数据
	private List<T> datas;// 当前页的数据

	public int getCurrentPage() {return currentPage;}
	public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}
	public int getSize() {return size;}
	public void setSize(int size) {this.size = size;}
	public int getTotalPage() {return totalPage;}
	private void setTotalPage(int dataCount) {
		int div = dataCount / this.size;
		this.totalPage = dataCount % this.size == 0 ? div : div + 1;
	}
	public int getDataCount() {return dataCount;}
	public void setDataCount(int dataCount) {
		this.dataCount = dataCount;
		setTotalPage(dataCount);
	}
	public List<T> getDatas() {return datas;}
	public void setDatas(List<T> datas) {this.datas = datas;}
}

PageService.java（Jdbc工具类详见https://blog.csdn.net/qq_40302790/article/details/95972109）

public class PageService {
	
	private static int count; //数据库中总记录数
	private static SplitPage<Person> pages = new SplitPage<>();
	
	static {
		count = JdbcUtil.selectCount("select count(*) from person");
		pages.setDataCount(count);
	}
	
	public SplitPage<Person> getCurrentDatas(int current) {
		String sql = "select * from person limit ?,?";
		pages.setCurrentPage(current);
		int size = pages.getSize();
		List<Person> datas = JdbcUtil.select(sql, Person.class, (current-1)*size,size);
		pages.setDatas(datas);
		return pages;
	}

}

Servlet：

@WebServlet("/page")
public class PageAction extends HttpServlet {

	private final PageService pageService = new PageService();

	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String currentPage = request.getParameter("curr");
		int current = currentPage == null ? 1 : Integer.parseInt(currentPage);
		SplitPage<Person> pages = pageService.getCurrentDatas(current);
		request.setAttribute("pages", pages);
		request.getRequestDispatcher("list.jsp").forward(request, response);
	}

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

}

jsp页面（list.jsp）

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>分页显示数据</title>
<style type="text/css">
	td {text-align: center;}
</style>
</head>

<body>
	<h2 align="center">第${ pages.currentPage}页，共${pages.totalPage}页</h2>
	<hr>
	<table align="center" width="50%" border="1" cellspacing="0">
		<tr>
			<th>序号</th>
			<th>姓名</th>
			<th>性别</th>
			<th>年龄</th>
			<th>籍贯</th>
		</tr>
		<c:forEach var="p" items="${pages.datas}">
			<tr>
				<td>${p.id}</td>
				<td>${p.name}</td>
				<td>${p.gender}</td>
				<td>${p.age}</td>
				<td>${p.province}</td>
			</tr>
		</c:forEach>
	</table>
	<hr>
	<div align="center">
		<font size="5"> <c:if test="${pages.currentPage != 1 }">
				<a href="page">首页</a>&nbsp;&nbsp;&nbsp;&nbsp;
    <a href="page?curr=${pages.currentPage-1 }">上一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
    </c:if> <c:if test="${pages.currentPage != pages.totalPage }">
				<a href="page?curr=${pages.currentPage+1 }">下一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
    <a href="page?curr=${pages.totalPage }">末页</a>&nbsp;&nbsp;&nbsp;&nbsp;
		</c:if>
		</font>
	<form action="page" method="get">
		<font size="5">跳转到第 <select name="curr">
				<c:forEach var="i" begin="1" end="${pages.totalPage}">
					<option value="${i}">${i }</option>
				</c:forEach>
		</select>页&nbsp;&nbsp;<input type="submit" value="确定" /></font>
	</form>
	</div>
</body>
</html>