数据库元数据metadata获取

项目需求

最近开发一个聚合获取数据的需求, 提供一个接口, 根据输入的检索条件, 获取数据库表中的数据, 并且将数据和数据元数据同时进行返回, 返回的数据格式为:

{
	"data":[
		{
			"id":1,
			"name":"wt"
		},
		{
			"id":2,
			"name":"lily"
		}
	],
	"metadata": [
		{
			"fieldName": "字段名称 id",
			"fieldType": "字段类型 int",
			"fieldComment": "字段备注 主键"
		},
		{
			"fieldName": "字段名称 name",
			"fieldType": "字段类型 varcher",
			"fieldComment": "字段备注 姓名"
		{
	]
}

SQL语句获取数据库元数据信息

• 方式一

SHOW COLUMNS FROM `user`

结果:

这种方式获取到的结果, 只有最简单的一些描述信息, 而且获取不到字段的配置信息.

• 方式二

select * from information_schema.columns where table_name = 'user' AND TABLE_SCHEMA = 'test'

JdbcTemplate获取metadata元数据信息

• 使用三方连接池方式获取metadata元数据信息

ResultSet resultSet = jdbcTemplate.getDataSource().getConnection().getMetaData().getColumns(null, "test", "user", "%");
while (resultSet.next()) {
	Map<String, Object> map = new HashMap<>();
	map.put("columnName", resultSet.getString("COLUMN_NAME"));
	map.put("dataType", resultSet.getString("TYPE_NAME"));
	map.put("comment", resultSet.getString("REMARKS"));
	metadataList.add(map);
}

项目中, 使用的数据库是: MySQL和GBase两种数据库, 这种方式获取metadata信息时, 对于MySQL是没有问题的, 但是对于GBase数据库来说, 是获取不到数据信息的. 我使用了Druid连接池和Hikari连接池进行测试, 对GBase数据库都是无法获取到metadata信息的.

使用java原生的jdbc获取metadata元数据信息

public List getMetaDataWithResultSet() {
	Connection connection = getJdkConnect();
	List<Map<String, Object>> metadataList = new ArrayList<>();
	try {
		ResultSet resultSet = connection.getMetaData().getColumns(null, "test", "user", "%");
		while (resultSet.next()) {
 			Map<String, Object> map = new HashMap<>();
			map.put("columnName", resultSet.getString("COLUMN_NAME"));
			map.put("dataType", resultSet.getString("TYPE_NAME"));
			map.put("comment", resultSet.getString("REMARKS"));
			metadataList.add(map);
		}
	} catch (SQLException ex) {
		ex.printStackTrace();
	}
	return metadataList;
}

public Connection getJdkConnect() {
	Connection connection = null;
	try {
		connection = DriverManager.getConnection(mysqlProperties.getUrl(),mysqlProperties.getUsername(), mysqlProperties.getPassword());
	} catch (SQLException ex) {
		ex.printStackTrace();
	}
	return connection;
}

这种java原生的方式对于MySQL和GBase数据库都是可以获取到metadata元数据信息的.

JdbcTemplate执行SQL语句, 获取metadata元数据信息

public List getMetaDataFromTable(String tableName) {
	String sql = "select * from information_schema.columns where table_name = '" + tableName +"'";
	return jdbcTemplate.queryForList(sql);
}

返回结果:

[
    {
        "TABLE_CATALOG":"def",
        "TABLE_SCHEMA":"test",
        "TABLE_NAME":"user",
        "COLUMN_NAME":"id",
        "ORDINAL_POSITION":1,
        "IS_NULLABLE":"NO",
        "DATA_TYPE":"bigint",
        "NUMERIC_PRECISION":19,
        "NUMERIC_SCALE":0,
        "COLUMN_TYPE":"bigint(20)",
        "COLUMN_KEY":"PRI",
        "EXTRA":"auto_increment",
        "PRIVILEGES":"select,insert,update,references",
        "COLUMN_COMMENT":"主键",
        "GENERATION_EXPRESSION":""
    },
    {
        "TABLE_CATALOG":"def",
        "TABLE_SCHEMA":"test",
        "TABLE_NAME":"user",
        "COLUMN_NAME":"name",
        "ORDINAL_POSITION":2,
        "IS_NULLABLE":"NO",
        "DATA_TYPE":"varchar",
        "CHARACTER_MAXIMUM_LENGTH":255,
        "CHARACTER_OCTET_LENGTH":255,
        "CHARACTER_SET_NAME":"latin1",
        "COLLATION_NAME":"latin1_swedish_ci",
        "COLUMN_TYPE":"varchar(255)",
        "COLUMN_KEY":"",
        "EXTRA":"",
        "PRIVILEGES":"select,insert,update,references",
        "COLUMN_COMMENT":"姓名",
        "GENERATION_EXPRESSION":""
    },
    {
        "TABLE_CATALOG":"def",
        "TABLE_SCHEMA":"test",
        "TABLE_NAME":"user",
        "COLUMN_NAME":"age",
        "ORDINAL_POSITION":3,
        "IS_NULLABLE":"NO",
        "DATA_TYPE":"int",
        "NUMERIC_PRECISION":10,
        "NUMERIC_SCALE":0,
        "COLUMN_TYPE":"int(10)",
        "COLUMN_KEY":"",
        "EXTRA":"",
        "PRIVILEGES":"select,insert,update,references",
        "COLUMN_COMMENT":"年龄",
        "GENERATION_EXPRESSION":""
    },
    {
        "TABLE_CATALOG":"def",
        "TABLE_SCHEMA":"test",
        "TABLE_NAME":"user",
        "COLUMN_NAME":"ct_time",
        "ORDINAL_POSITION":4,
        "IS_NULLABLE":"NO",
        "DATA_TYPE":"datetime",
        "DATETIME_PRECISION":0,
        "COLUMN_TYPE":"datetime",
        "COLUMN_KEY":"",
        "EXTRA":"on update CURRENT_TIMESTAMP",
        "PRIVILEGES":"select,insert,update,references",
        "COLUMN_COMMENT":"创建时间",
        "GENERATION_EXPRESSION":""
    }
]

这种方式, 试了一下, 对于GBase数据库同样是适用的.