针对不同数据库，获取当前用户所有有权限查看的表，以及表的创建时间、更新时间、注释等信息，表中字段的相关信息(包含分页实现) -

weitao1026

浏览: 1057972 次
性别:
来自: 上海

最近访客更多访客>>

vicento4

723499280

liuzidong

s1986q

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

针对不同数据库，获取当前用户所有有权限查看的表，以及表的创建时间、更新时间、注释等信息，表中字段的相关信息(包含分页实现)

博客分类：

mysql/oracle/sqlserver/db2/mongdb/redis/neo4j/GreenPlum/Teradata/hsqldb/Derby/sakila

最近在处理一个需求，需求是这样的：

给定任意一个数据库的JDBC连接、用户名、密码
查询出所有有权限访问的表的相关信息：表名，创建时间，更新时间，注释
要支持分页
数据库类型有：MySQL、GBase、Oracle、DB2、Greenplum、Hive
本来还有 HDFS和Kafka的，但是后来去掉了。

我自己平时主要使用的是 MySQL，所以，对于 mysql 而言，这个需求还是比较好处理的。但是其他几个就比较困难了，甚至有些之前连名字都没有听过，如 Greenplum、GBase。

不过，需求总得处理啊。就为这个需求，我整整搞了四天。下面就把处理方法记录一下吧。在重点的地方我会加上注释。

首先，看一下最终的类型的结构图吧

类结构图

然后看一下使用方式

根据不同的数据库类型，使用对应的子类进行处理。
使用方式

AbstractTableInfoUtil.Java

所有处理类的超类

public abstract class AbstractTableInfoUtil {

    // 一个对象，它包括了 jdbc url、jdbc driver class、username、password
    protected final BiDataSource dataSource;

    public AbstractTableInfoUtil(final BiDataSource dataSource) {
        this.dataSource = dataSource;
    }

    // 一个枚举类，用来记录当前是哪个类型的数据库
    protected abstract SourceType getSourceType();

    // 获取有权限访问的 表 的总数
    public int getTableCount() throws SQLException {
        int tableCount = 0;
        if (dataSource != null) {
            try {
                Class.forName(dataSource.datasourceType.className);
            } catch (ClassNotFoundException e) {
                play.Logger.error(e.getMessage(), e);
            }

            // 具体由子类实现
            tableCount = getTableCount(dataSource);
        }

        return tableCount;
    }

    // 由子类实现 获取表的总数
    protected abstract int getTableCount(final BiDataSource dataSource);

    // 根据分页参数，获取指定的 表的集合
    public List getTableInfoList(final int pageSize, final int pageNumber) throws SQLException {
        List tableInfoList = Collections.emptyList();
        if (dataSource != null) {
            try {
                Class.forName(dataSource.datasourceType.className);
            } catch (ClassNotFoundException e) {
                play.Logger.error(e.getMessage(), e);
            }

            // 具体由子类实现
            tableInfoList = getTableInfoList(dataSource, pageSize, pageNumber);
        }
        return tableInfoList;
    }

    // 由子类实现 获取指定数量的 表的集合
    protected abstract List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber);

    // 获取指定的表的所有字段
    public List getTableFields(final String schemaName, final String tableName) throws SQLException {
        List list = Collections.emptyList();
        if (dataSource != null) {
            try {
                Class.forName(dataSource.datasourceType.className);
            } catch (ClassNotFoundException e) {
                play.Logger.error(e.getMessage(), e);
            }

            // 具体由子类实现
            list = getTableFields(dataSource, schemaName, tableName);
        }
        return list;
    }

    // 由子类实现 获取指定表的所有字段
    protected abstract List getTableFields(final BiDataSource dataSource, final String schemaName, final String tableName);
}

AbstractDBTableInfoUtil.java

作为所有数据处理类的超类。
之所以新建这个超类，是因为需求一开始还有 HDFS 和 Kafka ，而这两个是非DB类的。

public abstract class AbstractDBTableInfoUtil extends AbstractTableInfoUtil {
    private static final String TABLE = "TABLE";
    private static final String TABLE_SCHEMA = "TABLE_SCHEM";
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String COLUMN_NAME = "COLUMN_NAME";
    private static final String COLUMN_SIZE = "COLUMN_SIZE";
    private static final String TYPE_NAME = "TYPE_NAME";
    private static final String COLUMN_DEF = "COLUMN_DEF";

