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 codeGetting 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 codeGets 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 codeGenerate 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 codeTo 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 codeDesktop 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 codeMaking portal