✅作者简介：热爱国学的Java后端开发者，修心和技术同步精进。
🍎个人主页：Java Fans的博客
🍊个人信条：不迁怒，不贰过。小知识，大智慧。
💞当前专栏：JAVA开发者成长之路
✨特色专栏：国学周更-心性养成之路
🥭本文内容：JSP——分页查询
更多内容点击👇
      JSP——标准标签库 (JSTL)

文章目录

• • 1.1 概念
  • 1.2 分页实现思路
  • 1.3 分页代码实现步骤
  • • 1.3.1 数据库准备
    • 1.3.2 数据库配置文件
    • 1.3.3 创建实体类
    • 1.3.4 创建StudentDao接口
    • 1.3.5 StudentDaoImpl实现类
    • 1.3.6 创建StudentService接口
    • 1.3.7 StudentServiceImpl实现类
    • 1.3.8 studentShow0.jsp
    • 1.3.9 运行效果图部分截图

1.1 概念

  分页是Web应用程序非常重要的一个技术。数据库中的数据可能是成千上万的，不可能把这么多的数据一次显示在浏览器上面。一般根据每行数据在页面上所占的空间设置每页显示若干行，比如一般20行是一个比较理想的显示状态。

1.2 分页实现思路

  对于海量的数据查询，需多少就取多少，显然是最佳的解决方法，假如某个表中有200万条记录，第一页取前20条，第二页取21-40条记录，第三页取41-60条记录，依此类推

  SELECT * FROM 表名 LIMIT 0,20;//第一页
  SELECT * FROM 表名 LIMIT 20,20;//第二页
  SELECT * FROM 表名 LIMIT 40,20;//第三页

1.3 分页代码实现步骤

  第一步：确定每页显示的数据数量

  第二步：确定分页显示所需的总页数

  第三步：编写SQL查询语句，实现数据查询

  第四步：在JSP页面中进行分页显示设置

1.3.1 数据库准备