    /**
     * 需要排除的 schema。
     * 一般是那些系统的schema。
     * 在子类中具体指定。
     */
    protected String excludeSchema;

    public AbstractDBTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);
    }

    // util 方法，用于将 "%" 和 "_" 进行转义
    protected static String escapeWildcard(String source, String escape) {
        if (null == source || "".equals(source)) {
            return null;
        }
        String result = source.replace("%", (escape + "%"));
        result = result.replace("_", (escape + "_"));
        return result;
    }

    // 获取有权限访问的所有表的总数
    @Override
    public int getTableCount(final BiDataSource dataSource) {
        int total = 0;

        // 由子类生成 获取总数 的SQL 语句
        String sql = getTableCountSQL();

        play.Logger.info(sql);

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ResultSet countResult = ps.executeQuery();

            if (countResult.next()) {
                total = countResult.getInt(1);
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }
        return total;
    }

    // 获取表的总数的SQL。由子类实现。
    protected abstract String getTableCountSQL();

    // 获取表的所有字段
    // 所有数据库都会提供相应的 JDBC driver，
    // 所以我们只要通过 JDBC 中的 getColumns() 方法，就可以获取表中的所有字段
    @Override
    protected List getTableFields(final BiDataSource dataSource, final String schemaName, final String tableName) {
        List list = new ArrayList();

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password)) {

            DatabaseMetaData metaData = connection.getMetaData();

            // 注意，此处 需要对 schema 和 table 的名称进行 "%" 和 "_" 字符的转义
            final String schema = escapeWildcard(schemaName, metaData.getSearchStringEscape());
            final String table = escapeWildcard(tableName, metaData.getSearchStringEscape());

            ResultSet resultSet;

            SourceType sourceType = this.getSourceType();

            // mysql 和 gbase 与其他DB的处理方式不一样
            // 为啥不一样?如果不这么处理，那么，mysql 将无法获取 jdbc url 指定的schema 以外的schema 中的表的字段
            // 比如，给定一个jdbc url: jdbc:mysql://localhost:3306/test
            // 那么，我们将只能获取 test 这个schema 下的表的字段
            // 具体请参照: http://stackoverflow.com/questions/38557956/databasemetadatagetcolumns-returns-an-empty-resultset

            if (sourceType == SourceType.MYSQL || sourceType == SourceType.GBASE) {
                resultSet = metaData.getColumns(schema, null, table, "%");
            } else {
                resultSet = metaData.getColumns(null, schema, table, "%");
            }

            while (resultSet.next()) {
                // TableFieldInfo 是一个POJO
                TableFieldInfo tableFieldInfo = new TableFieldInfo();
                tableFieldInfo.name = resultSet.getString(COLUMN_NAME);
                tableFieldInfo.type = resultSet.getString(TYPE_NAME);
                tableFieldInfo.defaultValue = resultSet.getObject(COLUMN_DEF);
                tableFieldInfo.length = resultSet.getInt(COLUMN_SIZE);
                Map map = tableFieldInfo.toMap();
                list.add(map);
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }
        return list;
    }

    // 获取 jdbc url 下所有有权限访问的 schema
    // 本来没有这个方法的，在最后处理到 Hive 时，没有办法了，才加了这个方法
    public List<String> getSchemas(final BiDataSource dataSource) {
        List<String> schemaList = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password)) {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            ResultSet schemaSet = dbMetaData.getSchemas();
            while (schemaSet.next()) {
                schemaList.add(schemaSet.getString(TABLE_SCHEMA));
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return schemaList;
    }

    // 获取指定 schema 下的所有表
    // 本来没有这个方法的，在最后处理到 Hive 时，没有办法了，才加了这个方法
    // 不过，这个方法被下面的同名方法给取代了，但也没有删除此方法。
    public List<String> getTables(final BiDataSource dataSource, String schemaName) {
        List<String> tableList = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password)) {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            final String schema = escapeWildcard(schemaName, dbMetaData.getSearchStringEscape());

            ResultSet tableRet;

            SourceType sourceType = this.getSourceType();
            // 此处处理，请参见上面 getTableFields() 方法的说明
            if (sourceType == SourceType.MYSQL || sourceType == SourceType.GBASE) {
                tableRet = dbMetaData.getTables(schema, null, "%", new String[]{TABLE});
            } else {
                tableRet = dbMetaData.getTables(null, schema, "%", new String[]{TABLE});
            }

            while (tableRet.next()) {
                tableList.add(tableRet.getString(TABLE_NAME));
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableList;
    }

    // 获取指定 schema 下的所有表
    // 本来没有这个方法的，在最后处理到 Hive 时，没有办法了，才加了这个方法
    // 与上面的方法基本一样，该方法可以同时处理多个 schema
    public List<Pair<String, String>> getTables(final BiDataSource dataSource, List<String> schemas) {
        // pair: {key: schema, value: table}
        List<Pair<String, String>> tableList = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password)) {
            DatabaseMetaData dbMetaData = connection.getMetaData();
            for (String schemaName : schemas) {

                final String schema = escapeWildcard(schemaName, dbMetaData.getSearchStringEscape());

                ResultSet tableRet;

                SourceType sourceType = this.getSourceType();
                try {
                    // 此处处理，请参见上面 getTableFields() 方法的说明
                    if (sourceType == SourceType.MYSQL || sourceType == SourceType.GBASE) {
                        tableRet = dbMetaData.getTables(schema, null, "%", new String[]{TABLE});
                    } else {
                        tableRet = dbMetaData.getTables(null, schema, "%", new String[]{TABLE});
                    }

                    while (tableRet.next()) {
                        tableList.add(new Pair(schemaName, tableRet.getString(TABLE_NAME)));
                    }
                } catch (SQLException e) {
                    play.Logger.error(e.getMessage(), e);
                }
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableList;
    }
}

