pagehelper+文件上传与下载+学习笔记连接数据库的查询分页（小九原创）spring+pagehelper+mybaties+thymeleaf+file+异步任务+遇到的limit错误

小九(努力学习ing)

已于 2024-04-07 13:53:53 修改

阅读量511

点赞数 15

文章标签：笔记 spring mysql html spring boot java 后端

于 2024-04-07 13:51:35 首次发布

本文链接：https://blog.csdn.net/2301_78303474/article/details/137460840

版权

分页笔记

PageHelper分页遇到的limit错误

原sql语句

  <select id="selectFile" >
        select * from  filetable；
    </select>

修改后的sql语句

  <select id="selectFile" >
        select * from  filetable
    </select>

sql语句后面不能加；（冒号）

一、导入依赖

      <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.6</version>
        </dependency>

二-配置分页属性（也可以不添加配置，使用默认配置）

在application.yml文件中添加

	pagehelper:
	  helperDialect: mysql
	  reasonable: true
	  supportMethodsArguments: true
	  params: count=countSql

在application.properties文件中添加

	#PageHelper分页插件配置
	pagehelper.helperDialect=mysql
	pagehelper.reasonable=true
	pagehelper.supportMethodsArguments=true
	pagehelper.params=count=countSql

三、前提是有连接数据库的图片查询

sql数据库

create database if not exists headTable;
use headTable;
create table if not exists head_file
(
    id       int primary key auto_increment ,
    filename varchar(100)    not null,
    filePath varchar(100)    not null   unique,
    fileType varchar(100)   not null
    );

前端页面代码如下

th:each="item:${allFileList}"遍历出查询的值

 th:src="@{/file/{image}(image=${item.filename})}"

file指的是

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>图书展示</title></head>
<link th:href="@{/login/css/table.css}" rel="stylesheet">
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<body>
<input type="button" value="返回" onclick="history.back(-1)"/>
<table>
    <tr>
        <th>id</th>
        <th>名字</th>
        <th>图片</th>
        <th>下载</th>
    </tr>
    <tr th:each="item:${allFileList}">
        <td th:text="${item.id}">chrome.exe</td>
        <td th:text="${item.filename}">chrome.exe</td>
        <td><img th:if="${item.fileType == '1'}" th:src="@{/file/{image}(image=${item.filename})}" width="150px"
                 height="150px">
            <span th:if="${item.fileType == '0'}">此文件无预览图！！！</span></td>
        <td><a th:href="@{/download(filename=${item.filename})}">下载文件</a></td>
    </tr>


</table>
</body>
</html>

上传页面<a>标签跳转查询页面、

<a th:href="@{/seclectImg}">所有文件图片上传列表</a></br>

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>动态添加文件上传列表</title>
    <link th:href="@{/login/css/bootstrap.min.css}" rel="stylesheet">
    <script th:src="@{/login/js/jquery.min.js}" src="../js/jquery-3.2.1.min.js"></script>
</head>
<body>
<div th:if="${uploadStatus}" style="color: red" th:text="${uploadStatus}">上传成功</div>
<a th:href="@{/queryPaging/1}">分页列表</a></br>
<a th:href="@{/seclectImg}">所有文件图片上传列表</a></br>
<input type="button" value="返回" onclick="history.back(-1)"/>
<form th:action="@{/uploadFile}" method="post" enctype="multipart/form-data">上传文件:&nbsp;&nbsp;
    <input type="button" value="添加文件" onclick="add()"/>
    <div id="file" style="margin-top: 10px;" th:value="文件上传区域"></div>
    <input id="submit" type="submit" value="上传" style="display: none;
    margin-top: 10px;"/>
</form>
</body>
<script type="text/javascript">
    function add(){
        var innerdiv = "<div>";
        innerdiv += "<input type='file' name='fileUpload' required='required'>" + "<input type='button' value='删除' onclick='remove(this)'>";
        innerdiv += "</div>";
        $("#file").append(innerdiv);
        $("#submit").css("display", "block");
    }
    function remove(obj)
    {
        $(obj).parent().remove();
        if ($("#file div").length == 0) {
            $("#submit").css("display", "none");
        }
    }
</script>
</html>

MyWebMVCConfig类

package com.example.uploaddemo.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.ViewControllerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class MyWebMVCConfig implements WebMvcConfigurer {
    @Value("${file.location}")
    String location;
    @Value("${file.path}")
    String path;
    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler(path).addResourceLocations("file:" + location);
    }

    //映射路径 提高用户体验
    // 无任何业务逻辑只是简单的业务逻辑可以这样子写，代码更简洁，管理集中
    //addViewControllers 适用于仅仅需要页面跳转而没有具体的业务逻辑的代码
    @Override
    // 重定向
    public void addViewControllers(ViewControllerRegistry registry) {
        // registry.addViewController("/login.html").setViewName("login");
        //参数传入就是controller中的URL，setViewName中传入的参数就是原return的页面。
        registry.addRedirectViewController("/*.html","/gotoHeader");
        // 直接配置请求路径（/gotoHeader）和页面之间的映射。
    }
}

FileMapper接口

package com.example.uploaddemo.mapper;
import com.example.uploaddemo.entity.FileTable;
import org.apache.ibatis.annotations.Mapper;


