【SSM项目】01_PageHelper插件后端实现分页查询操作

最新推荐文章于 2024-08-14 20:18:15 发布

lucky__cc

最新推荐文章于 2024-08-14 20:18:15 发布

阅读量754

点赞数

分类专栏： SSM 项目实战

本文链接：https://blog.csdn.net/qq1437722579/article/details/109230166

版权

项目实战同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

SSM

5 篇文章 0 订阅

订阅专栏

本篇文章主要实现分页和查询功能
效果展示：
在这里插入图片描述下拉框遍历:

查找效果

1.pom.xml配置

  <!--pageHelper-->
  <dependency>
     <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
         <version>5.1.2</version>
  </dependency>

2.mybatis-config.xml配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
            <property name="supportMtehodsArguments" value="true"/>
        </plugin>
    </plugins>

</configuration>

3.applicationContext.xml加载mybatis-config.xml

在这里插入图片描述

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
         http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context
         https://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:db.properties"/>

    <!--开启注解的扫描 希望处理service和dao，controller不需要spring框架去处理 -->
    <context:component-scan base-package="com.qf.dao">
        <!--配置哪些注解不扫描-->
        <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
    </context:component-scan>

    <!--Spring整合mybatis框架-->
    <!--配置连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="driverClassName" value="${jdbc.driver}"/>
    </bean>

    <!--配置SqlSessionFactory工厂-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 数据库连接池 -->
        <property name="dataSource" ref="dataSource"/>
        <!-- 引入mybatis-config.xml -->
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
    </bean>

    <!--配置Dao接口所在包-->
    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.qf.dao"/>
    </bean>

</beans>

4.dao层代码

    /**
     * 查询所有视频信息
     * @return list集合
     */
    List<Video> findAllVideo(QueryVo queryVo);

    /**
     * 获取信息总条数
     * @return
     */
    Integer getTotalCount();

4.1 mapper.xml(sql语句实现)

sql语句根据自己需要自己编写哦~，调用方法是通过id获取的的方法，方法名与id保持一致

    <!--联表查询-->
    <select id="findAllVideo" resultMap="VideoMap">
        SELECT video.id,title,detail,speaker.speaker_name,time,play_num,speaker_id,course_id
        FROM video
        INNER JOIN speaker
        ON video.speaker_id=speaker.id
        <trim prefix="WHERE" prefixOverrides="AND|OR">
            <if test="title != null and title != ''">
                and title like concat('%',#{title},'%')
            </if>
            <if test="speakerId != null and speakerId != ''">
                and speaker_id = #{speakerId}
            </if>
            <if test="courseId != null and courseId != ''">
                and course_id = #{courseId}
            </if>
        </trim>
    </select>
    <!--查询总条数-->
    <select id="getTotalCount" resultType="java.lang.Integer">
    SELECT count(*) from video
  </select>

5.service层代码

VideoService.java

    /**
     * 查询所有视频信息
     * @return list集合
     * @param queryVo
     */
    List<Video> findAllVideo(QueryVo queryVo);

    /**
     * 获取总信息条数
     * @return 总信息条数
     */
    Integer getTotalCount();

VideoServiceImpl.java

    @Autowired
    private VideoDao videoDao;

    @Override
    public List<Video> findAllVideo(QueryVo queryVo) {

        return videoDao.findAllVideo(queryVo);
    }

    @Override
    public Integer getTotalCount() {
        return videoDao.getTotalCount();
    }

6.编写controller层代码

VideoController


    @Autowired
    private VideoService videoService;

    @Autowired
    private SpeakerService speakerService;

    @Autowired
    private CourseService courseService;

    /**
     * 分页查找
     * @param pageNum 首页
     * @param model 用于存储数据
     * @param queryVo 查询
     * @return 展示界面
     */
    @RequestMapping("/findByPage")
    public String findByPage(@RequestParam(defaultValue = "1",required = false) Integer pageNum,
                             Model model,
                             QueryVo queryVo) {

        model.addAttribute("queryVo", queryVo);
        

        if (pageNum < 1) {
            pageNum = 1;
        } else if (pageNum > getTotalPage()) {
            pageNum = getTotalPage();
        }

        //课程名下拉菜单
        List<Course> allCourse = courseService.findAllCourse();
        model.addAttribute("allCourse",allCourse);

        //主讲人下拉菜单
        List<Speaker> allSpeaker = speakerService.findAllSpeaker();
        model.addAttribute("allSpeaker",allSpeaker);

        PageHelper.startPage(pageNum, 10);
        List<Video> videos = videoService.findAllVideo(queryVo);


        PageInfo<Video> videoPageInfo = new PageInfo<>(videos);
        model.addAttribute("videoPageInfo", videoPageInfo);


        return "/behind/videoList";
    }

    /**
     * 获取总页数
     * @return
     */
    public Integer getTotalPage() {
        Integer pageSize = 10;

        Integer totalCount = videoService.getTotalCount();

        if (totalCount % pageSize == 0) {
            return totalCount / pageSize;
        } else {
            return totalCount / pageSize + 1;
        }
    }

7.查询的参数代码QueryVo

7.1 pojo层

package com.qf.pojo;

import lombok.Data;

@Data
public class QueryVo {

    /**
     * 标题
     */
    private String title;
    /**
     * 主讲人
     */
    private Integer speakerId;
    /**
     * 课程表
     */
    private Integer courseId;
}

8.前端jsp起分页作用的代码

form2为你展示的内容的form表单的id

    <div class="container">
        <div class="navbar-right" style="padding-right: 17px">
            <tr>
                <a href="javascript:void(0)" onclick="queryVideo(1)">首页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pageNum-1})">上一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pageNum+1})">下一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pages})">尾页</a>&nbsp;&nbsp;&nbsp;&nbsp;
            </tr>
        </div>
    </div>
    
