spring boot项目从零开始-(9)下载-流式查询优化

前提

先集成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，时间最快