分页查询_小记录（模糊查询点页数暂未完成，只能上下页）

axbhealj

已于 2023-04-14 11:31:05 修改

阅读量57

点赞数

分类专栏：各种经验分享文章标签：数据库 sql java

于 2023-04-14 11:29:20 首次发布

本文链接：https://blog.csdn.net/axbhealj/article/details/130149344

版权

各种经验分享专栏收录该内容

40 篇文章 1 订阅

订阅专栏

一、数据准备


DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sex` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `birthday` date NULL DEFAULT NULL,
  `phone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `address` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `hobby` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `pic` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES (9, '李逍垚', '男', '2022-01-29', '123', '郑州', 'coding', 'https://qf2201.oss-cn-beijing.aliyuncs.com/img/1650957654174.png');
INSERT INTO `t_student` VALUES (14, '刘洁', '女', '2022-03-14', '123', '郑州', 'coding', 'https://qf2201.oss-cn-beijing.aliyuncs.com/img/1651025986871.png');
INSERT INTO `t_student` VALUES (18, '张文桧', '男', '2022-01-13', '888', '郑州', '听音乐', 'https://qf2201.oss-cn-beijing.aliyuncs.com/img/1651114571498.png');
INSERT INTO `t_student` VALUES (19, '胡图图123', '男', '2022-03-14', '888', '郑州', '听音乐', 'https://qf2201.oss-cn-beijing.aliyuncs.com/img/1651114700812.png');
INSERT INTO `t_student` VALUES (25, '李孟阳', '女', '2022-01-14', '135', '郑州', 'playgame', '');
INSERT INTO `t_student` VALUES (32, '张飞', '男', '2022-03-15', '123', '郑州', 'eating', NULL);
INSERT INTO `t_student` VALUES (40, '海龙战神', '男', '2022-03-15', '111', '郑州', '貂蝉', NULL);
INSERT INTO `t_student` VALUES (45, '哦哦', '男', '2022-03-15', '123', '郑州', '郑州', NULL);
INSERT INTO `t_student` VALUES (55, '妲己222', '女', '2022-03-25', '123', '123', '123', NULL);
INSERT INTO `t_student` VALUES (66, '李垚', '男', '2022-04-24', '111', '222', '写代码', 'https://qf2201.oss-cn-beijing.aliyuncs.com/img/1650794347858.png');
INSERT INTO `t_student` VALUES (73, '骚琦', '男', '2022-07-20', '123', '郑州', '喊麦', 'https://java2207.oss-cn-hangzhou.aliyuncs.com/2207/saoqi.jpg');
INSERT INTO `t_student` VALUES (76, '晓雨3', '女', '2022-07-15', '123', '4567', 'play', 'https://java2207.oss-cn-hangzhou.aliyuncs.com/image/1658310400041.jpg');
INSERT INTO `t_student` VALUES (79, 'zs', '男', '2022-10-11', '123', '123', '123', '123');
INSERT INTO `t_student` VALUES (80, 'zszzzz', '男', '2022-10-11', '123', '123', '123', '123');
INSERT INTO `t_student` VALUES (81, '321', '保密', '2022-10-11', '2222', 'zz', '1232222', '123');
INSERT INTO `t_student` VALUES (82, 'afeng', '男', '2022-10-11', '123', '123', '111', 'https://java2212.oss-cn-hangzhou.aliyuncs.com/image/1665474350740.jpg');
INSERT INTO `t_student` VALUES (83, 'xiaomei', '女', '2022-10-12', '123', '123', '123', 'https://java2212.oss-cn-hangzhou.aliyuncs.com/image/1665474464939.jpg');
INSERT INTO `t_student` VALUES (84, '222', '保密', '2022-10-12', '222', '222', '222', '');
INSERT INTO `t_student` VALUES (85, '张三', '男', '1999-11-11', NULL, '222', 'coding', NULL);
INSERT INTO `t_student` VALUES (86, '李思思', '女', '1999-12-12', NULL, '222', 'coding', NULL);
INSERT INTO `t_student` VALUES (91, 'zs', '男', '0199-01-11', '11111', '1111', '111', NULL);
INSERT INTO `t_student` VALUES (92, 'ls', '女', '1999-01-10', '111', '郑州', 'coding', NULL);
INSERT INTO `t_student` VALUES (93, '啊啊啊啊', '男', '1999-01-12', '1111', '偃师', 'play', NULL);
INSERT INTO `t_student` VALUES (94, 'Lone Ranger', '男', '2111-01-10', '158922', '偃师', 'money', NULL);
INSERT INTO `t_student` VALUES (95, '111', '1', '1999-01-10', '111', '1111', '111', NULL);
INSERT INTO `t_student` VALUES (96, '11', '11', NULL, '', '', '', NULL);
INSERT INTO `t_student` VALUES (99, '张', '1', NULL, '1', '1', '1', NULL);
INSERT INTO `t_student` VALUES (100, '张', '2', NULL, '', '', '', NULL);

