Javaweb学习笔记之DbUtils(二)：分页

最新推荐文章于 2024-04-29 02:26:15 发布

秋忆夏伤

最新推荐文章于 2024-04-29 02:26:15 发布

阅读量289

点赞数

分类专栏： javaweb学习笔记

本文链接：https://blog.csdn.net/qq_29331365/article/details/99407031

版权

javaweb学习笔记专栏收录该内容

56 篇文章 2 订阅

订阅专栏

项目结构：

其中 PageServlet.java:

package servlet;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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.List;

/**
 * 分页 sql 语句：使用 limit 关键字
 *  select * from book limit 0,3; // 参数1：从第一条数据开始查(索引从 0 开始)；   参数2：总共查询几条数据；
 *
 * 分页查询需要记住 4个变量：
 *  int currentPage = 3;                            // 当前页
 *  int pageSize = 4;                               // 每页显示的条数
 *  int count = select count(*) from book;          // 总共有多少条数据
 *  int totalPage = Math.ceil(count*1.0/pageSize);  // 总共有多少页（向上取整）
 *
 * 最终分页语句：
 *  select * from book limit (currentPage-1)*pageSize, pageSize;
 */
@WebServlet(name = "PageServlet", value="/servlet/pageServlet")
public class PageServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            int pageSize = 4;       // 每页显示的记录数
            int currentPage = 1;    // 当前页
            int count = 0;          // 总记录数
            int totalPage;          // 总页数

            // 从 上一页 或 下一页 得到的 当前页，第一次访问时，currPage 为 null；
            String currPage = request.getParameter("currentPage");
            if (currPage != null){
                currentPage = Integer.parseInt(currPage);
            }

            // 获取总记录数
            QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
            long ccount = queryRunner.query("select count(*) from book", new ScalarHandler<>());
            count = (int) ccount;

            // 获取总页数
            totalPage = (int) Math.ceil(count * 1.0 / pageSize);

            // 获取分页数据
            List<Book> books = queryRunner.query("select * from book limit ?,?", new BeanListHandler<Book>(Book.class), (currentPage - 1) * pageSize, pageSize);

            // 将分页数据封装到 javabean
            PageBean pageBean = new PageBean();
            pageBean.setBooks(books);               // 分页数据
            pageBean.setCount(count);               // 总记录数
            pageBean.setCurrentPage(currentPage);   // 当前页
            pageBean.setPageSize(pageSize);         // 每页显示的记录数
            pageBean.setTotalPage(totalPage);       // 总页数

            // 设置 pageBean 到 request 域中，然后跳转到页面
            request.setAttribute("pageBean", pageBean);
            request.getRequestDispatcher("/product_list.jsp").forward(request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

其中 product_list.jsp:

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <title>bookStore列表</title>
    <%--导入css --%>
    <link rel="stylesheet" href="${pageContext.request.contextPath}/css/main.css" type="text/css"/>
</head>
<body class="main">

<jsp:include page="head.jsp"/>
<jsp:include page="menu_search.jsp"/>

<div id="divpagecontent">
    <table width="100%" border="0" cellspacing="0">
        <tr>
            <td>
                <div style="text-align:right; margin:5px 10px 5px 0">
                    <a href="${pageContext.request.contextPath}/index.jsp">首页</a>
                    &nbsp;&nbsp;&nbsp;&nbsp;&gt;&nbsp;&nbsp;&nbsp;&nbsp;计算机
                    &nbsp;&nbsp;&nbsp;&nbsp;&gt;&nbsp;&nbsp;&nbsp;&nbsp;图书列表
                </div>
                <table cellspacing="0" class="listcontent">
                    <tr>
                        <td>
                            <h1>商品目录</h1>
                            <hr/>
                            <h1>计算机</h1>&nbsp;&nbsp;&nbsp;&nbsp;共100种商品
                            <hr/>
                            <div style="margin-top:20px; margin-bottom:5px">
                                <img src="${pageContext.request.contextPath}/images/productlist.gif" width="100%" height="38"/>
                            </div>

                            <table cellspacing="0" class="booklist">
                                <tr>
                                    <c:forEach var="book" items="${pageBean.books}">
                                    <td>
                                        <div class="divbookpic">
                                            <p>
                                                <a href="#"><img src="" width="115" height="129" border="0"/></a>
                                            </p>
                                        </div>
                                        <div class="divlisttitle">
                                            <a href="${pageContext.request.contextPath}/servlet/findBookInfoServlet?id=${book.id}">书名:${book.name}<br/>售价:${book.price}</a>
                                        </div>
                                    </td>
                                    </c:forEach>
                                </tr>
                            </table>

                            <div class="pagination">
                                <ul>
                                    <li class="disablepage"><a href="${pageContext.request.contextPath}/servlet/pageServlet?currentPage=${pageBean.currentPage==1 ? 1 : pageBean.currentPage-1}">&lt;&lt;上一页</a></li>
                                    <li> 第${pageBean.currentPage}页 / 共${pageBean.totalPage}页 </li>
                                    <li class="nextPage"><a href="${pageContext.request.contextPath}/servlet/pageServlet?currentPage=${pageBean.currentPage==pageBean.totalPage ? pageBean.totalPage : pageBean.currentPage+1}">&lt;&lt;下一页</a></li>
                                </ul>
                            </div>
                        </td>
                    </tr>
                </table>
            </td>
        </tr>
    </table>
</div>
<jsp:include page="foot.jsp"/>
</body>
</html>

其中 PageBean.java:

package servlet;

import java.util.List;

/**
 * 分页 javabean
 */
public class PageBean {
    private int currentPage;    // 当前页
    private int pageSize;       // 每页显示的记录数
    private int count;          // 总记录数
    private int totalPage;      // 总页数
    private List<Book> books;   // 分页数据

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public List<Book> getBooks() {
        return books;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }

    @Override
    public String toString() {
        return "PageBean{" +
                "currentPage=" + currentPage +
                ", pageSize=" + pageSize +
                ", count=" + count +
                ", totalPage=" + totalPage +
                ", books=" + books +
                '}';
    }
}

其中 Book.java:

package servlet;

/**
 * 对应 Book 数据表
 */
public class Book {
    private String id;
    private String name;
    private double price;
    private int pnum;
    private String category;
    private String description;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public int getPnum() {
        return pnum;
    }

    public void setPnum(int pnum) {
        this.pnum = pnum;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", pnum=" + pnum +
                ", category='" + category + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

其中 C3P0Utils.java:

package servlet;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0Utils {
    /**
     * 创建 C3P0 连接池对象（默认加载 src 下的 c3p0-config.xml 配置文件）
     */
    private static DataSource dataSource = new ComboPooledDataSource();

    /**
     * 传出 DataSource 对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

    /**
     * 从连接池中获取一个 数据库连接对象
     */
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }

    /**
     * 释放资源
     */
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

其中 c3p0-config.xml:

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day17</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="initialPoolSize">3</property>
        <property name="maxPoolSize">10</property>
        <property name="maxIdleTime">3000</property>
    </default-config>
</c3p0-config>

其中数据库数据：