SpringBoot 查询数据库导出报表文件

一、背景

1、需求

几千万条报表数据导出到Excel中

2、问题

1. 在数据量导出不大时，我们的常规做法是使用MySQL直接查询出全部数据，整理规划成Excel列表，使用POI写入到Excel文件中
2. 但是当数据量较大时，使用MySQL查询出所有数据，一会超时断开连接，二会内存溢出，使用POI暂时不支持分布写入数据到Excel中

3、解决

1. 使用数据库流式读取可以解决数据库读取时间过长，内存溢出问题，这个解决了一次性读取全部数据到内存中
2. 使用CSV文件代替xlsx/xls文件写入，CSV也可以使用Excel打开操作，并且也可另存为xlsx/xls，CSV本质是文本文件，不用确定尾结点，可以如TXT一样持续向文件追加内容

二、代码实现

1、mapper文件

使用Cursor游标标识，流式查询数据数据

  @Select("<script>"
      + "select * from `user_report`"
      + "</script>")
  Cursor<UserReport> selectCursorByCondition();

2、读取写入文件

1. @Transactional(readOnly = true)：搭配数据库Cursor查询使用，事务注解为只读，保证数据整体的一致性
2. fieldArr：字段数组，规定要写入文件的字段项，比如查询的全部字段为a,b,c,d，而我只想显示a,c,d
3. headerArr：字段名数组，对应文件标题栏，需要不字段数组a，c，d名称一一对应
4. CsvUtils：一个简单的工具类，后续贴代码

/**
   * 导出Excel
   * @param fieldArr       字段数组
   * @param headerArr      字段名数组
   * @param fileName       文件名
   */
  @Transactional(readOnly = true)
  public void exportList(HttpServletResponse response,
      String[] fieldArr, String[] headerArr, String fileName) {
    //设置文件格式
    response.setCharacterEncoding("UTF-8");
    response.addHeader("Content-Type", "application/csv");
    try {
       //文件名 设置为中文
      fileName = new String(fileName.getBytes("gb2312"), "iso8859-1");
    } catch (Exception e) {
      log.error("file name show error：{}", e.getMessage());
    }
    //响应头部
    response.addHeader("Content-Disposition", "attachment; filename=" + fileName + "(" +
        DateUtil.format(DateUtil.date(), DatePattern.NORM_DATETIME_PATTERN) + ").csv");
    //查询数据
    Cursor<UserReport> modelStream = xxMapper.selectCursorByCondition();

    try {
      PrintWriter out = response.getWriter();
      try {
        //写入标题行
        out.write(CsvUtils.getTitleLine(headerArr));
        //写入数据行
        modelStream.forEach(item -> {
          out.write(CsvUtils.getRowLine(fieldArr, item));
        });
        out.flush();
      } catch (Exception e) {
        log.error("write file error:{}", e.getMessage());
      } finally {
        out.close();
      }
    } catch (Exception e) {
      log.error("exportList error:{}", e.getMessage());
    }


  }

3、CsvUtils工具类

import java.lang.reflect.Field;
import java.util.HashSet;
import java.util.Set;

/**
 * @Author: catcoder
 * @Desc:
 * @Time: 10:05 2021/8/2
 **/
public class CsvUtils {

  /**
   * CSV文件列分隔符
   */
  public static final String CSV_COLUMN_SEPARATOR = ",";

  /**
   * CSV文件行分隔符
   */
  public static final String CSV_ROW_SEPARATOR = System.lineSeparator();


  /**
   * 获取标题行
   *
   * @param headerArr 标题数组
   * @return
   */
  public static String getTitleLine(String[] headerArr) {
    StringBuffer line = new StringBuffer("");
    for (String title : headerArr) {
      line.append(title).append(CSV_COLUMN_SEPARATOR); //添加标题行数据
    }
    line.append(CSV_ROW_SEPARATOR); //换行数据
    return line.toString();
  }

  /**
   * 或去数据行
   *
   * @param fieldArr 字段数组
   * @param obj      实体对象
   * @return
   */
  public static String getRowLine(String[] fieldArr, Object obj) {
    StringBuffer line = new StringBuffer("");
    Class<?> srcClass = obj.getClass();

    //获取Obj 所有字段
    Set<String> objFiled = new HashSet<>();
    Field[] fields = obj.getClass().getDeclaredFields();
    for (Field field : fields) {
      objFiled.add(field.getName());
    }

    for (String field : fieldArr) {
      try {
        // 获取对象对应的Field
        if (objFiled.contains(field)) {
          Field objField = srcClass.getDeclaredField(field);
          objField.setAccessible(true); //设置private可访问
          Object value = objField.get(obj);
          line.append(value); //添加元素
        }
      } catch (Exception e) {
        e.printStackTrace();
      } finally {
        line.append(CSV_COLUMN_SEPARATOR); //CSV间隔数据
      }
    }
    line.append(CSV_ROW_SEPARATOR);//换行数据
    return line.toString();
  }
}