MySQLTableInfoUtil.java

Mysql数据库对应的处理类。

public class MySQLTableInfoUtil extends AbstractDBTableInfoUtil {
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String TABLE_SCHEMA = "TABLE_SCHEMA";
    private static final String CREATE_TIME = "CREATE_TIME";
    private static final String UPDATE_TIME = "UPDATE_TIME";
    private static final String TABLE_COMMENT = "TABLE_COMMENT";

    public MySQLTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // 指定系统 schema
        excludeSchema = "('information_schema')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.MYSQL;
    }

    // 获取表的总数的SQL
    @Override
    protected String getTableCountSQL() {
        // 在 使用 MessageFormat 时，一定要注意：若字符串需要使用单引号(')时，必须连续写两个，否则，将在 format 时，将会被去掉
        // 即，如果写成 'T'，那么，最终结果是 T，如果是 ''T''，最终结果才是 'T'
        // 不过，作为参数传给 format 时，只需要写一个 ' 即可，如上面的 excludeSchema 的值
        String sql = MessageFormat.format("SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN {0} AND TABLE_TYPE=''BASE TABLE''", excludeSchema);
        play.Logger.info(sql);
        return sql;
    }

    // 获取指定数量的 表的集合，即分页
    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        // mysql的版本需要在 5.7 以上，否则，create_time 和 update_time 可能返回NULL
        play.Logger.warn("For mysql, make sure the server which you connect to is of version >= 5.7.x");
        play.Logger.warn("If not, the CREATE_TIME and UPDATE_TIME maybe return NULL");

        List tableProperties = new ArrayList();

        final int offset = (pageNumber - 1) * pageSize;
        final int limit = pageSize;

        // 把字段名作成了参数({0} ~ {4})传了进去，这样做是为了保证在下面的 ResultSet get时，可以直接使用 列名，而不是使用 index
        String sql = MessageFormat.format("" +
                        "SELECT {0}, {1}, {2}, {3}, {4} FROM INFORMATION_SCHEMA.TABLES " +
                        "WHERE TABLE_SCHEMA NOT IN {5} AND TABLE_TYPE=''BASE TABLE'' " +
                        "ORDER BY {1} LIMIT ? OFFSET ?",
                TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME, excludeSchema);

        play.Logger.info(sql);

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(1, limit);
            ps.setInt(2, offset);

            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                // TableInfo 是一个POJO
                TableInfo tableInfo = new TableInfo();

                tableInfo.schema = resultSet.getString(TABLE_SCHEMA);
                tableInfo.tableComment = resultSet.getString(TABLE_COMMENT);
                tableInfo.tableName = resultSet.getString(TABLE_NAME);

                Date createTime = resultSet.getDate(CREATE_TIME);
                tableInfo.createTime = createTime != null ? createTime.getTime() : -1;

                Date updateTime = resultSet.getDate(UPDATE_TIME);
                tableInfo.updateTime = updateTime != null ? updateTime.getTime() : tableInfo.createTime;

                tableProperties.add(tableInfo.toMap());
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableProperties;
    }
}

