package org.example.backend.config;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
@Configuration
public class ExcelExportConfig {
@Value("${excel.export.path}")
private String exportPath;
public String getExportPath() {
return exportPath;
}
}package org.example.backend.entity;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDateTime;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BiliVideo {
@ExcelIgnore // 自增ID
private Long id;
@ExcelProperty(index = 0)
private Long aid;
@ExcelProperty(index = 1)
private String bvid;
@ExcelProperty(index = 2)
private Long cid;
@ExcelProperty(index = 3)
private String tnameV2;
@ExcelProperty(index = 4)
private String pidNameV2;
@ExcelProperty(index = 5)
private String title;
@ExcelProperty(index = 6)
private Long playCount;
@ExcelProperty(index = 7)
private Integer bulletScreenCount;
@ExcelProperty(index = 8)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private LocalDateTime publishTime;
@ExcelProperty(index = 9)
private String videoUrl;
@ExcelIgnore
private Integer flag;
public Long getAid() {
return aid;
}
public void setAid(Long aid) {
this.aid = aid;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getBvid() {
return bvid;
}
public void setBvid(String bvid) {
this.bvid = bvid;
}
public Long getCid() {
return cid;
}
public void setCid(Long cid) {
this.cid = cid;
}
public String getTnameV2() {
return tnameV2;
}
public void setTnameV2(String tnameV2) {
this.tnameV2 = tnameV2;
}
public String getPidNameV2() {
return pidNameV2;
}
public void setPidNameV2(String pidNameV2) {
this.pidNameV2 = pidNameV2;
}
public Long getPlayCount() {
return playCount;
}
public void setPlayCount(Long playCount) {
this.playCount = playCount;
}
public Integer getBulletScreenCount() {
return bulletScreenCount;
}
public void setBulletScreenCount(Integer bulletScreenCount) {
this.bulletScreenCount = bulletScreenCount;
}
public LocalDateTime getPublishTime() {
return publishTime;
}
public void setPublishTime(LocalDateTime publishTime) {
this.publishTime = publishTime;
}
public String getVideoUrl() {
return videoUrl;
}
public void setVideoUrl(String videoUrl) {
this.videoUrl = videoUrl;
}
public Integer getFlag() {
return flag;
}
public void setFlag(Integer flag) {
this.flag = flag;
}
}
package org.example.backend.controller;
import jakarta.servlet.http.HttpServletResponse;
import org.example.backend.config.ExcelExportConfig;
import org.example.backend.service.BiliVideoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Collections;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/excel")
public class ExcelController {
@Autowired
private BiliVideoService biliVideoService;
@Autowired
private ExcelExportConfig excelExportConfig;
@PostMapping("/import")
public String importExcel(@RequestParam("file") MultipartFile file) {
try {
biliVideoService.importExcel(file);
return "导入成功";
} catch (Exception e) {
e.printStackTrace();
return "导入失败: " + e.getMessage();
}
}
@PostMapping("/export")
public Map<String, String> exportExcel(@RequestBody List<Map<String, Object>> data) {
try {
String fileName = biliVideoService.exportExcel(data);
// 构建完整的下载URL
String downloadUrl = "/excel/download/" + fileName;
return Collections.singletonMap("url", downloadUrl);
} catch (Exception e) {
e.printStackTrace();
return Collections.singletonMap("error", "导出失败: " + e.getMessage());
}
}
// 新增下载接口
@GetMapping("/download/{fileName}")
public void downloadFile(@PathVariable String fileName, HttpServletResponse response) {
try {
String filePath = excelExportConfig.getExportPath() + fileName;
File file = new File(filePath);
if (!file.exists()) {
response.setStatus(HttpServletResponse.SC_NOT_FOUND);
return;
}
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
// 文件流输出
Files.copy(file.toPath(), response.getOutputStream());
response.flushBuffer();
} catch (Exception e) {
e.printStackTrace();
response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
}
}
}package org.example.backend.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.backend.entity.BiliVideo;
import java.util.List;
@Mapper
public interface BiliVideoMapper {
List<BiliVideo> list(Integer flag, String pidNameV2);
// 批量插入
void batchInsert(@Param("list") List<BiliVideo> list);
//单条插入
void insertSingle(BiliVideo video);
// 清空表
void truncateTable();
// 查询所有
List<BiliVideo> selectAll();
//修改
void updateById(Long id,Boolean flag);
}
package org.example.backend.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.example.backend.config.ExcelExportConfig;
import org.example.backend.converter.DynamicCellStyleStrategy;
import org.example.backend.converter.IntegerConverter;
import org.example.backend.converter.LongConverter;
import org.example.backend.dao.PageBean;
import org.example.backend.entity.BiliVideo;
import org.example.backend.listener.ExcelDataListener;
import org.example.backend.mapper.BiliVideoMapper;
import org.example.backend.service.BiliVideoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.IOException;
import java.util.*;
@Service
public class BiliVideoServiceImpl implements BiliVideoService {
@Autowired
private BiliVideoMapper biliVideoMapper;
@Autowired
private ExcelExportConfig excelExportConfig; // 注入配置
// 属性名到中文表头的映射
private static final Map<String, String> HEADER_MAPPING = new LinkedHashMap<String, String>() {{
put("id", "id");
put("aid", "aid");
put("bvid", "bvid");
put("title", "视频标题");
put("tnamev2", "视频类型");
put("pid_name_v2", "视频分类");
put("play_count", "播放量");
put("bullet_screen_count", "弹幕数");
put("publish_time", "发布时间");
put("video_url", "视频地址");
put("flag", "标记");
}};
@Override
//分页查询
public PageBean<BiliVideo> list(Integer pageNum, Integer pageSize, Integer flag, String pidNameV2) {
//1.创建PageBean对象
PageBean<BiliVideo> pb = new PageBean<>();
//2.开启分页查询 PageHelper
PageHelper.startPage(pageNum,pageSize);
//3.调用mapper
List<BiliVideo> as = biliVideoMapper.list(flag,pidNameV2);
//Page中提供了方法,可以获取PageHelper分页查询后 得到的总记录条数和当前页数据
Page<BiliVideo> p = (Page<BiliVideo>) as;
//把数据填充到PageBean对象中
pb.setTotal(p.getTotal());
pb.setItems(p.getResult());
return pb;
}
@Override
public void importExcel(MultipartFile file) throws IOException {
biliVideoMapper.truncateTable();
EasyExcel.read(file.getInputStream(), BiliVideo.class,
new ExcelDataListener(biliVideoMapper))
.registerConverter(new LongConverter()) // 注册自定义转换器
.registerConverter(new IntegerConverter())
.sheet(0)
.headRowNumber(1) // 根据实际表头行数设置（通常为1）
.doRead();
}
@Override
public void updateById(Long id, Boolean flag) {
biliVideoMapper.updateById(id,flag);
}
@Override
public List<BiliVideo> selectAll() {
return biliVideoMapper.selectAll();
}
@Override
public String exportExcel(List<Map<String, Object>> data) throws IOException {
// 生成唯一文件名
String fileName = "B站视频数据_" + System.currentTimeMillis() + ".xlsx";
String fullPath = excelExportConfig.getExportPath() + fileName;
// 创建目录（如果不存在）
File dir = new File(excelExportConfig.getExportPath());
if (!dir.exists()) dir.mkdirs();
// 动态生成表头（原有逻辑）
List<List<String>> head = new ArrayList<>();
Set<String> includedFields = new LinkedHashSet<>();
if (!data.isEmpty()) {
includedFields.addAll(data.get(0).keySet());
}
for (String field : HEADER_MAPPING.keySet()) {
if (includedFields.contains(field)) {
head.add(Collections.singletonList(HEADER_MAPPING.get(field)));
}
}
// 写入本地文件（代替原有的response输出）
try (ExcelWriter excelWriter = EasyExcel.write(fullPath)
.registerWriteHandler(new DynamicCellStyleStrategy())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("视频数据")
.head(head)
.build();
// 构建数据行
List<List<Object>> dataList = new ArrayList<>();
for (Map<String, Object> row : data) {
List<Object> rowData = new ArrayList<>();
for (String field : includedFields) {
if (HEADER_MAPPING.containsKey(field)) {
rowData.add(row.get(field));
}
}
dataList.add(rowData);
}
excelWriter.write(dataList, writeSheet);
}
return fileName; // 返回文件名（不含路径）
}
}
<?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="org.example.backend.mapper.BiliVideoMapper">
<insert id="batchInsert" parameterType="list">
INSERT INTO bili_video (
aid,
bvid,
cid,
tnamev2,
pid_name_v2,
title,
play_count,
bullet_screen_count,
publish_time,
video_url,
flag
) VALUES
<foreach collection="list" item="item" separator=",">
(
#{item.aid},
#{item.bvid},
#{item.cid},
#{item.tnameV2},
#{item.pidNameV2},
#{item.title},
#{item.playCount},
#{item.bulletScreenCount},
#{item.publishTime},
#{item.videoUrl},
#{item.flag}
)
</foreach>
</insert>
<insert id="insertSingle" parameterType="org.example.backend.entity.BiliVideo">
INSERT INTO bili_video (
aid, bvid, cid, tnamev2, pid_name_v2, title,
play_count, bullet_screen_count, publish_time, video_url, flag
) VALUES (
#{aid}, #{bvid}, #{cid}, #{tnameV2}, #{pidNameV2}, #{title},
#{playCount}, #{bulletScreenCount}, #{publishTime}, #{videoUrl}, #{flag}
)
</insert>
<select id="selectAll" resultType="org.example.backend.entity.BiliVideo">
SELECT * FROM bili_video
</select>
<select id="list" resultType="org.example.backend.entity.BiliVideo">
SELECT * FROM bili_video
<where>
<if test="flag != null">
AND flag = #{flag}
</if>
<if test="pidNameV2 != null and pidNameV2 != ''">
AND pid_name_v2 = #{pidNameV2}
</if>
</where>
</select>
<update id="truncateTable">
TRUNCATE TABLE bili_video
</update>
<update id="updateById" parameterType="map">
UPDATE bili_video
SET flag = #{flag}
WHERE id = #{id}
</update>
</mapper>修改上述代码，使用springboot+Mybatis+EasyExcel编写代码，实现读取mysql数据flag值为1的所有数据存储到数据库，返回下载excel绝对路径
最新发布
本文探讨了在使用ibatis框架进行数据库操作时，当select语句的返回类型不确定时，默认返回类型的处理方式。特别指出，在无法明确指定返回类型的情况下，ibatis将默认返回Long类型而非Integer。
扫一扫
5893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
