Java分页的相关知识笔记

最新推荐文章于 2021-08-13 17:11:04 发布

迷茫小猿

最新推荐文章于 2021-08-13 17:11:04 发布

阅读量273

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/fjj2026296/article/details/106064754

版权

Java 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一、分页查询的SQL语句

（一）MySQL的分页：

第几页：currentPage

每页要显示的条数：everyPageNum

比如要查询的表名为：student

start =（currentPage - 1）* everyPageNum ;

SQL语句： SELECT * FROM student LIMIT start，everyPageNum ；

（二）Oracle的分页查询：

第几页：currentPage

每页要显示的条数：everyPageNum

比如要查询的表名为：student

start = (currentPage - 1) * everyPageNum + 1;

end = currentPage * everyPageNum ;

SQL语句(查询第几页(currentPage)的数据并按学号(studyid)排序)：

SELECT * FROM(

SELECT ROWNUM r, t.* FROM

(SELECT s.* FROM student s ORDER BY studyid ASC) t

WHERE ROWNUM <= end

)WHER r >= start ;

(注：这里的SQL语句思想为先按学号将表排序，再查询行号并作为该表的一列，并且查询前end条数据[这里简单优化，只查出前end条可以不用查询出全部数据]，再在新表中查询从start开始到结束的语句。)

（三）SQLServer的分页查询：

1、SQLServer2003

第几页：currentPage

每页要显示的条数：everyPageNum

比如要查询的表名为：student

before = (currentPage - 1) * everyPageNum;

SQL语句(查询第几页(currentPage)的数据并按学号(studyid)排序)：

SELECT TOP everyPageNum * FROM student WHERE ID NOT IN

(SELECT TOP before ID FROM student ORDER BY studyid ASC)

(注：SQLServer中的ROW_NUMBER()必须通过OVER()来绑定一列作为行数，并且OVER()中可以直接将这列排序，然后作为一个整体的新表查询指定的条数，优化思想同上。)

2、SQLServer2005

第几页：currentPage

每页要显示的条数：everyPageNum

比如要查询的表名为：student

start = (currentPage - 1) * everyPageNum + 1;

end = currentPage * everyPageNum ;

SQL语句(查询第几页(currentPage)的数据并按学号(studyid)排序)：

SELECT * FROM(

SELECT ROW_NUMBER() OVER(studyid ORDER BY studyid ASC) AS r, * FROM Student

WHERE r <= end

)WHER r >= start ;

3、SQLServer2012

第几页：currentPage

每页要显示的条数：everyPageNum

比如要查询的表名为：student

start = (currentPage - 1) * everyPageNum + 1;

SQL语句(查询第几页(currentPage)的数据并按学号(studyid)排序)：

SELECT * FROM student ORDER BY studyid

OFFSET start ROWS FETCH NEXT everyPageNum ROWS ONLY;

二、分页的业务实现

Servlet部分代码：

自己的简单分页包装类PageUtil.java

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	IDoctorDao ld = new DoctorDaoImpl();
    	PageUtil pu = new PageUtil();//创建分页业务对象
    	//数据总条数
    	int sumCount = ld.queryCountDoctor();//这里去查找数据总条数
    	//当前页码数
    	String currentPageStr = request.getParameter("currentPage");//从前台获取当前页

    	PageUtil dp = pu.dividePage(currentPageStr, sumCount, everyPageNum);//调用分页业务方法，并返回一个包含分页参数的分页对象
		
    	List<Object> doctorList = ld.queryByPageNum(dp.getStartNum(), everyPageNum);//获取当前页的数据列表		
    	
		request.setAttribute("doctorList", doctorList);
		request.setAttribute("pageNum", dp.getPageNum());
		request.setAttribute("prePage", dp.getPrePage());
		request.setAttribute("nextPage", dp.getNextPage());
		request.setAttribute("everyPageNum", everyPageNum);
		request.setAttribute("currentPage", dp.getCurrentPage());
		
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}