GBaseTableInfoUtil.java

GBase数据库的处理。
本来对GBase这个数据库一点都不了解，后来在网上找了它的使用手册之后，发现对它的处理跟MYSQL是一样的。

public class GBaseTableInfoUtil extends MySQLTableInfoUtil {

    public GBaseTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.GBASE;
    }
}

OracleTableInfoUtil.java

Oracle 数据库的处理。

public class OracleTableInfoUtil extends AbstractDBTableInfoUtil {

    private static final String OWNER = "OWNER";
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String COMMENTS = "COMMENTS";
    private static final String CREATED = "CREATED";
    private static final String LAST_DDL_TIME = "LAST_DDL_TIME";

    private static final String EXCLUDE_SCHEMA_PREFIX_APEX = "'APEX%'";
    private static final String EXCLUDE_TABLESPACE_PREFIX_SYSTEM = "'SYS%'";


    public OracleTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // Oracle的系统 schema 非常多，无法列全，此处是根据我自己的oracle中已经存在的 系统schema 进行列举的
        excludeSchema = "" +
                "('ANONYMOUS', 'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSNMP', 'DIP', 'DVF', 'DVSYS', " +
                "'FLOWS_FILES', 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', " +
                "'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OJVMSYS', " +
                "'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', " +
                "'OUTLN', 'OWBSYS', 'OWBSYS_AUDIT', 'SCOTT', 'SI_INFORMTN_SCHEMA', " +
                "'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSBACKUP', " +
                "'SYSDG', 'SYSKM', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.ORACLE;
    }

    @Override
    protected String getTableCountSQL() {
        // 大家可以注意一下，这里我还加了两个限制条件
        // 1. OWNER NOT LIKE 'APEX%'
        // 2. TABLESPACE_NAME NOT LIKE 'SYS%'
        // 这两个条件都是为了过滤 系统schema 的
        String sql = MessageFormat.format("SELECT COUNT(1) FROM ALL_TABLES WHERE OWNER NOT IN {0} AND OWNER NOT LIKE {1} AND TABLESPACE_NAME NOT LIKE {2}",
                excludeSchema, EXCLUDE_SCHEMA_PREFIX_APEX, EXCLUDE_TABLESPACE_PREFIX_SYSTEM);
        play.Logger.info(sql);
        return sql;
    }

    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        List tableProperties = new ArrayList();

        final int start = (pageNumber - 1) * pageSize + 1;
        final int end = start + pageSize;

        // oracle 的相关信息需要从几张表中关联得出
        // 1. ALL_TABLES
        // 2. ALL_OBJECTS
        // 3. USER_TAB_COMMENTS
        // 另外，请注意一下 Oracle 的分页方式。其中，为了让结果按 TABLE_NAME排序，我们必须做一个子查询，否则，分页出来的结果可能不是你想要的
        String sql = MessageFormat.format(
                "" +
                        "SELECT " +
                        "  * " +
                        "FROM " +
                        "  (" +
                        "    SELECT " +
                        "      AT.*, UTC.{2} {2}, AO.{3} {3}, AO.{4} {4}, ROWNUM RN" +
                        "    FROM" +
                        "      (" +
                        "        SELECT" +
                        "          {0}, {1}" +
                        "        FROM" +
                        "          ALL_TABLES" +
                        "        WHERE" +
                        "          OWNER NOT IN {5}" +
                        "          AND" +
                        "          OWNER NOT LIKE {6}" +
                        "          AND" +
                        "          TABLESPACE_NAME NOT LIKE {7}" +
                        "        ORDER BY {1}" +
                        "      ) AT" +

                        "      LEFT JOIN" +
                        "      ALL_OBJECTS AO" +
                        "      ON" +
                        "        AT.OWNER=AO.OWNER" +
                        "        AND" +
                        "        AT.TABLE_NAME=AO.OBJECT_NAME" +

                        "      LEFT JOIN" +
                        "      USER_TAB_COMMENTS UTC" +
                        "      ON" +
                        "        AO.OBJECT_NAME=UTC.TABLE_NAME" +

                        "    WHERE" +
                        "      ROWNUM<?" +
                        "  ) " +
                        "WHERE" +
                        "  RN>=?",
                OWNER, TABLE_NAME, COMMENTS, CREATED, LAST_DDL_TIME, excludeSchema, EXCLUDE_SCHEMA_PREFIX_APEX, EXCLUDE_TABLESPACE_PREFIX_SYSTEM);

        play.Logger.info(sql);

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(1, end);
            ps.setInt(2, start);

            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo();

                tableInfo.schema = resultSet.getString(OWNER);
                tableInfo.tableName = resultSet.getString(TABLE_NAME);
                tableInfo.tableComment = resultSet.getString(COMMENTS);

                Date createTime = resultSet.getDate(CREATED);
                tableInfo.createTime = createTime != null ? createTime.getTime() : -1;

                Date updateTime = resultSet.getDate(LAST_DDL_TIME);
                tableInfo.updateTime = updateTime != null ? updateTime.getTime() : tableInfo.createTime;

                tableProperties.add(tableInfo.toMap());
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableProperties;
    }
}

