一、jsp页面
<%--
Created by IntelliJ IDEA.
User: 杪&秋
Date: 2022/12/20
Time: 13:24
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" %>
<html>
<head>
<title>分页查询</title>
<style>
a{
text-decoration: none;
}
table{
border:1px solid blue;
width:600px;
border-collapse: collapse;
}
td{
border:1px solid blue;
}
</style>
</head>
<body>
<table>
<tr>
<td>序号</td>
<td>用户名</td>
<td>性别</td>
<td>年龄</td>
<td>成绩</td>
<%--<td>操作</td>--%>
</tr>
<%--pb.beanList获取当前页的信息--%>
<c:forEach items="${requestScope.pb.beanList}" var="user" varStatus="s">
<tr>
<td>${s.index+1}</td>
<td>${user.getName()}</td>
<td>${user.getSex()}</td>
<td>${user.getAge()}</td>
<td>${user.getScore()}</td>
<%--<td><a href="delect?id=${user.id}">删除</a>/<a href="edit?id=${user.id}">修改</a></td>--%>
</tr>
</c:forEach>
</table>
<br>
<%--从servlet获取 ${pb.pc}获取当前页 ${pb.tp}总页数--%>
第${pb.pc}页/共${pb.tp}页
<a href="<c:url value='page?pc=1'/>">首页</a>
<%--当前页大于1时才允许有上一页操作--%>
<c:if test="${pb.pc>1}">
<a href="<c:url value='page?pc=${pb.pc-1}'/>">上一页</a>
</c:if>
<%--当前页小于总页数时才允许有下一页操作--%>
<c:if test="${pb.pc<pb.tp}">
<a href="<c:url value='page?pc=${pb.pc+1}'/>">下一页</a>
</c:if>
<a href="<c:url value='page?pc=${pb.tp}'/>">尾页</a>
</body>
</html>
二、web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<servlet>
<servlet-name>queryPageBean</servlet-name>
<servlet-class>com.iotek.action.queryPageBean</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>queryPageBean</servlet-name>
<url-pattern>/page</url-pattern>
</servlet-mapping>
</web-app>
三、jdbc.properties配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:/student
user=root
password=
四、JdbcUtile
package com.iotek.utile;
import org.junit.Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* @Author:
* @CreateTime: 2022-12-30 13:35
*/
public class JdbcUtile {
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
private static Properties ps;
static {
ps = new Properties();
try {
ps.load(JdbcUtile.class.getClassLoader().getResourceAsStream("jdbc.properties"));
DRIVER = ps.getProperty("driver");
URL = ps.getProperty("url");
USER = ps.getProperty("user");
PASSWORD = ps.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PASSWORD);
System.out.println("连接成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Connection connection){
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void a(){
getConn();
}
}
五、dao
package com.iotek.dao;
import com.iotek.entity.PageBean;
import com.iotek.entity.Student;
public interface StudentDao {
/**
* @description: 分页查询
* @param int pc 当前页
* @param int ps 页的大小
* @return PageBean<Student>
* @time: 2022/12/30 14:05
**/
public PageBean<Student> queryPageBeanStudent(int pc,int ps);
}
六、daoImpl
package com.iotek.dao.impl;
import com.iotek.utile.JdbcUtile;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* @Author:
* @CreateTime: 2022-12-30 14:04
*/
public class BaseDao {
private QueryRunner qr = new QueryRunner();
private Connection connection;
/**
* @description:查询所有
* @param String sql 查询sql语句
* @param Class<T> type
* @param Object args
* @return List<T>
* 返回空则数据库为空
* @time: 2022/12/30 14:11
**/
public <T> List<T> queryAll(String sql,Class<T> type,Object...args){
connection = JdbcUtile.getConn();
try {
System.out.println("1111111111111111");
return qr.query(connection,sql,new BeanListHandler<T>(type),args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtile.closeAll(connection);
}
return null;
}
/**
* @description:执行一行一列
* @param String sql
* @param Object args
* @return Object
* @time: 2022/12/30 14:22
**/
public Object querNumber(String sql,Object...args){
connection = JdbcUtile.getConn();
try {
return qr.query(connection,sql,new ScalarHandler<>(),args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtile.closeAll(connection);
}
return null;
}
}
package com.iotek.dao.impl;
import com.iotek.dao.StudentDao;
import com.iotek.entity.PageBean;
import com.iotek.entity.Student;
import java.util.List;
/**
* @Author:
* @CreateTime: 2022-12-30 14:02
*/
public class StudentDaoImpl extends BaseDao implements StudentDao {
/**
* @description: 解题思路
* @param int pc 当前页
* @param int ps 页的大小
* @return PageBean<Student>
* @time: 2022/12/30 14:13
**/
@Override
public PageBean<Student> queryPageBeanStudent(int pc, int ps) {
PageBean<Student> pageBean = new PageBean<>();
pageBean.setPc(pc);
pageBean.setPs(ps);
pageBean.setTr(countStudent());//总记录数
//分页查询
/*
* 分页查询的公式
* select * from stu limit (pc -1) * ps , ps;
* select * from stu limit 0 , 3; -->第一页 ,每页3条数据
**/
String sql = "select * from stu limit "+(pc-1)*ps+","+ps;
System.out.println("sql语句"+sql);
List<Student> students = queryAll(sql,Student.class);
System.out.println("查询出来的内容"+students);
pageBean.setBeanList(students);//每页的内容
return pageBean;
}
public int countStudent(){
String sql = "SELECT COUNT(*) FROM STU";
int count = Integer.parseInt(querNumber(sql).toString());
return count;
}
}
七、 service
package com.iotek.service;
import com.iotek.entity.PageBean;
import com.iotek.entity.Student;
public interface StudentService {
public PageBean<Student> selectPageBeanStudent(int pc,int ps);
}
八、serviceImpl
package com.iotek.service.impl;
import com.iotek.dao.StudentDao;
import com.iotek.dao.impl.StudentDaoImpl;
import com.iotek.entity.PageBean;
import com.iotek.entity.Student;
import com.iotek.service.StudentService;
/**
* @Author:
* @CreateTime: 2022-12-30 14:42
*/
public class StudentSerivceImpl implements StudentService {
private StudentDao studentDao = new StudentDaoImpl();
@Override
public PageBean<Student> selectPageBeanStudent(int pc, int ps) {
return studentDao.queryPageBeanStudent(pc,ps);
}
}
九、action
package com.iotek.action;
import com.iotek.entity.PageBean;
import com.iotek.entity.Student;
import com.iotek.service.StudentService;
import com.iotek.service.impl.StudentSerivceImpl;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.IOException;
public class queryPageBean extends HttpServlet {
private StudentService studentService = new StudentSerivceImpl();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int pc = getPc(request,response);//获取当前页
int ps = 10;//页的大小 10
PageBean<Student> pageBean = studentService.selectPageBeanStudent(pc, ps);//当前页的信息
request.setAttribute("pb",pageBean);//将数据传递给页面
request.getRequestDispatcher("queryPage.jsp").forward(request,response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected int getPc(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String pc = request.getParameter("pc");//从页面获取
//trim()方法去掉空格
if (pc == null || pc.trim().isEmpty()) {
return 1;//pc 没有传递数据,默认查询第一页
}
return Integer.parseInt(pc);//获取正常页码
}
}
10、entity
package com.iotek.entity;
/**
* @Author:
* @CreateTime: 2022-12-30 13:46
*/
public class Student {
private Integer id;
private String name;
private String sex;
private Integer age;
private double score;
public Student() {
super();
}
public Student(Integer id, String name, String sex, Integer age, double score) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.score = score;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
}
package com.iotek.entity;
import java.util.List;
/**
* @Author:
* @CreateTime: 2022-12-30 13:48
*/
//<T> 泛型 任意类型 模板
public class PageBean<T> {
private Integer pc;//当前页
private Integer tr;//总记录数 105条数据
private Integer ps;//页的大小 10条记录
private Integer tp;//总页数 11页
private List<T> beanList;//页的记录集合 --> 每一页记录的内容
public Integer getPc() {
return pc;
}
public void setPc(Integer pc) {
this.pc = pc;
}
public Integer getTr() {
return tr;
}
public void setTr(Integer tr) {
this.tr = tr;
}
public Integer getPs() {
return ps;
}
public void setPs(Integer ps) {
this.ps = ps;
}
//总页数由计算得出不需要设置值不需要set()方法
public Integer getTp() {
int tp = tr/ps;
return tr % ps == 0 ? tp : tp +1;
}
/* public void setTp(Integer tp) {
this.tp = tp;
}*/
public List<T> getBeanList() {
return beanList;
}
public void setBeanList(List<T> beanList) {
this.beanList = beanList;
}
@Override
public String toString() {
return "PageBean{" +
"pc=" + pc +
", tr=" + tr +
", ps=" + ps +
", tp=" + tp +
", beanList=" + beanList +
'}';
}
}
11、项目层次图
12、运行截图
链接:https://pan.baidu.com/s/1uxutGNG8cSFIrLjYd28_3w?pwd=1234
提取码:1234