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

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 模版文件，点击下载可得