package org.example;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.poi.ss.formula.functions.Column;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import javax.annotation.PostConstruct;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@SpringBootApplication
public class DatabaseExportApplication {
public static void main(String[] args) {
SpringApplication.run(DatabaseExportApplication.class, args);
}
@PostConstruct
public void exportDatabaseTables() throws IOException {
String databaseName = "XX_XXXX";
String outputFilePath = "./tables.xlsx";
List<String> tables = getTables(databaseName);
Workbook workbook = new XSSFWorkbook();
try (Connection connection = getConnection()) { // 获取数据库连接
for (String tableName : tables) {
String tableDefinition = getTableDefinition(connection, databaseName, tableName);
List<FieldDetails> columns = getColumns(connection, databaseName, tableName);
Sheet sheet = workbook.createSheet(tableName);
// Create table definition row
Row definitionRow = sheet.createRow(0);
definitionRow.createCell(0).setCellValue("Table Definition");
definitionRow.createCell(1).setCellValue(tableDefinition);
// Create header row for column names
// Create header row for column names
Row headerRow = sheet.createRow(1);
headerRow.createCell(0).setCellValue("列名称");
headerRow.createCell(1).setCellValue("数据类型");
headerRow.createCell(2).setCellValue("大小");
headerRow.createCell(3).setCellValue("小数位数");
headerRow.createCell(4).setCellValue("默认值");
headerRow.createCell(5).setCellValue("是否为空");
headerRow.createCell(6).setCellValue("是否主键");
headerRow.createCell(7).setCellValue("是否自增");
headerRow.createCell(8).setCellValue("备注");
// Write column details
int rowIndex = 2;
for (FieldDetails column : columns) {
Row row = sheet.createRow(rowIndex++);
row.createCell(0).setCellValue(column.getColumnName());
row.createCell(1).setCellValue(column.getDataType());
row.createCell(2).setCellValue(column.getColumnSize());
row.createCell(3).setCellValue(column.getDecimalDigits());
row.createCell(4).setCellValue(column.getColumnDef());
row.createCell(5).setCellValue(column.getNullable());
row.createCell(6).setCellValue(column.getPkName());
row.createCell(7).setCellValue(column.getIsAutoincrement());
row.createCell(8).setCellValue(column.getRemarks());
}
// Adjust column width
for (int i = 0; i < 4; i++) {
sheet.autoSizeColumn(i);
}
}
} // 在此处自动关闭连接
catch (SQLException e) {
throw new RuntimeException(e);
} catch (Exception e) {
throw new RuntimeException(e);
}
try (FileOutputStream outputStream = new FileOutputStream(outputFilePath)) {
workbook.write(outputStream);
}
workbook.close();
}
private String getTableDefinition(Connection connection, String databaseName, String tableName) {
String tableDefinition = "";
try (ResultSet resultSet = connection.getMetaData().getTables(databaseName, null, tableName, null)) {
if (resultSet.next()) {
tableDefinition = resultSet.getString("REMARKS");
}
} catch (Exception e) {
e.printStackTrace();
}
return tableDefinition;
}
private List<FieldDetails> getColumns(Connection connection, String databaseName, String tableName) {
List<FieldDetails> columns = new ArrayList<>();
try (ResultSet resultSet = connection.getMetaData().getColumns(databaseName, null, tableName, null)) {
while (resultSet.next()) {
// 列名称
String columnName = resultSet.getString("COLUMN_NAME");
// 列的数据类型名称
String dataType = resultSet.getString("TYPE_NAME");
// 列的大小（长度/精度）
int columnSize = resultSet.getInt("COLUMN_SIZE");
// 小数位数
int decimalDigits = resultSet.getInt("DECIMAL_DIGITS");
// 列的默认值
String columnDef = resultSet.getString("COLUMN_DEF");
// 列是否可为空
Boolean isNullable = resultSet.getBoolean("IS_NULLABLE");
// 主键
String pkName ="";
// 处理获得的主键
DatabaseMetaData metaData = connection.getMetaData();
ResultSet primaryKeyResultSet = metaData.getPrimaryKeys(databaseName, null, tableName);
while (primaryKeyResultSet.next()) {
pkName = primaryKeyResultSet.getString("COLUMN_NAME");
pkName = pkName.equals(columnName)?"主键":"";
}
// 是否自增
String isAutoincrement = resultSet.getString("IS_AUTOINCREMENT");
// 列的注释说明
String remarks = resultSet.getString("REMARKS");
FieldDetails fieldDetails = new FieldDetails(columnName,dataType,columnSize,decimalDigits,columnDef,isNullable,pkName,isAutoincrement,remarks);
columns.add(fieldDetails);
}
} catch (Exception e) {
e.printStackTrace();
}
return columns;
}
private List<String> getTables(String databaseName) {
List<String> tables = new ArrayList<>();
try (Connection connection = getConnection();
ResultSet resultSet = connection.getMetaData().getTables(databaseName, null, null, new String[]{"TABLE"})) {
while (resultSet.next()) {
String tableName = resultSet.getString("TABLE_NAME");
tables.add(tableName);
}
} catch (Exception e) {
e.printStackTrace();
}
return tables;
}
private Connection getConnection() throws Exception {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://XX.XX.XX.XX:3306/XX_XXXX");
config.setUsername("root");
config.setPassword("123456");
config.setDriverClassName("com.mysql.cj.jdbc.Driver");
config.setMaximumPoolSize(5); // 增加最大连接数
HikariDataSource dataSource = new HikariDataSource(config);
return dataSource.getConnection();
}
private static class FieldDetails {
// 列名称
private String columnName;
// 列的数据类型名称
private String dataType;
// 列的大小（长度/精度）
private int columnSize;
// 小数位数
private int decimalDigits;
// 列的默认值
private String column
