MySql生成ER【StarUML】文件
1. 背景
要画ER图，一个个打费时费力，StarUML文件打开是json。那么就有可能自动生成。
2. 效果
把表结构生成好，自己只要维护关系即可。
3. 代码
import lombok.Data;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author liwei
* @version 1.0
* @className MySqlToStarUML
* @date 2022/9/21 22:47
*/
public class MySqlToStarUML {
/**
* 自动生成代码入口
*
* @author liwei
* @date 2022-09-25 00:58:45
* @param args
* @return void
*/
public static void main(String[] args) {
localTest();
}
public static void localTest() {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test_nacos?useUnicode=true&characterEncoding=UTF-8";
String userName = "root";
String password = "密码";
String filePath = "D:\\test_nacos.mdj";
List<Table> tableList = getTableList(driver, url, userName, password);
saveAsFileWriter(filePath, getProject(url, tableList));
System.out.println("===============生成成功================");
}
/**
* 获取项目字符串
*
* @author liwei
* @date 2023-03-16 18:37:01
* @param url
* 地址
* @param tableList
* 表集合
* @return {@link String}
*/
public static String getProject(String url, List<Table> tableList) {
String database = getDBByUrl(url);
Project project = new Project();
// 不能使用中文
project.setName(database);
project.set_id("AAAAAA_Project");
List<OwnedElement> erddatamodels = new ArrayList<>();
List<OwnedElement> ownedElements = new ArrayList<>();
ERDDataModel erdDataModel = new ERDDataModel();
erdDataModel.setName("Data Model1");
erdDataModel.set_id("AAAAAA_DataModel1");
erdDataModel.set_parent(project.get_id());
erddatamodels.add(erdDataModel);
ERDDiagram erdDiagram = new ERDDiagram();
erdDiagram.setName("ERDDiagram1");
erdDiagram.set_id("AAAAAA_ERDDiagram1");
erdDiagram.set_parent(erdDataModel.get_id());
ownedElements.add(erdDiagram);
for (Table table : tableList) {
table.set_parent(erdDataModel.get_id());
}
ownedElements.addAll(tableList);
erdDataModel.setOwnedElements(ownedElements);
project.setOwnedElements(erddatamodels);
return project.toString();
}
/**
* 通过url获取数据库
*
* @author liwei
* @date 2022-09-23 09:21:09
* @param url
* 地址
* @return {@link String}
*/
public static String getDBByUrl(String url) {
if (null == url || url.isEmpty()) {
throw new RuntimeException("地址为空");
}
if (url.indexOf(":") == 0 && url.length() <= 1) {
throw new RuntimeException("地址有误");
}
while (url.indexOf(":") > 0) {
url = url.substring(url.indexOf(":") + 1);
}
if (url.indexOf("?") > 0) {
url = url.substring(0, url.indexOf("?"));
}
if (url.indexOf("/") > 0) {
url = url.substring(url.indexOf("/") + 1);
}
return url;
}
/**
* 保存内容到文件
*
* @author liwei
* @date 2022-11-22 14:19:47
* @param filePath
* 文件路径
* @param content
* 内容
* @return void
*/
private static void saveAsFileWriter(String filePath, String content) {
FileWriter fwriter = null;
try {
fwriter = new FileWriter(filePath);
fwriter.write(content);
} catch (IOException ex) {
ex.printStackTrace();
} finally {
try {
if (null != fwriter) {
fwriter.flush();
fwriter.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
}
}
/**
* 获取表集合
*
* @author liwei
* @date 2022-11-22 14:20:24
* @param driver
* 驱动
* @param url
* 连接
* @param userName
* 账号
* @param password
* 密码
* @return {@link List< Table>}
*/
private static List<Table> getTableList(String driver, String url, String userName, String password) {
Connection connection;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, userName, password);
} catch (ClassNotFoundException e) {
throw new RuntimeException("加载驱动失败，找不到：" + driver);
} catch (SQLException e) {
throw new RuntimeException("获取数据库连接失败，请检查配置和日志", e);
}
String database = getDBByUrl(url);
String sqlTable = String.format("select * from information_schema.tables where TABLE_SCHEMA='%s'", database);
List<Map<String, String>> mapList = runSql(connection, sqlTable);
List<Table> tableList = new ArrayList<>();
for (int i = 0; i < mapList.size(); i++) {
Map<String, String> map = mapList.get(i);
Table table = new Table();
String tableId = String.valueOf(i + 1);
table.set_id(tableId);
table.setName(map.get("TABLE_NAME"));
table.setDocumentation(map.get("TABLE_COMMENT"));
String sqlColumn = String.format("select * from information_schema.columns where TABLE_SCHEMA='%s' and TABLE_NAME = '%s'", database, table.getName());
List<Map<String, String>> mapList2 = runSql(connection, sqlColumn);
List<Column> columnList = new ArrayList<>();
for (Map<String, String> stringMap : mapList2) {
Column column = new Column();
column.setTableId(tableId);
column.setName(stringMap.get("COLUMN_NAME"));
column.setType(stringMap.get("DATA_TYPE"));
String columnType = stringMap.get("COLUMN_TYPE");
if (columnType.indexOf("(") > 0) {
column.setLength(columnType.substring(columnType.indexOf("(") + 1, columnType.indexOf(")")));
} else {
column.setLength(stringMap.get("CHARACTER_MAXIMUM_LENGTH"));
}
column.setOrdinalPosition(Integer.parseInt(stringMap.get("ORDINAL_POSITION")));
column.setNullable("YES".equals(stringMap.get("IS_NULLABLE")));
column.setPrimaryKey("PRI".equals(stringMap.get("COLUMN_KEY")));
column.setUnique("UNI".equals(stringMap.get("COLUMN_KEY")));
columnList.add(column);
}
columnList.sort((c1, c2) -> c1.ordinalPosition - c2.getOrdinalPosition());
table.setColumns(columnList);
tableList.add(table);
}
close(null, connection, null);
return tableList;
}
/**
* 关闭连接
*
* @author liwei
* @date 2022-09-23 09:21:53
* @param pstmt
* 预编译
* @param conn
* 连接
* @param rs
* 结果集
* @return void
*/
public static void close(PreparedStatement pstmt, Connection conn, ResultSet rs) {
try {
if(null != rs) {
rs.close();
rs = null;
}
if(null != pstmt) {
pstmt.close();
pstmt = null;
}
if(null != conn) {
conn.close();
conn = null;
}
} catch (SQLException e) {
throw new RuntimeException("关闭数据库连接异常", e);
}
}
/**
* 运行sql
*
* @author liwei
* @date 2022-11-22 14:21:40
* @param conn
* 连接
* @param sql
* 执行的sql
* @return {@link List< Map< String, String>>}
*/
public static List<Map<String, String>> runSql(Connection conn, String sql) {
if (null == sql || sql.isEmpty()) {
throw new RuntimeException("执行的sql不可为空");
}
List<Map<String, String>> list = new ArrayList<>();
if(null == conn) {
throw new RuntimeException("获取数据库连接失败");
}
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Map<String, String> map = new HashMap<>();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
map.put(metaData.getColumnName(i), rs.getString(i));
}
list.add(map);
}
} catch (SQLException e) {
throw new RuntimeException("执行sql异常", e);
} finally {
close(pstmt, null, rs);
}
return list;
}
@Data
static class Table extends OwnedElement {
private String _type = "ERDEntity";
private String documentation;
private List<Column> columns;
@Override
public String toString() {
return "{" +
"\"_type\":\"" + _type + '\"' +
", \"_id\":\"" + super._id + '\"' +
", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
", \"name\":\"" + super.name + " " + documentation + '\"' +
", \"documentation\":\"" + documentation + '\"' +
", \"columns\":" + columns +
'}';
}
}
@Data
static class Column {
private String _type = "ERDColumn";
private String name;
private String tableId;
private String type;
private String length;
// UNI、PRI
private String columnKey;
private int ordinalPosition;
private Boolean primaryKey;
private Boolean unique;
private Boolean nullable;
@Override
public String toString() {
return "{" +
"\"_type\":\"ERDColumn\"" +
", \"_parent\":{\"$ref\":\"" + tableId + "\"}" +
", \"name\":\"" + name + '\"' +
", \"type\":\"" + type + '\"' +
(null != length ? ", \"length\":\"" + length + '\"' : "") +
(primaryKey ? ", \"primaryKey\":\"" + primaryKey + "\"" : "") +
(unique ? ", \"unique\":\"" + unique + "\"" : "") +
(nullable ? ", \"nullable\":\"" + nullable + "\"" : "") +
'}';
}
}
@Data
static class Project extends OwnedElement {
private String _type = "Project";
@Override
public String toString() {
return "{" +
"\"_type\":\"Project\"" +
", \"_id\":\"" + super._id + '\"' +
", \"name\":\"" + super.name + '\"' +
", \"ownedElements\":" + super.ownedElements +
"}";
}
}
@Data
static class OwnedElement {
private String _type;
private String _id;
private String _parent;
private String name;
private List<OwnedElement> ownedElements;
}
@Data
static class ERDDataModel extends OwnedElement {
private String _type = "ERDDataModel";
@Override
public String toString() {
return "{" +
"\"_type\":\"ERDDataModel\"" +
", \"_id\":\"" + super._id + '\"' +
", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
", \"name\":\"" + super.name + '\"' +
", \"ownedElements\":" + super.ownedElements +
"}";
}
}
@Data
static class ERDDiagram extends OwnedElement {
private String _type = "ERDDiagram";
@Override
public String toString() {
return "{" +
"\"_type\":\"ERDDiagram\"" +
", \"_id\":\"" + super._id + '\"' +
", \"_parent\":{\"$ref\":\"" + super._parent + "\"}" +
", \"name\":\"" + super.name + '\"' +
(null != super.ownedElements ? ", \"ownedViews\":" + super.ownedElements : "") +
"}";
}
}
@Data
static class ERDEntityView extends OwnedElement {
private String _type = "ERDEntityView";
private String tableId;
@Override
public String toString() {
return "{" +
"\"_type\":\"ERDEntityView\"" +
", \"model\":{\"" + tableId + "\"}" +
(null != super.ownedElements ? ", \"subViews\":" + super.ownedElements : "") +
"}";
}
}
}