第六章 分页

分页分为前端分页和后端分页,
前端分页是将所有的记录取出来,将集合存放到Session中,然后根据起始条件来取部分集合的内容,从而达到分页的一个效果.这种分页对服务器端负担较大,想想如果很多个用户都打个结果集页面,那么服务器维护Session会消耗大量内存,已经不推荐使用,

后端分页是通过SQL语句在数据中取出部分记录,直接在数据库中就分好,所以这种分页是现在比较常见的一种,效率较高,对服务器负担较小.

示例:
SQL脚本
CREATE TABLE studentInfo(
ID INT IDENTITY,
SNAME NVARCHAR(10),
AGE INT,
ADDRESS NVARCHAR(100),
PRIMARY KEY(ID)
)
insert into studentInfo values('张三1',22,'武汉');
insert into studentInfo values('张三2',22,'武汉');
insert into studentInfo values('张三3',22,'武汉');
insert into studentInfo values('张三4',22,'武汉');
insert into studentInfo values('张三5',22,'武汉');
insert into studentInfo values('张三6',22,'武汉');
insert into studentInfo values('张三7',22,'武汉');
insert into studentInfo values('张三8',22,'武汉');
insert into studentInfo values('张三9',22,'武汉');
insert into studentInfo values('张三10',22,'武汉');

insert into studentInfo values('张三11',22,'武汉');
insert into studentInfo values('张三12',22,'武汉');
insert into studentInfo values('张三13',22,'武汉');
insert into studentInfo values('张三14',22,'武汉');
insert into studentInfo values('张三15',22,'武汉');
insert into studentInfo values('张三16',22,'武汉');
insert into studentInfo values('张三17',22,'武汉');
insert into studentInfo values('张三18',22,'武汉');
insert into studentInfo values('张三19',22,'武汉');
insert into studentInfo values('张三20',22,'武汉');

insert into studentInfo values('张三21',22,'武汉');
insert into studentInfo values('张三22',22,'武汉');
insert into studentInfo values('张三23',22,'武汉');
insert into studentInfo values('张三24',22,'武汉');
insert into studentInfo values('张三25',22,'武汉');
insert into studentInfo values('张三26',22,'武汉');
insert into studentInfo values('张三27',22,'武汉');
insert into studentInfo values('张三28',22,'武汉');
insert into studentInfo values('张三29',22,'武汉');
insert into studentInfo values('张三30',22,'武汉');

insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');
insert into studentInfo values('李四',22,'荆州');

insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');
insert into studentInfo values('李四',22,'咸宁');

insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('李四',22,'潜江');
insert into studentInfo values('王五',22,'襄樊');
insert into studentInfo values('王五',22,'襄樊');
insert into studentInfo values('王五',22,'襄樊');
insert into studentInfo values('王五',22,'襄樊');
分页的存储过程
alter PROCEDURE findStudentInfo
@pageSize int,@currentPageNo int,@fieldName nvarchar(20), @fieldvalue nvarchar(20)
AS
begin
declare @strSQL nvarchar(1000)
if (@fieldName is null) Or (len(@fieldName)=0)
begin
set @strSQL='SELECT TOP '+CONVERT(nvarchar(20),@pageSize)+' * FROM studentInfo WHERE ID NOT IN (SELECT TOP '+CONVERT(nvarchar(20), @pageSize*(@currentPageNo-1))+' ID FROM studentInfo)'
end
else
begin
set @strSQL='SELECT TOP '+CONVERT(nvarchar(20), @pageSize)+' * FROM studentInfo WHERE ID NOT IN (SELECT TOP '+CONVERT(nvarchar(20), @pageSize*(@currentPageNo-1))+' ID FROM studentInfo where '+CONVERT(nvarchar(20),@fieldName)+' like ''%'+CONVERT(nvarchar(20),@fieldvalue)+'%'') and '+CONVERT(nvarchar(20),@fieldName)+' like ''%'+CONVERT(nvarchar(20),@fieldvalue)+'%'''
end
exec (@strSQL)
end
取记录总数的存储过程
create PROCEDURE GetStudentCount
@fieldName nvarchar(20), @fieldvalue nvarchar(20)
AS
begin
declare @strSQL nvarchar(1000)
if (@fieldName is null) Or (len(@fieldName)=0)
begin
set @strSQL='select count(*) from studentInfo'
end
else
begin
set @strSQL='select count(*) from studentInfo where '+convert(nvarchar(20),@fieldName)+' like ''%'+@fieldvalue+'%'''
end
exec (@strSQL)
end

