关系型数据库的元数据(MySQL,Oracle)

/*** @description 抓取MySQL, Oracle的元数据信息* @author: ZhiWen* @create: 2019-09-29 17:45**/@Servicepublic class MetaFetchImpl implements MetaFetch {@Overridepublic List<DataTableMeta> fetch(MetaFetchDTO metaFetchDTO) {List<DataTableMeta> dataTableMetas = null;String jdbcUrl = Constants.MYSQL_JDBC_HEAD + metaFetchDTO.getUrl() + Constants.MYSQL_META_INFO;SqlUtil sqlUtil = new SqlUtil(jdbcUrl, metaFetchDTO.getUsername(), metaFetchDTO.getPassword());String sqlText = \"select t1.table_schema,\\n\" +\"       t1.table_name,\\n\" +\"       t1.column_name,\\n\" +\"       t1.ordinal_position,\\n\" +\"       t1.is_nullable,\\n\" +\"       t1.data_type,\\n\" +\"       t1.column_type,\\n\" +\"       t1.character_maximum_length,\\n\" +\"       t1.numeric_precision,\\n\" +\"       t1.numeric_scale,\\n\" +\"       t1.column_key,\\n\" +\"       t1.column_comment\\n\" +\"  from information_schema.columns t1\\n\" +\" where t1.table_schema = \'\" +metaFetchDTO.getSchema() +\"\' and t1.table_name = \'\" +metaFetchDTO.getTable() + \"\'\";try {dataTableMetas = sqlUtil.executePageQuery(sqlText);} catch (Exception e) {e.printStackTrace();}return dataTableMetas;}@Overridepublic List<DataTableMeta> fetchOracle(OracleRequestDTO requestDTO) {List<DataTableMeta> dataTableMetas = null;String jdbcUrl = String.format(SqlConst.oracleUrl, requestDTO.getUrl(), requestDTO.getDatabaseName());SqlUtil sqlUtil = new SqlUtil(jdbcUrl, Constants.ORACLE_OWNER, Constants.ORACLE_PASSWORD);String owner = requestDTO.getOwner().toUpperCase();String tableName = requestDTO.getTableName().toUpperCase();String sqlText = \"select t1.owner,\\n\" +\" t1.table_name,\\n\" +\" t1.column_name,\\n\" +\" t1.data_type,\\n\" +\" t1.data_length,\\n\" +\" t1.data_precision,\\n\" +\" t1.data_scale,\\n\" +\" t1.nullable,\\n\" +\" t1.column_id,\\n\" +\" t1.internal_column_id,\\n\" +\" t1.hidden_column,\\n\" +\" t1.virtual_column,\\n\" +\" decode(t2.is_pk, 1, \'Y\', \'N\') as is_pk,\\n\" +\" decode(t2.position, null, -1, t2.position) as position,\\n\" +\" t1.char_length,\\n\" +\" t1.char_used,\\n\" +\" tcc.comments\\n\" +\" from (select * from dba_tab_cols t\\n\" +\" where t.owner = \'\" + owner +\"\' and t.table_name = \'\" + tableName + \"\') t1\\n\" +\" left join all_col_comments tcc on (t1.owner = tcc.owner and t1.table_name = tcc.table_name and t1.column_name = tcc.column_name)\\n\" +\" left join (select cu.owner, cu.table_name, cu.column_name, cu.position, 1 as is_pk\\n\" +\" from dba_cons_columns cu, dba_constraints au\\n\" +\" where cu.constraint_name = au.constraint_name\\n\" +\" and cu.owner = au.owner\\n\" +\" and au.constraint_type = \'P\'\\n\" +\" and au.table_name = \'\" + tableName + \"\'\\n\" +\" and au.owner = \'\" + owner + \"\') t2 on (t1.column_name = t2.column_name and t1.table_name = t2.table_name and t1.owner = t2.owner)\";try {dataTableMetas = sqlUtil.executeQueryOracle(sqlText);} catch (Exception e) {e.printStackTrace();}return dataTableMetas;}}

补充一个查询MySQL列名,类型,描述的SQL:

String sql = \"SELECT\\n\" +\" column_name,\\n\" +\" data_type,\\n\" +\" column_comment\\n\" +\"FROM\\n\" +\" information_schema. COLUMNS\\n\" +\"WHERE\\n\" +\" table_schema =(SELECT DATABASE())\\n\" +\" AND table_name =?\\n\" +\"ORDER BY\\n\" +\" ordinal_position\";