首先讲一下分页的算法:
定义四个变量
- int pageSize 每页显示多少行记录
- int pageNow 当前是在第几页
- int pageCount 一共有多少页
- int rowCount 一共有多少行记录
说明:pageSize是程序员指定的,pageNow是用户选择的,rowCount是从数据库的表中查询得到的
计算pageCount的方法为:
if(rowCount%pageSize == 0){
pageCount = rowCount/pageSize;
}else{
pageCount = rowCount/pageSize + 1;
}
而在数据库中分页查询的方法为:
--分页查询
--查询1-3行数据
select top 3 * from emp order by sal desc;
--查询4-6行数据
select top 3 *
from emp
where empno not in (select top 3 empno from emp order by sal desc)
order by sal desc;
所以最终在Servlet中分页查询的方法为:
select top pageSize *
from emp
where empno not in (select top pageSize*(pageNow-1) empno from emp order by sal desc)
order by sal desc;
最后通过一个例子来说明Servlet的分页方法,在用户管理系统中添加对用户信息分页的功能:
/**
* @(#)HelloGen.java
*
*欢迎界面
* @author
* @version 1.00 2017/2/23
*/
package com.chongqing;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
public class wel extends HttpServlet {
//处理get请求
public void doGet(HttpServletRequest req,HttpServletResponse res){
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
//首先判断Session中是否有登录时添加的属性
HttpSession mySession = req.getSession(true);
String val = (String)mySession.getAttribute("usname");
try {
if(null == val)
{
//返回登录界面
res.sendRedirect("login?info=error");
return;
}
//中文乱码
res.setContentType("text/html;charset=gbk");
PrintWriter pw = res.getWriter();
//把所有内容放在中间
pw.println("<body><center>");
pw.println("Welcom!");
//分页的功能
int pageSize = 3; //每页显示3条记录
int pageNow = 1; //首先显示第一页的内容
int pageCount = 0; //一共有多少页(通过计算获得)
int rowCount = 0; //一共有多少行记录(通过查表获得)
//动态的接收pageNow
String sPageNow = req.getParameter("pageNow");
if(null != sPageNow){
pageNow = Integer.parseInt(sPageNow);
}
//连接数据库
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
ct = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=TestServlet","sa","293313");
ps = ct.prepareStatement("select count(*) from users");
rs = ps.executeQuery();
if(rs.next()){
rowCount = rs.getInt(1);//获得rowCount的值
}
//计算pageCount的值
if(rowCount%pageSize == 0){
pageCount = rowCount/pageSize;
}else{
pageCount = rowCount/pageSize + 1;
}
ps = ct.prepareStatement("select top "+pageSize+" * from users where userID not in (select top "+pageSize*(pageNow-1)+" userID from users)");
//ps.setInt(1,pageSize); //不能采用对问号赋值的方式
//ps.setInt(2,pageSize*(pageNow-1));
rs = ps.executeQuery();
//用表格显示用户的信息
pw.println("<table border = 1>");
pw.println("<tr><td>userID</td><td>username</td><td>passwd</td><td>email</td><td>grade</td></tr>");
while(rs.next()){
pw.println("<tr>");
pw.println("<td>"+rs.getInt(1)+"</td>");
pw.println("<td>"+rs.getString(2)+"</td>");
pw.println("<td>"+rs.getString(3)+"</td>");
pw.println("<td>"+rs.getString(4)+"</td>");
pw.println("<td>"+rs.getInt(5)+"</td>");
pw.println("</tr>");
}
pw.println("</table>");
//显示超链接
if(1 != pageNow){
pw.println("<a href=wel?pageNow="+1+">首页</a>");
}
for(int i=1; i<=pageCount; ++i ){
pw.println("<a href=wel?pageNow="+i+">"+i+"</a>");
}
if(pageCount != pageNow){
pw.println("<a href=wel?pageNow="+pageCount+">尾页</a>");
}
pw.println("</body></center>");
}
catch (Exception ex) {
ex.printStackTrace();
}finally{
//关闭资源!!!
//关闭顺序是,谁后创建谁先关闭
try {
if(null != rs){
rs.close();
}
if(null != ps){
ps.close();
}
if(null != ct){
ct.close();
}
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
}
public void doPost(HttpServletRequest req,HttpServletResponse res){
this.doGet(req,res);
}
}
运行结果: