木子空间

# 数据分页实现-案例

# 效果

# 数据表

CREATE TABLE `computer` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `stock` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

# model层

public class Computer {
    private int id;
    private String name;
    private Double price;
    private int stock;
}

# dao层

package example.dao;

import example.model.Computer;
import example.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class ComputerDao {
    private final QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    public List<Computer> getComputersByPage(int page, int pageSize) throws SQLException {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT * FROM computer LIMIT ? OFFSET ?";
        return queryRunner.query(sql, new BeanListHandler<>(Computer.class), pageSize, offset);
    }

    public int getComputerCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM computer";
        return queryRunner.query(sql, rs -> {
            rs.next();
            return rs.getInt(1);
        });
    }
}

# service层

public interface IComputerService {
    
    Map<String, Object> listComputersWithPagination(int page, int pageSize) throws SQLException;
}



public class ComputerServiceImpl implements IComputerService {

    ComputerDao computerDao=new ComputerDao();

    @Override
    public Map<String, Object> listComputersWithPagination(int page, int pageSize) throws SQLException {
        List<Computer> computers = computerDao.getComputersByPage(page, pageSize);
        int totalCount = computerDao.getComputerCount();
        int totalPages = (int) Math.ceil((double) totalCount / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("computers", computers);
        result.put("totalPages", totalPages);
        result.put("currentPage", page);
        return result;
    }
}

# controller层

@WebServlet(urlPatterns = {"/computer"})
public class ComputerServlet extends HttpServlet {

    IComputerService computerService = new ComputerServiceImpl();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        switch (action) {
            case "list":
                try {
                    listServlet(req, resp);
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
                break;
        }
    }
    private void listServlet(HttpServletRequest req, HttpServletResponse resp) throws SQLException, ServletException, IOException {
        int page = 1;
        int pageSize = 5; // 每页显示的条数

        // 获取页码参数
        String pageParam = req.getParameter("page");
        if (pageParam != null) {
            page = Integer.parseInt(pageParam);
        }

        // 获取分页数据
        Map<String, Object> data = computerService.listComputersWithPagination(page, pageSize);

        req.setAttribute("computers", data.get("computers"));
        req.setAttribute("totalPages", data.get("totalPages"));
        req.setAttribute("currentPage", data.get("currentPage"));
        
        req.getRequestDispatcher("computerList.jsp").forward(req, resp);
    }
}

# 页面

<%@ page contentType="text/html; charset=UTF-8" language="java" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
        <!DOCTYPE html>
        <html lang="en">

        <head>
            <script src="https://www.liyansheng.top/cdn/watermark.js"></script>
            <meta charset="UTF-8">
            <meta name="viewport" content="width=device-width, initial-scale=1.0">
            <title>电脑产品列表</title>
        </head>

        <body>
            <h1>电脑产品列表</h1>
            <table border="1">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>名称</th>
                        <th>价格</th>
                        <th>库存</th>
                        <th>操作</th>
                    </tr>
                </thead>
                <tbody>
                    <c:forEach var="computer" items="${computers}">
                        <tr>
                            <td>${computer.id}</td>
                            <td>${computer.name}</td>
                            <td>${computer.price}</td>
                            <td>${computer.stock}</td>
                            <td>
                                <c:if test="${sessionScope.user.role == 'admin'}">
                                    <a href="/computer?action=edit&id=${computer.id}">编辑</a> |
                                    <a href="/computer?action=delete&id=${computer.id}">删除</a>
                                </c:if>


                                <c:if test="${sessionScope.user.role == 'user'}">
                                    <a href="/cart?action=add&id=${computer.id}">加购</a>
                                </c:if>
                            </td>
                        </tr>
                    </c:forEach>
                </tbody>
            </table>
            <div>
                <!-- 首页链接 -->
                <a href="/computer?action=list&page=1">首页</a>

                <!-- 上一页链接 -->
                <c:if test="${currentPage > 1}">
                    <a href="/computer?action=list&page=${currentPage - 1}">上一页</a>
                </c:if>

                <!-- 分页页码 -->
                <c:forEach var="i" begin="1" end="${totalPages}">
                    <a href="/computer?action=list&page=${i}"
                        style="margin: 0 5px; ${i == currentPage ? 'font-weight:bold;' : ''}">
                        ${i}
                    </a>
                </c:forEach>

                <!-- 下一页链接 -->
                <c:if test="${currentPage < totalPages}">
                    <a href="/computer?action=list&page=${currentPage + 1}">下一页</a>
                </c:if>

                <!-- 尾页链接 -->
                <a href="/computer?action=list&page=${totalPages}">尾页</a>
            </div>
            <hr>
            <p style="color: red;">${requestScope.msg}</p>

            <br />
            <c:if test="${sessionScope.user.role == 'user'}">
                <a href="/cart?action=list">我的购物车</a>
            </c:if>
            <c:if test="${sessionScope.user.role == 'admin'}">
                <a href="/computer?action=add">发布电脑产品</a>
            </c:if>
            <br>
            <a href="/">主页</a>
        </body>

        </html>

← 密码加密 →