Springboot应用中导出MySQL数据到CSV文件

最新推荐文章于 2024-07-29 16:41:47 发布

yb歪比

最新推荐文章于 2024-07-29 16:41:47 发布

阅读量1.4k

点赞数 1

文章标签： sql java spring boot

本文链接：https://blog.csdn.net/weixin_47006863/article/details/131716590

版权

问题描述：最近有个需求是在spring boot应用中允许用户对某数据表中的数据进行备份

方案1：SQL语句直接备份

在MySQL中，可以使用 SELECTI...INTO OUTFILE 语句将表的内容导出成一个文本文件。SELECT...INTO OUTFILE 语句基本格式如下:

SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

其中[OPTIONS]为可选参数，包括 FIELDS 和 LINES 子句，其常用的取值有：

FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

在Springboot应用中Mapper层备份方法和SQL语句的实例如下：

@Select({
    "select * into outfile #{filePath}",
    "fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\\r\\n'",
    "from Log",
    "where gmt_create between #{startTime} and #{endTime}",
})
void backup(@Param("startTime")Date startTime, @Param("endTime")Date endTime, @Param("filePath") String filePath);

存在的问题

1. 文件路径权限问题

如果出现错误The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，则是因为MySQL 限制了数据的导出路径。

首先在MySQL中执行命令show variables like ‘%secure%’查看secure_file_priv的设定值:

为NULL，表示mysql不允许数据的导入导出
为某路径，表示mysql只允许数据在该路径下的导入导出
为空，表示mysql允许所有路径下的导入导出

出现错误应该是secure_file_priv指定的路径不是在方法中传入的路径或者secure_file_priv为NULL

然后可以在mysql的安装目录下，找到配置文件（my.conf或my.ini），在配置文件的[mysqld]中，添加secure_file_priv=[文件路径]，其中文件路径可以空着不填，表示允许所有路径下的导入导出，修改保存后重启mysql服务即可。

2. 文件已存在问题

该语句只能将数据备份到新建文件中，如果文件已存在需要更改文件路径

3. 导出文件不带列名问题

该方法默认导出的数据不带列名，如果需要带列名可以对SQL语句进行改造：

"select ‘id’,'gmt_create','name'",
"union all",
"select * from log",
"into outfile #{filePath}",
"fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\\r\\n'",

方案缺陷

这种备份方法将文件保存在MySQL的远程服务器上，备份文件的清理不能通过后台应用完成；若MySQL和后台应用运行在同一服务器的不同docker中，可以通过volume设置共享文件夹来实现后台应用管理备份文件
不便于用户查看下载备份文件

方案2：借助opencsv实现备份

引入maven依赖，我用的是opencsv 4.4版本，可以在官网查看不同版本区别opencsv –

<dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>4.4</version>
</dependency>

编写导出csv文件工具类

public class CsvUtil {
    public static<T> void createCsvFile(List<T> data, String filePath, String fileName, HttpServletResponse response) throws IOException {
        Writer writer = null;
        //导出并保存到本地
        if(filePath != null){
            writer = new FileWriter(filePath + fileName + ".csv");
        }
        //导出并随请求返回以供用户下载
        if(response != null){
            response.reset();
            response.setContentType("APPLICATION/OCTET-STREAM");
            fileName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            response.setHeader("Content-disposition", "attachment;  filename=" + fileName + ".csv");
            response.setCharacterEncoding("UTF-8");
            writer = response.getWriter();
        }
        if(writer == null){
            return;
        }
        try{
            writer.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
            CSVWriter csvWriter = new CSVWriter(writer, ICSVWriter.DEFAULT_SEPARATOR, ICSVWriter.NO_QUOTE_CHARACTER,
                    ICSVWriter.NO_ESCAPE_CHARACTER, ICSVWriter.DEFAULT_LINE_END);
            StatefulBeanToCsv<T> beanToCsv = new StatefulBeanToCsvBuilder<T>(writer)
                    .withMappingStrategy(new CustomMappingStrategy(data.get(0).getClass()))
                    .build();
            beanToCsv.write(data);
            csvWriter.close();
        } catch (CsvRequiredFieldEmptyException e) {
            throw new RuntimeException(e);
        } catch (CsvDataTypeMismatchException e) {
            throw new RuntimeException(e);
        }
        writer.close();
    }
}

自定义CSV写入策略CustomMappingStrategy<T>（目的是让CSV中带列名并且列的顺序和实体类中定义的属性顺序一致，并且无需改造实体类添加注解）

public class CustomMappingStrategy<T> extends HeaderColumnNameMappingStrategy<T> {
    private Locale errorLocale = Locale.getDefault();
    public CustomMappingStrategy(Class<? extends T> type){
        super();
        this.setErrorLocale(errorLocale);
        this.setType(type);
    }

