在系统中如何实现分页显示。
参考解答
分页分为前台和后台，后台关心的是分页的SQL如何编写，前台关心的是数据如何分页显示。
分页的目的是减少每次查询的数据量，在数据量大时减轻数据库访问压力。
分页的类型可以分为逻辑分页和物理分页，使用sql语句查询部分数据称为物理分页（推荐），将数据全部查询出来用java代码进行分页称为逻辑分页（不推荐）。
1. 后台分页
1) sql实现
Oralce（假设号参数为page，每页记录数参数为size）
select a.* from (
select b.*, rownum rn from ( select * from 表 order by ... ) b
where rownum <= ${page*size}
) a
where rn > ${(page-1)*size}
查询第一页可以简化为：
select * from (
( select * from 表 order by ... )
) where rownum <= ${page*size}
MySQL（假设号参数为page，每页记录数参数为size）
select * from 表 order by ... limit ${(page-1)*size}, ${size}
查询第一页可以简化为：
select * from 表 order by ... limit ${size}
2) po实现
package po;
public class Emp {
private int empno;
private String ename;
private double sal;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
}
3) dao实现
package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import po.Emp;
public class EmpDao {
static{
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public int count() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
ps = conn.prepareStatement("select count(*) from emp");
rs = ps.executeQuery();
rs.next();
int count = rs.getInt(1);
return count;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
public List<Emp> find(int page, int rows) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");
ps = conn.prepareStatement("select a.* from ( select b.*, rownum rn from (select * from emp order by sal desc) b where rownum <= ? ) a where rn > ?");
ps.setInt(1, page * rows);
ps.setInt(2, (page - 1) * rows);
rs = ps.executeQuery();
List<Emp> list = new ArrayList<>(rows);
while (rs.next()) {
Emp e = new Emp();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setSal(rs.getDouble("sal"));
list.add(e);
}
return list;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
}
}
}
}
4) 分页栏抽象：
考虑了最大页数，例如最大页数控制在10，[]内为当前页：
[1] 2 3 4 5 6 7 8 9 10
1 [2] 3 4 5 6 7 8 9 10
1 2 [3] 4 5 6 7 8 9 10
1 2 3 [4] 5 6 7 8 9 10
1 2 3 4 [5] 6 7 8 9 10
1 2 3 4 5 [6] 7 8 9 10
2 3 4 5 6 [7] 8 9 10 11
3 4 5 6 7 [8] 9 10 11 12
4 5 6 7 8 [9] 10 11 12 13
package vo;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;
public class Pagebar<T> implements Iterable<T> {
@Override
public String toString() {
return "Pagebar [list=" + list + ", count=" + count + ", page=" + page + ", rows=" + rows + ", total=" + total
+ ", pageMax=" + pageMax + ", pageLeftMax=" + pageLeftMax + ", begin=" + begin + ", end=" + end + "]";
}
private List<T> list;
private int count;
private int page;
private int rows;
private int total;
private int pageMax;
private int pageLeftMax;
private int begin;
private int end;
@Override
public Iterator<T> iterator() {
return list.iterator();
}
public List<T> getList() {
return list;
}
public int getCount() {
return count;
}
public int getPage() {
return page;
}
public int getRows() {
return rows;
}
public int getTotal() {
return total;
}
public int getPageMax() {
return pageMax;
}
public int getPageLeftMax() {
return pageLeftMax;
}
public int getBegin() {
return begin;
}
public int getEnd() {
return end;
}
private Pagebar(List<T> list, int count, int page, int rows, int total, int pageMax, int pageLeftMax, int begin,
int end) {
super();
this.list = list;
this.count = count;
this.page = page;
this.rows = rows;
this.total = total;
this.pageMax = pageMax;
this.pageLeftMax = pageLeftMax;
this.begin = begin;
this.end = end;
}
public static class PagebarBuilder<T> {
private List<T> list; // 当前页记录
private Integer count; // 总记录数
private int page = 1; // 页号
private int rows = 10; // 每页行数
private int total; // 总页数
private int pageMax = 10; // 最多显示页数
private int pageLeftMax = 5; // 当前页左侧最多显示页数
private int begin; // 起始页号
private int end; // 结束页号
public PagebarBuilder<T> setPage(int page) {
this.page = page;
return this;
}
public PagebarBuilder<T> setRows(int rows) {
this.rows = rows;
return this;
}
public PagebarBuilder<T> setPageMax(int pageMax) {
this.pageMax = pageMax;
return this;
}
public PagebarBuilder<T> setPageLeftMax(int pageLeftMax) {
this.pageLeftMax = pageLeftMax;
return this;
}
public PagebarBuilder<T> setListAndCount(List<T> list, Integer count) {
this.list = list;
this.count = count;
return this;
}
protected void calculateBeginEnd() {
int right = pageMax - pageLeftMax - 1;
if (total <= pageMax) {
begin = 1;
end = total;
} else {
if (page <= pageLeftMax + 1) {
begin = 1;
end = pageMax;
} else {
if (total - page <= right) {
begin = total - (pageMax - 1);
end = total;
} else {
begin = page - pageLeftMax;
end = page + right;
}
}
}
}
private void calculateTotal() {
if (count % rows == 0) {
total = count / rows;
} else {
total = count / rows + 1;
}
}
public Pagebar<T> build() {
Objects.requireNonNull(list, "list can't be null");
Objects.requireNonNull(count, "count can't be null");
calculateTotal();
calculateBeginEnd();
Pagebar<T> pagebar = new Pagebar<>(list, count, page, rows, total, pageMax, pageLeftMax, begin, end);
return pagebar;
}
}
public static void main(String[] args) {
List<Integer> list = Arrays.asList(1, 2);
Integer count = 100;
for (int i = 1; i < 10; i++) {
Pagebar<Integer> pb = new PagebarBuilder<Integer>().setPage(i).setRows(2).setListAndCount(list, count).build();
for (int p = pb.getBegin(); p <= pb.getEnd(); p++) {
if (p == i) {
System.out.print("[" + p + "]\t");
} else {
System.out.print(" " + p + " \t");
}
}
System.out.println();
}
}
}
2. 前台显示：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>分页示例</title>
<style type="text/css">
#empTBL{border-collapse: collapse;width:50%;margin:0 auto;}
#empTBL tr{border: 1px black solid; }
#empTBL td{padding: 15px;}
#pagebar {margin:0 auto;width:50%;text-align: center;}
#pagebar a {margin:0 8px;}
#pagebar span{margin:0 8px; color: blue;}
</style>
</head>
<body>
<table id="empTBL">
<tr>
<td>编号</td><td>姓名</td><td>工资</td>
</tr>
<c:forEach items="${pagebar.list}" var="emp">
<tr>
<td>${emp.empno}</td><td>${emp.ename}</td><td>${emp.sal}</td>
</tr>
</c:forEach>
</table>
<div id="pagebar">
<c:forEach begin="${pagebar.begin}" end="${pagebar.end}" var="n">
<c:if test="${n==pagebar.page}">
<span>[第${n}页]</span>
</c:if>
<c:if test="${n!=pagebar.page}">
<a href="list?page=${n}">第${n}页</a>
</c:if>
</c:forEach>
</div>
</body>
</html>
效果：