<script type="application/javascript" src="../../js/jquery-1.8.3.min.js"></script>
<script>

    function queryVideo(pageNum) {
        $("#pageNum").val(pageNum);
        $("#form1").submit();
    }

    // function delAll() {
    //     $("#form2").submit();
    // }
</script>

9.现奉上videoList.jsp页面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" isELIgnored="false" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="p" uri="http://yanzhenwei.com/common/" %>
<!-- 分页插件 -->
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">

    <!--表示使用IE最新的渲染模式进行解析-->
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <!--
            兼容一些移动设备，会根据屏幕的大小缩放
            width=device-width  表示宽度是设备的宽度（很多手机的宽度都是980px）
            initial-scale=1  初始化缩放级别   1-5
            minimum-scale=1  maximum-scale=5
            user-scalable=no  禁止缩放
        -->
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
    <title>视频列表管理</title>

    <!-- Bootstrap -->
    <link href="${pageContext.request.contextPath}/css/bootstrap.min.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->

    <!-- 如果IE版本小于9，加载以下js,解决低版本兼容问题 -->
    <!--[if lt IE 9]>
    <script src="https://cdn.bootcss.com/html5shiv/3.7.3/html5shiv.min.js"></script>
    <script src="https://cdn.bootcss.com/respond.js/1.4.2/respond.min.js"></script>
    <![endif]-->


    <!--
            引入网络的jquery  ,如果想换成自己的，导入即可
            网站优化：建议将你网站的css\js等代码，放置在互联网公共平台上维护，比如：七牛
        -->
    <script src="${pageContext.request.contextPath}/js/jquery-1.12.4.min.js"></script>

    <script src="${pageContext.request.contextPath}/js/bootstrap.min.js"></script>
    <script src="${pageContext.request.contextPath}/js/confirm.js"></script>
    <style type="text/css">
        th {
            text-align: center;
        }
    </style>
    <script type="text/javascript">
        function showAddPage() {
            location.href = "${pageContext.request.contextPath}/video/toAddOrUpdateVideo";
        }

        $(function () {
            $("#btn").click(function () {
                if (deleteNum > 0) {

                    Confirm.show('溫馨提示', '您確定要刪除这' + deleteNum + '条记录嗎？', {
                        'Delete': {
                            'primary': true,
                            'callback': function () {
                                //不是ajax，模拟提交
                                $("#form2").submit();
                                //如果是一个正常表单的提交，按钮必须是type=submit,并且必须在form表单里面
                            }
                        }
                    });

                } else {
                    alert("您暂未选择任何数据，请选择您要删除的数据！");
                }

            });

        });

        function delVideoById(obj, id, name) {

            Confirm.show('溫馨提示', '您確定要刪除' + name + '嗎？', {
                'Delete': {
                    'primary': true,
                    'callback': function () {
                        //此处需要调用ajax
                        var params = {
                            "id": id
                        };
                        $.post("${pageContext.request.contextPath}/video/videoDel", params, function (data) {
                            if (data == 'success') {
                                Confirm.show('处理结果', '恭喜您删除成功');
                                //请用js删除掉那条记录
                                $(obj).parent().parent().remove();
                            } else {
                                Confirm.show('处理结果', '操作失败');
                            }
                        });

                    }
                }
            });

            //阻止事件默认行为   a  href  onclick
            //先执行onclick  后跳转
            return false;
        }

        var deleteNum = 0;

        function selectAll(obj) {
            //dom  jquery
            var value = obj.checked;
            //alert(value);
            var arr = document.getElementsByName("ids");
            for (var i = 0; i < arr.length; i++) {
                arr[i].checked = value;
            }
            if (value) {
                deleteNum = arr.length;
            } else {
                deleteNum = 0;
            }

            $("#delNum").text(deleteNum);

        }

        function selectOne(obj) {
            if (obj.checked) {
                deleteNum += 1;
            } else {
                deleteNum -= 1;
            }

            if (deleteNum == 0) {
                document.getElementById("checkAllId").checked = false;
            }

            var arr = document.getElementsByName("ids");
            if (deleteNum == arr.length) {
                document.getElementById("checkAllId").checked = true;
            }

            $("#delNum").text(deleteNum);
        }

        //解决选择下拉框内容到输入框内容的问题
        function showName(obj, id, type) {
            var name = $(obj).text();

            if (type == 1) {
                $("#speakerName").html(name + "<span class='caret'></span>");
                $("#speakerId").val(id);
            } else {
                $("#courseName").html(name + "<span class='caret'></span>");
                $("#courseId").val(id);
            }

        }
    </script>
