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

/**
 * @description 抓取MySQL, Oracle的元数据信息
 * @author: ZhiWen
 * @create: 2019-09-29 17:45
 **/
@Service
public class MetaFetchImpl implements MetaFetch {

    @Override
    public 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;
    }

    @Override
    public 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";