Boring task
All of this has to be talked about many years ago.
At that time, I just joined a new company, and the project manager assigned me a relatively simple job, organizing a metadata table for all database fields.
Because many project documents are not complete, it is necessary to organize a basic dictionary table.
If it were you, how would you handle this task?
repetitive work
At the beginning, I prepared to sort all the fields manually, then sort out the corresponding SQL and insert it into the meta-database management table.
meta_table metadata table information
meta_field metadata field information
There was a bit of passion at the beginning, and then endless repetition, which felt very boring.
So, I wrote an open source gadget by myself.
https://github.com/houbb/metadata
Metadata management
Metadata can automatically store all table information and field information in the metadata table for unified reference.
(Notes need to ensure that the library itself already contains notes for tables and fields)
Database table design
At the beginning, three common databases were implemented: mysql oracle sql-server.
Taking mysql as an example, the corresponding table building statement is:
drop table if exists meta_field;
drop table if exists meta_model;
/*==============================================================*/
/* Table: meta_field */
/*==============================================================*/
create table meta_field
(
ID int not null auto_increment comment '自增长主键',
uid varchar(36) comment '唯一标识',
name varchar(125) comment '名称',
dbObjectName varchar(36) comment '数据库表名',
alias varchar(125) comment '别名',
description varchar(255) comment '描述',
isNullable bool comment '是否可为空',
dataType varchar(36) comment '数据类型',
createTime datetime comment '创建时间',
updateTime datetime comment '更新时间',
primary key (ID)
)
auto_increment = 1000
DEFAULT CHARSET=utf8;
alter table meta_field comment '元数据字段表';
/*==============================================================*/
/* Table: meta_model */
/*==============================================================*/
create table meta_model
(
ID int not null auto_increment comment '自增长主键',
uid varchar(36) comment '唯一标识',
name varchar(125) comment '名称',
dbObjectName varchar(36) comment '数据库表名',
alias varchar(125) comment '别名',
description varchar(255) comment '描述',
category varchar(36) comment '分类',
isVisible bool comment '是否可查询',
isEditable bool comment '是否可编辑',
createTime datetime comment '创建时间',
updateTime datetime comment '更新时间',
primary key (ID)
)
DEFAULT CHARSET=utf8;
alter table meta_model comment '元数据实体表';Data initialization
Metadata is a web application. After the deployment is started, the page specifies the database connection information to complete the initialization of all data.
To test the script
CREATE DATABASE `metadata-test`
DEFAULT CHARACTER SET UTF8;
USE `metadata-test`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
`username` varchar(255) DEFAULT NULL COMMENT '用户名',
`password` varchar(255) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='用户表';As an example, the corresponding table and field information can all be initialized to the corresponding table.
Everything looks great, and it takes a few minutes. Isn't it?
Automatic code generation
Originally, if the metadata was not accidental, I would hardly modify it anymore.
Not long ago, I implemented a low-code platform with automatic code generation based on mybatis-plus-generator.
The open source address is as follows:
http://github.com/houbb/low-code
I found that although the metadata application is good as a web application, its reusability is very poor, and I cannot implement a code generation tool on this basis.
Thus, the idea of implementing a basic jdbc metadata management tool was born.
Stones from other hills, can learn.
Let's take the source code of MPG directly as an example to learn and reform.
Database metadata
Core principle
The core point of metadata management is that all databases have metadata management.
Let's take mysql as an example to view all table information.
show table status;as follows:
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------+
| word | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2021-07-22 19:39:13 | NULL | NULL | utf8_general_ci | NULL | | 敏 感词表 |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------+
1 row in set (0.00 sec)Corresponding field information view
show full fields from word;The output is as follows:
mysql> show full fields from word;
+-------------+------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------+------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 应用自增主键 |
| word | varchar(128) | utf8_general_ci | NO | UNI | NULL | | select,insert,update,references | 单词 |
| type | varchar(8) | utf8_general_ci | NO | | NULL | | select,insert,update,references | 类型 |
| status | char(1) | utf8_general_ci | NO | | S | | select,insert,update,references | 状态 |
| remark | varchar(64) | utf8_general_ci | NO | | | | select,insert,update,references | 配置描述 |
| operator_id | varchar(64) | utf8_general_ci | NO | | system | | select,insert,update,references | 操作员名称 |
| create_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | 创建时间戳 |
| update_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | 更新时间戳 |
+-------------+------------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
8 rows in set (0.01 sec)Very comprehensive information can be obtained. The is based on these basic information to generate the corresponding code text .
Among them, the table building statement of word is as follows:
create table word
(
id int unsigned auto_increment comment '应用自增主键' primary key,
word varchar(128) not null comment '单词',
type varchar(8) not null comment '类型',
status char(1) not null default 'S' comment '状态',
remark varchar(64) not null comment '配置描述' default '',
operator_id varchar(64) not null default 'system' comment '操作员名称',
create_time timestamp default CURRENT_TIMESTAMP not null comment '创建时间戳',
update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间戳'
) comment '敏感词表' ENGINE=Innodb default charset=UTF8 auto_increment=1;
create unique index uk_word on word (word) comment '唯一索引';Expandability
Although the metadata acquisition is described above, mysql is taken as an example.
But when we implement the tools, we must consider the corresponding scalability.
It can be mysql or common oracle/sql-server.
The way to obtain each database is different, so it needs to be implemented differently depending on the configuration.
After the metadata is obtained, the processing methods can also be very diversified.
It can be output from the console, can be stored in the library, and can generate corresponding markdown/html/pdf/word/excel documents in different forms.
Ease of use
A good tool should shield users from complex implementation details.
Users only need to simply specify the configuration information, the table they want to obtain, and the processing method.
As for how to implement it, users don't need to care.
Source code implementation
Next, we combine the source code of MPG and extract the most core parts for explanation.
Get database connection
How to get the connection based on the connection information?
I hope that those who often use tools such as mybatis will remember:
public class DbConnection implements IDbConnection {
/**
* 驱动连接的URL
*/
private String url;
/**
* 驱动名称
*/
private String driverName;
/**
* 数据库连接用户名
*/
private String username;
/**
* 数据库连接密码
*/
private String password;
//getter&setter
@Override
public Connection getConnection() {
Connection conn = null;
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException | SQLException e) {
throw new JdbcMetaException(e);
}
return conn;
}
}The definition of the IDbConnection interface is very simple:
public interface IDbConnection {
/**
* 获取数据库连接
* @return 连接
* @since 1.0.0
*/
Connection getConnection();
}This is convenient for later replacement implementation, you can even use database connection pool:
https://github.com/houbb/jdbc-pool
Metadata query script
For different databases, the query method is different.
Take mysql as an example, the implementation is as follows:
public class MySqlQuery extends AbstractDbQuery {
@Override
public DbType dbType() {
return DbType.MYSQL;
}
@Override
public String tablesSql() {
return "show table status";
}
@Override
public String tableFieldsSql() {
return "show full fields from `%s`";
}
@Override
public String tableName() {
return "NAME";
}
@Override
public String tableComment() {
return "COMMENT";
}
@Override
public String fieldName() {
return "FIELD";
}
@Override
public String fieldType() {
return "TYPE";
}
@Override
public String fieldComment() {
return "COMMENT";
}
@Override
public String fieldKey() {
return "KEY";
}
@Override
public boolean isKeyIdentity(ResultSet results) throws SQLException {
return "auto_increment".equals(results.getString("Extra"));
}
@Override
public String nullable() {
return "Null";
}
@Override
public String defaultValue() {
return "Default";
}
}Among them, show table status used to view all table metadata; show full fields from %s can view the field metadata of a specific table.
The two properties of nullable() and defaultValue() are newly added by the old horse and are not available in MPG, because code generation does not care about these two fields.
Core realization
After doing the above preparations, we can start writing the core code.
@Override
public List<TableInfo> getTableList(TableInfoContext context) {
// 连接
Connection connection = getConnection(context);
DbType dbType = DbTypeUtils.getDbType(context.getDriverName());
IDbQuery dbQuery = DbTypeUtils.getDbQuery(dbType);
// 构建元数据查询 SQL
String tableSql = buildTableSql(context);
// 执行查询
List<TableInfo> tableInfoList = queryTableInfos(connection, tableSql, dbQuery, context);
return tableInfoList;
}Specific database implementation
The specific database implementation is different and can be obtained according to the driverName.
The implementation of DbTypeUtils is as follows:
/**
* @author binbin.hou
* @since 1.0.0
*/
public final class DbTypeUtils {
private DbTypeUtils(){}
/**
* 根据驱动获取 dbType
* @param driverName 驱动信息
* @return 结果
* @since 1.1.0
*/
public static DbType getDbType(final String driverName) {
DbType dbType = null;
if (driverName.contains("mysql")) {
dbType = DbType.MYSQL;
} else if (driverName.contains("oracle")) {
dbType = DbType.ORACLE;
} else if (driverName.contains("postgresql")) {
dbType = DbType.POSTGRE_SQL;
} else {
throw new JdbcMetaException("Unknown type of database!");
}
return dbType;
}
/**
* 获取对应的数据库查询类型
* @param dbType 数据库类型
* @return 结果
* @since 1.0.0
*/
public static IDbQuery getDbQuery(final DbType dbType) {
IDbQuery dbQuery = null;
switch (dbType) {
case ORACLE:
dbQuery = new OracleQuery();
break;
case SQL_SERVER:
dbQuery = new SqlServerQuery();
break;
case POSTGRE_SQL:
dbQuery = new PostgreSqlQuery();
break;
default:
// 默认 MYSQL
dbQuery = new MySqlQuery();
break;
}
return dbQuery;
}
}Table data query sql
Construct sql for table data query according to the corresponding IDbQuery.
/**
* 构建 table sql
* @param context 上下文
* @return 结果
* @since 1.0.0
*/
private String buildTableSql(final TableInfoContext context) {
// 获取 dbType & DbQuery
final String jdbcUrl = context.getDriverName();
DbType dbType = DbTypeUtils.getDbType(jdbcUrl);
IDbQuery dbQuery = DbTypeUtils.getDbQuery(dbType);
String tablesSql = dbQuery.tablesSql();
if (DbType.POSTGRE_SQL == dbQuery.dbType()) {
//POSTGRE_SQL 使用
tablesSql = String.format(tablesSql, "public");
}
// 简化掉 oracle 的特殊处理
return tablesSql;
}Just get the corresponding tablesSql directly, which is very simple.
Table information construction
Query directly based on the constructed tableSql, and then construct the most basic table information.
try(PreparedStatement preparedStatement = connection.prepareStatement(tablesSql);) {
List<TableInfo> tableInfoList = new ArrayList<>();
ResultSet results = preparedStatement.executeQuery();
TableInfo tableInfo;
while (results.next()) {
String tableName = results.getString(dbQuery.tableName());
if (StringUtil.isNotEmpty(tableName)) {
String tableComment = results.getString(dbQuery.tableComment());
tableInfo = new TableInfo();
tableInfo.setName(tableName);
tableInfo.setComment(tableComment);
tableInfoList.add(tableInfo);
} else {
System.err.println("当前数据库为空！！！");
}
}
} catch (SQLException e) {
throw new JdbcMetaException(e);
}The filtering of the table information is omitted here.
Field information construction
After the table information is constructed, specific field information is constructed.
try {
String tableFieldsSql = dbQuery.tableFieldsSql();
if (DbType.POSTGRE_SQL == dbQuery.dbType()) {
tableFieldsSql = String.format(tableFieldsSql, "public", tableInfo.getName());
} else {
tableFieldsSql = String.format(tableFieldsSql, tableInfo.getName());
}
PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);
ResultSet results = preparedStatement.executeQuery();
while (results.next()) {
TableField field = new TableField();
// 省略 ID 相关的处理
// 省略自定义字段查询
// 处理其它信息
field.setName(results.getString(dbQuery.fieldName()));
field.setType(results.getString(dbQuery.fieldType()));
String propertyName = getPropertyName(field.getName());
DbColumnType dbColumnType = typeConvert.getTypeConvert(field.getType());
field.setPropertyName(propertyName);
field.setColumnType(dbColumnType);
field.setComment(results.getString(dbQuery.fieldComment()));
field.setNullable(results.getString(dbQuery.nullable()));
field.setDefaultValue(results.getString(dbQuery.defaultValue()));
fieldList.add(field);
}
} catch (SQLException e) {
throw new JdbcMetaException(e);
}The realization of the field information is also relatively simple, directly query according to the corresponding sql, and then build it.
Processing of results
After a large number of deletions, we can obtain the most basic table metadata information.
But how to deal with this list information?
We can define an interface:
public interface IResultHandler {
/**
* 处理
* @param context 上下文
* @since 1.0.0
*/
void handle(final IResultHandlerContext context);
}The attribute of context is relatively simple, currently it is List<TableInfo> .
We can achieve a console output:
public class ConsoleResultHandler implements IResultHandler {
@Override
public void handle(IResultHandlerContext context) {
List<TableInfo> tableInfoList = context.tableInfoList();
for(TableInfo tableInfo : tableInfoList) {
// 数据
System.out.println("> " + tableInfo.getName() + " " + tableInfo.getComment());
System.out.println();
List<TableField> tableFields = tableInfo.getFields();
System.out.println("| 序列 | 列名 | 类型 | 是否为空 | 缺省值 | 描述 |");
System.out.println("|:---|:---|:---|:---|:---|:---|");
String format = "| %d | %s | %s | %s | %s | %s |";
int count = 1;
for (TableField field : tableFields) {
String info = String.format(format, count, field.getName(),
field.getType(), field.getNullable(), field.getDefaultValue(),
field.getComment());
System.out.println(info);
count++;
}
System.out.println("\n\n");
}
}
}The corresponding markdown field information is output on the console.
You can also implement your own html/pdf/word/excel and so on.
Test verification
We wrote so much in front of it, mainly based on the principle.
So whether the tool is easy to use, you still have to experience it.
Test code
JdbcMetadataBs.newInstance()
.url("jdbc:mysql://127.0.0.1:3306/test")
.includes("word")
.execute();Specifies to output the table information of test.word.
Effect
The corresponding log is as follows:
> word 敏感词表
| 序列 | 列名 | 类型 | 是否为空 | 缺省值 | 描述 |
|:---|:---|:---|:---|:---|:---|
| 1 | id | int(10) unsigned | NO | null | 应用自增主键 |
| 2 | word | varchar(128) | NO | null | 单词 |
| 3 | type | varchar(8) | NO | null | 类型 |
| 4 | status | char(1) | NO | S | 状态 |
| 5 | remark | varchar(64) | NO | | 配置描述 |
| 6 | operator_id | varchar(64) | NO | system | 操作员名称 |
| 7 | create_time | timestamp | NO | CURRENT_TIMESTAMP | 创建时间戳 |
| 8 | update_time | timestamp | NO | CURRENT_TIMESTAMP | 更新时间戳 |This is a simple markdown format, the actual effect is as follows:
word sensitive vocabulary
| sequence | Column name | Types of | Is it empty | Default value | describe |
|---|---|---|---|---|---|
| 1 | id | int(10) unsigned | NO | null | Application auto-incrementing primary key |
| 2 | word | varchar(128) | NO | null | word |
| 3 | type | varchar(8) | NO | null | Types of |
| 4 | status | char(1) | NO | S | state |
| 5 | remark | varchar(64) | NO | Configuration description | |
| 6 | operator_id | varchar(64) | NO | system | Operator name |
| 7 | create_time | timestamp | NO | CURRENT_TIMESTAMP | Creation timestamp |
| 8 | update_time | timestamp | NO | CURRENT_TIMESTAMP | Update timestamp |
In this way, we have one of the simplest jdbc metadata management tools.
Of course, this is only the v1.0.0 version, and there are many features that need to be added in the future.
summary
MPG basically every tool necessary for using mybatis has greatly improved our efficiency.
Knowing the corresponding implementation principle allows us to use it better, and on the basis of it, realize our own brains.
I am an old horse, and I look forward to seeing you again next time.
Note: There are many codes involved, and the text is simplified. If you are interested in the source code, you can follow {Lao Ma Xiaoxifeng} and reply {code generation} in the background to get it.
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。