</head>
<body>


<nav class="navbar-inverse">
    <div class="container">
        <!-- Brand and toggle get grouped for better mobile display -->
        <div class="navbar-header">

            <a class="navbar-brand" href="${pageContext.request.contextPath}/video/findByPage">视频管理系统</a>
        </div>

        <div class="collapse navbar-collapse"
             id="bs-example-navbar-collapse-9">
            <ul class="nav navbar-nav">
                <li class="active"><a href="${pageContext.request.contextPath}/video/findByPage">视频管理</a></li>
                <li><a href="${pageContext.request.contextPath}/speaker/findByPage">主讲人管理</a></li>
                <li><a href="${pageContext.request.contextPath}/showCourseList">课程管理</a></li>


            </ul>
            <p class="navbar-text navbar-right">
                <span>${admin.username}</span> <i class="glyphicon glyphicon-log-in"
                                                         aria-hidden="true"></i>&nbsp;&nbsp;<a
                    href="${pageContext.request.contextPath}/admin/exit"
                    class="navbar-link">退出</a>
            </p>
        </div>
        <!-- /.navbar-collapse -->


    </div>
    <!-- /.container-fluid -->
</nav>

<div class="jumbotron" style="padding-top: 15px;padding-bottom: 15px;">
    <div class="container">
        <h2>视频管理</h2>
    </div>
</div>

