首先得知道分页是什么?
分页就是让页面可以显示指定条数的数据。通过分页功能,可以更容易的管理数据,查看数据。
分页的分类
分页的实现分为真分页和假分页两种,也就是物理分页和逻辑分页。
1.真分页(物理分页):
实现原理: SELECT * FROM xxx [WHERE...] LIMIT #{param1}, #{param2}
第一个参数是开始数据的索引位置
第二个参数是要查询多少条数据
优点: 不会造成内存溢出
缺点: 翻页的速度比较慢
2.假分页(逻辑分页):
实现原理: 一次性将所有的数据查询出来放在内存之中,
每次需要查询的时候就直接从内存之中去取出相应索引区间的数据
优点: 分页的速度比较快
缺点: 可能造成内存溢出
- 总页数:pages
总页数 = 总条数 % 页面大小 == 0 ? 总条数 / 页面大小 : 总条数 / 页面大小 + 1 - 上一页:opage
上一页 = 当前页 - 1 > = 1 ? 当前页 - 1 : 1 - 下一页:npage
下一页 = 当前页 + 1 <= pages ? 当前页 + 1 : pages - 尾页:lpage
尾页 = 总条数 % 页面大小 == 0 ? 总条数 - 页面大小 : 总条数 - 总条数 % 页面大小
这里我使用的是JSP进行书写的分页代码
首先是show页面,进行页面的展示输出,我这里将上一页,下一页等进行加到select后面进行传递
参数,
<%--
Created by IntelliJ IDEA.
User: 24428
Date: 2023/4/19
Time: 10:35
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page isELIgnored="false"%>
<html>
<head>
<title>Title</title>
</head>
<body>
<table align="center" border="2px">
<p>
<a href="add.jsp">添加数据</a>
</p>
<tr>
<td>员工姓名</td>
<td>员工手机号</td>
<td>员工邮箱</td>
<td>员工职位</td>
<td>删除按钮</td>
</tr>
<c:forEach items="${list}" var="firm">
<tr>
<td>${firm.na}</td>
<td>${firm.phone}</td>
<td>${firm.email}</td>
<td>${firm.ties}</td>
<%--<td><a href="delete?name=${firm.na}">删除</a></td>--%>
<td><a href="delete?phone=${firm.phone}">删除</a></td>
</tr>
</c:forEach>
<tr>
<td> <a href="select?flag=1">首页</a> </td>
<td><a href="select?flag=2">上一页</a></td>
<td><a href="select?flag=3">下一页</a></td>
<td><a href="select?flag=4">尾页</a></td>
</tr>
</table>
</body>
</html>
这里是进行书写起始页,总页数,
private int page=0;//起始页
private static int count;
private static int pages;//总页数 4
private int size;
public static void getCount(){
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
List<Firm> list = queryRunner.query("SELECT * FROM firm", new BeanListHandler<Firm>(Firm.class));
count = list.size();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//总共的页数
public static int getpages(){
getCount();
return pages = count%3==0?count/3 : (count/3)+1;
}
接下来就是进行判断所带的参数如何
String flag = req.getParameter("flag");
if (flag == null || flag.equals("1")) {
page=0;
DataSource dataSource = DruidUtil.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
try {
List<Firm> list = runner.query("select * from firm limit 0,3", new BeanListHandler<Firm>(Firm.class));
System.out.println(list);
//添加域
req.setAttribute("list", list);
req.getSession().setAttribute("page", 0);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} else if (flag.equals("3")) {
//首先获取前端所携带数据 page域中的
Object page1 = req.getSession().getAttribute("page");
String page3 = String.valueOf(req.getSession().getAttribute("page"));
//将page转成 数值类型
int p = Integer.parseInt(page3);
//判断
if (p == 0) {//0
page += 3;//起始位置。当前页 3 6
} else {//3
page =p+3;
}
getCount();
if (page>=count){
page=p;
}
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
Object[] param = {page};
List<Firm> list = queryRunner.query("select * from firm limit ?,3", new BeanListHandler<Firm>(Firm.class),param);
//添加到作用域
req.setAttribute("list", list);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} else if (flag.equals("2")) {
//首先获取前端所携带数据 page域中的 起始位置
String page2 = String.valueOf(req.getSession().getAttribute("page"));
//将page转成 数值类型
int p = Integer.parseInt(page2);
// 起始页 = (当前页-1)*条数
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
page = Integer.parseInt(page2)-3;
if (page<0){
page=0;
}
Object[] s={page};
List<Firm> query = queryRunner.query("select * from firm limit ?,3", new BeanListHandler<Firm>(Firm.class),s);
req.setAttribute("list", query);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}else if (flag.equals("4")) {
int pages = getpages();
int temp = (pages-1)*3;
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
Object[] params = {temp};
String select = "select * from firm limit ?,3";
try {
List<Firm> query = queryRunner.query(select, new BeanListHandler<Firm>(Firm.class), params);
req.setAttribute("list", query);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
e.printStackTrace();
}
}
这是全部的代码,记得要继承Httpservlet
package Test;
import Bean.Firm;
import Util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
@WebServlet("/select")
public class Select extends HttpServlet {
private int page=0;//起始页
private static int count;
private static int pages;//总页数 4
private int size;
public static void getCount(){
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
List<Firm> list = queryRunner.query("SELECT * FROM firm", new BeanListHandler<Firm>(Firm.class));
count = list.size();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//总共的页数
public static int getpages(){
getCount();
return pages = count%3==0?count/3 : (count/3)+1;
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String flag = req.getParameter("flag");
if (flag == null || flag.equals("1")) {
page=0;
DataSource dataSource = DruidUtil.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
try {
List<Firm> list = runner.query("select * from firm limit 0,3", new BeanListHandler<Firm>(Firm.class));
System.out.println(list);
//添加域
req.setAttribute("list", list);
req.getSession().setAttribute("page", 0);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} else if (flag.equals("3")) {
//首先获取前端所携带数据 page域中的
Object page1 = req.getSession().getAttribute("page");
String page3 = String.valueOf(req.getSession().getAttribute("page"));
//将page转成 数值类型
int p = Integer.parseInt(page3);
//判断
if (p == 0) {//0
page += 3;//起始位置。当前页 3 6
} else {//3
page =p+3;
}
getCount();
if (page>=count){
page=p;
}
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
Object[] param = {page};
List<Firm> list = queryRunner.query("select * from firm limit ?,3", new BeanListHandler<Firm>(Firm.class),param);
//添加到作用域
req.setAttribute("list", list);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
} else if (flag.equals("2")) {
//首先获取前端所携带数据 page域中的 起始位置
String page2 = String.valueOf(req.getSession().getAttribute("page"));
//将page转成 数值类型
int p = Integer.parseInt(page2);
// 起始页 = (当前页-1)*条数
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
try {
page = Integer.parseInt(page2)-3;
if (page<0){
page=0;
}
Object[] s={page};
List<Firm> query = queryRunner.query("select * from firm limit ?,3", new BeanListHandler<Firm>(Firm.class),s);
req.setAttribute("list", query);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}else if (flag.equals("4")) {
int pages = getpages();
int temp = (pages-1)*3;
QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
Object[] params = {temp};
String select = "select * from firm limit ?,3";
try {
List<Firm> query = queryRunner.query(select, new BeanListHandler<Firm>(Firm.class), params);
req.setAttribute("list", query);
//把当前位置存入作用域
req.getSession().setAttribute("page", page);
req.getRequestDispatcher("show.jsp").forward(req, resp);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
/* public static void main(String[] args) throws Exception{
Connection connetion = DruidUtil.getConnetion();
Statement statement = connetion.createStatement();
ResultSet resultSet = statement.executeQuery("select * from firm");
System.out.println(resultSet);
}*/
}
还有一个删除的方法
package Test;
import Util.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.SQLException;
@WebServlet("/delete")
public class Delete extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//接受前端的数据
String ph = req.getParameter("phone");
Object[] pp={ph};
System.out.println(ph);
DataSource dataSource = DruidUtil.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
try {
int update = runner.update("delete from firm where phone= ?",pp);
System.out.println(update);
req.getSession().setAttribute("page", update);
req.getRequestDispatcher("select").forward(req, resp);
/* //接受前端的数据
String name = req.getParameter("name");
Object[] pp={name};
System.out.println(name);
DataSource dataSource = DruidUtil.getDataSource();
QueryRunner runner = new QueryRunner(dataSource);
try {
int update = runner.update("delete from firm where na= ?",pp);
System.out.println("delete from firm where na= '"+name+"'");
System.out.println(update);
*//* req.getSession().setAttribute("page", update);
req.getRequestDispatcher("select").forward(req, resp);*/
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
}
当然Jar包无法上传,就附带一张图,去进行下载所需jar包