CREATE DATABASE information
DROP TABLE student
CREATE TABLE `student`(
`sid` INT PRIMARY KEY COMMENT '学号',
`sname` VARCHAR(10) NOT NULL COMMENT '姓名',
`password` VARCHAR(10) NOT NULL COMMENT '密码',
`sex` VARCHAR(5) NOT NULL COMMENT '性别',
`phone` VARCHAR(15) NOT NULL COMMENT '手机号',
`qq` VARCHAR(15) NOT NULL COMMENT 'QQ号',
`sclass` VARCHAR(5) NOT NULL COMMENT '班级',
`grade` VARCHAR(5) NOT NULL COMMENT '年纪'
);INSERT INTO `student` VALUES(20221001,'赵馨','123456','女','15768965625','254562145','1','高一');
INSERT INTO `student` VALUES(20221002,'向翔','123456','男','15796542135','254554845','1','高一');
INSERT INTO `student` VALUES(20221003,'伯姜洲','123456','男','89745215488','254567845','1','高一');
INSERT INTO `student` VALUES(20221004,'千柿鬼鲛','123456','女','12458762145','254561478','1','高一');
INSERT INTO `student` VALUES(20221005,'弥彦','123456','女','15768961245','254784145','2','高一');
INSERT INTO `student` VALUES(20221006,'蝎','123456','女','1458954621','2545628745','2','高一');
INSERT INTO `student` VALUES(20221007,'飞段','123456','男','7895425484','2545541245','2','高一');
INSERT INTO `student` VALUES(20221008,'大蛇丸','123456','男','12456784512','258542145','2','高一');
INSERT INTO `student` VALUES(20221009,'小南','123456','女','32145784512','254567895','1','高二');
INSERT INTO `student` VALUES(20221010,'迪达拉','123456','女','36547895412','254478145','1','高二');
INSERT INTO `student` VALUES(20221011,'角都','123456','男','15745125625','254578125','1','高二');
INSERT INTO `student` VALUES(20221012,'绝','123456','男','15768451225','254565127','1','高二');
INSERT INTO `student` VALUES(20221013,'长门','123456','男','15766532625','254514745','2','高二');
INSERT INTO `student` VALUES(20221014,'宇智波带士','123456','男','17845965625','251542145','2','高二');
INSERT INTO `student` VALUES(20221015,'油女志乃','123456','女','15184565625','254985145','2','高二');
INSERT INTO `student` VALUES(20221016,'春野樱','123456','女','15745128725','254566585','2','高二');
INSERT INTO `student` VALUES(20221017,'佐助','123456','男','15768478955','254562623','1','高三');
INSERT INTO `student` VALUES(20221018,'漩涡鸣人','123456','男','15774512625','251242145','1','高三');
INSERT INTO `student` VALUES(20221019,'赤丸','123456','男','15768512455','254562587','1','高三');
INSERT INTO `student` VALUES(20221020,'木叶丸','123456','男','15768784525','254515445','1','高三');
INSERT INTO `student` VALUES(20221021,'张三','123456','男','15768966521','254562964','2','高三');
INSERT INTO `student` VALUES(20221022,'李四','123456','男','15768784425','254562328','2','高三');
INSERT INTO `student` VALUES(20221023,'王五','123456','男','15761487445','254562582','2','高三');
INSERT INTO `student` VALUES(20221024,'赵六','123456','男','15768541425','254567815','2','高三');
INSERT INTO `student` VALUES(20221025,'赵','123456','女','15768965625','254562145','1','高一');
INSERT INTO `student` VALUES(20221026,'向','123456','男','15796542135','254554845','1','高一');
INSERT INTO `student` VALUES(20221027,'伯姜','123456','男','89745215488','254567845','1','高一');
INSERT INTO `student` VALUES(20221028,'千柿鬼','123456','女','12458762145','254561478','1','高一');
INSERT INTO `student` VALUES(20221029,'弥','123456','女','15768961245','254784145','2','高一');
INSERT INTO `student` VALUES(20221030,'蝎1','123456','女','1458954621','2545628745','2','高一');
INSERT INTO `student` VALUES(20221031,'飞','123456','男','7895425484','2545541245','2','高一');
INSERT INTO `student` VALUES(20221032,'大蛇','123456','男','12456784512','258542145','2','高一');
INSERT INTO `student` VALUES(20221033,'小','123456','女','32145784512','254567895','1','高二');
INSERT INTO `student` VALUES(20221034,'迪达','123456','女','36547895412','254478145','1','高二');
INSERT INTO `student` VALUES(20221035,'角','123456','男','15745125625','254578125','1','高二');
INSERT INTO `student` VALUES(20221036,'绝0','123456','男','15768451225','254565127','1','高二');
INSERT INTO `student` VALUES(20221037,'长','123456','男','15766532625','254514745','2','高二');
INSERT INTO `student` VALUES(20221038,'tgedg','123456','男','17845965625','251542145','2','高二');
INSERT INTO `student` VALUES(20221039,'油女志','123456','女','15184565625','254985145','2','高二');
INSERT INTO `student` VALUES(20221040,'春野','123456','女','15745128725','254566585','2','高二');
INSERT INTO `student` VALUES(20221041,'佐','123456','男','15768478955','254562623','1','高三');
INSERT INTO `student` VALUES(20221042,'漩涡鸣','123456','男','15774512625','251242145','1','高三');
INSERT INTO `student` VALUES(20221043,'赤','123456','男','15768512455','254562587','1','高三');
INSERT INTO `student` VALUES(20221044,'木叶','123456','男','15768784525','254515445','1','高三');
INSERT INTO `student` VALUES(20221045,'张','123456','男','15768966521','254562964','2','高三');
INSERT INTO `student` VALUES(20221046,'李','123456','男','15768784425','254562328','2','高三');
INSERT INTO `student` VALUES(20221047,'王','123456','男','15761487445','254562582','2','高三');
INSERT INTO `student` VALUES(20221048,'赵','123456','男','15768541425','254567815','2','高三');

1.3.2 数据库配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/information
username=root
password=huanghuang
initialSize=10
maxActive=80
maxIdle=5
maxWait=3000

1.3.3 创建实体类

分页实体类：Page类

  分页数据根据所需的实体类。类中包含页码、页大小（每页显示多少条数据）、总条数、总页数、起始行

package cn.bdqn.entity;
public class Page {private Integer pageIndex;//页码private Integer pageSize;//页大小  显示多少行数据private Integer totalCounts;//数据的总行数private Integer totalPages;//总页数private Integer startRows;//起始行public Page(Integer pageIndex) {this(pageIndex, 8);}public Page(Integer pageIndex, Integer pageSize) {this.pageIndex = pageIndex;this.pageSize = pageSize;this.setStartRows((pageIndex - 1) * pageSize);}public Integer getPageIndex() {return pageIndex;}public void setPageIndex(Integer pageIndex) {this.pageIndex = pageIndex;}public Integer getPageSize() {return pageSize;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}public Integer getTotalCounts() {return totalCounts;}public void setTotalCounts(Integer totalCounts) {this.totalCounts = totalCounts;this.setTotalPages(totalCounts % pageSize == 0 ? totalCounts / pageSize : totalCounts / pageSize + 1);}public Integer getTotalPages() {return totalPages;}public void setTotalPages(Integer totalPages) {this.totalPages = totalPages;}public Integer getStartRows() {return startRows;}public void setStartRows(Integer startRows) {this.startRows = startRows;}@Overridepublic String toString() {return "Page{" +"pageIndex=" + pageIndex +", pageSize=" + pageSize +", totalCounts=" + totalCounts +", totalPages=" + totalPages +", startRows=" + startRows +'}';}
}

