分页查询详解

分页查询

数据库结构

创建对应的实体类

public class Student {
    private Integer id;
    private String name;
    private Integer age;
    private String address;

    public Student() {
    }

    public Student(Integer id, String name, Integer age, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
    }


    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 Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }
}

分页查询最主要的5个参数：

• 当前页数：pageNum
• 每页的数据条数：pageSize
• 总数据条数：totalSize
• 总页数【可以求出来】：pageCount
• 每页展示的数据：data

public class PageBean<T> {
    //当前页数
    private int pageNum;
    //每页数据条数
    private int pageSize;
    //总数据
    private long totalSize;
    //总页数
    private int pageCount;
    //每页的数据
    private List<T> data;

    public PageBean() {
    }

    public PageBean(int pageNum, int pageSize, long totalSize, List<T> data) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
        this.totalSize = totalSize;
        this.data = data;

        pageCount = (int) (totalSize % pageSize == 0 ? totalSize / pageSize : totalSize / pageSize + 1);
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public long getTotalSize() {
        return totalSize;
    }

    public void setTotalSize(long totalSize) {
        this.totalSize = totalSize;
    }

    public int getPageCount() {
        return pageCount;
    }

    public void setPageCount(int pageCount) {
        this.pageCount = pageCount;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

}

展示数据的第一步：从数据库查询到对应页数的数据

用到的数据库连接池
如果不懂，请移步
数据库连接池
DBUtils工具类的使用

public class DataSourceUtils {

    private static DruidDataSource dataSource;

    static {
        try {
            Properties properties = new Properties();
            InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("database.properties");
            properties.load(is);

            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static DataSource getDataSource(){
        return dataSource;
    }
}

dao接口

public interface StudentDao {
    List<Student> findByPage(int pageNum, int pageSize);
    Long getCount();
}

dao实现类

public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> findByPage(int pageNum, int pageSize) {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select * from student order by id limit ? , ?", new BeanListHandler<Student>(Student.class), (pageNum-1)*pageSize, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询失败", e);
        }
    }

    @Override
    public Long getCount() {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            return qr.query("select count(*) from student", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("获取失败", e);
        }
    }
}

第二步：处理业务逻辑

接口

public interface StudentService {
    PageBean<Student> findPage(int pageNum, int pageSize);
}

实现类
为什么要返回pageBean，因为前端展示数据不只是需要数据，还有下面的页码，跳转等功能，需要用到pageBean中的那5个参数其中的一些，所以要返回pageBean

public class StudentServiceImpl implements StudentService {
    @Override
    public PageBean<Student> findPage(int pageNum, int pageSize) {

        StudentDao studentDao = new StudentDaoImpl();
        List<Student> data = studentDao.findByPage(pageNum, pageSize);
        Long totalSize = studentDao.getCount();

        PageBean<Student> pageBean = new PageBean<>(pageNum, pageSize, totalSize, data);

        return pageBean;
    }
}

第三步：查询数据，在前端展示

@WebServlet(name = "ShowStudentsServlet", value = "/showstudents")
public class ShowStudentsServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //前端获取数据
        String pageNum = request.getParameter("pageNum");
        String pageSize = request.getParameter("pageSize");
        String pageCount = request.getParameter("pageCount");

        //初始化数据
        int pn = 1;
        int ps = 6;
        //如果不为空
        if (!StringUtils.isEmpty(pageNum)){
            pn = Integer.parseInt(pageNum);
            int pc = Integer.parseInt(pageCount);
            if (pn < 1){
                pn = 1;
            }

            if (pn > pc){
                pn = pc;
            }
        }
        if (!StringUtils.isEmpty(pageSize)){
            ps = Integer.parseInt(pageSize);

            if (ps < 1){
                ps = 6;
            }
        }


        //查询值，返回页面
        StudentService studentService = new StudentServiceImpl();
        PageBean<Student> pageBean = studentService.findPage(pn, ps);

        request.setAttribute("pageBean", pageBean);
        request.getRequestDispatcher("/showstudents.jsp").forward(request, response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }
}

最后：页面展示
可能有很多代码冗余，见谅，也有一个第0页的bug，还没有解决，望大家多提提宝贵意见

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>分页展示</title>
</head>
<body>
    <h2>分页展示</h2>
    <table border="1">
        <tr>
            <td>编号</td>
            <td>姓名</td>
            <td>年龄</td>
            <td>地址</td>
        </tr>
        <c:forEach var="stu" items="${pageBean.data}">
            <tr>
                <td>${stu.id}</td>
                <td>${stu.name}</td>
                <td>${stu.age}</td>
                <td>${stu.address}</td>
            </tr>
        </c:forEach>
    </table>


    <div>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=1&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">首页</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum-1}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">上一页</a>

        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum-1}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">${pageBean.pageNum-1}</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">${pageBean.pageNum}</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum+1}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">${pageBean.pageNum+1}</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum+2}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">${pageBean.pageNum+2}</a>
        ....
        <a>${pageBean.pageNum}/${pageBean.pageCount}</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageNum+1}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">下一页</a>
        <a href="${pageContext.request.contextPath}/showstudents?pageNum=${pageBean.pageCount}&pageSize=${pageBean.pageSize}&pageCount=${pageBean.pageCount}">尾页</a>
    </div>

    <div>
        <form action="${pageContext.request.contextPath}/showstudents" method="post">
            <label for="getpage">请输入跳转的界面</label>
            <input id="getpage" name="pageNum" type="text">
            <input type="submit" value="提交">
        </form>
    </div>
</body>
</html>