数据库设计文档生成工具DB-Document

为了对接其它数据采集方，对接我们的数据库方便。开发了一个对整库生成Excel格式的数据库文档的工具

连接数据库

开发小工具程序，在此不需要引用过重的其它框架。在此使用最自己的JDBC链接。

链接数据库，获取该用户权限能访问的所有数据库名称。

public static List<String> testConnection(String ip, String port, String username, String password) throws ClassNotFoundException, SQLException {
    List<String> database = new ArrayList<String>();
    String dbUrl = String.format("jdbc:mysql://%s:%s?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", ip, port);
    // 注册 JDBC 驱动
    Class.forName(JDBC_DRIVER);
    // 打开链接
    System.out.println("连接数据库...");
    Connection connection = DriverManager.getConnection(dbUrl, username, password);
    if (connection != null) {
         database = showDataBase(connection);
        connection.close();
    }
    return database;
}
复制代码

获取数据库结构

获取指定数据库的所有表信息

定义结构

@Data
public class Table {
    private String tableName;//表名
    private String tableComment;//表的描述信息
}
复制代码

准备sql

SELECT
	table_name,
	Table_comment 
FROM
	information_schema.`TABLES` 
WHERE
	table_schema = '%s'  #指定数据库名称
复制代码

获取指定表格的所有列的定义信息

定义结构

@Data
public class Column {
    @ExcelProperty("序号")
    @ColumnWidth(10)
    private String ordinalPosition;

    @ExcelProperty("列名")
    @ColumnWidth(20)
    private String columnName;

    @ExcelProperty("类型")
    @ColumnWidth(30)
    private String columnType;

    @ExcelProperty("是否为空")
    @ColumnWidth(15)
    private String isNullable;

    @ExcelProperty("默认值")
    @ColumnWidth(10)
    private String columnDefault;

    @ExcelProperty("描述")
    @ColumnWidth(50)
    private String columnComment;

}
复制代码

准备sql

SELECT
	ordinal_position,
	column_name,
	is_nullable,
	column_type,
	column_default,
	column_comment 
FROM
	information_schema.`COLUMNS` 
WHERE
	table_schema = '%s' 
	AND table_name = '%s'
复制代码

生成EXCEL

当我们拿到数据库的结构之后，就可以安装我们想要的格式生成文档了。这里我们生成最常见的Excel格式的文档。

第一页为目录，后面每一页对应一个表的设计

效果

表格.png

代码

public void buildExcel(String path, String database, List<Table> tables) {
    File file = new File(path + "/" + database + ".xls");
    List<Index> list = new ArrayList<Index>();
    for (Table table : tables) {
        Index index = new Index();
        index.setTableName(table.getTableName());
        index.setTableComment(table.getTableComment());
        list.add(index);
    }
    ExcelWriter excelWriter = EasyExcel.write(file).build();
    //写入第一个sheet页（为目录）
    WriteSheet writeSheet = EasyExcel.writerSheet(0, "数据库表格目录").registerWriteHandler(new IndexWriteHandler()).head(Index.class).build();
    excelWriter.write(list, writeSheet);
    //写入后续的sheet页
    for (int i = 0; i < tables.size(); i++) {
        Table table = tables.get(i);
        String sheetName = table.getTableName();
        WriteSheet writeSheetTemp = EasyExcel.writerSheet(i + 1, sheetName).head(Column.class).build();
        excelWriter.write(table.getColumnList(), writeSheetTemp);
    }
    excelWriter.finish();
}
复制代码

为了给目录添加链接，需要定义一个WriteHandler

public class IndexWriteHandler implements CellWriteHandler {
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }

    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }

    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        if (cell.getRowIndex() >= 1 && cell.getColumnIndex() == 0) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CreationHelper helper = workbook.getCreationHelper();
            Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.DOCUMENT);
            hyperlink.setAddress(cell.getStringCellValue());
            CellStyle link_style = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setUnderline(Font.U_SINGLE);
            font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            link_style.setFont(font);
            cell.setHyperlink(hyperlink);
            cell.setCellStyle(link_style);
            System.out.println(cell.getStringCellValue());
        }

    }
}
复制代码

桌面程序界面

界面

为了方便使用，用Swing做了一个界面。不熟悉Swing的可以忽略这部分。

使用设计器

工程.png

代码

