Java基础94 分页查询（以MySQL数据库为例）

时间：2023-03-08 22:00:18

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 配置文件

 url=jdbc:mysql://localhost:3306/school

 user=root

 password=123456

 driverClass=com.mysql.jdbc.Driver

JdbcUtil.java 封装文件（连接数据库）

 package com.shore.util;

 import java.io.FileInputStream;

 import java.io.FileNotFoundException;

 import java.io.IOException;

 import java.io.InputStream;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Statement;

 import java.util.Properties;

 public class JdbcUtil {

     //连接数据库的URL

     private static String url=null;

     private static String user=null;//用户名

     private static String password=null;//密码

     private static String driverClass=null;

     //静态代码块中(只加载一次)

     static{

         try {

             //读取db.properties

             Properties props=new Properties();

             InputStream in=JdbcUtil.class.getResourceAsStream("/db.properties");

             //加载文件

             props.load(in);

             url=props.getProperty("url");

             user=props.getProperty("user");

             password=props.getProperty("password");

             driverClass=props.getProperty("driverClass");

             //注册驱动

             Class.forName(driverClass);

         } catch (FileNotFoundException e) {

             e.printStackTrace();

         } catch (IOException e) {

             e.printStackTrace();

         } catch (ClassNotFoundException e) {

             e.printStackTrace();

             System.out.println("注册驱动失败");

         }

     }

     /*

       * 获取连接

       * */

     public static Connection getConnection(){

         try {

             Connection conn=DriverManager.getConnection(url, user, password);

             return conn;

         } catch (SQLException e) {

             e.printStackTrace();

             throw new RuntimeException();

         }

     }

      /*

       * 释放资源

       * */

     public static void close(Connection conn,Statement stmt,ResultSet rs){

         try {

             if(stmt!=null) stmt.close();

             if(conn!=null) conn.close();

             if(rs!=null) rs.close();

         } catch (SQLException e) {

             e.printStackTrace();

         }

     }

 }

