分页在读取数据库里的数据需要用,在以后数据库肯定还会有很多数据,一个页面装不下,所以需要分页功能。数据库查询的分页语句是“SELECT * FROM emp LIMIT 0, 5;”这里0是指起始行,5是查询5行,第二页起始行就是5,每页也是查询5条数据。起始行=(页大小-1)*行数。分页查询还需要知道一共多少页(总页数=数据总数%每页条数==0?数据总数/每页条数:数据总数/每页条数+1)。
代码实现
2.1创建一个emp表多添加几条数据
2.2把需要的架包添加在web下面创建一个lib的文件夹并且添加为库。还得准备工具类没有的找我
(没有的请发私信)
2.3添加emp的实体类
package com.cxyzxc.www.entity;public class Emp {private int eid;private String ename;private int age;private double salary;public Emp() {}public Emp(int eid, String ename, int age, double salary) {this.eid = eid;this.ename = ename;this.age = age;this.salary = salary;}public int getEid() {return eid;}public void setEid(int eid) {this.eid = eid;}public String getEname() {return ename;}public void setEname(String ename) {this.ename = ename;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public double getSalary() {return salary;}public void setSalary(double salary) {this.salary = salary;}@Overridepublic String toString() {return "Emp{" +"eid=" + eid +", ename='" + ename + '\'' +", age=" + age +", salary=" + salary +'}';}
}
2.4因为需要用到分页所以也要有个Page实体类进行运算
package com.cxyzxc.www.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;
//总页数用三目运算符,总行数模页大小余数为0就是总行数除以页大小,不是就是总行数除以页大小+1this.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 +'}';}
2.5Dao接口类
package com.cxyzxc.www.dao;import com.cxyzxc.www.entity.Emp;
import com.cxyzxc.www.entity.Page;import java.util.List;public interface EmpDao1 {
//查询所有数据public List<Emp> selectAll(Page page);
//查询行数public long selectCount();
}
2.6DaoImpl实现类
package com.cxyzxc.www.dao.Impl;import com.cxyzxc.www.dao.EmpDao1;
import com.cxyzxc.www.entity.Emp;
import com.cxyzxc.www.entity.Page;
import com.cxyzxc.www.utils.Dbutils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;import java.sql.SQLException;
import java.util.List;public class EmpDaoImpl01 implements EmpDao1 {private QueryRunner queryRunner = new QueryRunner();@Overridepublic List<Emp> selectAll(Page page) {List<Emp> emps= null;try {emps = queryRunner.query(Dbutils.getConnection(),"select * from emp limit ?,?",new BeanListHandler<Emp>(Emp.class),page.getStartRows(),page.getPageSize());} catch (SQLException e) {throw new RuntimeException(e);}return emps;}@Overridepublic long selectCount() {try {return queryRunner.query(Dbutils.getConnection(), "select count(*) from emp;",new ScalarHandler<>());} catch (SQLException e) {e.printStackTrace();}return 0;}
}
2.7controller
package com.cxyzxc.www.controller;import com.cxyzxc.www.dao.EmpDao1;
import com.cxyzxc.www.dao.Impl.EmpDaoImpl01;
import com.cxyzxc.www.entity.Emp;
import com.cxyzxc.www.entity.Page;import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.util.List;@WebServlet(name = "ServletAll", value = "/ServletAll")
public class ServletAll extends HttpServlet {@Overrideprotected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//获取页大小String pageIndex= request.getParameter("pageIndex");
//当页大小为空要赋值给一个1if (pageIndex == null) {pageIndex="1";request.setAttribute("pageIndex",pageIndex);}
//将页大小传进去Page page = new Page(Integer.valueOf(pageIndex));System.out.println(page);
//调用业务类查询行数EmpDao1 empDao1= new EmpDaoImpl01();
//总行数long count =empDao1.selectCount();page.setTotalCounts((int)count);
//查询所有数据,并将page和emp表的数据存储在作用于中并就行转发到jsp中List<Emp> emps=empDao1.selectAll(page);request.setAttribute("emps",emps);request.setAttribute("page",page);request.getRequestDispatcher("/showAllEmp.jsp").forward(request, response);}@Overrideprotected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}
}
2.8showAllEmp.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head><title>Title</title>
</head>
<body><h1>emp所有数据</h1>
<table border="1" cellspacing="0" width="800px"><tr><td>员工编码</td><th>员工姓名</th><th>员工年龄</th><th>员工工资</th><th colspan="2">操作</th></tr>
// items="${emps}是获得存储的数据<c:forEach var="emp" items="${emps}"><tr><td>${emp.eid}</td><td>${emp.ename}</td><td>${emp.age}</td><td>${emp.salary}</td><td colspan="2"><a>修改</a><a>删除</a></td></tr></c:forEach><tr><td colspan="6">
//页码等于1时是首页<a href="<c:url context='${pageContext.request.contextPath}' value="/ServletAll?pageIndex=1"/> ">首页</a>
<%-- 当前页码大于1--%><c:if test="${page.pageIndex>1}"><a href="<c:url context='${pageContext.request.contextPath}' value='/ServletAll?pageIndex=${page.pageIndex -1}'/> ">上一页</a></c:if>
//当前页等于1时就不用跳转给个a标签<c:if test="${pageIndex==1}"><a>上一页</a></c:if>
<%-- page.pageIndex 是指当前页 ,当前页小于总页数就下一页,页码就需要加1 --%><c:if test="${page.pageIndex< page.totalPages}"><a href="<c:url context='${pageContext.request.contextPath}' value="/ServletAll?pageIndex=${page.pageIndex +1}"/> ">下一页 </a></c:if>
//当前页等于总页数就只用a标签包着<c:if test="${page.pageIndex == page.totalPages}"><a>下一页</a></c:if>
//尾页就是跳转到当前页调到总页数<a href="<c:url context='${pageContext.request.contextPath}' value="/ServletAll?pageIndex=${page.totalPages}"/> ">尾页</a></td></tr>
</table>
</body>
</html>