package cn.jogeen.dbdocument.ui;

![UI.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/eab1766962dc4007b4b12f031c096184~tplv-k3u1fbpfcp-watermark.image)
import cn.jogeen.dbdocument.excel.ExcelService;
import cn.jogeen.dbdocument.jdbc.connection.ConnectionUtils;
import cn.jogeen.dbdocument.jdbc.dao.DataBaseDao;
import cn.jogeen.dbdocument.jdbc.dao.DataBaseDaoImpl;
import cn.jogeen.dbdocument.jdbc.model.Column;
import cn.jogeen.dbdocument.jdbc.model.Table;

import javax.swing.*;
import java.awt.event.*;
import java.io.File;
import java.util.List;

/**
 * @Autor  jogeen
 */
public class MainDialog extends JDialog {
    private JPanel contentPane;
    private JButton buttonOK;
    private JButton buttonCancel;
    private JTextField t_address;
    private JTextField t_port;
    private JTextField t_username;
    private JTextField t_password;
    private JButton connect_btn;
    private JList database_list;
    private JButton choose_btn;
    private JLabel path_label;

    private JFileChooser jfc = new JFileChooser(new File("C:\\"));

    MainDialog mainDialog;


    String address;
    String port;
    String username;
    String password;
    String database;

    public MainDialog() {
        setTitle("数据库文档生成工具-JoGeen");
        setContentPane(contentPane);
        setModal(true);
        getRootPane().setDefaultButton(buttonOK);
        mainDialog=this;
        buttonOK.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                database = (String) database_list.getSelectedValue();
                DataBaseDao dataBaseDao = new DataBaseDaoImpl(address, port, database, username, password);
                List<Table> tablse = dataBaseDao.showTables(database);
                for (Table table : tablse) {
                    List<Column> columns = dataBaseDao.showColumns(database, table.getTableName());
                    table.setColumnList(columns);
                }
                dataBaseDao.closeConnection();
                ExcelService excelService = new ExcelService();
                try{
                    excelService.buildExcel(path_label.getText(), database, tablse);
                }catch (Exception e1){
                    JOptionPane.showMessageDialog(null,"生成失败","生成失败",JOptionPane.ERROR_MESSAGE);
                }

                JOptionPane.showMessageDialog(null,"生成成功","操作成功",JOptionPane.INFORMATION_MESSAGE);
            }
        });

        buttonCancel.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                onCancel();
            }
        });

        // call onCancel() when cross is clicked
        setDefaultCloseOperation(DO_NOTHING_ON_CLOSE);
        addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e) {
                onCancel();
            }
        });

        // call onCancel() on ESCAPE
        contentPane.registerKeyboardAction(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                onCancel();
            }
        }, KeyStroke.getKeyStroke(KeyEvent.VK_ESCAPE, 0), JComponent.WHEN_ANCESTOR_OF_FOCUSED_COMPONENT);
        connect_btn.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                address = t_address.getText();
                port = t_port.getText();
                username = t_username.getText();
                password = t_password.getText();
                try {
                    List<String> databaseNames = ConnectionUtils.testConnection(address, port, username, password);
                    if (databaseNames.isEmpty()) {
                        JOptionPane.showMessageDialog(null,"链接失败","链接失败",JOptionPane.WARNING_MESSAGE);
                    }

                    DefaultListModel<String> listModel = new DefaultListModel<String>();
                    for (String databaseName : databaseNames) {
                        listModel.addElement(databaseName);
                    }

                    database_list.setModel(listModel);
                } catch (Exception e1) {
                    JOptionPane.showMessageDialog(null,"链接失败","链接失败",JOptionPane.WARNING_MESSAGE);
                }
            }
        });
        choose_btn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                jfc.setFileSelectionMode(1);
                int i = jfc.showOpenDialog(null);
                if(i==1){
                    return;
                }else{
                    File selectedFile = jfc.getSelectedFile();
                    path_label.setText(selectedFile.getAbsolutePath());
                }
            }
        });
    }

    private void onOK() {
        // add your code here
        dispose();
    }

    private void onCancel() {
        // add your code here if necessary
        dispose();
    }

    public static void main(String[] args) {
        MainDialog dialog = new MainDialog();
        dialog.pack();
        dialog.setVisible(true);
        System.exit(0);
    }
}
复制代码