Git仓库
https://gitee.com/Lin_DH/system
介绍
从 MySQL 中，下载保存的 blob 格式的文件。
代码实现
第一步：配置文件
application.yml
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/system?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: root
mybatis-plus:
type-aliases-package: com.lm.system.common
mapper-locations: classpath:com.lm.system/mapper/*Mapper.xml
check-config-location: true
configuration:
#日志实现，不配置不会输出SQL日志
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
第二步：编写实体类
SysFile.java
package com.lm.system.common;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
/**
* @author DUHAOLIN
* @date 2024/10/17
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SysFile {
@TableId(value = "id", type = IdType.INPUT)
private Integer id;
private String name;
private String format;
private byte[] data;
private long size;
private Date createTime;
}
第三步：编写dao层接口
SysFileMapper.java
package com.lm.system.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.lm.system.common.SysFile;
import java.util.List;
/**
* @author DUHAOLIN
* @date 2024/10/17
*/
//@DS("system")
public interface SysFileMapper {
int insertFile(SysFile file);
List<SysFile> queryFiles();
SysFile queryFileById(Integer id);
}
第四步：编写dao层实现SQL
SysFileMapper.xml
<?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.lm.system.mapper.SysFileMapper">
<resultMap id="files" type="com.lm.system.common.SysFile">
<id property="id" column="id" jdbcType="INTEGER" />
<result property="name" column="name" jdbcType="VARCHAR" />
<result property="format" column="format" jdbcType="VARCHAR" />
<result property="data" column="data" jdbcType="BLOB" />
<result property="size" column="size" jdbcType="DOUBLE" />
<result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
</resultMap>
<insert id="insertFile" parameterType="com.lm.system.common.SysFile">
INSERT INTO t_file (NAME, FORMAT, DATA, SIZE)
VALUES (#{name}, #{format}, #{data}, #{size})
</insert>
<select id="queryFiles" resultMap="files">
SELECT ID, NAME, FORMAT, `SIZE`, CREATE_TIME
FROM t_file
</select>
<!-- SELECT ID, NAME, FORMAT,-->
<!-- (CASE WHEN `SIZE` <![CDATA[<]]> 1024 THEN CONCAT(`SIZE`, ' b')-->
<!-- WHEN `SIZE` <![CDATA[>=]]> 1024 AND `SIZE` <![CDATA[<]]> 1024000 THEN CONCAT(ROUND(`SIZE` / 1024, 2), ' KB')-->
<!-- WHEN `SIZE` <![CDATA[>=]]> 1024000 AND `SIZE` <![CDATA[<]]> 1024000000 THEN CONCAT(ROUND(`SIZE` / 1024000, 2), ' MB')-->
<!-- END) `SIZE`,-->
<!-- DATE_FORMAT(CREATE_TIME, '%Y-%m-%d %h:%i:%s') CREATE_TIME-->
<!-- FROM t_file-->
<select id="queryFileById" resultType="com.lm.system.common.SysFile">
SELECT ID, NAME, FORMAT, DATA, `SIZE`, CREATE_TIME
FROM t_file
WHERE ID = #{id}
</select>
</mapper>
第五步：编写下载页面
download.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head lang="en">
<meta charset="UTF-8" />
<title>文件下载页面</title>
<script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script>
</head>
<body>
<h1>文件下载页面</h1>
<table border="1">
<!-- 表头 -->
<thead>
<tr>
<th>#</th>
<th>ID</th>
<th>文件名</th>
<th>格式</th>
<th>文件大小</th>
<th>创建时间</th>
<th>操作</th>
</tr>
</thead>
<!-- 表体 -->
<tbody>
<tr th:each="file,stats:${files}">
<td th:text="${stats.count}"></td>
<td th:text="${file.id}"></td>
<td th:text="${file.name}"></td>
<td th:text="${file.format}"></td>
<td th:if="${file.size} < 1024" th:text="${#numbers.formatDecimal(file.size, 0, 0)} + ' b'"></td>
<td th:if="${file.size} >= 1024 and ${file.size} < 1024000" th:text="${#numbers.formatDecimal(file.size/1024, 0, 0)} + ' KB'"></td>
<td th:if="${file.size} >= 1024000 and ${file.size} < 1024000000" th:text="${#numbers.formatDecimal(file.size/1024000, 0, 0)} + ' MB'"></td>
<td th:text="${#dates.format(file.createTime, 'yyyy-MM-dd HH:mm:ss')}"></td>
<td>
<button th:onclick="'downloadFile(\'' + ${file.id} + '\');'">下载</button>
</td>
</tr>
</tbody>
</table>
<script th:inline="javascript">
let files = [[${files}]];
function downloadFile(id) {
$.ajax({
url: '/downloadFile/' + id,
type: 'GET',
responseType: 'blob',
success: function (res, status, xhr) {
let link = document.createElement("a");
link.href = this.url;
let filename = xhr.getResponseHeader("Content-Disposition").split("attachment; filename=")[1];
link.setAttribute("download", filename);
link.click();
window.URL.revokeObjectURL(link.href); //释放内存
},
error: function (xhr, status, error) {
console.log("error", error);
}
});
}
</script>
</body>
</html>
第七步：编写文件 Controller 类
FileController.java
package com.lm.system.controller;
import com.lm.system.common.SysFile;
import com.lm.system.exception.FileException;
import com.lm.system.mapper.SysFileMapper;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.util.Arrays;
import java.util.Base64;
import java.util.List;
/**
* @author DUHAOLIN
* @date 2024/10/15
*/
@Controller
public class FileController {
@Resource
private SysFileMapper fileMapper;
private final static String FILE_FORMAT_TXT = "txt";
private final static String FILE_FORMAT_JPEG = "jpg";
private final static String FILE_FORMAT_PNG = "png";
@Value("${file.upload.path}")
private String path;
@GetMapping("uploadPage")
public String uploadPage() {
return "upload";
}
@PostMapping("upload")
@ResponseBody
public String upload(@RequestParam("file") MultipartFile file) throws IOException {
//校验文件
try {
String[] fileFormats = { FILE_FORMAT_TXT };
checkFile(file, fileFormats);
} catch (FileException e) {
e.printStackTrace();
return e.getMessage();
}
String filename = path + file.getOriginalFilename().replace(FILE_FORMAT_TXT, "_" + System.currentTimeMillis() + FILE_FORMAT_TXT);
java.io.File newFile = new java.io.File(filename);
Files.copy(file.getInputStream(), newFile.toPath());
return "新文件已生成，" + newFile.getAbsolutePath();
}
private void checkFile(MultipartFile file, String[] fileFormats) {
//校验文件大小
checkSize(file.getSize());
//校验文件名
checkFilename(file.getOriginalFilename());
//校验文件格式
checkFileFormat(file.getOriginalFilename(), fileFormats);
}
private void checkSize(long size) {
if (size > 10485760L) //10MB
throw new FileException("文件大于10MB");
}
private void checkFilename(String filename) {
if (!StringUtils.hasText(filename))
throw new FileException("文件名有误");
}
private void checkFileFormat(String filename, String[] fileFormats) {
int i = filename.lastIndexOf(".");
String suffix = filename.substring(i + 1); //文件后缀
long c = Arrays.stream(fileFormats).filter(s -> s.equals(suffix)).count(); //判断是否存在该文件后缀
if (c < 1) throw new FileException("文件格式有误，该文件类型为：" + suffix);
}
@GetMapping("multiFileUploadPage")
public String multiFileUploadPage() {
return "multiFileUpload";
}
@PostMapping("multiFileUpload")
@ResponseBody
public String multiFileUpload(@RequestParam("files") MultipartFile[] files) throws IOException {
StringBuilder sb = new StringBuilder();
for (MultipartFile file : files) {
//校验文件
boolean b = true;
try {
String[] fileFormats = new String[] { FILE_FORMAT_TXT };
checkFile(file, fileFormats);
} catch (FileException e) {
e.printStackTrace();
sb.append(file.getOriginalFilename()).append(e.getMessage()).append("<br>");
b = false;
}
if (b) { //文件格式不对则不进行上传
String filename = path + file.getOriginalFilename().replace(FILE_FORMAT_TXT, "_" + System.currentTimeMillis() + FILE_FORMAT_TXT);
java.io.File newFile = new java.io.File(filename);
Files.copy(file.getInputStream(), newFile.toPath());
sb.append("新文件已生成，").append(newFile.getAbsolutePath()).append("<br>");
}
}
return sb.toString();
}
@GetMapping("uploadToDBPage")
public String uploadToDBPage() {
return "uploadToDB";
}
@PostMapping("uploadToDB")
@ResponseBody
public String uploadToDB(@RequestParam("file") MultipartFile file) throws IOException {
//校验文件
try {
String[] fileFormats = { FILE_FORMAT_TXT, FILE_FORMAT_JPEG, FILE_FORMAT_PNG };
checkFile(file, fileFormats);
} catch (FileException e) {
e.printStackTrace();
return e.getMessage();
}
//构建存储对象
SysFile sysFile = SysFile.builder()
.name(getFilename(file.getOriginalFilename()))
.format(getFileFormat(file.getOriginalFilename()))
.data(file.getBytes())
.size(file.getSize())
.build();
int i = fileMapper.insertFile(sysFile);
return i > 0 ? "添加成功" : "添加失败";
}
private String getFileFormat(String filename) {
int i = filename.lastIndexOf(".");
return filename.substring(i + 1); //文件后缀
}
/**
* 获取不带后缀的文件名
*/
private String getFilename(String originalFilename) {
int i = originalFilename.lastIndexOf(".");
return originalFilename.substring(0, i);
}
@GetMapping("downloadPage")
public String downloadPage(ModelMap map) {
List<SysFile> files = fileMapper.queryFiles();
map.addAttribute("files", files);
return "download";
}
@GetMapping("downloadFile/{id}")
public void downloadFile(@PathVariable Integer id, HttpServletResponse response) throws IOException {
SysFile sysFile = fileMapper.queryFileById(id);
String filename = sysFile.getName() + "_" + System.currentTimeMillis() + "." + sysFile.getFormat();
//指定下载文件名
response.setHeader("Content-Disposition", "attachment; filename=" + filename);
response.setCharacterEncoding("UTF-8");
//告知浏览器文件大小
response.addHeader("Content-Length", String.valueOf(sysFile.getSize()));
//内容类型为通用类型，表示二进制数据流
response.setContentType(getContentType(sysFile.getFormat()));
InputStream is = new ByteArrayInputStream(sysFile.getData());
OutputStream os = response.getOutputStream();
byte[] buffer = new byte[1024];
int l = 0;
while ((l = is.read(buffer)) != -1) {
os.write(buffer, 0, l);
}
is.close();
os.close();
}
private String getContentType(String format) {
switch (format) {
case FILE_FORMAT_TXT:
return MediaType.TEXT_PLAIN_VALUE;
case FILE_FORMAT_JPEG:
return MediaType.IMAGE_JPEG_VALUE;
case FILE_FORMAT_PNG:
return MediaType.IMAGE_PNG_VALUE;
default:
return MediaType.APPLICATION_OCTET_STREAM_VALUE; //通用类型
}
}
}
效果图
成功下载 txt 文件。
成功下载 jpg 文件。
成功下载 png 文件。
项目结构图
