首先讲一下分页的算法:
定义四个变量
- 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); } }
运行结果: