MySQL 表结构导出工具应用与分析

package org.jeecgframework.boot.myexportdbstructure;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class MysqlExporterToExcel extends JFrame {
private JTextField hostField;
private JTextField userField;
private JPasswordField passwordField;
private JTextField databaseField;
private JTextField outputFileField;

public MysqlExporterToExcel() {
setTitle(“MySQL 表结构导出工具”);
setSize(600, 400);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setLocationRelativeTo(null);

JPanel panel = new JPanel();
panel.setLayout(new GridLayout(6, 2));

JLabel hostLabel = new JLabel(“主机:”);
hostField = new JTextField(“12.45.67.89:3306”);
JLabel userLabel = new JLabel(“用户名:”);
userField = new JTextField(“root”);
JLabel passwordLabel = new JLabel(“密码:”);
passwordField = new JPasswordField(“123456”);
JLabel databaseLabel = new JLabel(“数据库:”);
databaseField = new JTextField(“crm_cloud_ai”);
String defaultPath = “/Users/fsdownload/数据库设计.xlsx”;

JLabel outputFileLabel = new JLabel(“输出文件:”);
outputFileField = new JTextField(defaultPath);
JButton exportButton = new JButton(“导出”);

panel.add(hostLabel);
panel.add(hostField);
panel.add(userLabel);
panel.add(userField);
panel.add(passwordLabel);
panel.add(passwordField);
panel.add(databaseLabel);
panel.add(databaseField);
panel.add(outputFileLabel);
panel.add(outputFileField);
panel.add(new JLabel());
panel.add(exportButton);

add(panel);

exportButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String host = hostField.getText();
String user = userField.getText();
String password = new String(passwordField.getPassword());
String database = databaseField.getText();
String outputFile = outputFileField.getText();

if (host.isEmpty() || user.isEmpty() || database.isEmpty() || outputFile.isEmpty()) {
JOptionPane.showMessageDialog(MysqlExporterToExcel.this,
“请确保主机、用户名、数据库和输出文件都已填写”, “输入错误”, JOptionPane.ERROR_MESSAGE);
return;
}

File file = new File(outputFile);
File parentDir = file.getParentFile();
if (!parentDir.exists()) {
if (!parentDir.mkdirs()) {
JOptionPane.showMessageDialog(MysqlExporterToExcel.this,
“无法创建目录: ” + parentDir.getAbsolutePath(), “目录创建失败”, JOptionPane.ERROR_MESSAGE);
return;
}
}

exportTableStructure(host, user, password, database, outputFile);
}
});
}

