经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
MySql生成ER【StarUML】文件
来源:cnblogs  作者:xiaostudy  时间:2023/3/17 8:59:03  对本文有异议

1. 背景

要画ER图,一个个打费时费力,StarUML文件打开是json。那么就有可能自动生成。

2. 效果

把表结构生成好,自己只要维护关系即可。

image

3. 代码

  1. import lombok.Data;
  2. import java.io.FileWriter;
  3. import java.io.IOException;
  4. import java.sql.*;
  5. import java.util.ArrayList;
  6. import java.util.HashMap;
  7. import java.util.List;
  8. import java.util.Map;
  9. /**
  10. * @author liwei
  11. * @version 1.0
  12. * @className MySqlToStarUML
  13. * @date 2022/9/21 22:47
  14. */
  15. public class MySqlToStarUML {
  16. /**
  17. * 自动生成代码入口
  18. *
  19. * @author liwei
  20. * @date 2022-09-25 00:58:45
  21. * @param args
  22. * @return void
  23. */
  24. public static void main(String[] args) {
  25. localTest();
  26. }
  27. public static void localTest() {
  28. String driver = "com.mysql.cj.jdbc.Driver";
  29. String url = "jdbc:mysql://localhost:3306/test_nacos?useUnicode=true&characterEncoding=UTF-8";
  30. String userName = "root";
  31. String password = "密码";
  32. String filePath = "D:\\test_nacos.mdj";
  33. List<Table> tableList = getTableList(driver, url, userName, password);
  34. saveAsFileWriter(filePath, getProject(url, tableList));
  35. System.out.println("===============生成成功================");
  36. }
  37. /**
  38. * 获取项目字符串
  39. *
  40. * @author liwei
  41. * @date 2023-03-16 18:37:01
  42. * @param url
  43. * 地址
  44. * @param tableList
  45. * 表集合
  46. * @return {@link String}
  47. */
  48. public static String getProject(String url, List<Table> tableList) {
  49. String database = getDBByUrl(url);
  50. Project project = new Project();
  51. // 不能使用中文
  52. project.setName(database);
  53. project.set_id("AAAAAA_Project");
  54. List<OwnedElement> erddatamodels = new ArrayList<>();
  55. List<OwnedElement> ownedElements = new ArrayList<>();
  56. ERDDataModel erdDataModel = new ERDDataModel();
  57. erdDataModel.setName("Data Model1");
  58. erdDataModel.set_id("AAAAAA_DataModel1");
  59. erdDataModel.set_parent(project.get_id());
  60. erddatamodels.add(erdDataModel);
  61. ERDDiagram erdDiagram = new ERDDiagram();
  62. erdDiagram.setName("ERDDiagram1");
  63. erdDiagram.set_id("AAAAAA_ERDDiagram1");
  64. erdDiagram.set_parent(erdDataModel.get_id());
  65. ownedElements.add(erdDiagram);
  66. for (Table table : tableList) {
  67. table.set_parent(erdDataModel.get_id());
  68. }
  69. ownedElements.addAll(tableList);
  70. erdDataModel.setOwnedElements(ownedElements);
  71. project.setOwnedElements(erddatamodels);
  72. return project.toString();
  73. }
  74. /**
  75. * 通过url获取数据库
  76. *
  77. * @author liwei
  78. * @date 2022-09-23 09:21:09
  79. * @param url
  80. * 地址
  81. * @return {@link String}
  82. */
  83. public static String getDBByUrl(String url) {
  84. if (null == url || url.isEmpty()) {
  85. throw new RuntimeException("地址为空");
  86. }
  87. if (url.indexOf(":") == 0 && url.length() <= 1) {
  88. throw new RuntimeException("地址有误");
  89. }
  90. while (url.indexOf(":") > 0) {
  91. url = url.substring(url.indexOf(":") + 1);
  92. }
  93. if (url.indexOf("?") > 0) {
  94. url = url.substring(0, url.indexOf("?"));
  95. }
  96. if (url.indexOf("/") > 0) {
  97. url = url.substring(url.indexOf("/") + 1);
  98. }
  99. return url;
  100. }
  101. /**
  102. * 保存内容到文件
  103. *
  104. * @author liwei
  105. * @date 2022-11-22 14:19:47
  106. * @param filePath
  107. * 文件路径
  108. * @param content
  109. * 内容
  110. * @return void
  111. */
  112. private static void saveAsFileWriter(String filePath, String content) {
  113. FileWriter fwriter = null;
  114. try {
  115. fwriter = new FileWriter(filePath);
  116. fwriter.write(content);
  117. } catch (IOException ex) {
  118. ex.printStackTrace();
  119. } finally {
  120. try {
  121. if (null != fwriter) {
  122. fwriter.flush();
  123. fwriter.close();
  124. }
  125. } catch (IOException ex) {
  126. ex.printStackTrace();
  127. }
  128. }
  129. }
  130. /**
  131. * 获取表集合
  132. *
  133. * @author liwei
  134. * @date 2022-11-22 14:20:24
  135. * @param driver
  136. * 驱动
  137. * @param url
  138. * 连接
  139. * @param userName
  140. * 账号
  141. * @param password
  142. * 密码
  143. * @return {@link List< Table>}
  144. */
  145. private static List<Table> getTableList(String driver, String url, String userName, String password) {
  146. Connection connection;
  147. try {
  148. Class.forName(driver);
  149. connection = DriverManager.getConnection(url, userName, password);
  150. } catch (ClassNotFoundException e) {
  151. throw new RuntimeException("加载驱动失败,找不到:" + driver);
  152. } catch (SQLException e) {
  153. throw new RuntimeException("获取数据库连接失败,请检查配置和日志", e);
  154. }
  155. String database = getDBByUrl(url);
  156. String sqlTable = String.format("select * from information_schema.tables where TABLE_SCHEMA='%s'", database);
  157. List<Map<String, String>> mapList = runSql(connection, sqlTable);
  158. List<Table> tableList = new ArrayList<>();
  159. for (int i = 0; i < mapList.size(); i++) {
  160. Map<String, String> map = mapList.get(i);
  161. Table table = new Table();
  162. String tableId = String.valueOf(i + 1);
  163. table.set_id(tableId);
  164. table.setName(map.get("TABLE_NAME"));
  165. table.setDocumentation(map.get("TABLE_COMMENT"));
  166. String sqlColumn = String.format("select * from information_schema.columns where TABLE_SCHEMA='%s' and TABLE_NAME = '%s'", database, table.getName());
  167. List<Map<String, String>> mapList2 = runSql(connection, sqlColumn);
  168. List<Column> columnList = new ArrayList<>();
  169. for (Map<String, String> stringMap : mapList2) {
  170. Column column = new Column();
  171. column.setTableId(tableId);
  172. column.setName(stringMap.get("COLUMN_NAME"));
  173. column.setType(stringMap.get("DATA_TYPE"));
  174. String columnType = stringMap.get("COLUMN_TYPE");
  175. if (columnType.indexOf("(") > 0) {
  176. column.setLength(columnType.substring(columnType.indexOf("(") + 1, columnType.indexOf(")")));
  177. } else {
  178. column.setLength(stringMap.get("CHARACTER_MAXIMUM_LENGTH"));
  179. }
  180. column.setOrdinalPosition(Integer.parseInt(stringMap.get("ORDINAL_POSITION")));
  181. column.setNullable("YES".equals(stringMap.get("IS_NULLABLE")));
  182. column.setPrimaryKey("PRI".equals(stringMap.get("COLUMN_KEY")));
  183. column.setUnique("UNI".equals(stringMap.get("COLUMN_KEY")));
  184. columnList.add(column);
  185. }
  186. columnList.sort((c1, c2) -> c1.ordinalPosition - c2.getOrdinalPosition());
  187. table.setColumns(columnList);
  188. tableList.add(table);
  189. }
  190. close(null, connection, null);
  191. return tableList;
  192. }
  193. /**
  194. * 关闭连接
  195. *
  196. * @author liwei
  197. * @date 2022-09-23 09:21:53
  198. * @param pstmt
  199. * 预编译
  200. * @param conn
  201. * 连接
  202. * @param rs
  203. * 结果集
  204. * @return void
  205. */
  206. public static void close(PreparedStatement pstmt, Connection conn, ResultSet rs) {
  207. try {
  208. if(null != rs) {
  209. rs.close();
  210. rs = null;
  211. }
  212. if(null != pstmt) {
  213. pstmt.close();
  214. pstmt = null;
  215. }
  216. if(null != conn) {
  217. conn.close();
  218. conn = null;
  219. }
  220. } catch (SQLException e) {
  221. throw new RuntimeException("关闭数据库连接异常", e);
  222. }
  223. }
  224. /**
  225. * 运行sql
  226. *
  227. * @author liwei
  228. * @date 2022-11-22 14:21:40
  229. * @param conn
  230. * 连接
  231. * @param sql
  232. * 执行的sql
  233. * @return {@link List< Map< String, String>>}
  234. */
  235. public static List<Map<String, String>> runSql(Connection conn, String sql) {
  236. if (null == sql || sql.isEmpty()) {
  237. throw new RuntimeException("执行的sql不可为空");
  238. }
  239. List<Map<String, String>> list = new ArrayList<>();
  240. if(null == conn) {
  241. throw new RuntimeException("获取数据库连接失败");
  242. }
  243. PreparedStatement pstmt = null;
  244. ResultSet rs = null;
  245. try {
  246. pstmt = conn.prepareStatement(sql);
  247. rs = pstmt.executeQuery();
  248. while (rs.next()) {
  249. Map<String, String> map = new HashMap<>();
  250. ResultSetMetaData metaData = rs.getMetaData();
  251. int columnCount = metaData.getColumnCount();
  252. for (int i = 1; i <= columnCount; i++) {
  253. map.put(metaData.getColumnName(i), rs.getString(i));
  254. }
  255. list.add(map);
  256. }
  257. } catch (SQLException e) {
  258. throw new RuntimeException("执行sql异常", e);
  259. } finally {
  260. close(pstmt, null, rs);
  261. }
  262. return list;
  263. }
  264. @Data
  265. static class Table extends OwnedElement {
  266. private String _type = "ERDEntity";
  267. private String documentation;
  268. private List<Column> columns;
  269. @Override
  270. public String toString() {
  271. return "{" +
  272. "\"_type\":\"" + _type + '\"' +
  273. ", \"_id\":\"" + super._id + '\"' +
  274. ", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
  275. ", \"name\":\"" + super.name + " " + documentation + '\"' +
  276. ", \"documentation\":\"" + documentation + '\"' +
  277. ", \"columns\":" + columns +
  278. '}';
  279. }
  280. }
  281. @Data
  282. static class Column {
  283. private String _type = "ERDColumn";
  284. private String name;
  285. private String tableId;
  286. private String type;
  287. private String length;
  288. // UNI、PRI
  289. private String columnKey;
  290. private int ordinalPosition;
  291. private Boolean primaryKey;
  292. private Boolean unique;
  293. private Boolean nullable;
  294. @Override
  295. public String toString() {
  296. return "{" +
  297. "\"_type\":\"ERDColumn\"" +
  298. ", \"_parent\":{\"$ref\":\"" + tableId + "\"}" +
  299. ", \"name\":\"" + name + '\"' +
  300. ", \"type\":\"" + type + '\"' +
  301. (null != length ? ", \"length\":\"" + length + '\"' : "") +
  302. (primaryKey ? ", \"primaryKey\":\"" + primaryKey + "\"" : "") +
  303. (unique ? ", \"unique\":\"" + unique + "\"" : "") +
  304. (nullable ? ", \"nullable\":\"" + nullable + "\"" : "") +
  305. '}';
  306. }
  307. }
  308. @Data
  309. static class Project extends OwnedElement {
  310. private String _type = "Project";
  311. @Override
  312. public String toString() {
  313. return "{" +
  314. "\"_type\":\"Project\"" +
  315. ", \"_id\":\"" + super._id + '\"' +
  316. ", \"name\":\"" + super.name + '\"' +
  317. ", \"ownedElements\":" + super.ownedElements +
  318. "}";
  319. }
  320. }
  321. @Data
  322. static class OwnedElement {
  323. private String _type;
  324. private String _id;
  325. private String _parent;
  326. private String name;
  327. private List<OwnedElement> ownedElements;
  328. }
  329. @Data
  330. static class ERDDataModel extends OwnedElement {
  331. private String _type = "ERDDataModel";
  332. @Override
  333. public String toString() {
  334. return "{" +
  335. "\"_type\":\"ERDDataModel\"" +
  336. ", \"_id\":\"" + super._id + '\"' +
  337. ", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
  338. ", \"name\":\"" + super.name + '\"' +
  339. ", \"ownedElements\":" + super.ownedElements +
  340. "}";
  341. }
  342. }
  343. @Data
  344. static class ERDDiagram extends OwnedElement {
  345. private String _type = "ERDDiagram";
  346. @Override
  347. public String toString() {
  348. return "{" +
  349. "\"_type\":\"ERDDiagram\"" +
  350. ", \"_id\":\"" + super._id + '\"' +
  351. ", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
  352. ", \"name\":\"" + super.name + '\"' +
  353. (null != super.ownedElements ? ", \"ownedViews\":" + super.ownedElements : "") +
  354. "}";
  355. }
  356. }
  357. @Data
  358. static class ERDEntityView extends OwnedElement {
  359. private String _type = "ERDEntityView";
  360. private String tableId;
  361. @Override
  362. public String toString() {
  363. return "{" +
  364. "\"_type\":\"ERDEntityView\"" +
  365. ", \"model\":{\"" + tableId + "\"}" +
  366. (null != super.ownedElements ? ", \"subViews\":" + super.ownedElements : "") +
  367. "}";
  368. }
  369. }
  370. }

原文链接:https://www.cnblogs.com/xiaostudy/p/17224092.html

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号