DB2TableInfoUtil.java

DB2数据库的处理。

public class DB2TableInfoUtil extends AbstractDBTableInfoUtil {

    private static final String TABSCHEMA = "TABSCHEMA";
    private static final String TABNAME = "TABNAME";
    private static final String REMARKS = "REMARKS";
    private static final String CREATE_TIME = "CREATE_TIME";
    private static final String STATS_TIME = "STATS_TIME";
    private static final String ALTER_TIME = "ALTER_TIME";


    public DB2TableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // 系统 schema
        excludeSchema = "('SYSCAT', 'SYSIBM', 'SYSIBMADM', 'SYSPUBLIC', 'SYSSTAT', 'SYSTOOLS')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.DB2;
    }

    @Override
    protected String getTableCountSQL() {
        // 从 SYSCAT.TABLES 表中可以得到想要的结果
        String sql = MessageFormat.format("SELECT COUNT(1) FROM SYSCAT.TABLES WHERE TABSCHEMA NOT IN {0} AND TYPE = ''T''", excludeSchema);
        play.Logger.info(sql);
        return sql;
    }

    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        List tableProperties = new ArrayList();

        final int start = (pageNumber - 1) * pageSize;
        final int end = start + pageSize;

        // DB2 的分页方式与 Oracle 的类似。而且，为了排序，也需要先做一个 子查询
        String sql = MessageFormat.format(
                "" +
                        "SELECT " +
                        "  TAB.* " +
                        "FROM " +
                        "  (" +
                        "    SELECT " +
                        "      T.*, ROWNUMBER() OVER() AS ROW_NUMBER " +
                        "    FROM" +
                        "      (" +
                        "        SELECT" +
                        "          {0}, {1}, {2}, {3}, {4}, {5}" +
                        "        FROM" +
                        "          SYSCAT.TABLES" +
                        "        WHERE" +
                        "          TABSCHEMA NOT IN {6}" +
                        "          AND" +
                        "          TYPE=''T''" +
                        "          ORDER BY {1}" +
                        "      ) AS T" +
                        "  ) TAB " +
                        "WHERE" +
                        "  TAB.ROW_NUMBER>?" +
                        "  AND" +
                        "  TAB.ROW_NUMBER<=?",
                TABSCHEMA, TABNAME, REMARKS, CREATE_TIME, STATS_TIME, ALTER_TIME, excludeSchema);

        play.Logger.info(sql);

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(1, start);
            ps.setInt(2, end);

            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo();

                tableInfo.schema = resultSet.getString(TABSCHEMA);
                tableInfo.tableName = resultSet.getString(TABNAME);
                tableInfo.tableComment = resultSet.getString(REMARKS);

                Date createTime = resultSet.getDate(CREATE_TIME);
                tableInfo.createTime = createTime != null ? createTime.getTime() : -1;

                Date statsTime = resultSet.getDate(STATS_TIME);
                Date alterTime = resultSet.getDate(ALTER_TIME);
                tableInfo.updateTime = (statsTime != null ? statsTime.getTime() : (alterTime != null ? alterTime.getTime() : tableInfo.createTime));

                tableProperties.add(tableInfo.toMap());
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableProperties;
    }
}