学生实体类：Student类

package cn.bdqn.entity;public class Student {private int sid;private String sname;private String password;private String sex;private String phone;private String qq;private String sclass="";private String grade="";public Student() {}public Student(int sid, String sname, String password, String sex, String phone, String qq, String sclass, String grade) {this.sid = sid;this.sname = sname;this.password = password;this.sex = sex;this.phone = phone;this.qq = qq;this.sclass = sclass;this.grade = grade;}public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getQq() {return qq;}public void setQq(String qq) {this.qq = qq;}public String getSclass() {return sclass;}public void setSclass(String sclass) {this.sclass = sclass;}public String getGrade() {return grade;}public void setGrade(String grade) {this.grade = grade;}@Overridepublic String toString() {return "Student{" +"sid=" + sid +", sname='" + sname + '\'' +", password='" + password + '\'' +", sex='" + sex + '\'' +", phone='" + phone + '\'' +", qq='" + qq + '\'' +", sclass='" + sclass + '\'' +", grade='" + grade + '\'' +'}';}
}

1.3.4 创建StudentDao接口

package cn.bdqn.dao;//import cn.bdqn.entity.PageBean;
import cn.bdqn.entity.Page;
import cn.bdqn.entity.Student;import java.util.List;public interface StudentDao {//对Student表进行增删改查。int insert(Student student);int update(Student student);int delete(int sid);Student selectOne(int sid);//获取学号和密码相匹配的学生Student selectOne(int sid, String password);List selectAll();void deleteByIds(String[] ids);//条件查询List allInfo2(Student student);//条件查询的结果上，统计总记录，把分页条件作为参数Long totalCounts();//升级:分页功能
//    List allInfo(PageBean pageBean);public List selectAll(Page page);public long selectCount();public List searchByStuName(String stuName);
}

1.3.5 StudentDaoImpl实现类

package cn.bdqn.dao.impl;import cn.bdqn.dao.StudentDao;
//import cn.bdqn.entity.PageBean;
import cn.bdqn.entity.Page;
import cn.bdqn.entity.Student;
import cn.bdqn.utils.DBUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class StudentDaoImpl implements StudentDao {private QueryRunner queryRunner=new QueryRunner(DBUtils.getDataSource());//对Student表进行增删改查@Overridepublic int insert(Student student) {String sql="insert into `student` values(?,?,?,?,?,?,?,?);";Object[] args={student.getSid(),student.getSname(),student.getPassword(),student.getSex(),student.getPhone(),student.getQq(),student.getSclass(),student.getGrade()};try {return queryRunner.update(sql,args);} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic int update(Student student) {String sql="update `student` set sname=?,password=?,sex=?,phone=?,qq=?,sclass=?,grade=? where sid=?;";Object[] args={student.getSname(),student.getPassword(),student.getSex(),student.getPhone(),student.getQq(),student.getSclass(),student.getGrade(),student.getSid()};try {return queryRunner.update(sql,args);} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic int delete(int sid) {String sql="delete from `student` where sid=?";try {return queryRunner.update(sql,sid);} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic Student selectOne(int sid) {String sql="select * from `student` where sid=?;";try {return queryRunner.query(sql,new BeanHandler(Student.class),sid);} catch (SQLException e) {throw new RuntimeException(e);}}//获取学号和密码相匹配的学生@Overridepublic Student selectOne(int sid, String password) {String sql="select * from `student` where sid=? and password=?;";Object[] args={sid,password};try {return queryRunner.query(sql,new BeanHandler(Student.class),args);} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic List selectAll() {String sql="select * from `student`;";try {return queryRunner.query(sql,new BeanListHandler(Student.class));} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic void deleteByIds(String[] ids) {for (String id : ids) {delete(Integer.parseInt(id));}}
//条件查询sql语句@Overridepublic List allInfo2(Student student) {String sql="select * from `student` where 1=1 ";if(!(student.getSclass()==null || student.getSclass().equals(""))){sql+=" and sclass like '%"+student.getSclass()+"%'";}if(!(student.getGrade()==null || student.getGrade().equals(""))){sql+=" and grade like '%"+student.getGrade()+"%'";}
//        sql+=" limit ?,?";System.out.println("sql"+sql);try {return queryRunner.query(sql,new BeanListHandler(Student.class));} catch (SQLException e) {throw new RuntimeException(e);}}@Overridepublic List selectAll(Page page) {try {List emps = queryRunner.query(DBUtils.getDataSource().getConnection(),"select * from student limit ?,?",new BeanListHandler(Student.class),page.getStartRows(),page.getPageSize());return emps;} catch (SQLException e) {e.printStackTrace();}return null;}@Overridepublic long selectCount() {try {return (long)queryRunner.query(DBUtils.getDataSource().getConnection(),"select count(*) from student;",new ScalarHandler<>());} catch (SQLException e) {e.printStackTrace();}return 0;}@Overridepublic Long totalCounts() {String sql="select count(*) from student";//返回的统计结果  记录数try {return queryRunner.query(sql,new ScalarHandler());} catch (SQLException e) {throw new RuntimeException(e);}}//根据姓名模糊查询@Overridepublic List searchByStuName(String stuName){//模糊查询String sql="select * from `student` where sname like ?;";try {return queryRunner.query(sql,new BeanListHandler(Student.class),stuName);} catch (SQLException e) {throw new RuntimeException(e);}}
}