SET FOREIGN_KEY_CHECKS = 1;

二、前端

<div class="page-header">

    <h1>学员列表 <small>
        <a type="button" href="${pageContext.request.contextPath}/studentAdd.jsp" class="btn btn-info">学员添加</a>
    </small></h1>
    姓名：<input type="text" id="s_name" name="s_name" value="${s_name}">
    电话：<input type="text" id="s_phone" name="s_phone" value="${s_phone}">
    <small>
        <a type="button" onclick="findByCon()" class="btn btn-info">查询</a>
    </small>
</div>
<%-- 上面为模糊查询条件 --%>

<table class="table">
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>性别</th>
        <th>生日</th>
        <th>手机号</th>
        <th>地址</th>
        <th>爱好</th>
        <th>头像</th>
        <th>操作</th>
    </tr>
    <c:forEach items="${page.pageDate}" var="stu">
        <tr>
            <td>${stu.id}</td>
            <td>${stu.name}</td>
            <td>${stu.sex}</td>
            <td>
                <fmt:formatDate value="${stu.birthday}" pattern="yyyy-MM-dd"></fmt:formatDate>
                    <%--                    ${stu.birthday}--%>
            </td>
            <td>${stu.phone}</td>
            <td>${stu.address}</td>
            <td>${stu.hobby}</td>
            <td>
                <img src="${stu.pic}" width="110px" height="100px">
            </td>
            <td>
                <a class="btn btn-primary" href="${pageContext.request.contextPath}/studentUpdate?id=${stu.id}"
                   role="button">编辑</a>
                    <%--                <a class="btn btn-warning" href="${pageContext.request.contextPath}/studentDelete?id=${stu.id}"--%>
                    <%--                   role="button">删除</a>--%>
                <button class="btn btn-warning" onclick="deleteStudent(${stu.id})">删除</button>
            </td>
        </tr>
    </c:forEach>

    <%--    分页    --%>
    <div class="col-sm-offset-5">
        <nav aria-label="Page navigation">
            <ul class="pagination">
                <li class="disabled">
                    <a>
                        <span>当前第${page.pageNum}页</span>
                    </a>
                </li>
                <%-- 上一页 --%>
                <c:if test="${page.pageNum>1}">
                    <li>
                        <a href="${pageContext.request.contentType}/studentList?pageNum=${page.pageNum-1}"
                           aria-label="Previous">
                            <span>&laquo;</span>
                        </a>
                    </li>
                </c:if>

                <%--  页码 --%>
                <c:forEach var="num" begin="${page.startPageNum}" end="${page.endPageNum}">
                    <c:choose>
                        <c:when test="${num==page.pageNum}">
                            <li class="disabled"><a href="#">${num}</a></li>
                        </c:when>
                        <c:otherwise>
                            <li><a href="${pageContext.request.contextPath}/studentList?pageNum=${num}">${num}</a></li>
                        </c:otherwise>
                    </c:choose>
                </c:forEach>

                <%-- 下一页 --%>
                <c:if test="${page.pageNum<page.pageTotal}">
                    <li>
                        <a href="${pageContext.request.contextPath}/studentList?pageNum=${page.pageNum+1}"
                           aria-label="Previous">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                </c:if>
            </ul>
        </nav>
    </div>


</table>

三、后端

实体类：

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Page<T> {
    // 数据
    private List<T> pageDate;
    // 页数
    private Integer pageNum;
    // 总页数
    private Integer pageTotal;
    // 总条数
    private Integer totalRecord;
    private Integer startPageNum;
    private Integer endPageNum;
}

servlet：


        String num = request.getParameter("pageNum");
        String s_name = request.getParameter("s_name");
        String s_phone = request.getParameter("s_phone");

        Integer pageNum = 1;
        if (!StringUtils.isNullOrEmpty(num)) {
            pageNum = Integer.parseInt(num);
        }

        StudentService studentService = new StudentServiceImpl1();
        Page page = studentService.getPageData(pageNum,s_name,s_phone);


        if (page != null) {
            request.setAttribute("page", page);
            request.getRequestDispatcher("/studentList.jsp").forward(request, response);
        } else {
            request.setAttribute("error", "查询出现错误");
            request.getRequestDispatcher("/jsp/fault.jsp").forward(request, response);
        }