import java.util.List;

@Mapper
public interface FileMapper {
    void addFile(FileTable file); //添加 上传文件
    List<FileTable> selectFile();//查询 显示所有上传的文件列表
}

异步任务AsyncService类

package com.example.uploaddemo.service;

import com.example.uploaddemo.entity.FileTable;
import com.example.uploaddemo.mapper.FileMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class AsyncService {

    @Autowired
    FileMapper fileMapper;
    @Async
    public void addFileAsync(FileTable file)
    {
        fileMapper.addFile(file);
    }

    public List<FileTable> selectFile()
    {

        return fileMapper.selectFile();
    }

}

FiletableDao.xml mapper

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.uploaddemo.mapper.FileMapper">
    <!--添加-->
    <insert id="addFile" parameterType="FileTable">
        insert into filetable(id,filename,filePath,fileType)
        values (0,#{filename},#{filePath},#{fileType})
    </insert>

    <select id="selectFile" >
        select * from  filetable
    </select>
</mapper>

四、分页前端页面

去首页

<a th:if="${pageinfo.isFirstPage==false}" th:href="@{/queryPaging/1}">
                去首页&nbsp;&nbsp;
            </a>

去上一页

 <a th:if="${pageinfo.hasPreviousPage}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pageNum}-1)}">
                ⋘&nbsp;&nbsp;
            </a>

去下一页

   <a th:if="${pageinfo.hasNextPage}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pageNum}+1)}">
                ⋙&nbsp;&nbsp;
            </a>

第几页

 第<span th:text="${pageinfo.pageNum}"></span> 页&nbsp;&nbsp;

一共多少页

    共<span th:text="${pageinfo.pages}"></span> 页&nbsp;&nbsp;

去尾页

 <a th:if="${pageinfo.isLastPage==false}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pages})}">
                去尾页
            </a>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>分页版</title>
    <link th:href="@{/login/css/table.css}" rel="stylesheet">
<body>
<div style="margin-bottom: 10px">文件分页列表：</div>
<table>
    <tr>
        <th>id</th>
        <th>名字</th>
        <th>图片</th>
        <th>下载</th>
    </tr>
    <tr th:each="item:${pageinfo.list}">
        <td th:text="${item.id}">chrome.exe</td>
        <td th:text="${item.filename}">chrome.exe</td>
        <td><img th:if="${item.fileType == '1'}" th:src="@{/file/{image}(image=${item.filename})}" width="150px"
                 height="150px">
            <span th:if="${item.fileType == '0'}">此文件无预览图！！！</span></td>
        <td><a th:href="@{/download(filename=${item.filename})}">下载文件</a></td>
    </tr>
    <tr>
        <td>
            <a th:if="${pageinfo.isFirstPage==false}" th:href="@{/queryPaging/1}">
                去首页&nbsp;&nbsp;
            </a>
        </td>
        <td>
            <a th:if="${pageinfo.hasPreviousPage}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pageNum}-1)}">
                ⋘&nbsp;&nbsp;
            </a>
            第<span th:text="${pageinfo.pageNum}"></span> 页&nbsp;&nbsp;
            <a th:if="${pageinfo.hasNextPage}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pageNum}+1)}">
                ⋙&nbsp;&nbsp;
            </a>
        </td>
        <td>
            共<span th:text="${pageinfo.pages}"></span> 页&nbsp;&nbsp;
        </td>
        <td>
            <a th:if="${pageinfo.isLastPage==false}" th:href="@{/queryPaging/{pagenum}(pagenum=${pageinfo.pages})}">
                去尾页
            </a>
        </td>
    </tr>
</table>
</body>
</html>

五、后端逻辑

FileTableServicePaging业务逻辑组件类

package com.example.uploaddemo.service;

import com.example.uploaddemo.entity.FileTable;
import com.example.uploaddemo.mapper.FileMapper;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class FileTableServicePaging {
    @Autowired
    FileMapper fileMapper;
    int pagesize = 4;

    public PageInfo<FileTable> selectFile(int pagenum) {
        PageHelper.startPage(pagenum, pagesize);
        List<FileTable> list = fileMapper.selectFile();
        PageInfo<FileTable> pageInfo = new PageInfo<>(list);
        return pageInfo;
    }
}

控制层MyController类


    /**
     * 分页
     */
    @Autowired
    FileTableServicePaging fileTableServicePaging;
    @RequestMapping("/queryPaging/{pagenum}")
    public ModelAndView Paging(@PathVariable("pagenum") int pagenum) {
        PageInfo<FileTable> pageInfo = fileTableServicePaging.selectFile(pagenum);
        ModelAndView mav = new ModelAndView();
        mav.addObject("pageinfo", pageInfo);
        mav.setViewName("paging");
        return mav;
    }

六、测试

上传文件图片

小九(努力学习ing)

关注

15
点赞
踩
7

收藏

觉得还不错? 一键收藏
1
评论
pagehelper+文件上传与下载+学习笔记连接数据库的查询分页（小九原创）spring+pagehelper+mybaties+thymeleaf+file+异步任务+遇到的limit错误

pagehelper分页异步任务文件的上传与下载整合到一起的练习
复制链接

扫一扫