package com.rongyichuang.tools;
|
|
import org.springframework.boot.CommandLineRunner;
|
import org.springframework.stereotype.Component;
|
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* 数据库表结构导出工具
|
*/
|
@Component
|
public class DatabaseSchemaExporter implements CommandLineRunner {
|
|
private static final String URL = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
|
private static final String USERNAME = "ryc";
|
private static final String PASSWORD = "KiYap3E8X8RLcM6T";
|
private static final String DATABASE_NAME = "ryc";
|
|
@Override
|
public void run(String... args) throws Exception {
|
if (args.length > 0 && "export-schema".equals(args[0])) {
|
exportDatabaseSchema();
|
}
|
}
|
|
public void exportDatabaseSchema() {
|
System.out.println("🔄 开始导出数据库表结构...");
|
|
try (Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
|
StringBuilder sqlBuilder = new StringBuilder();
|
sqlBuilder.append("-- Database schema dump for ryc\n");
|
sqlBuilder.append("-- Generated at: ").append(new java.util.Date()).append("\n\n");
|
|
// 获取所有表名
|
List<String> tableNames = getAllTableNames(conn);
|
|
for (String tableName : tableNames) {
|
System.out.println("📋 导出表: " + tableName);
|
String createTableSql = getCreateTableStatement(conn, tableName);
|
sqlBuilder.append(createTableSql).append("\n\n");
|
}
|
|
// 写入文件
|
String outputPath = "d:\\code\\new-ryc\\db_actual.sql";
|
try (FileWriter writer = new FileWriter(outputPath)) {
|
writer.write(sqlBuilder.toString());
|
}
|
|
System.out.println("✅ 数据库表结构导出完成: " + outputPath);
|
|
// 特别检查t_employee表的结构
|
checkEmployeeTableStructure(conn);
|
|
} catch (Exception e) {
|
System.err.println("❌ 导出数据库表结构失败: " + e.getMessage());
|
e.printStackTrace();
|
}
|
}
|
|
private List<String> getAllTableNames(Connection conn) throws SQLException {
|
List<String> tableNames = new ArrayList<>();
|
DatabaseMetaData metaData = conn.getMetaData();
|
|
try (ResultSet tables = metaData.getTables(DATABASE_NAME, null, "%", new String[]{"TABLE"})) {
|
while (tables.next()) {
|
String tableName = tables.getString("TABLE_NAME");
|
if (tableName.startsWith("t_")) { // 只导出业务表
|
tableNames.add(tableName);
|
}
|
}
|
}
|
|
return tableNames;
|
}
|
|
private String getCreateTableStatement(Connection conn, String tableName) throws SQLException {
|
try (Statement stmt = conn.createStatement();
|
ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName)) {
|
|
if (rs.next()) {
|
String createSql = rs.getString(2);
|
return "-- Table: " + tableName + "\n" + createSql + ";";
|
}
|
}
|
return "";
|
}
|
|
private void checkEmployeeTableStructure(Connection conn) throws SQLException {
|
System.out.println("\n🔍 检查t_employee表结构:");
|
|
DatabaseMetaData metaData = conn.getMetaData();
|
try (ResultSet columns = metaData.getColumns(DATABASE_NAME, null, "t_employee", null)) {
|
while (columns.next()) {
|
String columnName = columns.getString("COLUMN_NAME");
|
String dataType = columns.getString("TYPE_NAME");
|
int columnSize = columns.getInt("COLUMN_SIZE");
|
String isNullable = columns.getString("IS_NULLABLE");
|
String defaultValue = columns.getString("COLUMN_DEF");
|
|
System.out.printf(" 📝 %s: %s(%d) %s %s%n",
|
columnName,
|
dataType,
|
columnSize,
|
"YES".equals(isNullable) ? "NULL" : "NOT NULL",
|
defaultValue != null ? "DEFAULT " + defaultValue : ""
|
);
|
}
|
}
|
}
|
}
|