serviceImpl：

1、查询全部时：

 @Override
    public Page getPageData(Integer pageNum) {

        Page studentPage = new Page();

        studentPage.setPageNum(pageNum);
        Integer pageSize = 5;

        List<Student> pageData = studentDao.getPageData(pageNum, pageSize);
        studentPage.setPageDate(pageData);

        Integer totalRecord = studentDao.getTotalRecord();
        studentPage.setTotalRecord(totalRecord);

        Integer totalPageNum = totalRecord % pageSize == 0 ? (totalRecord / pageSize) : (totalRecord / pageSize) + 1;
        studentPage.setPageTotal(totalPageNum);

        Integer startPageNum = 1;
        Integer endPageNum = 5;
        if (pageNum - 2 > 1) {
            startPageNum = pageNum - 2;
            endPageNum = pageNum + 2;
        }
        if (pageNum + 2 > totalPageNum) {
            startPageNum=totalPageNum-4;
            endPageNum=totalPageNum;
        }
        if(totalPageNum<5){
            startPageNum=1;
            endPageNum=totalPageNum;
        }
        studentPage.setStartPageNum(startPageNum);
        studentPage.setEndPageNum(endPageNum);
        return studentPage;
    }

2、模糊查询时：


    @Override
    public Page getPageData(Integer pageNum, String s_name, String s_phone) {
        Page studentPage = new Page();

        studentPage.setPageNum(pageNum);
        Integer pageSize = 5;

        List<Student> pageData = studentDao.getPageData(pageNum, pageSize,s_name,s_phone);
        studentPage.setPageDate(pageData);

        Integer totalRecord = studentDao.getTotalRecord(s_name,s_phone);
        studentPage.setTotalRecord(totalRecord);

        Integer totalPageNum = totalRecord % pageSize == 0 ? (totalRecord / pageSize) : (totalRecord / pageSize) + 1;
        studentPage.setPageTotal(totalPageNum);

        Integer startPageNum = 1;
        Integer endPageNum = 5;
        if (pageNum - 2 > 1) {
            startPageNum = pageNum - 2;
            endPageNum = pageNum + 2;
        }
        if (pageNum + 2 > totalPageNum) {
            startPageNum=totalPageNum-4;
            endPageNum=totalPageNum;
        }
        if(totalPageNum<5){
            startPageNum=1;
            endPageNum=totalPageNum;
        }
        studentPage.setStartPageNum(startPageNum);
        studentPage.setEndPageNum(endPageNum);
        return studentPage;
    }

daoImpl：

1、查询全部时：


    @Override
    public List<Student> getPageData(Integer pageNum, Integer pageSize) {

        String sql = "select * from t_student limit ?,?";
        Integer start = (pageNum - 1) * pageSize;
        List<Student> students = DBUtil.selectAll(sql, Student.class, start, pageSize);
        return students;
    }

    @Override
    public Integer getTotalRecord() {

        List<Student> students = DBUtil.selectAll("select * from t_student", Student.class);
        Integer totalRecord = students.size();
        return totalRecord;
    }

2、模糊查询时


    @Override
    public List<Student> getPageData(Integer pageNum, Integer pageSize, String s_name, String s_phone) {
        StringBuffer sb = new StringBuffer("select * from t_student ");
        if (!StringUtils.isNullOrEmpty(s_name)) {
            sb.append(" and name like '%" + s_name + "%'");
        }
        if (!StringUtils.isNullOrEmpty(s_phone)) {
            sb.append(" and phone like '%" + s_phone + "%' ");
        }
        sb.append(" limit ?,?");
        String sql = sb.toString().replaceFirst("and", "where");

        Integer start = (pageNum - 1) * pageSize;
        List<Student> students = DBUtil.selectAll(sql, Student.class,start, pageSize);
        return students;
    }

    @Override
    public Integer getTotalRecord(String s_name, String s_phone) {
        StringBuffer sb = new StringBuffer("select * from t_student ");
        if (!StringUtils.isNullOrEmpty(s_name)) {
            sb.append(" and name like '%" + s_name + "%'");
        }
        if (!StringUtils.isNullOrEmpty(s_phone)) {
            sb.append(" and phone like '%" + s_phone + "%' ");
        }
        String sql = sb.toString().replaceFirst("and", "where");

        List<Student> students = DBUtil.selectAll(sql, Student.class);
        Integer totalRecord = students.size();
        return totalRecord;
    }