Boring task

It all started years ago.

I had just joined a new company, and the project manager assigned me a relatively simple task of putting together a metadata table for all the database fields.

Because many of the projects I took over were incomplete, I needed to organize a basic dictionary table.

How would you handle this task?

Repetitive work

At first, I was going to organize all the fields manually, and then organize the corresponding SQL to be inserted into the metadata management table.

Meta_table Metadata table information

Meta_field Indicates metadata field information

At first it was a little exciting, then it was endless repetition, and it was boring.

So I wrote an open source gadget of my own.

https://github.com/houbb/metadata

Metadata Management

Metadata automatically stores all table information and field information in the metadata table for unified query.

(Comments need to ensure that the library itself contains comments for tables and fields)

Database Table design

Initially, three common databases were implemented: mysql, Oracle, SQL-Server.

For example, mysql uses the following table to create a table:

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 'id ', uid VARCHar (36) COMMENT' id ', name Varchar (125) COMMENT 'id ', DbObjectName Varchar (36) COMMENT 'name ', alias Varchar (125) comment' name ', Description Varchar (255) comment 'name ', IsNullable bool comment 'can be null ', dataType varchar(36) comment' dataType ', createTime datetime comment 'createTime ', UpdateTime dateTime COMMENT 'updateTime ', primary key (ID)) auto_increment = 1000 DEFAULT CHARSET= UTF8; Alter table meta_field COMMENT 'alter table meta_field COMMENT '; /*==============================================================*/ /* Table: meta_model */ /*==============================================================*/ create table meta_model ( ID int not Null auto_increment COMMENT 'id ', uid VARCHar (36) COMMENT' id ', name Varchar (125) COMMENT 'id ', DbObjectName Varchar (36) COMMENT 'name ', alias Varchar (125) comment' name ', Description Varchar (255) comment 'name ', Category varchar(36) comment 'category ', isVisible bool COMMENT' can be queried ', isEditable bool comment 'can be edited ', CreateTime dateTime COMMENT 'createTime dateTime ', primary key (ID)) DEFAULT CHARSET= utF8; Alter table meta_model COMMENT 'alter table meta_model COMMENT ';

Data initialization

Metadata is a Web application. After the deployment is started, the database connection information is specified on the page, and all data is initialized.

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' iD ', 'username' varchar(255) DEFAULT NULL COMMENT 'username ',' password 'varchar(255) DEFAULT NULL COMMENT' password ', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT=' id ';

For example, you can initialize the corresponding table and field information to the corresponding table.

Everything looked great and was done in a few minutes. Isn’t it?

Automatic code generation

If metadata had not been unexpected, I would hardly have changed it.

Not long ago, I implemented a low-code platform for automatic code generation based on MyBatis – plus-Generator.

Open source address is as follows:

http://github.com/houbb/low-code

I found that although metadata is a good web application, its reuse is very poor, and I could not implement a code generation tool on this basis.

Thus was born the idea of implementing a most basic JDBC metadata management tool.

The stones of other mountains can be used to attack jade.

We will directly take MPG source code as an example, learning and transformation.

Database metadata

Core principles

The core of metadata management is that all databases have metadata management.

Use mysql as an example to view all tables.

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 | | | sensitive feeling word table +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+--------- --+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----- ---------+ 1 row in set (0.00 SEC)

View the corresponding field information

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 | The select, insert, update, the references application on the primary key | | | | word varchar (128) | utf8_general_ci | NO UNI | | NULL | | The select, insert, update, the references words | | | type | varchar (8) | utf8_general_ci | NO | | NULL | | The select, insert, update, the references type | | | status | char (1) | utf8_general_ci | NO | | S | | State of the select, insert, update, the references | | | remark | varchar (64) | utf8_general_ci | NO | | | | The select, insert, update, the references described | | configuration | operator_id | varchar (64) | utf8_general_ci | NO | | system | | The select, insert, update, the references | | operators name | create_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | | The select, insert, update, the references | create timestamp | | update_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | on the update CURRENT_TIMESTAMP | select, insert, update, and the references update timestamp | | +-------------+------------------+-----------------+------+-----+-------------------+-----------------------------+----- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 8 rows in the set (0.01 SEC)

You can get very comprehensive information, and code generation is based on this basic information to generate the corresponding code text.

In which, the construction sentence 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 'status ', remark varchar(64) not null COMMENT' Operator_id vARCHar (64) NOT null default 'system' COMMENT 'operator name ', Create_time TIMESTAMP default CURRENT_TIMESTAMP not NULL COMMENT 'create timestamp ', Update_time TIMESTAMP default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment 'update_time timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP COMMENT' update_time timestamp default CURRENT_TIMESTAMP NOT null on update CURRENT_TIMESTAMP COMMENT 'update_time timestamp default CURRENT_TIMESTAMP NOT null on update CURRENT_TIMESTAMP COMMENT' ENGINE=Innodb default charset=UTF8 auto_increment=1; Create unique index UK_word on word (word) comment 'unique index ';

expanding

Although metadata acquisition is described above, mysql is used as an example.

However, when we implement tools, we must consider the corresponding extensibility.

This can be mysql or the common Oracle/SQL-Server.

Each database is acquired differently, so it needs to be implemented differently depending on the configuration.

Once metadata is obtained, it can be processed in a variety of ways.

