MySQL-获取业务数据字典
更新时间
浏览
TIP
本文主要是介绍 MySQL-获取业务数据字典 。
# mysql获取数据字典
# 1、sql语句
<!--查看数据字典 -->
<select id="queryDbDictionary" parameterType="string"
resultType="DbDictionary">
SELECT
a.TABLE_SCHEMA as tableSchema,
a.TABLE_NAME as tableName,
a.COLUMN_NAME as columnName,
a.ORDINAL_POSITION as ordinalPosition,
a.COLUMN_DEFAULT as columnDefault,
a.IS_NULLABLE as isNullable,
a.COLUMN_TYPE as columnType,
a.COLUMN_COMMENT as columnComment,
a.COLUMN_KEY as columnKey,
a.EXTRA as extra,
b.TABLE_COMMENT as tableComment
from information_schema.COLUMNS a
LEFT JOIN information_schema.TABLES b ON a.TABLE_NAME=b.TABLE_NAME
where a.TABLE_SCHEMA=#{value}
ORDER BY b.TABLE_NAME
</select>
2、实体
/**
* 数据字典模型类
* Created by fuguangli on 2016/12/7.
*/
@Data
@EqualsAndHashCode(callSuper = false)
public class DbDictionary implements Serializable {
/**
*
*/
private static final long serialVersionUID = -7536298200802665693L;
private String tableSchema; //数据库名
private String tableName; //表明
private String ordinalPosition; //序号
private String columnName; //字段名
private String columnType; //字段类型
private String columnDefault; //字段默认
private String isNullable; //可否空
private String extra; //其他
private String columnKey; //主键约束
private String columnComment; //字段注释
private String tableComment; //表注释
}
# 3、service
@Override
public Map<String, Object> queryDbDictionary(String tableSchema) {
List<DbDictionary> dbDictionaries = qyInteriorDao.queryDbDictionary(tableSchema);
System.err.println(dbDictionaries.toString());
Map<String, Object> map = null;
String schema = tableSchema; // 数据库名称
if (dbDictionaries != null && dbDictionaries.size() > 0) {
map = new HashMap<>();
List<DbDictionary> columns = null;
Map<String, List<DbDictionary>> tables = null;
tables = new TreeMap<>();
for (DbDictionary db : dbDictionaries) {
columns = new LinkedList<>();
String tableName = db.getTableName();
if (tables.containsKey(tableName)) {
tables.get(tableName).add(db);
} else {
columns.add(db);
tables.put(tableName, columns);
}
}
map.put("schema", schema);
map.put("tables", tables);
System.err.println(map.toString());
return map;
}
return null;
}
# 4、jsp
<%--
Created by IntelliJ IDEA.
User: fuguangli
Date: 2016/12/7
Time: 11:36
To change this template use File | Settings | File Templates.
用于获取数据字典
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ include file="/base.jsp" %>
<html>
<head>
<title>${map.schema} 数据字典</title>
<link href="http://cdn.bootcss.com/twitter-bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container">
<h1 style="text-align:center;">${map.schema} 数据字典</h1>
<c:forEach items="${map.tables}" varStatus="status" var="table">
<h3>${table.key} 备注:${table.value[0].tableComment}</h3>
<table class="table table-hover table-bordered table-condensed">
<thead>
<tr>
<th>序号</th>
<th>字段名</th>
<th>数据类型</th>
<th>默认值</th>
<th>允许非空</th>
<th>其他选项</th>
<th>主键约束</th>
<th>备注</th>
</tr>
</thead>
<tbody>
<c:forEach items="${table.value}" var="columns" varStatus="s2">
<tr>
<td>${columns.ordinalPosition}</td>
<td>${columns.columnName}</td>
<td>${columns.columnType}</td>
<td>${columns.columnDefault}</td>
<td>${columns.isNullable}</td>
<td>${columns.extra}</td>
<td>${columns.columnKey}</td>
<td>${columns.columnComment}</td>
</tr>
</c:forEach>
</tbody>
</table>
</c:forEach>
</div>
</body>
</html>
# 5、最后的样子
# 参考文章
- https://www.cnblogs.com/yuan951/p/7338871.html
