spring boot项目从零开始-(9)下载

骑猪看日落 2022-09-03 02:30 220阅读 0赞

### spring boot项目从零开始-下载 ###

*  前提
 *  步骤
 *   *   *  数据库
         *  pom.xml
         *  application.yml
         *  model
         *  dao
         *  service
         *  controller及启动入口
 *  试验

# 前提 #

先集成sql server，参考：  
https://blog.csdn.net/sndayYU/article/details/104186820

说明：这里以csv的大文件下载为例（不能超出一定量的内存）

目录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]

# 步骤 #

### 数据库 ###

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>
    
            
            <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看下堆内存：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]  
可以看到内存和cpu不是很高。

三种方法比较：

*  总体上看使用mybatis的ResultHandler方法稍慢些，但代码可读性强；
 *  JdbcTemplate虽然快些，但需要自己解析列，处理空值等情况；
 *  若是不使用StringBuilder，直接写入response，时间最快

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]: /images/20220829/1411aab5b5b44fe3a7e24b0e0ce822a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]: /images/20220829/4bb7de4c419041d69536807510352851.png