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>&nbsp;<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 }页&nbsp;&nbsp;
 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       &nbsp;&nbsp;
16       <input type="button" id="bt1" value="跳转到" onclick="jump()"/>&nbsp;<input type="text" size="3" id="num" name="num" />页
17       &nbsp;&nbsp;
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

欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的推荐，或评论，谢谢！）