java实现mysql数据库表字段信息导出

java实现导出mysql数据库表字段信息导出，导出信息包含：数据库用户名，表英文名，字段英文名，字段中文名，字段业务描述，字段类型（字段长度），是否主键，是否必填，是否敏感字段

package src.main.biz.ucenter.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBSchemaToExcelExporter {

    public static void main(String[] args) {
        //String jdbcUrl = "jdbc:mysql://192.168.6.210:3306/ucenter_231227?serverTimezone=CTT&useUnicode=true&characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true";
        String jdbcUrl = "jdbc:mysql://192.2.6.119:3306/ghe_testbranch_guowang_report?serverTimezone=CTT&useUnicode=true&characterEncoding=UTF-8&useSSL=false&rewriteBatchedStatements=true";
        String username = "wasdfsaf";
        String password = "123safds";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Workbook workbook = new XSSFWorkbook()) {

            Sheet sheet = workbook.createSheet("Database Schema");

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            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("是否敏感字段");
            headerRow.createCell(9).setCellValue("是否参考数据");
            headerRow.createCell(10).setCellValue("参考数据映射关系");

            DatabaseMetaData dbmd = conn.getMetaData();
            ResultSet tablesRs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});

            while (tablesRs.next()) {
                String tableName = tablesRs.getString("TABLE_NAME");
                ResultSet columnsRs = dbmd.getColumns(null, null, tableName, "%");

                while (columnsRs.next()) {
                    Row dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                    dataRow.createCell(0).setCellValue(username);
                    dataRow.createCell(1).setCellValue(tableName);
                    dataRow.createCell(2).setCellValue(columnsRs.getString("COLUMN_NAME"));
                    dataRow.createCell(3).setCellValue(getColumnChineseName(columnsRs));
                    dataRow.createCell(4).setCellValue(getColumnChineseName(columnsRs));
                    dataRow.createCell(5).setCellValue(buildColumnTypeWithLength(columnsRs));
                    dataRow.createCell(6).setCellValue(isPrimaryKey(columnsRs) ? "是" : "否");
                    dataRow.createCell(7).setCellValue(isNotNullField(columnsRs) ? "是" : "否");
                    dataRow.createCell(8).setCellValue(isSensitiveField(columnsRs) ? "是" : "否");
                    dataRow.createCell(9).setCellValue(getIsEnum(columnsRs));
                    dataRow.createCell(10).setCellValue(getEnum(columnsRs));
                }

                columnsRs.close();
            }

            tablesRs.close();

            try (FileOutputStream outputStream = new FileOutputStream("F:\\file\\report.xlsx")) {
                workbook.write(outputStream);
            }

        } catch (SQLException | IOException e) {
            System.err.println("Error exporting database schema to Excel: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static String buildColumnTypeWithLength(ResultSet columnsRs) throws SQLException {
        String typeName = columnsRs.getString("TYPE_NAME");
        int columnSize = columnsRs.getInt("COLUMN_SIZE");
        int decimalDigits = columnsRs.getInt("DECIMAL_DIGITS");
        if (typeName.equalsIgnoreCase("VARCHAR") || typeName.startsWith("CHAR")) {
            return typeName + "(" + columnSize + ")";
        } else if (typeName.equalsIgnoreCase("DECIMAL") || typeName.equalsIgnoreCase("NUMERIC")) {
            return typeName + "(" + columnSize + ", " + decimalDigits + ")";
        } else {
            // 对于INT和BIGINT等整数类型，直接返回类型名加上可能存在的COLUMN_SIZE值
            return typeName + "(" + columnSize + ")";
        }
    }

    private static boolean isNotNullField(ResultSet columnsRs) throws SQLException {
        return !columnsRs.getBoolean("IS_NULLABLE");
    }

    private static boolean isPrimaryKey(ResultSet columnsRs) throws SQLException {
        //判断是否为phid
        boolean falg = false;
        String columnName = columnsRs.getString("COLUMN_NAME");
        if("phid".equals(columnName.toLowerCase())){
            falg = true;
        }
        return falg;
    }

    private static boolean isSensitiveField(ResultSet columnsRs) throws SQLException {
        String columnName = columnsRs.getString("COLUMN_NAME");
        return columnName.contains("phone") || columnName.contains("mobile") || columnName.contains("idcard") || columnName.contains("identity");
    }

    // 假设存在一个方法从元数据或自定义映射中获取字段中文名
    private static String getColumnChineseName(ResultSet columnsRs) throws SQLException {
        // 实现该方法，此处仅为占位符
        return columnsRs.getString("REMARKS"); // MySQL中的COMMENT字段通常映射为REMARKS
    }

    private static String getEnum(ResultSet columnsRs) throws SQLException {

        String remarks = columnsRs.getString("REMARKS");
        if(containsNumber(remarks)){
            return remarks;
        }
        return "";

    }

    private static String getIsEnum(ResultSet columnsRs) throws SQLException {
        String falg = "否";
        String remarks = columnsRs.getString("REMARKS");
        if(containsNumber(remarks)){
            falg = "是";
        }
        return falg;

    }

    private static boolean containsNumber(String remarks) {
        Pattern pattern = Pattern.compile("\\d+");
        Matcher matcher = pattern.matcher(remarks);
        return matcher.find();
    }
}