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> <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 }页
<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>
<input type="button" id="bt1" value="跳转到" onclick="jump()"/> <input type="text" size="3" id="num" name="num" />页
<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> <!-- 分页显示：结束 -->
最终效果图：
|
原创作者：DSHORE 作者主页：http://www.cnblogs.com/dshore123/ 原文出自：https://www.cnblogs.com/dshore123/p/10597898.html 欢迎转载，转载务必说明出处。（如果本文对您有帮助，可以点击一下右下角的 推荐，或评论，谢谢！） |