GreenPlumTableInfoUtil.java

Greenplum数据库的处理。
Greenplum是基于 postgresql 的。
这个是我调查时间最长的一个数据库。
它的 CREATE_TIME 和 UPDATE_TIME 没有专门的表来记录，表的注释还需要通过内置函数来获取。

public class GreenPlumTableInfoUtil extends AbstractDBTableInfoUtil {
    private static final String TABLE_CATALOG = "TABLE_CATALOG";
    private static final String TABLE_SCHEMA = "TABLE_SCHEMA";
    private static final String TABLE_NAME = "TABLE_NAME";
    private static final String CREATE_TIME = "CREATE_TIME";
    private static final String UPDATE_TIME = "UPDATE_TIME";
    private static final String TABLE_COMMENT = "TABLE_COMMENT";

    public GreenPlumTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);

        // 系统 schema
        excludeSchema = "('information_schema', 'pg_catalog', 'gp_toolkit')";
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.GREENPLUM;
    }

    @Override
    protected String getTableCountSQL() {
        String sql = MessageFormat.format("SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN {0} AND TABLE_TYPE=''BASE TABLE''", excludeSchema);
        play.Logger.info(sql);
        return sql;
    }

    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {

        List tableProperties = new ArrayList();

        final int offset = (pageNumber - 1) * pageSize;
        final int limit = pageSize;

        // 三个表通过 pg_class 表中的 OID 字段进行关关联
        // 请特别注意一下 INFORMATION_SCHEMA.TABLES 是如何来计算OID的
        // 也请注意一下，表的注释 是通过 OBJ_DESCRIPTION() 这个内置函数来获取的
        String sql = MessageFormat.format("" +
                        "SELECT" +
                        "  T.{0}, T.{1}, T.{2}, OBJ_DESCRIPTION(C.OID) AS \"{3}\", MIN(O.STATIME) AS \"{4}\", MAX(O.STATIME) AS \"{5}\" " +
                        "FROM" +
                        "  INFORMATION_SCHEMA.TABLES AS T" +

                        "  LEFT JOIN" +
                        "  PG_CLASS AS C" +
                        "  ON" +
                        "    C.OID=(T.{1}||''.''||T.{2})::REGCLASS" +

                        "  LEFT JOIN" +
                        "  PG_STAT_LAST_OPERATION AS O" +
                        "  ON" +
                        "    C.OID=O.OBJID " +

                        "WHERE" +
                        "  TABLE_SCHEMA NOT IN {6}" +
                        "  AND" +
                        "  TABLE_TYPE=''BASE TABLE'' " +
                        "GROUP BY" +
                        "  T.{0}, T.{1}, T.{2}, \"{3}\" " +
                        "ORDER BY {2} " +
                        "LIMIT ? OFFSET ?",
                TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT, CREATE_TIME, UPDATE_TIME, excludeSchema);

        play.Logger.info(sql);

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             PreparedStatement ps = connection.prepareStatement(sql)) {

            ps.setInt(2, offset);
            ps.setInt(1, limit);

            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                TableInfo tableInfo = new TableInfo();

                tableInfo.tableComment = resultSet.getString(TABLE_COMMENT);
                tableInfo.schema = resultSet.getString(TABLE_SCHEMA);
                tableInfo.tableName = resultSet.getString(TABLE_NAME);

                Date createTime = resultSet.getDate(CREATE_TIME);
                tableInfo.createTime = createTime != null ? createTime.getTime() : -1;

                Date updateTime = resultSet.getDate(UPDATE_TIME);
                tableInfo.updateTime = updateTime != null ? updateTime.getTime() : tableInfo.createTime;

                tableProperties.add(tableInfo.toMap());
            }
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableProperties;
    }
}

