数据库元数据metadata获取

项目需求SQL语句获取数据库元数据信息JdbcTemplate获取metadata元数据信息使用java原生的jdbc获取metadata元数据信息JdbcTemplate执行SQL语句, 获取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数据库同样是适用的.