读取mysql数据库表结构生成接口文档

我是老王我骄傲

已于 2023-10-27 17:29:27 修改

阅读量268

点赞数

文章标签：数据库 mysql

于 2023-10-27 17:18:14 首次发布

本文链接：https://blog.csdn.net/weixin_36498094/article/details/134080572

版权

1、引入依赖

<!-- 导出word -->
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.30</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/e-iceblue/spire.doc.free -->
        <dependency>
            <groupId>e-iceblue</groupId>
            <artifactId>spire.doc.free</artifactId>
            <version>2.7.3</version>
        </dependency>
    <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>
 <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

3、工具类ApiDoc

package com.example.rediscache.utils;

import com.baomidou.mybatisplus.core.toolkit.ExceptionUtils;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.util.Map;

public class ApiDoc {

    public static final String MAC = "Mac";
    /**
     * Windows
     */
    public static final String WINDOWS = "Windows";
    public static Logger log = LoggerFactory.getLogger(ApiDoc.class);

    /**
     * 将数据导入word模板生成word
     *
     * @param params 数据
     * @return docx临时文件的全路径
     * @throws IOException
     */
    public static String createWord(Map<String, Object> params) throws IOException {
        String templateFile = "api.doc.ftl";
        String filePath = "D://html";
        File file = null, templateFile1 = null;
        FileOutputStream fileOutputStream = null;
        Writer out = null;
        InputStream inputStream = null;
        try {
            File file1 = new File(filePath);
            //指定模板存放位置
            ClassPathResource tempFileResource = new ClassPathResource(templateFile);
            //创建临时文件
            file = File.createTempFile("接口文档-", ".docx", file1);
            //得到临时文件全路径  ,作为word生成的输出全路径使用
            String outputDir = file.getAbsolutePath();
            log.info("创建临时文件的路径为:{}", outputDir);

            //创建模板临时文件
            templateFile1 = File.createTempFile(templateFile, ".xml", file1);
            fileOutputStream = new FileOutputStream(templateFile1);
            inputStream = tempFileResource.getInputStream();
            IOUtils.copy(inputStream, fileOutputStream);
            //得到临时模板文件全路径
            String templateFilePath = templateFile1.getAbsolutePath();
            log.info("创建临时文件的路径为:{}", templateFilePath);

//           new ClassPathResource("aaa").getInputStream().close();
            // 设置FreeMarker的版本和编码格式
            Configuration configuration = new Configuration();
            configuration.setDefaultEncoding("UTF-8");
            // 设置FreeMarker生成Word文档所需要的模板的路径
            configuration.setDirectoryForTemplateLoading(templateFile1.getParentFile());
            // 设置FreeMarker生成Word文档所需要的模板
            Template t = configuration.getTemplate(templateFile1.getName(), "UTF-8");

            // 创建一个Word文档的输出流
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File(outputDir)), "UTF-8"));
            //FreeMarker使用Word模板和数据生成Word文档
            t.process(params, out); //将填充数据填入模板文件并输出到目标文件
        } catch (Exception e) {
            log.error("word生成报错,错误信息{}", e.getMessage());
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.flush();
                out.close();
            }
            if (inputStream != null) {
                inputStream.close();
            }
            if (fileOutputStream != null) {
                fileOutputStream.close();
            }
            if (templateFile1 != null) {
                templateFile1.delete();
            }
        }
        try {
            //获取系统信息
            String osName = System.getProperty("os.name");
            if (osName != null) {
                if (osName.contains(MAC)) {
                    Runtime.getRuntime().exec("open " + file.getAbsolutePath());
                } else if (osName.contains(WINDOWS)) {
                    Runtime.getRuntime().exec("cmd /c start " + file.getAbsolutePath());
                }
            }
        } catch (IOException e) {
            throw ExceptionUtils.mpe(e);
        }
        return file == null ? null : file.getAbsolutePath();
    }


}

4、编写测试方法

package com.example.rediscache;

import com.example.rediscache.utils.ApiDoc;
import org.junit.platform.commons.util.StringUtils;

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

public class MysqlCreateApiDoc {

    //读取数据库生成接口文档
    public static void main(String[] args) {
        String database = "ry-cloud";
        String url = String.format("jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=UTC", database);
        String username = "root";
        String password = "pyrx123";
        List<HashMap<String, Object>> list = new ArrayList<>();
        try {
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();
            ResultSet tables = stmt.executeQuery(String.format("SELECT table_name, table_comment " + "FROM information_schema.tables WHERE table_schema = '%s'", database));
            while (tables.next()) {
                String tableName = tables.getString("table_name");
                String table_comment = tables.getString("table_comment");
                System.out.println("Table: " + tableName + "---table_comment: " + table_comment);
                PreparedStatement ps = conn.prepareStatement("show full columns from " + tableName);
                ResultSet c = ps.executeQuery();
                HashMap<String, Object> map = new HashMap<>();
                //模块名称
                if (StringUtils.isBlank(table_comment)) {
                    map.put("model_name", toCamelCase(tableName));
                } else {
                    map.put("model_name", table_comment.replace("表", ""));
                }
                map.put("requestUrl", "/" + toCamelCase(tableName));
                List<HashMap<String, String>> paramslist = new ArrayList<>();
                while (c.next()) {
                    System.out.println("字段名：" + c.getString("Field") + "------" + "类型：" + convert(c.getString("Type")) + "------" + "备注: " + c.getString("Comment") + "------" + "是否必选: " + c.getString("Null"));
                    String Null = c.getString("Null").contains("YES") ? "是" : "否";
                    HashMap<String, String> methodParams = new HashMap<>();
                    methodParams.put("FILENAME", toCamelCase(c.getString("Field")));
                    methodParams.put("FILEType", convert(c.getString("Type")));
                    methodParams.put("Null", Null);
                    methodParams.put("FILEDESC", c.getString("Comment"));
                    paramslist.add(methodParams);
                }
                map.put("paramslist", paramslist);
                map.put("resultslist", paramslist);
                list.add(map);
            }
            conn.close();
            stmt.close();
            int length = list.size();
            for (int i = 0; i < length; i += 7) {
                // 处理子列表，例如打印出来
                Map<String, Object> params = new HashMap<>();
                params.put("tmp_title", "远程智能巡视");
                params.put("apilist", list.subList(i, Math.min(length, i + 7)));//告警点位汇总
                new ApiDoc().createWord(params);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static String convert(String fieldType) {
        if (fieldType.contains("varchar") || fieldType.contains("text")) {
            return "String";
        } else if (fieldType.contains("int")) {
            return "Integer";
        } else if (fieldType.contains("boolean") || fieldType.contains("char")) {
            return "Boolean";
        } else if (fieldType.contains("date") || fieldType.contains("time")) {
            return "Date";
        } else if (fieldType.contains("decimal") || fieldType.contains("double") || fieldType.contains("float")) {
            return "Double";
        }
        return "Object";
    }

    //下划线转驼峰
    public static String toCamelCase(CharSequence name) {
        if (null == name) {
            return null;
        }
        String name2 = name.toString();
        if (name2.contains("_")) {
            StringBuilder sb = new StringBuilder(name2.length());
            boolean upperCase = false;
            for (int i = 0; i < name2.length(); ++i) {
                char c = name2.charAt(i);
                if (c == '_') {
                    upperCase = true;
                } else if (upperCase) {
                    sb.append(Character.toUpperCase(c));
                    upperCase = false;
                } else {
                    sb.append(Character.toLowerCase(c));
                }
            }
            return sb.toString();
        }
        return name2;
    }

}

5、resources 目录下存放api.doc.ftl 模版文件，点击下载可得