HiveTableInfoUtil.java

Hive仓库的处理。
对hive的调查时间也很长。
它跟其它数据库不一样，因为它的 jdbc url 是连接hive的，而 hive 本身还有一个 meta store (即元数据的数据库)，这个meta store 我们是无法连接到的。这就导致了，我们无法像其它数据库那样，可以通过类似 information_schema.tables 类来获取 table一览。

所以，我进行了变通，先通过 AbstractDBTableInfoUtil 类中的 getSchemas() 方法来获取所有的 schema，然后，调用 AbstractDBTableInfoUtil 类中的 getTables() 遍历这些 schema，获取它们对应的所有表

public class HiveTableInfoUtil extends AbstractDBTableInfoUtil {
    private static final String COL_NAME = "col_name";
    private static final String DATA_TYPE = "data_type";
    private static final String COMMENT = "comment";

    private static final String CREATE_TIME = "CreateTime:";
    private static final String TABLE_PARAMETERS = "Table Parameters:";
    private static final String TABLE_PARAMETERS_COMMENT = "comment";
    private static final String TABLE_PARAMETERS_TRANSIENT_LAST_DDL_TIME = "transient_lastDdlTime";

    private static final String DATE_FORMAT = "EEE MMM dd HH:mm:ss z yyyy";

    public HiveTableInfoUtil(final BiDataSource dataSource) {
        super(dataSource);
    }

    @Override
    protected SourceType getSourceType() {
        return SourceType.HIVE;
    }

    @Override
    public int getTableCount(final BiDataSource dataSource) {
        // 先获取所有 schema，再获取每个 schema 的所有 table
        List<Pair<String, String>> tables = getTables(dataSource, getSchemas(dataSource));

        return tables.size();
    }

    @Override
    protected String getTableCountSQL() {
        return null;
    }

    @Override
    protected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {
        // 先获取所有 schema，再获取每个 schema 的所有 table
        List<Pair<String, String>> allTables = getTables(dataSource, getSchemas(dataSource));

        // 因为无法通过 SQL 进行分页，所以，只好手动分页
        final int start = (pageNumber - 1) * pageSize;
        final int end = start + pageSize;

        List<Pair<String, String>> targetTables = allTables.subList((start < 0 ? 0 : start), (end > allTables.size() ? allTables.size() : end));

        // 对于 分页 中的每个table获取相关信息
        List<TableInfo> tableProperties = getTableInfo(dataSource, targetTables);

        return tableProperties;
    }

    private List<TableInfo> getTableInfo(final BiDataSource dataSource, final List<Pair<String, String>> tables) {

        List<TableInfo> tableInfoList = new ArrayList<>();

        try (Connection connection = DriverManager.getConnection(dataSource.url, dataSource.username, dataSource.password);
             Statement ps = connection.createStatement()) {

            for (Pair<String, String> table : tables) {

                TableInfo tableInfo = getInitTableInfo(table);

                String sql = MessageFormat.format("DESC FORMATTED {0}.{1}", table.getKey(), table.getValue());
                play.Logger.info(sql);

                ResultSet resultSet = ps.executeQuery(sql);

                String col_name;
                String data_type;
                boolean tableParamPresented = false;
                // 对于 Hive 的表的结构信息的解析
                // 下方给出了一个 "desc formatted xxxHiveTable" 语句返回的样例
                // 这里的解析逻辑就是根据这个样例来进行的
                while (resultSet.next()) {
                    col_name = resolveNull(resultSet.getString(COL_NAME));
                    data_type = resolveNull(resultSet.getString(DATA_TYPE));

                    if (CREATE_TIME.equalsIgnoreCase(col_name)) {
                        tableInfo.createTime = getCreateTime(data_type);
                    }

                    if (TABLE_PARAMETERS.equalsIgnoreCase(col_name)) {
                        tableParamPresented = true;
                    }

                    if (tableParamPresented && TABLE_PARAMETERS_COMMENT.equalsIgnoreCase(data_type)) {
                        tableInfo.tableComment = resolveNull(resultSet.getString(COMMENT));
                    }

                    if (tableParamPresented && TABLE_PARAMETERS_TRANSIENT_LAST_DDL_TIME.equalsIgnoreCase(data_type)) {
                        tableInfo.updateTime = getUpdateTime(resolveNull(resultSet.getString(COMMENT)));
                    }
                }

                if (tableInfo.updateTime == -1) {
                    tableInfo.updateTime = tableInfo.createTime;
                }

                tableInfoList.add(tableInfo);
            }
        } catch (SQLException e) {
            play.Logger.error(e.getMessage(), e);
        }

        return tableInfoList;
    }

