导出mysql数据库表结构文档_mysql数据库结构导出doc

导出mysql数据库表结构文档

原文地址: https://blog.csdn.net/weixin_40873693/article/details/123681882

导出文档示例：

说明，文档是基poi工具来生成的（主要是依赖与数据库名正确的话可以直接运行）

一、需要的依赖

 
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

二具体代码

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
 
 
public class WordExportTable {
    
    public static final String driverUrl = "jdbc:mysql://127.0.0.1:3306/数据库名称?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&allowMultiQueries=true";
    public static final String username = "root";
    public static final String password = "123456";
    /**
     * 导出数据库需要与driverUrl中连接的数据库一致
     */
    public static final String dataBase = "数据库名称";
    /**
     * 不需要导出的表，可为null
     */
//    public static final String notTbales = "'survey','survey_answer_down'";
    public static final String notTbales = null;
    /**
     * 匹配前缀不导出，可为null
     */
//    public static final String notLike = "'sys_%'";
    public static final String notLike = null;
    /**
     * 文档标题
     */
    public static final String title = "数据库设计详细说明书";
    /**
     * 输出文档地址
     */
    public static final String path = "C:\\Users\\pc\\Desktop\\";
    /**
     * 输出文档名称
     */
    public static final String fileName = "数据库设计详细说明书V1.0.0.doc";
 
    public static void main(String[] args)throws Exception {
        //Blank Document
        XWPFDocument document= new XWPFDocument();
        //添加标题
        XWPFParagraph titleParagraph = document.createParagraph();
        //设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.CENTER);
        XWPFRun titleParagraphRun = titleParagraph.createRun();
        titleParagraphRun.setText(title);
        titleParagraphRun.setColor("000000");
        titleParagraphRun.setFontSize(20);
        WordExportTable we = new WordExportTable();
        List<JSONObject> list= we.getTables(dataBase);
        for (JSONObject json : list) {
            List<String[]> columns = we.getTablesDetail(dataBase, json.getString("name"));
            addTable(document, json.getString("name"), json.getString("remark"), columns);
        }
        //Write the Document in file system
        FileOutputStream out = new FileOutputStream(new File(path+fileName));
        document.write(out);
        out.close();
        System.out.println("create_table document written success.");
    }
 
    private  List<String[]> getTablesDetail(String schema, String tableName){
        List<String[]> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            //加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection(driverUrl, username, password);
            //定义sql语句 ?表示占位符
            String sql = "SELECT COLUMN_NAME  , COLUMN_TYPE  ,  COLUMN_DEFAULT  , IS_NULLABLE  ,  COLUMN_COMMENT "
                    +" FROM INFORMATION_SCHEMA.COLUMNS    WHERE  table_schema = ? and table_name = ?  ";
            //获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, schema);
            preparedStatement.setString(2, tableName);
            //向数据库发出sql执行查询，查询出结果集
            resultSet =  preparedStatement.executeQuery();
            int i = 1;
            //遍历查询结果集
            while(resultSet.next()){
                String[] str = new String[7];
                str[0] = i+"";
                str[1] = resultSet.getString("COLUMN_NAME");
                str[2] = resultSet.getString("COLUMN_TYPE");
                str[3] = resultSet.getString("COLUMN_DEFAULT");
                str[4] = resultSet.getString("IS_NULLABLE");
                str[5] = "";
                str[6] = resultSet.getString("COLUMN_COMMENT");
                list.add(str);
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
 
        }
        return list;
    }
 
    private  List<JSONObject> getTables(String schema){
        List<JSONObject> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            //加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection(driverUrl, username, password);
            //定义sql语句 ?表示占位符
            StringBuffer sql = new StringBuffer();
            sql.append("select TABLE_NAME,TABLE_COMMENT from information_schema.tables where table_schema= ? ");
            if(null != notLike){
                sql.append(" AND table_name NOT LIKE "+notLike);
            }
            if(null != notTbales){
                sql.append(" AND table_name NOT IN ("+notTbales+")");
            }
            //获取预处理statement
            preparedStatement = connection.prepareStatement(sql.toString());
            //设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, schema);
            //向数据库发出sql执行查询，查询出结果集
            resultSet =  preparedStatement.executeQuery();
            //遍历查询结果集
            while(resultSet.next()){
                JSONObject j = new JSONObject();
                j.put("name", resultSet.getString("TABLE_NAME"));
                j.put("remark", resultSet.getString("TABLE_COMMENT"));
                list.add(j);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            //释放资源
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
 
        }
        return list;
    }
 
    private static void addTable(XWPFDocument document,String tableName,String remark, List<String[]> columns){
 
        //两个表格之间加个换行
        document.createParagraph().createRun().setText("\r");
        // 标题1，1级大纲
        document.createParagraph().createRun().setText(remark+" "+tableName);
        //工作经历表格
        XWPFTable ComTable = document.createTable();
//        //列宽自动分割
//        CTTblWidth comTableWidth = ComTable.getCTTbl().addNewTblPr().addNewTblW();
//        comTableWidth.setType(STTblWidth.DXA);
//        comTableWidth.setW(BigInteger.valueOf(9072));
 
        CTTbl ttbl = ComTable.getCTTbl();
        int[] COLUMN_WIDTHS = new int[] {572,2072,1372,872,672,672,2572};
        CTTblGrid tblGrid = ttbl.getTblGrid() != null ? ttbl.getTblGrid()
                : ttbl.addNewTblGrid();
        for (int j = 0, len = COLUMN_WIDTHS.length; j < len; j++) {
            CTTblGridCol gridCol = tblGrid.addNewGridCol();
            gridCol.setW(new BigInteger(String.valueOf(COLUMN_WIDTHS[j])));
        }
        //表格第一行
        XWPFTableRow comTableRowOne = ComTable.getRow(0);
        setCellvalue(comTableRowOne.getCell(0), "序号");
        setCellvalue(comTableRowOne.addNewTableCell(),"字段名");
        setCellvalue(comTableRowOne.addNewTableCell(),"类型");
        setCellvalue(comTableRowOne.addNewTableCell(),"默认值");
        setCellvalue(comTableRowOne.addNewTableCell(),"是否可为空");
        setCellvalue(comTableRowOne.addNewTableCell(),"是否主键");
        setCellvalue(comTableRowOne.addNewTableCell(),"注释");
        for (String[] str : columns) {
            //表格第二行
            XWPFTableRow comTableRowTwo = ComTable.createRow();
            for (int j = 0; j < str.length; j++) {
                if(j==0 || j==3 || j==4 || j==5){
                    setCellvalue(comTableRowTwo.getCell(j),str[j]);
                }else{
                    comTableRowTwo.getCell(j).setText(str[j]);
                }
            }
        }
 
    }
 
    private static void setCellvalue(XWPFTableCell cell, String text){
        cell.setText(text);
        //垂直居中
        cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);
        CTTc cttc = cell.getCTTc();
        CTP ctp = cttc.getPList().get(0);
        CTPPr ctppr = ctp.getPPr();
        if (ctppr == null) {
            ctppr = ctp.addNewPPr();
        }
        CTJc ctjc = ctppr.getJc();
        if (ctjc == null) {
            ctjc = ctppr.addNewJc();
        }
        //水平居中
        ctjc.setVal(STJc.CENTER);
    }
 
}
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259