前端JSP部分代码:

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="cn.nenu.entity.*"%>
<%@ page import="java.lang.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>index</title>
</head>
<body>
	<center>
		<div class="tableClass">

			<table class="imagetable">
				<tr>
					<th>序号</th>
					<th>学号</th>
					<th>用户名</th>
					<th>密码</th>
					<th>性别</th>
					<th>操作</th>
				</tr>
				<%
					List<Doctor> li = (List<Doctor>) request.getAttribute("doctorList");
					int pageNum = (Integer) request.getAttribute("pageNum");
					int currentPage = (Integer) request.getAttribute("currentPage");
					int prePage = (Integer) request.getAttribute("prePage");
					int nextPage = (Integer) request.getAttribute("nextPage");
					int everyPageNum = (Integer) request.getAttribute("everyPageNum");
					int startNum = (currentPage - 1) * everyPageNum;
					for (int index = 0; index < li.size(); index++) {
				%>

				<tr>
					<td><%=startNum + index + 1%></td>
					<td><%=li.get(index).getUserID()%></td>
					<td><%=li.get(index).getUsername()%></td>
					<td><%=li.get(index).getPassword()%></td>
					<td>
						<%
							if (li.get(index).getSex() == 1) {
									out.print("女");
								} else {
									out.print("男");
								}
						%>

					</td>
					<td><a class="deleteButton"
						href="deleteController?userID=<%=li.get(index).getUserID()%>">×</a></td>
				</tr>


				<%
					}
				%>
			</table>

		</div>

		<div class="pageClass">
			<input type="hidden" id="currentPage" value="<%=currentPage%>">
			<a href="pageController?currentPage=1">首页</a> <a
				href="pageController?currentPage=<%=prePage%>">上一页</a>

			<%
				if (currentPage < 5) {
					for (int i = 1; i <= 9; i++) {
			%>
			<a id="pageBut<%=i%>" href="pageController?currentPage=<%=i%>"><%=i%></a>
			<%
				}
				} else if (currentPage > pageNum - 4) {
					for (int i = pageNum - 8; i <= pageNum; i++) {
			%>
			<a id="pageBut<%=i%>" href="pageController?currentPage=<%=i%>"><%=i%></a>
			<%
				}
				} else {
					for (int i = currentPage - 4; i <= currentPage + 4; i++) {
			%>
			<a id="pageBut<%=i%>" href="pageController?currentPage=<%=i%>"><%=i%></a>
			<%
				}
				}
			%>
			<a href="pageController?currentPage=<%=nextPage%>">下一页</a> <a
				href="pageController?currentPage=<%=pageNum%>">尾页</a>
		</div>
		<script type="text/javascript">
			var currentPage = document.getElementById("currentPage").value;
			document.getElementById('pageBut' + currentPage).style.backgroundColor = "#02BAFA";
			document.getElementById('pageBut' + currentPage).style.border = "1px solid #02BAFA";
			document.getElementById('pageBut' + currentPage).style.color = "white";
		</script>

	</center>
</body>
</html>

如果想让页码好看一点，可以加入Style，这里附上样式表：

.pageClass {
	margin-top: 10px;
}

.pageClass a {
	color: #02bafa;
	text-decoration: none;
	height: 25px;
	line-height: 25px;
	padding: 6px 11px;
	border: 1px solid lightgray;
	background-color: white;
	font-size: 14px;
	outline: none;
	background-color: white;
}

.pageClass a:hover {
	color: white;
	text-decoration: none;
	height: 25px;
	line-height: 25px;
	padding: 6px 11px;
	background-color: #02bafa;
	border: 1px solid #02bafa;
	font-size: 14px;
	outline: none;
	font-size: 14px;
}

迷茫小猿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java分页的相关知识笔记

一、分页查询的SQL语句（一）MySQL的分页：第几页：currentPage每页要显示的条数：everyPageNum比如要查询的表名为：studentstart =（currentPage - 1）* everyPageNum ;SQL语句： SELECT * FROM student LIMIT start，everyPageNum ；（二）Oracle的分页查询：第几页：currentPage每页要显示的条数：everyPageNum比如要查询的表名为：.
复制链接

扫一扫

专栏目录