    @Override
    public String[] generateHeader(T bean) throws CsvRequiredFieldEmptyException {
        if (this.type == null) {
            throw new IllegalStateException(ResourceBundle.getBundle("opencsv", this.errorLocale).getString("type.before.header"));
        }
        if(headerIndex.isEmpty()) {
            List<String> realHeaderList = loadFieldNames(type);
            String[] header = realHeaderList.toArray(new String[0]);
            // 实际上, 最终 csv 文件的列排序和数据按序获取都去都是通过 headerIndex 对象完成的, 所以这一步针对 header 重新赋值为想要的顺序即可. header 最终会用于初始化 headerIndex
            headerIndex.initializeHeaderIndex(header);
            return header;
        }
        return headerIndex.getHeaderIndex();
    }

    private List<String> loadFieldNames(Class<? extends T> cls) {
        List<String> fieldNames = new ArrayList<>();
        Field[] arr$ = FieldUtils.getAllFields(cls);
        int len$ = arr$.length;
        for(int i$ = 0; i$ < len$; ++i$) {
            Field field = arr$[i$];
            fieldNames.add(field.getName());
        }
        return fieldNames;
    }
}

在ServiceImpl中调用

	//导出并保存到本地
	@Override
    public void exportToCsv() {
        List<Student> students = studentMapper.queryAllStudent();
        String filePath = "/Users/syb/Documents/backup/test.csv";
        try{
            CsvUtil.createCsvFile(students, filePath, null, null);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
	//导出并随请求返回以供用户下载
    @Override
    public void exportAndDownload(HttpServletResponse response) {
        List<Student> students = studentMapper.queryAllStudent();
        try{
            CsvUtil.createCsvFile(students, null, "test", response);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

存在问题

1. 中文乱码问题

保存文件乱码：在写csv文件时，微软的csv采用的utf-8 bom的编码格式，因此我们可以在文件头3字节写入utf-8 bom说明文件格式

writer.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));

下载文件乱码：通过response下载的文件乱码可以设定字符编码方式为‘UTF-8’

response.setCharacterEncoding("UTF-8");

2. csv文件列名与列顺序问题

opencsv提供了两个注解，加在实体类的属性上面可以控制生成文件列的顺序和列名：

@CsvBindByPosition(position = 0) 来控制顺序，但是无法自动生成列名。
@CsvBindByName(column = "TradeID") 来控制列名，但是无法设置列的顺序。
无注解，自动获取属性名为列名，顺序为列名的字典序

我们往往需要的是带列名并且按照数据表中字段定义的顺序来导出文件，这可以通过改造实体类添加顺序控制注解+手动添加header实现，但明显比较复杂，因此需要通过自定义MappingStrategy实现。

OpenCSV提供了两种可用的MappingStrategy：

HeaderColumnNameMappingStrategy （默认使用）：允许根据自定义名称将 CVS 文件列映射到 bean 字段；将 bean 写入 CSV 时，这允许更改列标题名称，但我们无法控制列顺序，查看源码可以看到这里使用了TreeSet对列标题进行了排序
ColumnPositionMappingStrategy（带Position注解时使用）：允许根据列顺序将 CSV 文件列映射到 bean 字段；将 bean 写入 CSV 时，我们可以控制列顺序，但我们得到一个空标题

因此我们可以实现自定义的MappingStrategy，对HeaderColumnNameMappingStrategy进行扩展，并在创建StatefulBeanToCsv<T>对象时指定自定义的匹配策略（CustomMappingStrategy<T>）。查看源码发现，在类StatefulBeanToCsv<T>中，beforeFirstWrite方法调mappingStrategy.generateHeader(bean)获取header，因此需要在定义的匹配策略中重写generateHeader方法，实现返回和实体类字段定义顺序一致的header。

StatefulBeanToCsv<T> beanToCsv = new StatefulBeanToCsvBuilder<T>(writer)
        .withMappingStrategy(new CustomMappingStrategy(data.get(0).getClass()))
        .build();

       private void beforeFirstWrite(T bean) throws CsvRequiredFieldEmptyException {
        if (this.mappingStrategy == null) {
            this.mappingStrategy = OpencsvUtils.determineMappingStrategy(bean.getClass(), this.errorLocale);
        }

        if (this.csvwriter == null) {
            this.csvwriter = new CSVWriter(this.writer, this.separator, this.quotechar, this.escapechar, this.lineEnd);
        }

        String[] header = this.mappingStrategy.generateHeader(bean);
        if (header.length > 0) {
            this.csvwriter.writeNext(header, this.applyQuotesToAll);
        }

        this.headerWritten = true;
    }