java实现将数据库表以及各字段导出为开发设计文档

1.pom依赖：

<dependency>
   <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>`

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>

2.代码：

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Hyperlink;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xuexiaojun
 */
public class GenerateFile {


    //数据库url、用户名和密码
    static final String DB_URL="jdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&rewriteBatchedStatements=true";
    static final String USER="test";
    static final String PASS="test";
    static final String OUT_EXCELL_PATH="E:\\crexpress_table_info.xlsx";

    public static void main(String[] args) {
        try {
            //1、注册JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、获取数据库连接
            Connection connection = DriverManager.getConnection(DB_URL, USER, PASS);
            //3、操作数据库
            Statement statement = connection.createStatement();//获取操作数据库的对象
            //获取到该库schema下的所有表信息
            String queryAllTabBySchema = "SELECT TABLE_SCHEMA AS schemaName, TABLE_NAME AS tableName,`ENGINE` AS engingName, CREATE_TIME as createTime, TABLE_COLLATION as tableCollation, TABLE_COMMENT as tableComment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA ='crexpress1102';";
            ResultSet resultSet = statement.executeQuery(queryAllTabBySchema);//执行sql，获取结果集
            List<Map<String, String>> tableInfos = new ArrayList<>();
            while(resultSet.next()){ //遍历结果集，取出数据
                Map<String, String> tableInfo = new HashMap<>();
                tableInfo.put("schemaName", resultSet.getString("schemaName"));
                tableInfo.put("tableName", resultSet.getString("tableName"));
                tableInfo.put("engingName", resultSet.getString("engingName"));
                tableInfo.put("createTime", resultSet.getString("createTime"));
                tableInfo.put("tableCollation", resultSet.getString("tableCollation"));
                tableInfo.put("tableComment", resultSet.getString("tableComment"));
                tableInfos.add(tableInfo);
            }
            //4、关闭结果集
            resultSet.close();
            // 遍历所有表，查取每个表的详细信息
            String fieldByTableFat = "show full fields from %s;";

            Map<String, List<Map<String, String>>> allTableFieldInfos = new HashMap<>();
            for (Map<String, String> tableInfo : tableInfos) {
                String tableName = tableInfo.get("tableName");
                if (tableName == null || tableName == ""){
                    continue;
                }
                List<Map<String, String>> everyTableFieldInfos = new ArrayList<>();
                String fieldByTableSql = String.format(fieldByTableFat, tableName);
                ResultSet resultFieldSet = statement.executeQuery(fieldByTableSql);//执行sql，获取结果集
                while (resultFieldSet.next()){
                    Map<String, String> fieldInfo = new HashMap<>();
                    fieldInfo.put("field", resultFieldSet.getString("field"));
                    fieldInfo.put("type", resultFieldSet.getString("type"));
                    fieldInfo.put("collation", resultFieldSet.getString("collation"));
                    fieldInfo.put("null", resultFieldSet.getString("null"));
                    fieldInfo.put("default", resultFieldSet.getString("default"));
                    fieldInfo.put("comment", resultFieldSet.getString("comment"));
                    everyTableFieldInfos.add(fieldInfo);
                }
                allTableFieldInfos.put(tableName, everyTableFieldInfos);
                resultFieldSet.close();
            }
            //5、数据库操作对象、数据库连接
            statement.close();
            connection.close();
            //6、将表和表信息基于模板导出
            writeToExcell(allTableFieldInfos, tableInfos);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch(SQLException e){
            e.printStackTrace();
        } catch(Exception e){
            e.printStackTrace();
        }
    }

    public static String writeToExcell(Map<String, List<Map<String, String>>> allTableFieldInfos, List<Map<String, String>> tableInfos){
        //建立新HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        /* 设置为超链接的样式*/
        HSSFCellStyle linkStyle = wb.createCellStyle();
        HSSFFont cellFont= wb.createFont();
        cellFont.setUnderline((byte) 1);
        cellFont.setColor(HSSFColor.BLUE.index);
        linkStyle.setFont(cellFont);
        linkStyle.setWrapText(true);
        //建立新的sheet对象
        HSSFSheet sheet = wb.createSheet("汇总表页面");
        HSSFRow row = sheet.createRow((short)0);
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short)(30 * 20));
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("schemaName(数据库）");
        row.createCell(2).setCellValue("tableName（表名）");
        row.createCell(3).setCellValue("engingName（库引擎）");
        row.createCell(4).setCellValue("createTime（创建时间）");
        row.createCell(5).setCellValue("tableCollation（编码）");
        row.createCell(6).setCellValue("tableComment（备注）");
        int tableNum = 1;
        for (Map<String,String> tableInfo : tableInfos) {
            HSSFRow rowValue = sheet.createRow((short)tableNum++);
            rowValue.createCell(0).setCellValue(tableNum);
            rowValue.createCell(1).setCellValue(tableInfo.get("schemaName"));
            HSSFCell cell = rowValue.createCell(2);
            //设置超链接
            Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);
            // "#"表示本文档    "明细页面"表示sheet页名称  "A10"表示第几列第几行
            hyperlink.setAddress("#" +tableInfo.get("tableName") + "!A1");
            cell.setHyperlink(hyperlink);
            cell.setCellStyle(linkStyle);
            cell.setCellValue(tableInfo.get("tableName"));
            rowValue.createCell(3).setCellValue(tableInfo.get("engingName"));
            rowValue.createCell(4).setCellValue(tableInfo.get("createTime"));
            rowValue.createCell(5).setCellValue(tableInfo.get("tableCollation"));
            rowValue.createCell(6).setCellValue(tableInfo.get("tableComment"));
            //同时给每张表创建一个sheet页，存每个表的信息
            HSSFSheet sheetFiled = wb.createSheet(tableInfo.get("tableName"));
            sheetFiled.setDefaultColumnWidth(20);
            sheetFiled.setDefaultRowHeight((short)(30*20));
            HSSFRow rowField = sheetFiled.createRow((short)0);
            rowField.createCell(0).setCellValue("序号");
            rowField.createCell(1).setCellValue("field（字段名）");
            rowField.createCell(2).setCellValue("type（字段类型）");
            rowField.createCell(3).setCellValue("collation（编码）");
            rowField.createCell(4).setCellValue("null（是否为null)");
            rowField.createCell(5).setCellValue("default(默认值）");
            rowField.createCell(6).setCellValue("comment（备注）");
            List<Map<String, String>> fieldDataList = allTableFieldInfos.get(tableInfo.get("tableName"));
            int fieldNum = 1;
            for (Map<String, String> fieldData : fieldDataList) {
                HSSFRow rowFieldData = sheetFiled.createRow((short)fieldNum++);
                rowFieldData.createCell(0).setCellValue(fieldNum);
                rowFieldData.createCell(1).setCellValue(fieldData.get("field"));
                rowFieldData.createCell(2).setCellValue(fieldData.get("type"));
                rowFieldData.createCell(3).setCellValue(fieldData.get("collation"));
                rowFieldData.createCell(4).setCellValue(fieldData.get("null"));
                rowFieldData.createCell(5).setCellValue(fieldData.get("default"));
                rowFieldData.createCell(6).setCellValue(fieldData.get("comment"));
            }

        }
        /* 输出文件*/
        FileOutputStream fileOut = null;
        try {
            fileOut = new FileOutputStream(OUT_EXCELL_PATH);
            wb.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                fileOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

3.效果：

点击表超链接到各表sheet页：