分页查询
分页查询时需要注意的几个数据:
总的数据量:conutnumber
每页分多少数据:假设每页分3行数据
可以分多少页:countnumber%3!=0?(countnumber/3)+1:countnumber/3
每页的起始行数: (nowpage-1)*3
当前是多少页:nowpage
只要能明白上述的5个条件,程序就完成了一大部分…
代码:
作者创建的包和文件
前端代码:
find.jsp
<%--
Created by IntelliJ IDEA.
User: chen yan zi
Date: 2022/5/5
Time: 16:24
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib prefix="for" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<meta charset="utf-8">
<title>这是第${nowpage}页</title>
<link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://cdn.staticfile.org/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdn.staticfile.org/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<table class="table">
<tr>
<td>id</td>
<td>name</td>
<td>age</td>
</tr>
<c:forEach items="${list}" var="list">
<tr>
<td>${list.id}</td>
<td>${list.name}</td>
<td>${list.age}</td>
</tr>
</c:forEach>
</table>
<a href="nextservlet">下一页</a>
<a href="upservlet">上一页</a>
<a href="numberservlet">首页</a>
<a href="lastservlet">末页</a>
</body>
</html>
index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<body>
<a href="numberservlet" methods="post">点击获取数据</a>
</body>
</html>
BaseDao:连接和关闭数据库
package com.nubula.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class BaseDao {
private Connection con;
private PreparedStatement sta;
private ResultSet rs;
private String driver="com.mysql.jdbc.Driver";
private String url="jdbc:mysql://localhost:3306/school";
private String username="root";
private String password="root";
public Connection getCon(){
try {
Class.forName(driver);
con= DriverManager.getConnection(url, username, password);
}catch (Exception e){
e.printStackTrace();
}
return con;
}
public void Closed(ResultSet rs,PreparedStatement sta,Connection con) {
try {
if (rs != null) {
rs.close();
}
if (sta != null) {
sta.close();
}
if (con != null) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Student学生类
package com.nubula.entity;
public class Student {
private int id;
private String name;
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Student() {
}
public Student(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
StudentDao接口
package com.nubula.impl;
import com.nubula.entity.Student;
import java.util.List;
public interface StudentDao {
public List<Student> selectStudent();
public int getcountpage(int countnumber);
public int getstartnum(int nowpage);
public List<Student> getnumber(int nowpage);
}
StudentDaoimpl
package com.nubula.impl;
import com.nubula.entity.Student;
import com.nubula.utils.BaseDao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoimpl extends BaseDao implements StudentDao {
private Connection con;
private PreparedStatement sta;
private ResultSet rs;
@Override
/**
* 获取全部学生的数据
* 可以得到总数据
*/
public List<Student> selectStudent() {
con=this.getCon();
Student student=null;
String sql="select * from student";
List<Student> list=new ArrayList<>();
try {
sta = con.prepareStatement(sql);
rs=sta.executeQuery();
while (rs.next()){
student=new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
}catch (Exception e){
e.printStackTrace();
}
return list;
}
/**
* 总页数
*/
public int getcountpage(int countnumber){
int countpage;
if(countnumber%3!=0){
countpage= (countnumber/3)+1;
}else{
countpage=countnumber/3;
}
return countpage;
}
/**
* 获取起始行号
*/
public int getstartnum(int nowpage){
return (nowpage-1)*3;
}
/**
* 得到每页的数据
*/
public List<Student> getnumber(int startnum) {
con=this.getCon();
Student student=null;
String sql="select * from student limit ?,?";
List<Student> list=new ArrayList<>();
try {
sta = con.prepareStatement(sql);
sta.setInt(1,startnum);
sta.setInt(2,3);
rs=sta.executeQuery();
while (rs.next()){
student=new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
list.add(student);
}
}catch (Exception e){
e.printStackTrace();
}
return list;
}
}
package com.nubula.servlet;
import com.nubula.entity.Student;
import com.nubula.impl.StudentDao;
import com.nubula.impl.StudentDaoimpl;
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 java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/numberservlet")
public class numberservlet<nowpage> extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
StudentDao sd=new StudentDaoimpl();
List<Student> list=new ArrayList<>();
int countnumber= sd.selectStudent().size();
int countpage= sd.getcountpage(countnumber);
int nowpage=1;
int startnum= sd.getstartnum(nowpage);
list=sd.getnumber(startnum);
if(list!=null){
request.getSession().setAttribute("list",list);
request.getSession().setAttribute("nowpage",nowpage);
request.getRequestDispatcher("find.jsp").forward(request,response);
}
}
}
package com.nubula.servlet;
import com.nubula.entity.Student;
import com.nubula.impl.StudentDao;
import com.nubula.impl.StudentDaoimpl;
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.servlet.http.HttpSession;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/nextservlet")
public class nextservlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
StudentDao sd = new StudentDaoimpl();
List<Student> list = new ArrayList<>();
int countnumber = sd.selectStudent().size();
int countpage = sd.getcountpage(countnumber);
int nowpage=(int)request.getSession().getAttribute("nowpage");
if (nowpage!=(countpage)) {
nowpage=nowpage+1;
} else {
nowpage=countpage;
}
int startnum = sd.getstartnum(nowpage);
list = sd.getnumber(startnum);
if (list != null) {
request.getSession().setAttribute("list", list);
request.getSession().setAttribute("nowpage",nowpage);
request.getRequestDispatcher("find.jsp").forward(request, response);
}
}
}
package com.nubula.servlet;
import com.nubula.entity.Student;
import com.nubula.impl.StudentDao;
import com.nubula.impl.StudentDaoimpl;
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 java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/upservlet")
public class upservlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
StudentDao sd = new StudentDaoimpl();
List<Student> list = new ArrayList<>();
int countnumber = sd.selectStudent().size();
int countpage = sd.getcountpage(countnumber);
int nowpage=(int)request.getSession().getAttribute("nowpage");
if (nowpage!=1) {
nowpage=nowpage-1;
} else {
nowpage=1;
}
int startnum = sd.getstartnum(nowpage);
list = sd.getnumber(startnum);
if (list != null) {
request.getSession().setAttribute("list", list);
request.getSession().setAttribute("nowpage",nowpage);
request.getRequestDispatcher("find.jsp").forward(request, response);
}
}
}
package com.nubula.servlet;
import com.nubula.entity.Student;
import com.nubula.impl.StudentDao;
import com.nubula.impl.StudentDaoimpl;
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 java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/lastservlet")
public class lastservlet extends HttpServlet {
@Override
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
StudentDao sd=new StudentDaoimpl();
List<Student> list=new ArrayList<>();
int countnumber=sd.selectStudent().size();
int nowpage=sd.getcountpage(countnumber);
int startnum= sd.getstartnum(nowpage);
list=sd.getnumber(startnum);
if(list!=null) {
request.getSession().setAttribute("list", list);
request.getSession().setAttribute("nowpage", nowpage);
request.getRequestDispatcher("find.jsp").forward(request, response);
}
}
}
运行结果
点击下一页
运行结果
[外链图片转存中...(img-k1Z9SqAL-1651755603180)]
点击下一页
[外链图片转存中...(img-bu1GLFIJ-1651755603181)]
点击都有相对应的功能。