Java部分
DbManager
public class DbManager {
public static Connection getConnection() {
Connection conn = null;
try {
Context context = new InitialContext();// 获取容器的上下文，获取容器对象
// 相当于在容器的注册表中查找“jdbc/myinfo”这个数据源对象
DataSource ds = (DataSource) context
.lookup("java:comp/env/jdbc/test");
conn = ds.getConnection();// 从数据源中获取一个连接对象
} catch (Exception e) {
e.printStackTrace();
}
return conn;// 返回连接对象
}

public static void closeConnection(Connection conn, Statement st,
ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
StudentInfo
public class StudentInfo {
private int id;
private String sname;
private int age;
private String address;

public StudentInfo() {
super();
}

public StudentInfo(int id, String sname, int age, String address) {
super();
this.id = id;
this.sname = sname;
this.age = age;
this.address = address;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getSname() {
return sname;
}

public void setSname(String sname) {
this.sname = sname;
}

public int getAge() {
return age;
}

public void setAge(int age) {
this.age = age;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}
}
Dao
public class StudentInfoDao {
/**
* 分页查找学生,如果没有字段,后两参数可传null或""
* @param pageSize:每页大小
* @param currentPageNo:当前页
* @param fieldName:字段名
* @param fieldvalue:字段值
* @return
*/
public List<StudentInfo> findStudentInfo(int pageSize, int currentPageNo,
String fieldName, String fieldvalue) {
List<StudentInfo> list = new ArrayList<StudentInfo>();
Connection conn = DbManager.getConnection();
CallableStatement proc = null;
ResultSet rs = null;
try {
proc = conn.prepareCall("{call findStudentInfo(?,?,?,?)}");
proc.setInt(1, pageSize);
proc.setInt(2, currentPageNo);
proc.setString(3, fieldName);
proc.setString(4, fieldvalue);
rs = proc.executeQuery();
while (rs.next()) {
list.add(new StudentInfo(rs.getInt(1), rs.getString(2), rs
.getInt(3), rs.getString(4)));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbManager.closeConnection(conn, proc, rs);
}
return list;
}

/**
* 取学生记录数,如果没有字段,参数可传null或""
* @param fieldName:字段名
* @param fieldvalue:字段值
* @return
*/
public int GetStudentCount(String fieldName, String fieldvalue) {
int count = 0;
Connection conn = DbManager.getConnection();
CallableStatement proc = null;
ResultSet rs = null;
try {
proc = conn.prepareCall("{call GetStudentCount(?,?)}");
proc.setString(1, fieldName);
proc.setString(2, fieldvalue);
rs = proc.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbManager.closeConnection(conn, proc, rs);
}
return count;
}
}
service
public interface StudentInfoService {
public List<StudentInfo> findStudentInfo(int pageSize, int currentPageNo,
String fieldName, String fieldvalue);

public int GetStudentCount(String fieldName, String fieldvalue);
}
serviceImpl
public class StudentInfoServiceImpl implements StudentInfoService {
private StudentInfoDao studentInfoDao;
public StudentInfoServiceImpl() {
studentInfoDao = new StudentInfoDao();
}

public int GetStudentCount(String fieldName, String fieldvalue) {
return studentInfoDao.GetStudentCount(fieldName, fieldvalue);
}

public List<StudentInfo> findStudentInfo(int pageSize, int currentPageNo,
String fieldName, String fieldvalue) {
return studentInfoDao.findStudentInfo(pageSize, currentPageNo, fieldName, fieldvalue);
}
}
Servlet
public class StudentInfoServlet extends HttpServlet {
private StudentInfoService studentInfoService;
private int pageSize; // 每页大小

@Override
public void init() throws ServletException {
super.init();
studentInfoService = new StudentInfoServiceImpl();
pageSize = 5; // 每页大小,固定值
}

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

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
int currentPageNo = 0; // 当前页
String fieldName = "";// 字段名
String fieldValue = "";// 字段值
int totalCount = 0;// 总记录数
int totalPageCount = 0;// 总页数
List<StudentInfo> list = new ArrayList<StudentInfo>();

try {
currentPageNo = Integer.parseInt(request
.getParameter("currentPageNo"));
} catch (NumberFormatException e) {
currentPageNo = 1;
}
fieldName = request.getParameter("fieldName");
fieldValue = request.getParameter("fieldValue");

// 取总记录数
totalCount = studentInfoService.GetStudentCount(fieldName, fieldValue);
if (totalCount % pageSize == 0) {
totalPageCount = totalCount / pageSize;
} else {
totalPageCount = totalCount / pageSize + 1;
}
list = studentInfoService.findStudentInfo(pageSize,currentPageNo, fieldName, fieldValue);

request.setAttribute("list", list);
request.setAttribute("currentPageNo", currentPageNo);
request.setAttribute("totalCount", totalCount);
request.setAttribute("totalPageCount", totalPageCount);
request.setAttribute("pageSize", pageSize);
request.setAttribute("fieldName", fieldName);
request.setAttribute("fieldValue", fieldValue);
request.getRequestDispatcher("studentInfo_list.jsp").forward(request, response);
}
}
studentInfo_list.jsp
<%@page contentType="text/html; charset=utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<c:set var="basePath" value="${pageContext.request.contextPath}" />
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>分页示例</title>
<script type="text/javascript">
function navigation(navigateValue){
//进行当前页的一个控制
var currentPageNoObj = document.getElementById("currentPageNo");
var totalPageCount = document.getElementById("totalPageCount").value;
var val = Number(currentPageNoObj.value);
//alert(currentPageNoObj.value);
switch(navigateValue){
case "first":
currentPageNoObj.value = 1;
break;
case "previous":
if(currentPageNoObj.value != 1){
currentPageNoObj.value = val - 1;
}
break;
case "next":
if(currentPageNoObj.value < totalPageCount){
currentPageNoObj.value = val + 1;
}
break;
case "last":
currentPageNoObj.value = totalPageCount;
break;
}
document.getElementById("navigationForm").submit();
}
</script>
</head>
<body>
<form method="post" action="/ajaxdemo/StudentInfoServlet">
查询字段:
<select id="fieldName" name="fieldName">
<option value="" selected="selected">
请选择
</option>
<option value="sname">
姓名
</option>
<option value="address">
地址
</option>
</select>
查询值:
<input type="text" id="fieldValue" name="fieldValue" />
<input type="submit" value="查询" />
</form>
<table border="1" width="400px">
<caption>
学生信息列表
</caption>
<tr>
<th>
ID
</th>
<th>
姓名
</th>
<th>
年龄
</th>
<th>
地址
</th>
</tr>
<c:forEach var="studentInfo" items="${list}">
<tr>
<td>
${studentInfo.id}
</td>
<td>
${studentInfo.sname}
</td>
<td>
${studentInfo.age}
</td>
<td>
${studentInfo.address}
</td>
</tr>
</c:forEach>
</table>
<form method="post" id="navigationForm"
action="/ajaxdemo/StudentInfoServlet">
<input type="hidden" name="currentPageNo" id="currentPageNo"
value="${currentPageNo}" />
<input type="hidden" name="totalPageCount" id="totalPageCount"
value="${totalPageCount}" />
<input type="hidden" name="fieldName" value="${fieldName}" />
<input type="hidden" name="fieldValue" value="${fieldValue}"/>
共${totalCount}条记录,每页${pageSize}条,当前${currentPageNo}/${totalPageCount}页
<a href="#" onclick="navigation('first')">首页</a>  
<a href="#" onclick="navigation('previous')">上一页</a>  
<a href="#" onclick="navigation('next')">下一页</a>  
<a href="#" onclick="navigation('last')">未尾</a>
</form>
</body>
</html>