一、效果
Chrome 测试:718050 条数据由请求到下载完成不到 6 秒
Postman 测试:7.54 s,有 io 流影响,降低速度
下载的 csv 文件:数据完整
数据库使用的是 Docker Mysql8.0:
二、POM 依赖
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>5.7.0</version>
</dependency>
三、封装工具类
package com.example.demo.utils;
import com.opencsv.CSVWriter;
import org.apache.commons.lang3.StringUtils;
import java.io.IOException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* csv 工具封装
*
* @author yushanma
* @since 2022/10/18 19:57
*/
public class CsvUtil {
public static void writeToCsv(Writer writer, List<Map<String, Object>> dataList) throws IOException {
List<String[]> data = new ArrayList<>();
String[] header = getHeader(dataList.get(0));
data.add(header);
for (Map<String, Object> o : dataList) {
data.add(getRows(header, o));
}
// String myPath = "D:\\JWorkSpace\\demo\\src\\main\\resources\\out\\out.csv";
// CSVWriter csvWriter = new CSVWriter(
// Files.newBufferedWriter(Paths.get(myPath), StandardCharsets.UTF_8),
// CSVWriter.DEFAULT_SEPARATOR,
// CSVWriter.NO_QUOTE_CHARACTER,
// CSVWriter.NO_ESCAPE_CHARACTER,
// CSVWriter.DEFAULT_LINE_END);
CSVWriter csvWriter = new CSVWriter(
writer,
CSVWriter.DEFAULT_SEPARATOR,
CSVWriter.NO_QUOTE_CHARACTER,
CSVWriter.NO_ESCAPE_CHARACTER,
CSVWriter.DEFAULT_LINE_END);
csvWriter.writeAll(data);
csvWriter.flush();
}
/**
* 获取 header
* @param objectMap
* @return
*/
private static String[] getHeader(Map<String, Object> objectMap) {
return objectMap.keySet().toArray(new String[objectMap.keySet().size()]);
}
/**
* 字符串切割获取 rows
*
* @param s
* @return
*/
private String[] getRows(String s) {
String substring = StringUtils.substring(s, 1, s.length() - 1);
String[] split = substring.split(", ");
String[] rows = new String[split.length];
for (int i = 0; i < split.length; i++) {
String[] fields = split[i].split("=");
if (fields.length > 1) {
rows[i] = fields[1];
} else {
rows[i] = "";
}
}
return rows;
}
/**
* 通过 key => value 获取 rows
*
* @param header
* @param objectMap
* @return
*/
private static String[] getRows(String[] header, Map<String, Object> objectMap) {
String[] rows = new String[header.length];
for (int i = 0; i < rows.length; i++) {
rows[i] = objectMap.get(header[i]).toString();
}
return rows;
}
}
写入 CSV 的数据类型为 List<String[]> ,这很好理解,List 代表了数据集,String[] 代表了某行数据的记录,当 CSV 有 Header 时,List 的第一条记录即为 Header,否则全为数据内容。
获取 Header 时,通过 Map 的 KeySet 方法获取首条数据的 Header 作为代表;获取 Rows 时,可以根据 Key Value 匹配,也可以通过截取字符串获取,目前还没有想到最优解,默认使用了 Key Value 匹配方式。
四、ServiceImpl
1、模拟写入大量数据
使用多线程往数据表中写入模拟数据
@Override
public void addUser() {
for (int j = 0; j < 100; j++) {
new Thread(() -> {
for (int i = 0; i < 5000; i++) {
testDao.insertUser(UUID.randomUUID().toString(), "SysUser-" + (i + 1));
}
}).start();
}
}
2、模拟获取大量数据
@Override
public List<List<Map<String, Object>>> testsp() {
System.out.println(LocalDateTime.now().toString());
Map<String, Object> map = new HashMap<String, Object>(3);
map.put("UserId", "51fce378-4e93-11ed-b86f-0242ac110002");
List<List<Map<String, Object>>> sptest = testDao.sptest(map);
return sptest;
}
五、Controller
package com.example.demo.controller;
import com.example.demo.service.TestService;
import com.example.demo.utils.CsvUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
/**
* 测试控制层
*
* @author yushanma
* @since 2022/10/14 15:15
*/
@RestController
public class TestController {
@Resource
private TestService testService;
/**
* 模拟写入大量数据
*/
@PostMapping("/addUser")
public void addUser(){
testService.addUser();
}
/**
* 模拟下载数据
* @param response
* @throws IOException
*/
@GetMapping("/testsp")
public void download(HttpServletResponse response) throws IOException {
List<List<Map<String, Object>>> testsp = testService.testsp();
// 响应正文
response.reset();
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".csv");
CsvUtil.writeToCsv(new OutputStreamWriter(response.getOutputStream()),testsp.get(1));
}
}
这里提供下载方式时,是以流的形式一次性读取到内存中,通过响应输出流输出到前端,因为工具层调用的是 csvWriter.writeAll() 方法。