目录
一、分页查询的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 ;
(注:SQLServer中的ROW_NUMBER()必须通过OVER()来绑定一列作为行数,并且OVER()中可以直接将这列排序,然后作为一个整体的新表查询指定的条数,优化思想同上。)
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;
}