Can console output, can be put in storage, can generate the corresponding markdown/PDF/word/excel/HTML documentation of different forms.

Ease of use

A good tool should shield the user from complex implementation details.

The user only needs to specify the configuration information, the table to be obtained, and the processing method.

As for the implementation, the user can not care.

The source code to achieve

Next, we combine the source code of MPG, extract the most core part to explain.

Obtaining a database connection

How do I get a Connection based on connection information?

If you use tools like Mybatis regularly, remember:

Public class DbConnection implements IDbConnection {/** * implements IDbConnection */ private String URL; /** * driverName */ private String driverName; Private String username; /** * 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 {/** * get database Connection * @return Connection * @since 1.0.0 */ Connection getConnection(); }

To facilitate later replacement implementations, you can even use database connection pooling:

https://github.com/houbb/jdbc-pool

Metadata query script

For different databases, the query method is different.

Taking 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"; }}

Show table status is used to view all table metadata. Run the show full fields from %s command to view the field metadata of a specific table.

The nullable() and defaultValue() properties are new and not available in MPG because code generation doesn’t care about them.

The core to realize

With that done, 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); // Build metadata. SQL String tableSql = buildTableSql(Context); // Run query List<TableInfo> tableInfoList = queryTableInfos(Connection, tableSql, dbQuery, context); return tableInfoList; }

Specific database implementation

The implementation of the specific database is different and can be obtained according to driverName.

DbTypeUtils is implemented as follows:

/** * @author binbin.hou * @since 1.0.0 */ public final class DbTypeUtils {private DbTypeUtils(){} /** * get dbType from driver * @param driverName driver information * @return result * @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; } /** * Obtain the corresponding database query type * @param dbType Database type * @return result * @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: // default MYSQL dbQuery = new MySqlQuery(); break; } return dbQuery; }}

Table data query SQL

SQL that builds the table data query from the corresponding IDbQuery.

/** * buildTableSql * @param context context * @return result * @since 1.0.0 */ private String buildTableSql(final) TableInfoContext Context) {// Get 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 uses tablesSql = string.format (tablesSql, "public"); } // Simplify oracle's special handling by returning tablesSql; }

Get the corresponding tablesSql directly, very simple answer.

Table information construction

Based on the constructed tableSql query, you can build the 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 (" the database is currently empty!! ") ); } } } catch (SQLException e) { throw new JdbcMetaException(e); }

The filtering of table information is omitted.

Field information construction

When table information is built, concrete field information is built.

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(); 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 implementation of field information is also relatively simple, directly according to the corresponding SQL query, and then build.

Processing of results

After a lot of deletions, we can get the most basic table metadata information.

But what do you do with this list of information?

We can define an interface:

Public interface IResultHandler {/** * handle * @param context * @since 1.0.0 */ void Handle (final) IResultHandlerContext context); }

The context property is simple, so far it’s List
.

We can implement a console output:

public class ConsoleResultHandler implements IResultHandler { @Override public void handle(IResultHandlerContext context) { List<TableInfo> tableInfoList = context.tableInfoList(); for(TableInfo tableInfo : Println ("> "+ tableInfo.getName() +" "+ tableInfo.getComment()); System.out.println(); List<TableField> tableFields = tableInfo.getFields(); System. The out. Println (" | | sequence column type | | for null default | | | "); 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"); }}}

Output the corresponding markDown field information on the console.

You can also implement your own HTML/PDF/Word/Excel, etc.

The validation test

We’ve written all this stuff so far and it’s basically a principle implementation.

So whether the tool is easy to use, or to experience.

The test code

JdbcMetadataBs. NewInstance (). A url (" JDBC: mysql: / / 127.0.0.1:3306 / test "). Includes (" word "). The execute ();

Specify the table information for output test.word.

The effect

The corresponding logs are as follows:

> word sensitive word | | sequence column type | | for null | the default description | | | : - | : - | : - | : - | : - | : - | | | 1 | id int (10) unsigned | NO | null | Application on the primary key | | 2 | | word varchar (128) | NO | null word | | | 3 | type | varchar (8) | NO | null type | | | | 4 status | char (1) | NO state | S | | | | 5 remark | varchar (64) | NO | | configuration description | | | 6 operator_id | varchar (64) | NO | system | | operators name | 7 | create_time | timestamp | NO | CURRENT_TIMESTAMP create a timestamp | | | | 8 update_time | timestamp | NO | CURRENT_TIMESTAMP | Update timestamp |

This is the simple MarkDown format, which actually looks like this:

Word Sensitive word list

The sequence	The column name	type	Whether is empty	The default value	describe
1	id	int(10) unsigned	NO	null	Apply the increment primary key
2	word	varchar(128)	NO	null	The word
3	type	varchar(8)	NO	null	type
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	Creating a Timestamp
8	update_time	timestamp	NO	CURRENT_TIMESTAMP	Update timestamp

Thus, we have one of the simplest JDBC metadata management tools available.

Of course, this is only the V1.0.0 release, and there are many more features to be added.

summary

MPG is basically every tool necessary to use MyBatis, greatly improving our efficiency.

Knowing the corresponding implementation principle allows us to better use it and realize our own imagination on the basis of it.

I am an old horse, looking forward to the next reunion with you.

Note: There is a lot of code involved, which is simplified in this article. If you are interested in the source code, you can pay attention to {old horse xiao xifeng}, the background reply {code generation} can be obtained.