导出数据库表结构word文档

前言

导出数据库表结构到word文档。

效果展示

表

CREATE TABLE `user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `refer_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '关联id',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `age` int(2) NOT NULL DEFAULT '0' COMMENT '年龄',
  `salary` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '值',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标识(0: 正常,1: 已删除)',
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

word文档

废话不多说，直接上代码

Test.java

import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class Test {

    public static void main(String[] args) {

        XWPFDocument doc = new XWPFDocument();
        XWPFParagraph paragraph_title = doc.createParagraph();
        XWPFRun run_title = paragraph_title.createRun();
        run_title.setText("数据库表设计文档");
        run_title.setFontSize(16);
        run_title.setBold(true);

        FileOutputStream out = null;
        try {
            //数据库名
            String dbName = "dbName";
            //获取指定数据库的所有表信息
            List<Table> tableInfos = TableUtil.getTableInfos(dbName);
            if (CollectionUtils.isNotEmpty(tableInfos)) {
                for (int i = 0; i < tableInfos.size(); i++) {
                    makeTable(doc, tableInfos.get(i), i);
                }
            }

            String filePath = "D:/数据库表设计文档.docx";
            out = new FileOutputStream(new File(filePath));
            doc.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 制作表格
     *
     * @param doc
     * @param tableInfo
     * @param pos
     */
    private static void makeTable(XWPFDocument doc, Table tableInfo, Integer pos) {

        //换行
        XWPFParagraph paragraph_linebreak = doc.createParagraph();
        XWPFRun run_linebreak = paragraph_linebreak.createRun();
        run_linebreak.addBreak();
        run_linebreak.setBold(true);

        //表名
        XWPFParagraph paragraph_tablename = doc.createParagraph();
        XWPFRun run_tablename = paragraph_tablename.createRun();
        String tableName = "";
        if (StringUtils.isEmpty(tableInfo.getTABLE_COMMENT())) {
            tableName = "表名" + tableInfo.getTABLE_NAME();
        } else {
            tableName = "表名" + tableInfo.getTABLE_NAME() + "(" + tableInfo.getTABLE_COMMENT() + ")";
        }
        run_tablename.setText(tableName);
        run_tablename.setFontSize(14);
        run_tablename.setBold(true);

        //字段集合
        List<Column> columns = tableInfo.getColumns();

        //行数(字段数 + 标题行)
        int rows = columns.size() + 1;

        //列数
        int cols = 6;

        //创建表
        XWPFTable table = doc.createTable(rows, 6);
        //宽度
        table.setWidth(8000);

        //获取第 1 行
        XWPFTableRow row_title = table.getRow(0);
        //设置第 1 行单元格内容
        row_title.getCell(0).setText("字段名");
        row_title.getCell(1).setText("字段说明");
        row_title.getCell(2).setText("数据类型");
        row_title.getCell(3).setText("是否可为空");
        row_title.getCell(4).setText("默认值");
        row_title.getCell(5).setText("索引");
        //设置第 1 行单元格背景色
        for (int i = 0; i < cols; i++) {
            row_title.getCell(i).setColor("97FFFF");
        }

        //将数据库表字段信息写入表格中
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            XWPFTableRow row = table.getRow(i + 1);
            row.getCell(0).setText(column.getCOLUMN_NAME());
            row.getCell(1).setText(column.getCOLUMN_COMMENT());
            row.getCell(2).setText(column.getCOLUMN_TYPE());
            row.getCell(3).setText(column.getIS_NULLABLE());
            row.getCell(4).setText(column.getCOLUMN_DEFAULT());
            row.getCell(5).setText(column.getCOLUMN_KEY());
        }

        doc.setTable(pos, table);
    }
}		

TableUtil.groovy

import groovy.sql.Sql;

class TableUtil {

    /**
     * 获取指定数据库的所有表信息
     *
     * @param dbName
     * @return
     */
    static List<Table> getTableInfos(String dbName) {

        Sql sql = Sql.newInstance("jdbc:mysql://127.0.0.1:3306/" + dbName + "?characterEncoding=UTF-8&useCursorFetch=true&defaultFetchSize=1000&useOldAliasMetadataBehavior=true",
                "user",
                "password")

        def resultOfTables = sql.rows("SELECT t.TABLE_NAME,t.TABLE_COMMENT,t.ENGINE \n" +
                "\tFROM INFORMATION_SCHEMA.TABLES t \n" +
                "\tWHERE t.TABLE_SCHEMA='" + dbName + "';")

        def tables = new ArrayList<Table>()

        if (resultOfTables) {
            for (int i = 0; i < resultOfTables.size(); i++) {
                Table table = resultOfTables[i]
                def resultOfColumns = sql.rows("SELECT c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT,c.COLUMN_DEFAULT,c.COLUMN_KEY,c.IS_NULLABLE\n" +
                        "\tFROM INFORMATION_SCHEMA.COLUMNS c \n" +
                        "\tWHERE c.TABLE_SCHEMA='" + dbName + "' AND c.TABLE_NAME=?; ", [table.getTABLE_NAME()])
                if (resultOfColumns) {
                    def columns = new ArrayList<Column>()
                    for (int j = 0; j < resultOfColumns.size(); j++) {
                        Column column = resultOfColumns[j]
                        columns.add(column)
                    }
                    table.setColumns(columns)
                }

                tables.add(table)
            }
        }

        return tables
    }
}

Table.java

import lombok.Data;
import java.util.List;

@Data
public class Table {
    private String TABLE_NAME;
    private String TABLE_COMMENT;
    private String ENGINE;
    private List<Column> columns;
}

Column.java

import lombok.Data;

@Data
public class Column {
    private String COLUMN_NAME;
    private String COLUMN_TYPE;
    private String COLUMN_COMMENT;
    private String COLUMN_DEFAULT;
    private String COLUMN_KEY;
    private String IS_NULLABLE;
}

说明

这里为了方便调用sql，因此TableUtil使用了groovy开发。在java中使用groovy，需要引入groovy的依赖。当然了，如果不想使用groovy，可以使用jdbc/mybatis/jdbcTemplate等。

<dependency>
	<groupId>org.codehaus.groovy</groupId>
    <artifactId>groovy-all</artifactId>
    <version>3.0.4</version>
    <type>pom</type>
</dependency>