1.3.6 创建StudentService接口

package cn.bdqn.service;//import cn.bdqn.entity.PageBean;
import cn.bdqn.entity.Page;
import cn.bdqn.entity.Student;
import java.util.List;public interface StudentService {//对Student表进行增删改查。int insert(Student student);int update(Student student);int delete(int sid);Student selectOne(int sid);//获取学号和密码相匹配的学生Student selectOne(int sid, String password);List selectAll();//选择删除方法void deleteByIds(String[] ids);//条件查询List allInfo2(Student student);//分页
//    List allInfo(PageBean pageBean);public List selectAll(Page page);public long selectCount();//姓名模糊查询public List searchByStuName(String stuName);
}

1.3.7 StudentServiceImpl实现类

package cn.bdqn.service.impl;import cn.bdqn.dao.StudentDao;
import cn.bdqn.dao.impl.StudentDaoImpl;
//import cn.bdqn.entity.PageBean;
import cn.bdqn.entity.Page;
import cn.bdqn.entity.Student;
import cn.bdqn.service.StudentService;import java.util.List;public class StudentServiceImpl implements StudentService {StudentDao studentDao=new StudentDaoImpl();@Overridepublic int insert(Student student) {return studentDao.insert(student);}@Overridepublic int update(Student student) {return studentDao.update(student);}@Overridepublic int delete(int sid) {return studentDao.delete(sid);}@Overridepublic Student selectOne(int sid) {return studentDao.selectOne(sid);}//获取学号和密码相匹配的学生@Overridepublic Student selectOne(int sid, String password) {return studentDao.selectOne(sid,password);}@Overridepublic List selectAll() {return studentDao.selectAll();}@Overridepublic void deleteByIds(String[] ids) {studentDao.deleteByIds(ids);}@Overridepublic List allInfo2(Student student) {return studentDao.allInfo2(student);}@Overridepublic List selectAll(Page page) {return studentDao.selectAll(page);}@Overridepublic long selectCount() {return studentDao.selectCount();}@Overridepublic List searchByStuName(String stuName) {return studentDao.searchByStuName(stuName);}
}

1.3.8 studentShow0.jsp

<%@ page import="java.util.List" %>
<%@ page import="cn.bdqn.entity.Page" %>
<%@ page import="cn.bdqn.service.StudentService" %>
<%@ page import="cn.bdqn.service.impl.StudentServiceImpl" %>
<%@ page import="cn.bdqn.entity.Student" %>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>


<%--    --%>
<%--    --%>


<%String pageIndex = request.getParameter("pageIndex");if(pageIndex==null){//如果是第一次访问pageIndex ="1";request.setAttribute("pageIndex",pageIndex);}Page page1 = new Page(Integer.valueOf(pageIndex));//调用Dao，获取所有数据StudentService studentService = new StudentServiceImpl();long count = studentService.selectCount();page1.setTotalCounts((int)count);List students = studentService.selectAll(page1);request.setAttribute("students",students);request.setAttribute("page1",page1);
%>
【学号查询】
">
【姓名查询】

<%--                --%>">
【年级查询】
【班级查询】
学号
姓名
性别
手机号
QQ号
班级
年级
操作
${student.sid}
${student.sname}
${student.sex}
${student.phone}
${student.qq}
${student.sclass}
${student.grade}
 1}">">首页">上一页首页上一页">下一页">尾页下一页尾页

1.3.9 运行效果图部分截图

  码文不易，本篇文章就介绍到这里，如果想要学习更多Java系列知识，点击关注博主，博主带你零基础学习Java知识。与此同时，对于日常生活有困扰的朋友，欢迎阅读我的第四栏目：《国学周更—心性养成之路》，学习技术的同时，我们也注重了心性的养成。