【小工具】Maven项目集成poi生成数据库设计详细说明书

背景

编写数据库详细设计文档时经常因为数据库表过多和字段过多导致耗费大量的时间。本脚本可以自定义sql语句，查出数据库中所有表的表结构，并取需要的字段生成文档。
gitee地址：https://gitee.com/pengmqqq/mysql-to-word

1、前置准备

导入依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>

操作步骤

以下为main函数相关代码，相关自定义方法详见文章末尾

获取数据库连接

        String driverUrl = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "root";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(driverUrl, username, password);

获取数据库的需要生成文档的表

        //需要排除的表
        String notGenTables = "'not_gen_table1','not_gen_table1'";
        //按前缀匹配需要排除的表
        String tableNotLike = "'not_gen_%'";
        //获取数据库的表情单
        List<JSONObject> list = getTables(connection,"test",notGenTables,tableNotLike); 

创建文档

//Blank Document
        XWPFDocument document = new XWPFDocument();
        //添加标题
        XWPFParagraph titleParagraph = document.createParagraph();
        //设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.CENTER);
        XWPFRun titleParagraphRun = titleParagraph.createRun();
        titleParagraphRun.setText(TITLE);
        titleParagraphRun.setColor("000000");
        titleParagraphRun.setFontSize(20);

生成数据库表清单

        writeSummaryTable(document, list);

生成各个表详细结构

		int i = 1;
        for (JSONObject json : list) {
   
            List<String[]> tableDetail = getTableDetail(connection,"cyberops", json.getString("name"));
            String remark = json.getString("remark");
            String title = (i++) + ". " + json.getString("name");
            if (remark != null && !remark.isEmpty()) {
   
                title += "(" + remark + ")";
            }
            writeTable(document, title, tableDetail);
        }

将文件写入磁盘

		//将文件写入磁盘
        String file