分页查询实践
2018-08-28 本文已影响174人
测试老杨
练习使用分页查询
每页记录个数为6，请查询出第2页的数据（第7行至第12行）
SQL SERVER分页查询
思路：
先对记录进行排序，根据起始行号和结束行号筛选出对应的记录
SQL代码如下：
SELECT T.* FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY ID DESC) AS ROWNUM FROM T_EMP
) T
WHERE ROWNUM BETWEEN 7 AND 12;
image.png
ORACLE分页查询
SQL代码如下：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) A
)
WHERE RN BETWEEN 7 AND 12;
image.png
MySQL分页查询
SQL代码如下：
SELECT * FROM PW_WINDID_USER ORDER BY uid DESC LIMIT 6,6;
image.png
员工列表案例实践（SQL Server数据库）
新增Page类
代码如下：
package com.myerp.model;
import java.util.List;
public class MyPage<T> {
private int pageNum; //第几页
private int pageSize; //最多显示多少行
private int totalRecord; //一共有多少条记录
private int totalPage; //一共有多少页
private int startIndex; //从第几个记录开始
private List<T> list; //页面数据（对象集合）
private int start; //开始页的序号
private int end; //结束页的序号
public MyPage(int pageNum,int pageSize,int totalRecord) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.totalRecord = totalRecord;
if(totalRecord%pageSize==0){
this.totalPage = totalRecord/pageSize;
}else{
this.totalPage = totalRecord/pageSize + 1;
}
this.startIndex = (pageNum-1)*pageSize + 1 ;
this.start = 1;
this.end = 5;
if(totalPage <=5){
this.end = this.totalPage;
}else{
this.start = pageNum - 2;
this.end = pageNum + 2;
if(start < 0){
this.start = 1;
this.end = 5;
}
if(end > this.totalPage){
this.end = totalPage;
this.start = end-5;
}
}
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getStartIndex() {
return startIndex;
}
public void setStartIndex(int startIndex) {
this.startIndex = startIndex;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public int getEnd() {
return end;
}
public void setEnd(int end) {
this.end = end;
}
}
对EmployeeDao类进行重构
对查询所有员工的接口（方法名为findAll）进行重构，重构后代码如下：
/**
* 获取员工列表页
* @param pageNum
* @param pageSize
* @return
* @throws Exception
*/
public MyPage<Employee> findAllByPage(int pageNum,int pageSize) throws Exception{
List<Employee> emps =
new ArrayList<Employee>();
MyPage<Employee> page = new MyPage<Employee>(pageNum,pageSize,rows());
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try{
conn = DBUtil.getConnection();
pst = conn.prepareStatement("select t3.* from (select t1.*,t2.dname,t2.city,ROW_NUMBER() over (order by t1.ID desc) as rownum from t_emp t1,t_dept t2 where t1.deptno=t2.id) t3 where t3.rownum between ? and ?");
pst.setInt(1, page.getStartIndex());
pst.setInt(2, page.getStartIndex()+pageSize-1);
rs = pst.executeQuery();
while(rs.next()){
Employee emp = new Employee();
emp.setId(rs.getInt("id"));
emp.setName(rs.getString("name"));
emp.setSalary(rs.getDouble("salary"));
emp.setAge(rs.getInt("age"));
Dept dept = new Dept(rs.getInt("deptNo"),rs.getString("dname"),rs.getString("city"));
emp.setDept(dept);
emps.add(emp);
}
}catch(Exception e){
e.printStackTrace();
throw e;
}finally{
DBUtil.close(conn);
}
page.setList(emps);
return page;
}
新增获取员工总数的接口
代码如下：
/**
* 获取员工数
* @return
* @throws Exception
*/
public int rows() throws Exception{
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try{
conn = DBUtil.getConnection();
pst = conn.prepareStatement("select count(id) head_cnt from t_emp");
rs = pst.executeQuery();
if(rs.next()){
return rs.getInt(1);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close(conn);
}
return 0;
}
修改Servlet类
list.do请求处理的主要代码如下：
int pageNum = 1;
if(request.getParameter("page")!=null){
pageNum = Integer.parseInt(request.getParameter("page"));
}
//创建dao对象
EmployeeDAO dao = new EmployeeDAO();
//
int totalRows = dao.rows();
//
//调用findAll()方法获取数据
MyPage<Employee> page = dao.findAllByPage(pageNum,6);
//绑定数据到request中
request.setAttribute("currentPage",page);
//转发
request.getRequestDispatcher("listEmp.jsp").forward(request,response);
修改listEmp.jsp页面
增加如下HTML代码：
<div style="text-align:right;">
共有${currentPage.totalRecord}个员工，共${currentPage.totalPage}页，当前为第${currentPage.pageNum}页<br/>
<ul class="pagination">
<li>
<a href="list.do?page=${currentPage.pageNum-1}">«</a>
</li>
<c:forEach begin="${currentPage.start}" end="${currentPage.end}" step="1" var="i">
<c:if test="${currentPage.pageNum == i}">
<li><a class="active" href="#">${i}</a></li>
</c:if>
<c:if test="${currentPage.pageNum != i}">
<li><a href="list.do?page=${i}">${i}</a></li>
</c:if>
</c:forEach>
<li><a href="list.do?page=${currentPage.pageNum+1}">»</a></li>
</ul>
</div>
增加如下CSS代码：
<style>
ul.pagination {
display: inline-block;
padding: 0;
margin: 0;
}
ul.pagination li {display: inline;}
ul.pagination li a {
color: black;
float: left;
padding: 8px 16px;
text-decoration: none;
transition: background-color .3s;
border: 1px solid #ddd;
margin: 0 4px;
}
ul.pagination li a.active {
background-color: #4CAF50;
color: white;
border: 1px solid #4CAF50;
}
ul.pagination li a:hover:not(.active) {background-color: #ddd;}
</style>
访问员工列表页面
image.png