<div class="container">

    <div class="row">
        <div class="col-md-2">
            <button onclick="showAddPage()" type="button"
                    class="btn btn-info dropdown-toggle" data-toggle="dropdown"
                    aria-haspopup="true" aria-expanded="false">添加
            </button>

            <button id="btn" class="btn btn-primary" type="button">
                批量删除 <span class="badge" id="delNum">0</span>
            </button>
        </div>
        <div class="col-md-4"></div>
        <div class="col-md-6">
            <!-- 查询相关组件 -->
            <form id="form1" class="navbar-form navbar-right"  action="${pageContext.request.contextPath}/video/findByPage" method="post">
                <!-- 设置隐藏域 传递当前页码 -->
                <input type="hidden" name="pageNum" id="pageNum">
                <input type="text" name="title" class="form-control" placeholder="标题" value="${queryVo.title}">
                <div class="btn-group">
                    <button type="button" id="speakerName"
                            class="btn btn-primary dropdown-toggle" data-toggle="dropdown"
                            aria-haspopup="true" aria-expanded="false">
                        <c:forEach items="${allSpeaker}" var="speaker">
                            <c:if test="${speaker.id == queryVo.speakerId}">
                                ${speaker.speakerName}
                            </c:if>
                        </c:forEach>
                        <c:if test="${empty queryVo.speakerId}">
                            --请选择老师--
                        </c:if>
                        <span class="caret"></span>
                    </button>
                    <ul class="dropdown-menu">
                        <li value=''><a href="#" onclick="showName(this,'',1)">--请选择老师--</a>
                        </li>
                        <c:forEach items="${allSpeaker}" var="speaker">
                            <li value='${speaker.id}'><a href="#"
                                                         onclick="showName(this,'${speaker.id}',1)">${speaker.speakerName}</a>
                            </li>
                        </c:forEach>
                    </ul>
                    <input type="hidden" name="speakerId" id="speakerId" value="${queryVo.speakerId}"/>
                </div>
                <div class="btn-group">
                    <button type="button" id="courseName"
                            class="btn btn-primary dropdown-toggle" data-toggle="dropdown"
                            aria-haspopup="true" aria-expanded="false">
                        <c:forEach items="${allCourse}" var="course">
                            <c:if test="${course.id == queryVo.courseId}">
                                ${course.courseTitle}
                            </c:if>
                        </c:forEach>
                        <c:if test="${empty queryVo.courseId}">
                            --请选择课程--
                        </c:if>
                        <span class="caret"></span>
                    </button>
                    <ul class="dropdown-menu">
                        <li value=""><a href="#" onclick="showName(this,'',2)">--请选择课程--</a>
                        </li>
                        <c:forEach items="${allCourse}" var="course">
                            <li value="${course.id}"><a href="#"
                                                        onclick="showName(this,${course.id},2)">${course.courseTitle}</a>
                            </li>
                        </c:forEach>
                    </ul>
                    <input type="hidden" name="courseId" id="courseId" value="${queryVo.courseId}"/>
                </div>
                <button type="submit" class="btn btn-info dropdown-toggle">查询</button>
            </form>

        </div>

    </div>
</div>

<div class="container" style="margin-top: 20px;">
    <!--
        http://localhost/video/video/list
        相对路径就是将最后一个/后面的东西替换掉
        http://localhost/video/video/delBatchVideos
    -->

    <form id="form2" action="/video/delAll" method="post">

        <table class="table table-bordered table-hover"
               style="text-align: center;table-layout:fixed">
            <thead>
            <tr class="active">
                <th style="width:3%"><input type="checkbox" onclick="selectAll(this)"
                                            id="checkAllId"/></th>
                <th style="width:5%">序号</th>
                <th style="width:15%">名称</th>
                <th style="width:42%;">介绍</th>
                <th>讲师</th>
                <th>时长</th>
                <th style="width:7%">播放次数</th>
                <th>编辑</th>
                <th>删除</th>
            </tr>
            </thead>
            <tbody>
            <c:forEach items="${videoPageInfo.list}" var="video" varStatus="status">
                <tr>
                    <td><input type="checkbox" name="ids" value="${video.id}"
                               onclick="selectOne(this)"/></td>
                    <td>${status.count}</td>
                    <td>${video.title}</td>
                    <td style="overflow:hidden;white-space:nowrap;text-overflow:ellipsis;">${video.detail}</td>
                    <td>${video.speakerName}</td>
                    <td>${video.time}</td>
                    <td>${video.playNum}</td>
                    <td><a href="${pageContext.request.contextPath}/video/queryById?id=${video.id}"><span
                            class="glyphicon glyphicon glyphicon-edit" aria-hidden="true"></span></a></td>
                    <!-- js中如果使用el表达式，请用单引号包括，避免造成一些语法问题 -->
                    <td><a
                            onclick="return delVideoById(this,'${video.id}','${video.title}')"><span
                            class="glyphicon glyphicon-trash" aria-hidden="true"></span></a></td>
                </tr>

            </c:forEach>
            </tbody>
        </table>
    </form>

    <div class="container">
        <div class="navbar-right" style="padding-right: 17px">
            <tr>
                <a href="javascript:void(0)" onclick="queryVideo(1)">首页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pageNum-1})">上一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pageNum+1})">下一页</a>&nbsp;&nbsp;&nbsp;&nbsp;
                <a href="javascript:void(0)" onclick="queryVideo(${videoPageInfo.pages})">尾页</a>&nbsp;&nbsp;&nbsp;&nbsp;
            </tr>
        </div>
    </div>
    </center>
</div>
</body>
<script type="application/javascript" src="../../js/jquery-1.8.3.min.js"></script>
<script>

    function queryVideo(pageNum) {
        $("#pageNum").val(pageNum);
        $("#form1").submit();
    }

    // function delAll() {
    //     $("#form2").submit();
    // }
</script>
</html>