导出MySQL表结构到excle

因有提供表信息的需求，所有写了自动生成的代码，避免了无效的工作量。
只列出关键代码，依赖于mybatis-plus，easyexcel。
代码中是读取指定表名生成表结构，也可以改为读取所有表。

package com.jianshu.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.jianshu.FeignApplication;
import com.jianshu.domain.Columns;
import com.jianshu.domain.Tables;
import com.jianshu.excel.ColumnCell;
import com.jianshu.excel.TableCell;
import com.jianshu.service.ColumnsService;
import com.jianshu.service.TablesService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
@SpringBootTest(classes = FeignApplication.class)
public class TablesServiceImplTest {

    @Resource
    private TablesService tablesService;

    @Resource
    private ColumnsService columnsService;

    @Test
    public void test() {
        String tableNameFile = "C:\\Users\\archer\\Desktop\\tables.txt";
        String fileName = "C:\\Users\\archer\\Desktop\\表结构.xlsx";
        List<String> tableNames = new ArrayList<>();

        try {
            BufferedReader fileReader = new BufferedReader(new FileReader(tableNameFile));
            String line;
            while ((line = fileReader.readLine()) != null) {
                if (!StringUtils.isEmpty(line)) {
                    tableNames.add(line);
                }
            }
        } catch (IOException e) {
            log.info(e.getMessage(), e);
        }

        List<Tables> tablesList = tablesService.list(Wrappers.<Tables>lambdaQuery().in(Tables::getTableName, tableNames));
        List<Columns> columnsList = columnsService.list(Wrappers.<Columns>lambdaQuery().in(Columns::getTableName, tableNames));
        Map<String, List<Columns>> columnsMap = columnsList.stream().collect(Collectors.<Columns, String>groupingBy(Columns::getTableName));
        log.info("size {}", tablesList.size());

        List<TableCell> tableCellList = new ArrayList<>();
        List<ColumnCell> columnCellList = new ArrayList<>();
        for (Tables tables : tablesList) {
//            log.info("name {} {} {}", tables.getTableName(), tables.getTableComment(), tables.getTableRows());
            TableCell tableCell = new TableCell();
            tableCell.setTableName(tables.getTableName());
            tableCell.setTableComment(tables.getTableComment());
            tableCell.setTableRows(tables.getTableRows());
            tableCellList.add(tableCell);

            List<Columns> tableColumns = columnsMap.get(tables.getTableName());
            for (Columns columns : tableColumns) {
//                log.info("column {} {}", columns.getColumnName(), columns.getColumnComment());
                ColumnCell columnCell = new ColumnCell();
                columnCell.setTableName(tables.getTableName());
                columnCell.setColumnName(columns.getColumnName());
                columnCell.setColumnComments(columns.getColumnComment());
                columnCellList.add(columnCell);
            }
        }


        ExcelWriter excelWriter = EasyExcel.write(fileName).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "表信息").head(TableCell.class).build();
        excelWriter.write(tableCellList, writeSheet);
        writeSheet = EasyExcel.writerSheet(1, "字段信息").head(ColumnCell.class).build();
        excelWriter.write(columnCellList, writeSheet);
        excelWriter.finish();
    }

}

maven依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.12.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
<!-- 需要定义自己的 artifactId -->
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-openfeign</artifactId>
            <version>2.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>