Java基础94 分页查询（以MySQL数据库为例，Servlet技术）
1、概述
分页查询，也可叫做分批查询，基于数据库的分页语句(不同数据库是不同的)。
本文使用的事MySql数据库。
假设：每页显示10条数据.
Select * from contact limit M,N;
M：开始记录的索引。第一条数据的索引为0 （页数）
N：一次查询几条记录（每页显示的数据的条数）
则：
第一页：select * from contact limit 0,10;
第二页：select * from contact limit 10,10
............
第n页：select * from contact limit(M-1)*N,N;
在MySQL数据库中的分页查询操作：https://www.cnblogs.com/dshore123/p/10544241.html
2、实例演示
db.properties 配置文件
1 url=jdbc:mysql://localhost:3306/school 2 user=root 3 password=123456 4 driverClass=com.mysql.jdbc.Driver
JdbcUtil.java 封装文件（连接数据库）
1 package com.shore.util; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.sql.Connection; 8 import java.sql.DriverManager; 9 import java.sql.ResultSet; 10 import java.sql.SQLException; 11 import java.sql.Statement; 12 import java.util.Properties; 13 14 public class JdbcUtil { 15 //连接数据库的URL 16 private static String url=null; 17 private static String user=null;//用户名 18 private static String password=null;//密码 19 private static String driverClass=null; 20 //静态代码块中(只加载一次) 21 static{ 22 try { 23 //读取db.properties 24 Properties props=new Properties(); 25 InputStream in=JdbcUtil.class.getResourceAsStream("/db.properties"); 26 //加载文件 27 props.load(in); 28 url=props.getProperty("url"); 29 user=props.getProperty("user"); 30 password=props.getProperty("password"); 31 driverClass=props.getProperty("driverClass"); 32 //注册驱动 33 Class.forName(driverClass); 34 } catch (FileNotFoundException e) { 35 e.printStackTrace(); 36 } catch (IOException e) { 37 e.printStackTrace(); 38 } catch (ClassNotFoundException e) { 39 e.printStackTrace(); 40 System.out.println("注册驱动失败"); 41 } 42 } 43 /* 44 * 获取连接 45 * */ 46 public static Connection getConnection(){ 47 try { 48 Connection conn=DriverManager.getConnection(url, user, password); 49 return conn; 50 } catch (SQLException e) { 51 e.printStackTrace(); 52 throw new RuntimeException(); 53 } 54 } 55 /* 56 * 释放资源 57 * */ 58 public static void close(Connection conn,Statement stmt,ResultSet rs){ 59 try { 60 if(stmt!=null) stmt.close(); 61 if(conn!=null) conn.close(); 62 if(rs!=null) rs.close(); 63 } catch (SQLException e) { 64 e.printStackTrace(); 65 } 66 } 67 }
Page 实体类
1 package com.shore.entity; 2 3 import java.util.List; 4 5 //封装与分页有关的所有信息 6 public class Page { 7 private List records;//要显示的分页记录 8 private int currentPageNum;//当前页码；可由用户指定（用于输入页码，点击跳转到指定页）* 9 private int pageSize = 10;//每页显示的记录条数（这里是没页显示10条数据） * 10 private int totalPageNum;//总页数* 11 private int prePageNum;//上一页的页码* 12 private int nextPageNum;//下一页的页码* 13 14 private int startIndex;//数据库每页开始记录的索引（比如第2页是从11开始，第三页从21开始...）* 15 private int totalRecords;//总记录的条数* 16 //扩展的 17 private int startPage;//开始页码 18 private int endPage;//结束页码 19 20 private String url;//查询分页的请求servlet的地址 21 22 //currentPageNum：用户要看的页码 23 //totalRecords:总记录条数 24 public Page(int currentPageNum,int totalRecords){ 25 this.currentPageNum = currentPageNum; 26 this.totalRecords = totalRecords; 27 //计算总页数 28 totalPageNum = totalRecords%pageSize==0?totalRecords/pageSize:(totalRecords/pageSize+1); 29 //计算每页开始的索引 30 startIndex = (currentPageNum-1)*pageSize; 31 //计算开始和结束页码:9个页码 32 if(totalPageNum > 9){ 33 //超过9页 34 startPage = currentPageNum - 4; 35 endPage = currentPageNum + 4; 36 if(startPage < 1){ 37 startPage = 1; 38 endPage = 9; 39 } 40 if(endPage>totalPageNum){ 41 endPage = totalPageNum; 42 startPage = endPage - 8; 43 } 44 }else{ 45 //没有9页 46 startPage = 1; 47 endPage = totalPageNum; 48 } 49 } 50 public List getRecords() { 51 return records; 52 } 53 public void setRecords(List records) { 54 this.records = records; 55 } 56 public int getCurrentPageNum() { 57 return currentPageNum; 58 } 59 public void setCurrentPageNum(int currentPageNum) { 60 this.currentPageNum = currentPageNum; 61 } 62 public int getPageSize() { 63 return pageSize; 64 } 65 public void setPageSize(int pageSize) { 66 this.pageSize = pageSize; 67 } 68 public int getTotalPageNum() { 69 return totalPageNum; 70 } 71 public void setTotalPageNum(int totalPageNum) { 72 this.totalPageNum = totalPageNum; 73 } 74 //不能无限上一页（假如当前页是第1页，那么“上一页”这个按钮变为灰色，再点击，则 无反应） 75 public int getPrePageNum() { 76 prePageNum = currentPageNum-1; 77 if(prePageNum < 1){ 78 prePageNum = 1; 79 } 80 return prePageNum; 81 } 82 public void setPrePageNum(int prePageNum) { 83 this.prePageNum = prePageNum; 84 } 85 //不能无限下一页（假如当前页是最后一页，那么“下一页”这个按钮变为灰色，再点击，则 无反应） 86 public int getNextPageNum() { 87 nextPageNum = currentPageNum + 1; 88 if(nextPageNum > totalPageNum){ 89 if(nextPageNum > totalPageNum){ 90 nextPageNum = totalPageNum; 91 } 92 return nextPageNum; 93 } 94 public void setNextPageNum(int nextPageNum) { 95 this.nextPageNum = nextPageNum; 96 } 97 public int getStartIndex() { 98 return startIndex; 99 } 100 public void setStartIndex(int startIndex) { 101 this.startIndex = startIndex; 102 } 103 public int getTotalRecords() { 104 return totalRecords; 105 } 106 public void setTotalRecords(int totalRecords) { 107 this.totalRecords = totalRecords; 108 } 109 public int getStartPage() { 110 return startPage; 111 } 112 public void setStartPage(int startPage) { 113 this.startPage = startPage; 114 } 115 public int getEndPage() { 116 return endPage; 117 } 118 public void setEndPage(int endPage) { 119 this.endPage = endPage; 120 } 121 public String getUrl() { 122 return url; 123 } 124 public void setUrl(String url) { 125 this.url = url; 126 } 127 }
ContactDAOMySqlImpl 实现类
1 package com.shore.dao.impl; 2 3 import java.sql.Connection; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.util.ArrayList; 7 import java.util.List; 8 9 import com.shore.dao.ContactDAO; 10 import com.shore.entity.Contact; 11 import com.shore.util.JdbcUtil; 12 13 public class ContactDAOMySqlImpl implements ContactDAO{ 14 15 //总记录数 16 public int getTotalRecordsNum() { 17 Connection conn = null; 18 PreparedStatement stmt = null; 19 ResultSet rs = null; 20 try{ 21 //获取数据库的连接 22 conn = JdbcUtil.getConnection(); 23 //准备sql 24 String sql = "select count(*) from contact"; 25 //执行预编译的sql语句(检查语法) 26 stmt = conn.prepareStatement(sql); 27 //执行sql语句 28 rs = stmt.executeQuery(); 29 if(rs.next()){//把查到的结果返回给调用者 30 return rs.getInt(1); 31 } 32 return 0; 33 }catch(Exception e){ 34 throw new RuntimeException(e); 35 }finally{//关闭资源 36 JdbcUtil.close(conn, stmt, rs); 37 } 38 } 39 40 //每页的记录数 41 public List<Contact> getPageRecords(int startIndex, int offset) { 42 Connection conn = null; 43 PreparedStatement stmt = null; 44 ResultSet rs = null; 45 try{ 46 //获取数据库的连接 47 conn = JdbcUtil.getConnection(); 48 //执行预编译的sql语句(检查语法) 49 stmt = conn.prepareStatement("select * from contact limit ?,?"); 50 //设置参数 51 stmt.setInt(1, startIndex); 52 stmt.setInt(2, offset); 53 //发送参数,执行sql 54 rs = stmt.executeQuery(); 55 List<Contact> cs = new ArrayList<Contact>(); 56 while(rs.next()){ 57 Contact c=new Contact(); 58 c.setId(rs.getString("id")); 59 c.setName(rs.getString("name")); 60 c.setSex(rs.getString("sex")); 61 c.setAge(rs.getInt("age")); 62 c.setPhone(rs.getString("phone")); 63 c.setEmail(rs.getString("email")); 64 c.setQq(rs.getString("qq")); 65 cs.add(c); 66 } 67 return cs; 68 }catch(Exception e){ 69 throw new RuntimeException(e); 70 }finally{//关闭资源 71 JdbcUtil.close(conn, stmt, rs); 72 } 73 } 74 }
ContactServiceimpl 实现类
1 package com.shore.service.impl; 2 3 import java.util.List; 4 5 import com.shore.dao.ContactDAO; 6 import com.shore.dao.impl.ContactDAOMySqlImpl; 7 import com.shore.entity.Page; 8 import com.shore.service.ContactService; 9 10 public class ContactServiceimpl implements ContactService{ 11 ContactDAO dao=new ContactDAOMySqlImpl(); 12 13 public Page findPage(String pageNum) { 14 int num = 1;//用户要看的页码,默认是1 15 if(pageNum!=null&&!pageNum.trim().equals("")){//解析用户要看的页码 16 num = Integer.parseInt(pageNum); 17 } 18 int totalRecords = dao.getTotalRecordsNum();//得到总记录的条数 19 Page page = new Page(num, totalRecords);//对象创建出来后,很多的参数就已经计算完毕 20 //查询分页的记录（当前页显示的记录） 21 List records = dao.getPageRecords(page.getStartIndex(), page.getPageSize()); 22 page.setRecords(records); 23 return page; 24 } 25 }
ListContactServlet 类
1 package com.shore.servlet; 2 3 import java.io.IOException; 4 5 import javax.servlet.ServletException; 6 import javax.servlet.http.HttpServlet; 7 import javax.servlet.http.HttpServletRequest; 8 import javax.servlet.http.HttpServletResponse; 9 10 import com.shore.entity.Page; 11 import com.shore.service.ContactService; 12 import com.shore.service.impl.ContactServiceimpl; 13 14 public class ListContactServlet extends HttpServlet { 15 /* 16 * 显示所有联系人的逻辑 17 * */ 18 public void doGet(HttpServletRequest request, HttpServletResponse response) 19 throws ServletException, IOException { 20 ContactService service=new ContactServiceimpl(); 21 String num=request.getParameter("num"); 22 Page page=service.findPage(num); 23 page.setUrl("/ListContactServlet"); 24 request.setAttribute("page",page); 25 request.getRequestDispatcher("/listContact.jsp").forward(request, response); 26 } 27 28 public void doPost(HttpServletRequest request, HttpServletResponse response) 29 throws ServletException, IOException { 30 doGet(request, response); 31 } 32 }
listContact.jsp 查询页面
1 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> 2 <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 <html xmlns="http://www.w3.org/1999/xhtml"> 5 <head> 6 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 7 <title>查询所有联系人</title> 8 <style type="text/css"> 9 table td{ 10 /*文字居中*/ 11 text-align:center; 12 } 13 14 /*合并表格的边框*/ 15 table{ 16 border-collapse:collapse; 17 } 18 </style> 19 </head> 20 21 <body> 22 <center><h3>查询所有联系人</h3></center> 23 <table align="center" border="1" width="700px"> 24 <tr> 25 <th>编号</th> 26 <th>姓名</th> 27 <th>性别</th> 28 <th>年龄</th> 29 <th>电话</th> 30 <th>邮箱</th> 31 <th>QQ</th> 32 <th>操作</th> 33 </tr> 34 <c:forEach items="${page.records}" var="con" varStatus="varSta"> 35 <tr> 36 <td>${varSta.count }</td> 37 <td>${con.name }</td> 38 <td>${con.sex }</td> 39 <td>${con.age }</td> 40 <td>${con.phone }</td> 41 <td>${con.email }</td> 42 <td>${con.qq }</td> 43 <td><a href="${pageContext.request.contextPath }/QueryContactServlet?id=${con.id}">修改</a> <a href="${pageContext.request.contextPath }/DeleteContactServlet?id=${con.id}">删除</a></td> 44 </tr> 45 </c:forEach> 46 <tr> 47 <td colspan="8" align="center"><a href="${pageContext.request.contextPath }/addContact.jsp">[添加联系人]</a></td> 48 </tr> 49 </table> 50 <%@include file="/common/page.jsp"%> 51 </body> 52 </html>
page.jsp 被包含的页面
1 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> 2 <!-- 分页显示：开始 --> 3 4 第${page.currentPageNum }页/共${page.totalPageNum }页 5 <a href="${pageContext.request.contextPath}/ListContactServlet?num=1">首页</a> 6 <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.prePageNum}">上一页</a> 7 8 <c:forEach begin="${page.startPage}" end="${page.endPage}" var="num"> 9 <a href="${pageContext.request.contextPath}/ListContactServlet?num=${num}">${num}</a> 10 </c:forEach> 11 12 <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.nextPageNum}">下一页</a> 13 <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.totalPageNum }">尾页</a> 14 15 16 <input type="button" id="bt1" value="跳转到" onclick="jump()"/> <input type="text" size="3" id="num" name="num" />页 17 18 <select name="selNum" onchange="jump1(this)"> 19 <c:forEach begin="1" end="${page.totalPageNum }" var="num"> 20 <option value="${num}" ${page.currentPageNum==num?'selected="selected"':'' } >${num}</option> 21 </c:forEach> 22 </select> 23 24 <script type="text/javascript"> 25 function jump(){ 26 var numValue = document.getElementById("num").value; 27 //验证 28 if(!/^[1-9][0-9]*$/.test(numValue)){//验证是否是自然整数 29 alert("请输入正确的页码"); 30 return; 31 } 32 if(numValue>${page.totalPageNum}){ 33 alert("页码不能超过最大页数"); 34 return; 35 } 36 window.location.href="${pageContext.request.contextPath}/ListContactServlet?num="+numValue; 37 } 38 39 function jump1(selectObj){ 40 window.location.href="${pageContext.request.contextPath}/ListContactServlet?num="+selectObj.value; 41 } 42 </script> 43 44 <!-- 分页显示：结束 -->
最终效果图：
|
原创作者：DSHORE 作者主页：http://www.cnblogs.com/dshore123/ 原文出自：https://www.cnblogs.com/dshore123/p/10597898.html 欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！） |
