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、最后的样子
