Java实现MySQL表结构导出_java 导出mysql表结构设计文档

Java中连接MySQL数据库并将所有表的结构输出到Excel文件中，每个表的结构放在单独的Sheet页中，并以表名命名Sheet页。基于Apache POI 轻松生成一个包含所有表结构的Excel文件，非常适合数据库文档生成和审查。

引入依赖

<dependencies>
    <!-- MySQL JDBC驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.32</version>
    </dependency>
    <!-- Apache POI 用于操作Excel文件 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    <!-- Apache POI 用于操作Word文件 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!-- XMLBeans依赖 -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>5.1.1</version>
    </dependency>
</dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

连接MySQL并获取表结构,写入文件

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;

public class ExportDatabaseStructure {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/database";
        String username = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String[] tables = getTables(conn);
            exportToExcel(conn, tables);
            exportToWord(conn, tables);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String[] getTables(Connection conn) throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW TABLES");

        List<String> tables = new java.util.ArrayList<>();
        while (rs.next()) {
            tables.add(rs.getString(1));
        }
        return tables.toArray(new String[0]);
    }

    private static void exportToExcel(Connection conn, String[] tables) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        
        for (String table : tables) {
            Sheet sheet = workbook.createSheet(table);
            exportTableStructure(conn, table, (tableName, rs) -> {
                Row header = sheet.createRow(0);
                String[] headers = {"Column", "Type", "Null", "Key", "Default", "Extra"};
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = header.createCell(i);
                    cell.setCellValue(headers[i]);
                }

                int rowIndex = 1;
                while (rs.next()) {
                    Row row = sheet.createRow(rowIndex++);
                    row.createCell(0).setCellValue(rs.getString("Field"));
                    row.createCell(1).setCellValue(rs.getString("Type"));
                    row.createCell(2).setCellValue(rs.getString("Null"));
                    row.createCell(3).setCellValue(rs.getString("Key"));
                    row.createCell(4).setCellValue(rs.getString("Default"));
                    row.createCell(5).setCellValue(rs.getString("Extra"));
                }
            });
        }

        try (FileOutputStream fileOut = new FileOutputStream("DatabaseStructure.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
    }

    private static void exportToWord(Connection conn, String[] tables) throws Exception {
        XWPFDocument document = new XWPFDocument();

        for (String table : tables) {
            document.createParagraph().createRun().setText("Table: " + table);
            XWPFTable wordTable = document.createTable();

            // Create header row
            XWPFTableRow headerRow = wordTable.getRow(0);
            List<String> headers = Arrays.asList("Column", "Type", "Null", "Key", "Default", "Extra");
            for (int i = 0; i < headers.size(); i++) {
                if (i == 0) {
                    headerRow.getCell(0).setText(headers.get(i));
                } else {
                    headerRow.addNewTableCell().setText(headers.get(i));
                }
            }

            exportTableStructure(conn, table, (tableName, rs) -> {
                while (rs.next()) {
                    XWPFTableRow row = wordTable.createRow();
                    row.getCell(0).setText(rs.getString("Field"));
                    row.getCell(1).setText(rs.getString("Type"));
                    row.getCell(2).setText(rs.getString("Null"));
                    row.getCell(3).setText(rs.getString("Key"));
                    row.getCell(4).setText(rs.getString("Default"));
                    row.getCell(5).setText(rs.getString("Extra"));
                }
            });
        }

        try (FileOutputStream out = new FileOutputStream("DatabaseStructure.docx")) {
            document.write(out);
        }
        document.close();
    }

    private static void exportTableStructure(Connection conn, String table, TableStructureConsumer consumer) throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SHOW COLUMNS FROM " + table);
        consumer.accept(table, rs);
    }

    @FunctionalInterface
    private interface TableStructureConsumer {
        void accept(String tableName, ResultSet rs) throws Exception;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122