Database design Document generation tool DB-Document

In order to connect with other data collectors, it is convenient to connect with our database. Developed a tool to generate Excel format database documents for the whole library

Connecting to the database

There is no need to refer to other frameworks that are too heavy to develop widgets. Use your own JDBC links here.

Link to a database to obtain the names of all databases that the user can access.

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);
    // Register the JDBC driver
    Class.forName(JDBC_DRIVER);
    // Open the link
    System.out.println("Connect to database...");
    Connection connection = DriverManager.getConnection(dbUrl, username, password);
    if(connection ! =null) {
         database = showDataBase(connection);
        connection.close();
    }
    return database;
}
Copy the code

Getting the database structure

Gets all table information for the specified database

Define the structure

@Data
public class Table {
    private String tableName;/ / the name of the table
    private String tableComment;// Description of the table
}
Copy the code

Prepare the SQL

SELECT
	table_name,
	Table_comment 
FROM
	information_schema.`TABLES` 
WHERE
	table_schema = '%s'Specifies the database nameCopy the code

Gets the definition information for all columns of the specified table

Define the structure

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

    @ ExcelProperty (" column ")
    @ColumnWidth(20)
    private String columnName;

    @ ExcelProperty (" type ")
    @ColumnWidth(30)
    private String columnType;

    @ExcelProperty(" Empty or not ")
    @ColumnWidth(15)
    private String isNullable;

    @ExcelProperty(" default ")
    @ColumnWidth(10)
    private String columnDefault;

    @ ExcelProperty (" description ")
    @ColumnWidth(50)
    private String columnComment;

}
Copy the code

Prepare the 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'
Copy the code

Generate EXCEL

Once we have the structure of the database, we can install the document in the format we want. Here we generate documents in the most common Excel format.

The first page is the table of contents, and each subsequent page corresponds to a table design

The effect

code

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();
    // Write the first sheet (directory)
    WriteSheet writeSheet = EasyExcel.writerSheet(0.Database Table of Contents).registerWriteHandler(new IndexWriteHandler()).head(Index.class).build();
    excelWriter.write(list, writeSheet);
    // Write the following 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();
}
Copy the code

To add links to directories, you need to define a 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()); }}}Copy the code

Desktop application interface

interface

For ease of use, use Swing to do an interface. Those unfamiliar with Swing can ignore this section.

Using the Designer

code

packagecn.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(a) {
        setTitle("Database Document Generation Tool -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."Failed to generate"."Failed to generate",JOptionPane.ERROR_MESSAGE);
                }

                JOptionPane.showMessageDialog(null."Generated successfully"."Operation successful",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."Link failed"."Link failed",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."Link failed"."Link failed",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(a) {
        // add your code here
        dispose();
    }

    private void onCancel(a) {
        // 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); }}Copy the code

Making portal

Database design Document generation tool DB-Document

Database design Document generation tool DB-Document

Connecting to the database

Getting the database structure

Gets all table information for the specified database

Gets the definition information for all columns of the specified table

Generate EXCEL

Desktop application interface

interface

Using the Designer

code

Related Posts

Instead of an alarm clock, I wake up every day with a self-disciplined cat.

What? Do you know how to Spring? An article takes you through

Application and Operation practice of Elasticsearch in log analysis field