关于自动生成Mysql数据库说明书

唉，又到了项目交付的时候了，你是不是要熬夜补文档？详细设计文档？数据库设计文档？

今天，各位，咱们先拿数据库设计文档入手，如何用程序自动生成这样的文档

其实也很简单了，首先，咱们先连库，然后再解析数据库信息，再组织数据，最后生成word文档就行了，附一下关键代码，有需要的，可以私聊哟

        System.out.println("=============数据数据库信息==============");
        List<TableInfo> tableInfoList = DataBaseUtil.printTableStructure(driver, url, user, password);

        XWPFDocument document = DocUtil.createDocument();

        System.out.println("=============生成文档信息============");
        if (tableInfoList != null) {
            for (TableInfo tableInfo: tableInfoList) {
                DocUtil.writeTable(tableInfo, document);
            }
        }
        System.out.println("=============开始写文档============");
        DocUtil.writeToFile(document, outPath);

package com.wit.tools;

import com.wit.tools.model.ColumnInfo;
import com.wit.tools.model.TableInfo;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class DataBaseUtil {

    public static List<TableInfo> printTableStructure(String driver, String url, String user, String password) throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

        DatabaseMetaData metaData = connection.getMetaData();

        List<TableInfo> tableInfoList = new ArrayList<>();

        int index = 1;

        // 获取所有表
        ResultSet tableResultSet = metaData.getTables(connection.getCatalog(), connection.getSchema(), null, new String[]{"TABLE"});
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            System.out.println("table:" + tableName);


            // 获取表的注释
            String tableRemark = "";
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                tableRemark = parse(createDDL);
            }
            rs.close();

            TableInfo tableInfo = new TableInfo();
            tableInfoList.add(tableInfo);

            tableInfo.setTableName(tableName);
            tableInfo.setIndex(index);
            tableInfo.setInfo(tableRemark);

            List<ColumnInfo> columnInfoList = new ArrayList<>();
            tableInfo.setColList(columnInfoList);

            // 获取主键信息
            Set<String> primaryKeySet = new HashSet<>();
            ResultSet primaryKeys = metaData.getPrimaryKeys(connection.getCatalog(), connection.getSchema(), tableName);
            while (primaryKeys.next()) {
                String columnName = primaryKeys.getString("COLUMN_NAME");
                primaryKeySet.add(columnName);
            }
            // 获取表字段结构
            ResultSet columnResultSet = metaData.getColumns(connection.getCatalog(), connection.getSchema(), tableName, "%");
            while (columnResultSet.next()) {

                ColumnInfo columnInfo = new ColumnInfo();
                // 字段名称
                String columnName = columnResultSet.getString("COLUMN_NAME");
                columnInfo.setColName(columnName);
                // 数据类型
                String columnType = columnResultSet.getString("TYPE_NAME");
                columnInfo.setColType(columnType);
                // 字段长度
                int datasize = columnResultSet.getInt("COLUMN_SIZE");
                columnInfo.setColLength(datasize + "");

                boolean isPrimayKey = primaryKeySet.contains(columnName);
                columnInfo.setPrimayKey(isPrimayKey);

                // 小数部分位数
                int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                // 是否可为空 1代表可空 0代表不可为空
                int nullable = columnResultSet.getInt("NULLABLE");
                columnInfo.setNullable(nullable == 0);
                // 描述
                String remarks = columnResultSet.getString("REMARKS");
                columnInfo.setInfo(remarks);
                columnInfoList.add(columnInfo);
                System.out.println(columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + remarks + " isPrimayKey:" + isPrimayKey);
            }
            System.out.println("=================================");
            index++;
        }
        connection.close();

        return tableInfoList;
    }

    /**
     * 获取表的注释信息
     * @param all
     * @return
     */
    private static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

}

        

package com.wit.tools;

import com.wit.tools.model.ColumnInfo;
import com.wit.tools.model.TableInfo;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.util.StringUtils;

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

public class DocUtil {
//    public static void main(String[] args) {
//
//        try {
//            XWPFDocument docxDocument = new XWPFDocument();
//            //创建第一段落
//            XWPFParagraph firstParagraphX = docxDocument.createParagraph();
//            firstParagraphX.setAlignment(ParagraphAlignment.CENTER);
//
//            XWPFRun runTitle = firstParagraphX.createRun();
//            runTitle.setText("医院报告申请");
//            runTitle.setBold(true);
//            runTitle.setFontSize(24);
//            runTitle.setFontFamily("宋体");
//            runTitle.addCarriageReturn();//回车键
//            runTitle.setKerning(30);
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    public static XWPFDocument createDocument() {
        return new XWPFDocument();
    }