Page 实体类

 package com.shore.entity;

 import java.util.List;

 //封装与分页有关的所有信息

 public class Page {

     private List records;//要显示的分页记录

     private int currentPageNum;//当前页码；可由用户指定（用于输入页码，点击跳转到指定页）*

     private int pageSize = 10;//每页显示的记录条数（这里是没页显示10条数据） *

     private int totalPageNum;//总页数*

     private int prePageNum;//上一页的页码*

     private int nextPageNum;//下一页的页码*

     private int startIndex;//数据库每页开始记录的索引（比如第2页是从11开始，第三页从21开始...）*

     private int totalRecords;//总记录的条数*

     //扩展的

     private int startPage;//开始页码

     private int endPage;//结束页码

     private String url;//查询分页的请求servlet的地址

     //currentPageNum：用户要看的页码

     //totalRecords:总记录条数

     public Page(int currentPageNum,int totalRecords){

         this.currentPageNum = currentPageNum;

         this.totalRecords = totalRecords;

         //计算总页数

         totalPageNum = totalRecords%pageSize==0?totalRecords/pageSize:(totalRecords/pageSize+1);

         //计算每页开始的索引

         startIndex = (currentPageNum-1)*pageSize;

         //计算开始和结束页码:9个页码

         if(totalPageNum > 9){

             //超过9页

             startPage = currentPageNum - 4;

             endPage = currentPageNum + 4;

             if(startPage < 1){

                 startPage = 1;

                 endPage = 9;

             }

             if(endPage>totalPageNum){

                 endPage = totalPageNum;

                 startPage = endPage - 8;

             }

         }else{

             //没有9页

             startPage = 1;

             endPage = totalPageNum;

         }

     }

     public List getRecords() {

         return records;

     }

     public void setRecords(List records) {

         this.records = records;

     }

     public int getCurrentPageNum() {

         return currentPageNum;

     }

     public void setCurrentPageNum(int currentPageNum) {

         this.currentPageNum = currentPageNum;

     }

     public int getPageSize() {

         return pageSize;

     }

     public void setPageSize(int pageSize) {

         this.pageSize = pageSize;

     }

     public int getTotalPageNum() {

         return totalPageNum;

     }

     public void setTotalPageNum(int totalPageNum) {

         this.totalPageNum = totalPageNum;

     }

     //不能无限上一页（假如当前页是第1页，那么“上一页”这个按钮变为灰色，再点击，则 无反应）

     public int getPrePageNum() {

         prePageNum = currentPageNum-1;

         if(prePageNum < 1){

             prePageNum = 1;

         }

         return prePageNum;

     }

     public void setPrePageNum(int prePageNum) {

         this.prePageNum = prePageNum;

     }

     //不能无限下一页（假如当前页是最后一页，那么“下一页”这个按钮变为灰色，再点击，则 无反应）

     public int getNextPageNum() {

         nextPageNum = currentPageNum + 1;

         if(nextPageNum > totalPageNum){

         if(nextPageNum > totalPageNum){

             nextPageNum = totalPageNum;

         }

         return nextPageNum;

     }

     public void setNextPageNum(int nextPageNum) {

         this.nextPageNum = nextPageNum;

     }

     public int getStartIndex() {

         return startIndex;

     }

     public void setStartIndex(int startIndex) {

         this.startIndex = startIndex;

     }

     public int getTotalRecords() {

         return totalRecords;

     }

     public void setTotalRecords(int totalRecords) {

         this.totalRecords = totalRecords;

     }

     public int getStartPage() {

         return startPage;

     }

     public void setStartPage(int startPage) {

         this.startPage = startPage;

     }

     public int getEndPage() {

         return endPage;

     }

     public void setEndPage(int endPage) {

         this.endPage = endPage;

     }

     public String getUrl() {

         return url;

     }

     public void setUrl(String url) {

         this.url = url;

     }

 }

ContactDAOMySqlImpl 实现类

 package com.shore.dao.impl;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.util.ArrayList;

 import java.util.List;

 import com.shore.dao.ContactDAO;

 import com.shore.entity.Contact;

 import com.shore.util.JdbcUtil;

 public class ContactDAOMySqlImpl implements ContactDAO{

     //总记录数

     public int getTotalRecordsNum() {

         Connection conn = null;

         PreparedStatement stmt = null;

         ResultSet rs = null;

         try{

             //获取数据库的连接

             conn = JdbcUtil.getConnection();

             //准备sql

             String sql = "select count(*) from contact";

             //执行预编译的sql语句(检查语法)

             stmt = conn.prepareStatement(sql);

             //执行sql语句

             rs = stmt.executeQuery();

             if(rs.next()){//把查到的结果返回给调用者

                 return rs.getInt(1);

             }

             return 0;

         }catch(Exception e){

             throw new RuntimeException(e);

         }finally{//关闭资源

             JdbcUtil.close(conn, stmt, rs);

         }

     }

     //每页的记录数

     public List<Contact> getPageRecords(int startIndex, int offset) {

         Connection conn = null;

         PreparedStatement stmt = null;

         ResultSet rs = null;

         try{

             //获取数据库的连接

             conn = JdbcUtil.getConnection();

             //执行预编译的sql语句(检查语法)

             stmt = conn.prepareStatement("select * from contact limit ?,?");

             //设置参数

             stmt.setInt(1, startIndex);

             stmt.setInt(2, offset);

             //发送参数,执行sql

             rs = stmt.executeQuery();

             List<Contact> cs = new ArrayList<Contact>();

             while(rs.next()){

                 Contact c=new Contact();

                 c.setId(rs.getString("id"));

                 c.setName(rs.getString("name"));

                 c.setSex(rs.getString("sex"));

                 c.setAge(rs.getInt("age"));

                 c.setPhone(rs.getString("phone"));

                 c.setEmail(rs.getString("email"));

                 c.setQq(rs.getString("qq"));

                 cs.add(c);

             }

             return cs;

         }catch(Exception e){

             throw new RuntimeException(e);

         }finally{//关闭资源

             JdbcUtil.close(conn, stmt, rs);

         }

     }

 }

ContactServiceimpl 实现类

 package com.shore.service.impl;

 import java.util.List;

 import com.shore.dao.ContactDAO;

 import com.shore.dao.impl.ContactDAOMySqlImpl;

 import com.shore.entity.Page;

 import com.shore.service.ContactService;

 public class ContactServiceimpl implements ContactService{

     ContactDAO dao=new ContactDAOMySqlImpl();

     public Page findPage(String pageNum) {

         int num = 1;//用户要看的页码,默认是1

         if(pageNum!=null&&!pageNum.trim().equals("")){//解析用户要看的页码

             num = Integer.parseInt(pageNum);

         }

         int totalRecords = dao.getTotalRecordsNum();//得到总记录的条数

         Page page = new Page(num, totalRecords);//对象创建出来后,很多的参数就已经计算完毕

         //查询分页的记录（当前页显示的记录）

         List records = dao.getPageRecords(page.getStartIndex(), page.getPageSize());

         page.setRecords(records);

         return page;

     }

 }

ListContactServlet 类

 package com.shore.servlet;

 import java.io.IOException;

 import javax.servlet.ServletException;

 import javax.servlet.http.HttpServlet;

 import javax.servlet.http.HttpServletRequest;

 import javax.servlet.http.HttpServletResponse;

 import com.shore.entity.Page;

 import com.shore.service.ContactService;

 import com.shore.service.impl.ContactServiceimpl;

 public class ListContactServlet extends HttpServlet {

     /*

      * 显示所有联系人的逻辑

      * */

     public void doGet(HttpServletRequest request, HttpServletResponse response)

             throws ServletException, IOException {

         ContactService service=new ContactServiceimpl();

         String num=request.getParameter("num");

         Page page=service.findPage(num);

         page.setUrl("/ListContactServlet");

         request.setAttribute("page",page);

         request.getRequestDispatcher("/listContact.jsp").forward(request, response);

     }

     public void doPost(HttpServletRequest request, HttpServletResponse response)

             throws ServletException, IOException {

         doGet(request, response);

     }

 }

listContact.jsp 查询页面

 <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

 <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

 <!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>

 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

 <title>查询所有联系人</title>

 <style type="text/css">

     table td{

         /*文字居中*/

         text-align:center;

     }

     /*合并表格的边框*/

     table{

         border-collapse:collapse;

     }

 </style>

 </head>

 <body>

 <center><h3>查询所有联系人</h3></center>

 <table align="center" border="1" width="700px">

     <tr>

         <th>编号</th>

         <th>姓名</th>

         <th>性别</th>

         <th>年龄</th>

         <th>电话</th>

         <th>邮箱</th>

         <th>QQ</th>

         <th>操作</th>

     </tr>

     <c:forEach items="${page.records}" var="con" varStatus="varSta">

     <tr>

         <td>${varSta.count }</td>

         <td>${con.name }</td>

         <td>${con.sex }</td>

         <td>${con.age }</td>

         <td>${con.phone }</td>

         <td>${con.email }</td>

         <td>${con.qq }</td>

         <td><a href="${pageContext.request.contextPath }/QueryContactServlet?id=${con.id}">修改</a>&nbsp;<a href="${pageContext.request.contextPath }/DeleteContactServlet?id=${con.id}">删除</a></td>

     </tr>

     </c:forEach>

     <tr>

         <td colspan="8" align="center"><a href="${pageContext.request.contextPath }/addContact.jsp">[添加联系人]</a></td>

     </tr>

 </table>

 <%@include file="/common/page.jsp"%>

 </body>

 </html>

page.jsp 被包含的页面

 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

 <!-- 分页显示：开始 -->

       第${page.currentPageNum }页/共${page.totalPageNum }页&nbsp;&nbsp;

       <a href="${pageContext.request.contextPath}/ListContactServlet?num=1">首页</a>

       <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.prePageNum}">上一页</a>

       <c:forEach begin="${page.startPage}" end="${page.endPage}" var="num">

           <a href="${pageContext.request.contextPath}/ListContactServlet?num=${num}">${num}</a>

       </c:forEach>

       <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.nextPageNum}">下一页</a>

       <a href="${pageContext.request.contextPath}/ListContactServlet?num=${page.totalPageNum }">尾页</a>

       &nbsp;&nbsp;

       <input type="button" id="bt1" value="跳转到" onclick="jump()"/>&nbsp;<input type="text" size="3" id="num" name="num" />页

       &nbsp;&nbsp;

       <select name="selNum" onchange="jump1(this)">

           <c:forEach begin="1" end="${page.totalPageNum }" var="num">

               <option value="${num}" ${page.currentPageNum==num?'selected="selected"':'' } >${num}</option>

           </c:forEach>

       </select>

       <script type="text/javascript">

           function jump(){

               var numValue = document.getElementById("num").value;

               //验证

               if(!/^[1-9][0-9]*$/.test(numValue)){//验证是否是自然整数

                   alert("请输入正确的页码");

                   return;

               }

               if(numValue>${page.totalPageNum}){

                   alert("页码不能超过最大页数");

                   return;

               }

               window.location.href="${pageContext.request.contextPath}/ListContactServlet?num="+numValue;

           }

           function jump1(selectObj){

               window.location.href="${pageContext.request.contextPath}/ListContactServlet?num="+selectObj.value;

           }

       </script>

       <!-- 分页显示：结束 -->

最终效果图：

Java基础94 分页查询（以MySQL数据库为例）

原创作者：DSHORE

作者主页：http://www.cnblogs.com/dshore123/

原文出自：https://www.cnblogs.com/dshore123/p/10597898.html

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

相关文章