spring boot项目从零开始-下载-流式查询优化
前提
先集成sql server,参考:
https://blog.csdn.net/sndayYU/article/details/104186820
说明:这里以csv的大文件下载为例(不能超出一定量的内存)
目录:
步骤
数据库
drop table if exists dev_download
create table dev_download(
id int identity(1,1) primary key not null,
title varchar(100) not null,
content_type varchar(32) not null
)
-- 造数
set identity_insert dev_download off
declare @id int
declare @type int
set @id = 0
set @type = 0
while @id < 1000000
begin
insert into dev_download(title, content_type) values('title'+str(@id), 'type' + str(@type))
set @id = @id + 1
set @type = @type + 1
if @type = 10
begin
set @type = 0
end
end
pom.xml
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<parent>
<artifactId>spring-boot-dependencies</artifactId>
<groupId>org.springframework.boot</groupId>
<version>2.1.5.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<!-- sql server -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>7.2.2.jre8</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.16</version>
</dependency>
</dependencies>
application.yml
spring:
datasource:
minIdle: 1
maxActive: 5
initialSize: 1
filters: stat,wall
poolPreparedStatements: false
type: com.alibaba.druid.pool.DruidDataSource
maxPoolPreparedStatementPerConnectionSize: 20
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
url: jdbc:sqlserver://localhost:1433;databaseName=dev-data
username: sa
password: 12345678
model
package org.example.modal;
import lombok.Data;
@Data
public class DownloadItem {
private Long id;
private String title;
private String contentType;
}
dao
package org.example.dao;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.ResultHandler;
import org.example.modal.DownloadItem;
@Mapper
public interface DownloadMapper {
@ResultType(DownloadItem.class)
@Select("select id, title, content_type contentType " +
" from dev_download with(nolock) ")
void getAllDownloadItems(ResultHandler<DownloadItem> resultHandler);
}
service
package org.example.service;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.example.dao.DownloadMapper;
import org.example.modal.DownloadItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.function.Consumer;
@Service
public class DownloadService {
@Autowired
private DownloadMapper downloadMapper;
private final static String CSV_COL_SEPARATOR = ",";
private final static String CSV_ROW_SEPARATOR = "\r\n";
private void setHeader(HttpServletResponse response, String filename) throws Exception {
response.setContentType("application/csv;charset=gb18030");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename + ".csv", "UTF-8"));
}
/************************* 使用mybatis的ResultHandler进行下载 *****************************/
public void resultHandlerToCsv(HttpServletResponse response) throws Exception {
setHeader(response, "ResultHandler");
PrintWriter writer = response.getWriter();
// 标题行
writer.write("文章id,文章标题,文章类型");
// 写入数据
DownloadResultHandler handler = new DownloadResultHandler(item -> {
StringBuilder sb = new StringBuilder();
sb.append(item.getId()).append(CSV_COL_SEPARATOR);
sb.append(item.getId()).append(CSV_COL_SEPARATOR);
sb.append(item.getId()).append(CSV_COL_SEPARATOR);
sb.append(CSV_ROW_SEPARATOR);
// 及时写入response,避免内存过高
writer.write(sb.toString());
});
downloadMapper.getAllDownloadItems(handler);
// 刷新缓存
writer.flush();
}
static class DownloadResultHandler implements ResultHandler<DownloadItem> {
private final Consumer<DownloadItem> consumer;
public DownloadResultHandler(Consumer<DownloadItem> consumer) {
this.consumer = consumer;
}
@Override
public void handleResult(ResultContext<? extends DownloadItem> resultContext) {
this.consumer.accept(resultContext.getResultObject());
}
}
/************************* 使用Jdbc的RowCallbackHandler进行下载 *****************************/
@Autowired
private JdbcTemplate jdbcTemplate;
public void rowHandlerToCsv(HttpServletResponse response) throws Exception {
setHeader(response, "ResultHandler");
PrintWriter writer = response.getWriter();
// 标题行
writer.write("文章id,文章标题,文章类型");
// 写入数据
DownloadRowHandler handler = new DownloadRowHandler(item -> {
try {
StringBuilder sb = new StringBuilder();
sb.append(item.getInt("id")).append(CSV_COL_SEPARATOR);
sb.append(item.getString("title")).append(CSV_COL_SEPARATOR);
sb.append(item.getString("contentType")).append(CSV_COL_SEPARATOR);
sb.append(CSV_ROW_SEPARATOR);
// 及时写入response,避免内存过高
writer.write(sb.toString());
} catch (Exception e) {
e.printStackTrace();
System.out.println("col解析错误,越过");
}
});
jdbcTemplate.query(
"select id, title, content_type contentType from dev_download with(nolock)",
handler);
// 刷新缓存
writer.flush();
}
static class DownloadRowHandler implements RowCallbackHandler {
private final Consumer<ResultSet> consumer;
public DownloadRowHandler(Consumer<ResultSet> consumer) {
this.consumer = consumer;
}
@Override
public void processRow(ResultSet resultSet) throws SQLException {
this.consumer.accept(resultSet);
}
}
public void rowHandlerToCsv1(HttpServletResponse response) throws Exception {
setHeader(response, "ResultHandler");
PrintWriter writer = response.getWriter();
// 标题行
writer.write("文章id,文章标题,文章类型");
// 写入数据
DownloadRowHandler handler = new DownloadRowHandler(item -> {
try {
// 及时写入response,避免内存过高
writer.write(String.valueOf(item.getInt("id")));
writer.write(CSV_COL_SEPARATOR);
writer.write(item.getString("title"));
writer.write(CSV_COL_SEPARATOR);
writer.write(item.getString("contentType"));
writer.write(CSV_COL_SEPARATOR);
writer.write(CSV_ROW_SEPARATOR);
} catch (Exception e) {
e.printStackTrace();
System.out.println("col解析错误,越过");
}
});
jdbcTemplate.query(
"select id, title, content_type contentType from dev_download with(nolock)",
handler);
// 刷新缓存
writer.flush();
}
}
controller及启动入口
package org.example.controller;
import org.example.service.DownloadService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
@RestController
public class DownloadController {
@Autowired
private DownloadService downloadService;
// resultHandlerToCsv time = 27541
@GetMapping("/download1")
public void download1(HttpServletResponse response) throws Exception {
long time = System.currentTimeMillis();
downloadService.resultHandlerToCsv(response);
System.out.println("resultHandlerToCsv time = " + (System.currentTimeMillis() - time));
}
@GetMapping("/download2")
public void download2(HttpServletResponse response) throws Exception {
long time = System.currentTimeMillis();
downloadService.rowHandlerToCsv(response);
System.out.println("rowHandlerToCsv time = " + (System.currentTimeMillis() - time));
}
@GetMapping("/download3")
public void download3(HttpServletResponse response) throws Exception {
long time = System.currentTimeMillis();
downloadService.rowHandlerToCsv1(response);
System.out.println("rowHandlerToCsv1 time = " + (System.currentTimeMillis() - time));
}
}
package org.example;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RestController;
@SpringBootApplication
@RestController
public class MainApp {
public static void main(String[] args) {
SpringApplication.run(MainApp.class, args);
}
}
试验
接口不断访问3个接口
resultHandlerToCsv time = 12340
rowHandlerToCsv time = 11538
rowHandlerToCsv1 time = 6657
resultHandlerToCsv time = 10835
resultHandlerToCsv time = 11972
resultHandlerToCsv time = 13014
resultHandlerToCsv time = 11854
resultHandlerToCsv time = 14040
rowHandlerToCsv time = 26305
rowHandlerToCsv time = 27233
rowHandlerToCsv1 time = 7948
rowHandlerToCsv time = 7638
rowHandlerToCsv time = 6500
使用jdk里面的JvisualVM看下堆内存:
可以看到内存和cpu不是很高。
三种方法比较:
- 总体上看使用mybatis的ResultHandler方法稍慢些,但代码可读性强;
- JdbcTemplate虽然快些,但需要自己解析列,处理空值等情况;
- 若是不使用StringBuilder,直接写入response,时间最快