    public static void writeTable(TableInfo tableInfo, XWPFDocument docxDocument) {
        // 先写标题
        writeTitle(tableInfo.getIndex(), tableInfo.getTableName(), tableInfo.getInfo(), docxDocument);

        // 再写行数据
        writeTableColunm(tableInfo.getColList(), docxDocument);
    }

    /**
     * 创建数据库表设计的每个表的标题描述信息，包括序号和tableName,
     * @param index
     * @param tableName
     * @param docxDocument
     */
    private static void writeTitle(Integer index, String tableName, String tableInfo, XWPFDocument docxDocument) {

        //创建第一段落
        XWPFParagraph titlePara = docxDocument.createParagraph();
        titlePara.setAlignment(ParagraphAlignment.LEFT);

        XWPFRun runTitle = titlePara.createRun();
        if (StringUtils.isEmpty(tableInfo)) {
            runTitle.setText(index + "." + tableName);
        } else {
            runTitle.setText(index + "." + tableName + "-" + tableInfo);
        }
        runTitle.setBold(true);
        runTitle.setFontSize(20);
        runTitle.setFontFamily("宋体");
        runTitle.addCarriageReturn();//回车键
    }


    private static void writeTableColunm(List<ColumnInfo> colList, XWPFDocument docxDocument) {

        XWPFTable table = docxDocument.createTable(colList.size() + 1,6);
        table.setWidth("95%");
        table.setWidthType(TableWidthType.PCT);//设置表格相对宽度
        table.setTableAlignment(TableRowAlign.CENTER);

        //获取第一个表格
        List<XWPFTableRow> rows = table.getRows();

        XWPFTableRow titleRow = rows.get(0);

        List<XWPFTableCell> tableCells = titleRow.getTableCells();


        writeColumnContent(tableCells.get(0), "字段名", ParagraphAlignment.CENTER, "B0C4DE");
        writeColumnContent(tableCells.get(1), "字段类型", ParagraphAlignment.CENTER, "B0C4DE");
        writeColumnContent(tableCells.get(2), "长度", ParagraphAlignment.CENTER, "B0C4DE");
        writeColumnContent(tableCells.get(3), "不为Null", ParagraphAlignment.CENTER, "B0C4DE");
        writeColumnContent(tableCells.get(4), "主键", ParagraphAlignment.CENTER, "B0C4DE");
        writeColumnContent(tableCells.get(5), "描述", ParagraphAlignment.CENTER, "B0C4DE");


        // 写入列行
        for (int i = 0; i < colList.size(); i++) {

            ColumnInfo columnInfo = colList.get(i);

            XWPFTableRow colRow = rows.get(i + 1);
            List<XWPFTableCell> cells = colRow.getTableCells();

            writeColumnContent(cells.get(0), columnInfo.getColName(), ParagraphAlignment.CENTER, null);
            writeColumnContent(cells.get(1), columnInfo.getColType(), ParagraphAlignment.CENTER, null);
            writeColumnContent(cells.get(2), columnInfo.getColLength(), ParagraphAlignment.CENTER, null);
            writeColumnContent(cells.get(3), columnInfo.isNullable()?"Y": "", ParagraphAlignment.CENTER, null);
            writeColumnContent(cells.get(4), columnInfo.isPrimayKey()?"Y": "", ParagraphAlignment.CENTER, null);
            writeColumnContent(cells.get(5), columnInfo.getInfo(), ParagraphAlignment.LEFT, null);
        }
    }

    private static void writeColumnContent(XWPFTableCell cell, String content, ParagraphAlignment alignment, String fillColor) {
        XWPFParagraph cellParagraph = cell.getParagraphArray(0);
        cellParagraph.setAlignment(alignment);
        XWPFRun titleFun = cellParagraph.createRun() ;
        titleFun.setText(content);
        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        if (!StringUtils.isEmpty(fillColor)) {
            cell.getCTTc().addNewTcPr().addNewShd().setFill(fillColor);
        }
    }


    /**
     * 写入文档
     * @param docxDocument
     * @param path
     * @throws Exception
     */
    public static void writeToFile(XWPFDocument docxDocument, String path) throws Exception{
        File file = new File(path);
        FileOutputStream stream = new FileOutputStream(file);
        docxDocument.write(stream);
        stream.close();
        System.out.println("文件生成完成!");
    }
}

最后，，咱们附图一张，看看自动生成的效果。