    private TableInfo getInitTableInfo(Pair<String, String> table) {
        TableInfo tableInfo = new TableInfo();

        tableInfo.schema = table.getKey();
        tableInfo.tableName = table.getValue();

        // set init value
        tableInfo.createTime = -1;
        tableInfo.updateTime = -1;
        tableInfo.tableComment = null;
        return tableInfo;
    }

    private String resolveNull(final String str) {
        return (str == null ? "" : str.trim());
    }

    /*
     * create time is sth.like 'Wed Mar 01 10:47:12 CST 2017'
     */
    @SuppressWarnings("deprecation")
    private long getCreateTime(String createTime) {
        Date date;
        try {
            date = new SimpleDateFormat(DATE_FORMAT, Locale.US).parse(createTime);
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
            try {
                date = new Date(createTime);
            } catch (Exception e1) {
                date = new Date(-1);
            }
        }

        return date.getTime();
    }

    /*
     * update time is sth. like '1486978518',
     * this is UNIX time stamp, we need to append '000' to change it.
     */
    private long getUpdateTime(String updateTime) throws SQLException {
        long update;
        try {
            update = Long.parseLong(updateTime + "000");
        } catch (Exception e) {
            play.Logger.error(e.getMessage(), e);
            update = -1;
        }
        return update;
    }
}

“desc formatted test123;” 的返回值

> desc formatted test123;
OK
# col_name              data_type               comment             

id                      int                                         

# Detailed Table Information         
Database:               hiveDB               
Owner:                  hive                     
CreateTime:             Wed Mar 01 10:47:12 CST 2017     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://HA/apps/hive/warehouse/hiveDB.db/test123  
Table Type:             MANAGED_TABLE            
Table Parameters:        
    COLUMN_STATS_ACCURATE   false               
    comment                 modified comment for test123
    last_modified_by        hive                
    last_modified_time      1488336859          
    numFiles                1                   
    numRows                 -1                  
    rawDataSize             -1                  
    totalSize               2                   
    transient_lastDdlTime   1488336859          

# Storage Information        
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe   
InputFormat:            org.apache.hadoop.mapred.TextInputFormat     
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat   
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:         
    field.delim                                
    serialization.format                       
Time taken: 0.091 seconds, Fetched: 35 row(s)

分享到：

计算机网络协议有哪些 | RabbitMq、ActiveMq、ZeroMq、kafka之间的 ...

2017-07-21 14:32
浏览 756
评论(0)
分类:数据库
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

针对不同数据库，获取当前用户所有有权限查看的表，以及表的创建时间、更新时间、注释等信息，表中字段的相关信息(包含分页实现)

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

针对不同数据库，获取当前用户所有有权限查看的表，以及表的创建时间、更新时间、注释等信息，表中字段的相关信息(包含分页实现)

评论

发表评论

相关推荐

Mysql中DATE_SUB 使用方法结合查询一天内,一周内,一月内的信息实例讲解

MySQL里获取当前week、month、quarter的start_date/end_date

查看数据库

数据导入到数据库

使用数据库客户端工具Oracle SQL Developer加载第三方驱动连接mysql的方法

数据连接符

commit

Redis操作命令总结

PostgreSQL中表名、字段名大小写问题

怎么解决Greenplum中用pg

mysql unrecognized service问题解决

Oracle创建视图、通过视图创建表

PostgreSQL中表名、字段名大小写问题

关于性能测试几个名词概念的说明

数据库性能优化详解

Oracle怎样把varchar2型转成number型

oracle中字符串的大小比较，字符串与数字的比较和运算

greenplum 程序开发优化原则

PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践

PostgreSQL 时序最佳实践

最近访客更多访客>>