private void exportTableStructure(String host, String user, String password, String database, String outputFile) {
String url = “jdbc:mysql://” + host + “/” + database + “?useInformationSchema=true”;

try (Connection connection = DriverManager.getConnection(url, user, password);
Workbook workbook = new XSSFWorkbook()) {

// 创建样式
// 1. 总表头样式（大标题）
CellStyle titleStyle = workbook.createCellStyle();
Font titleFont = workbook.createFont();
titleFont.setBold(true);
titleFont.setFontHeightInPoints((short) 16);
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

// 2. 表名行样式
CellStyle boldStyle = workbook.createCellStyle();
Font boldFont = workbook.createFont();
boldFont.setBold(true);
boldFont.setFontHeightInPoints((short) 12);
boldStyle.setFont(boldFont);

// 3. 表头行样式（带边框）
CellStyle headerBorderStyle = workbook.createCellStyle();
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerBorderStyle.setFont(headerFont);
headerBorderStyle.setBorderTop(BorderStyle.THIN);
headerBorderStyle.setBorderBottom(BorderStyle.THIN);
headerBorderStyle.setBorderLeft(BorderStyle.THIN);
headerBorderStyle.setBorderRight(BorderStyle.THIN);
headerBorderStyle.setAlignment(HorizontalAlignment.CENTER);

// 4. 数据行样式（带边框）
CellStyle borderStyle = workbook.createCellStyle();
borderStyle.setBorderTop(BorderStyle.THIN);
borderStyle.setBorderBottom(BorderStyle.THIN);
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THIN);

if (connection.isValid(5)) {
Sheet sheet = workbook.createSheet(“表结构”);
int rowNum = 0;
String[] headers = {“序号”, “名称”, “类型”, “是否为空”, “长度”, “主键”, “索引”, “说明”};

// 添加总表头 “工单表结构”
Row titleRow = sheet.createRow(rowNum++);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue(“库表设计”);
titleCell.setCellStyle(titleStyle);

// 合并总表头单元格（A到I列）
sheet.addMergedRegion(new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
0,
headers.length – 1
));

// 设置总表头行高度
titleRow.setHeightInPoints(30);

// 在总表头下方添加一行空行
rowNum++;

String tableQuery = “SELECT TABLE_NAME, TABLE_COMMENT ” +
“FROM INFORMATION_SCHEMA.TABLES ” +
“WHERE TABLE_SCHEMA = ? ” +
“AND TABLE_TYPE = ‘BASE TABLE'”;
try (PreparedStatement tableStmt = connection.prepareStatement(tableQuery)) {
tableStmt.setString(1, database);
ResultSet tables = tableStmt.executeQuery();

while (tables.next()) {
String tableName = tables.getString(“TABLE_NAME”);
String tableComment = tables.getString(“TABLE_COMMENT”);

// 1. 创建表名行（不加边框）
Row tableNameRow = sheet.createRow(rowNum++);
Cell tableNameCell = tableNameRow.createCell(0);
String tableDisplay = “表名：” + tableName;
if (tableComment != null && !tableComment.isEmpty()) {
tableDisplay += “（” + tableComment + “）”;
}
tableNameCell.setCellValue(tableDisplay);
tableNameCell.setCellStyle(boldStyle);

// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(
tableNameRow.getRowNum(),
tableNameRow.getRowNum(),
0,
headers.length – 1
));

// 2. 创建表头行（带边框）
Row headerRow = sheet.createRow(rowNum++);
for (int col = 0; col < headers.length; col++) {
Cell cell = headerRow.createCell(col);
cell.setCellValue(headers[col]);
cell.setCellStyle(headerBorderStyle);
}

// 3. 获取字段详细信息
String columnQuery =
“SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, ” +
” CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_KEY, ” +
” EXTRA, COLUMN_COMMENT ” +
“FROM INFORMATION_SCHEMA.COLUMNS ” +
“WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? ” +
“ORDER BY ORDINAL_POSITION”;

try (PreparedStatement columnStmt = connection.prepareStatement(columnQuery)) {
columnStmt.setString(1, database);
columnStmt.setString(2, tableName);
ResultSet columns = columnStmt.executeQuery();

int serialNumber = 1;
while (columns.next()) {
String columnName = columns.getString(“COLUMN_NAME”);
String columnType = columns.getString(“COLUMN_TYPE”);
String isNullable = columns.getString(“IS_NULLABLE”);
String columnKey = columns.getString(“COLUMN_KEY”);
String extra = columns.getString(“EXTRA”);
String columnComment = columns.getString(“COLUMN_COMMENT”);

// 处理长度信息
int length = 0;
Long charLength = columns.getObject(“CHARACTER_MAXIMUM_LENGTH”, Long.class);
if (charLength != null) {
length = charLength.intValue();
} else if (columnType.contains(“(“)) {
try {
String sizePart = columnType.substring(
columnType.indexOf(‘(‘) + 1,
columnType.indexOf(‘)’)
).split(“,”)[0];
length = Integer.parseInt(sizePart);
} catch (Exception ignored) {}
}

// 确定主键和索引
String isPrimary = “PRI”.equals(columnKey) ? “是” : “否”;
String isIndex = (columnKey != null && !columnKey.isEmpty()) ? “是” : “否”;

// 创建数据行并添加边框
Row dataRow = sheet.createRow(rowNum++);
for (int col = 0; col < headers.length; col++) {
Cell cell = dataRow.createCell(col);
cell.setCellStyle(borderStyle);
}

// 填充数据
dataRow.getCell(0).setCellValue(serialNumber++);
dataRow.getCell(1).setCellValue(columnName);
dataRow.getCell(2).setCellValue(columnType);
dataRow.getCell(3).setCellValue(“YES”.equalsIgnoreCase(isNullable) ? “是” : “否”);
dataRow.getCell(4).setCellValue(length);
dataRow.getCell(5).setCellValue(isPrimary);
dataRow.getCell(6).setCellValue(isIndex);
dataRow.getCell(7).setCellValue(columnComment != null ? columnComment : “”);
}
}

// 4. 添加三行空白分隔行（不加边框）
for (int i = 0; i < 3; i++) {
sheet.createRow(rowNum++);
}
}
}

// 自动调整列宽
for (int col = 0; col < headers.length; col++) {
sheet.autoSizeColumn(col);
}

// 保存文件
try (FileOutputStream fileOut = new FileOutputStream(outputFile)) {
workbook.write(fileOut);
JOptionPane.showMessageDialog(this,
“表结构已成功导出到: ” + outputFile,
“导出成功”,
JOptionPane.INFORMATION_MESSAGE);
}
} else {
JOptionPane.showMessageDialog(this, “无法连接到数据库”, “连接错误”, JOptionPane.ERROR_MESSAGE);
}
} catch (SQLException sqlEx) {
if (sqlEx.getSQLState().startsWith(“28”)) {
JOptionPane.showMessageDialog(this, “用户名或密码错误”, “认证错误”, JOptionPane.ERROR_MESSAGE);
} else if (sqlEx.getSQLState().startsWith(“08”)) {
JOptionPane.showMessageDialog(this, “无法连接到数据库，请检查主机和端口”, “连接错误”, JOptionPane.ERROR_MESSAGE);
} else {
JOptionPane.showMessageDialog(this,
“数据库操作失败: ” + sqlEx.getMessage(),
“数据库错误”,
JOptionPane.ERROR_MESSAGE);
sqlEx.printStackTrace();
}
} catch (IOException ioEx) {
JOptionPane.showMessageDialog(this,
“文件写入失败: ” + ioEx.getMessage(),
“文件错误”,
JOptionPane.ERROR_MESSAGE);
ioEx.printStackTrace();
}
}

public static void main(String[] args) {
SwingUtilities.invokeLater(new Runnable() {
@Override
public void run() {
MysqlExporterToExcel exporter = new MysqlExporterToExcel();
exporter.setVisible(true);
}
});
}
}