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

本文主要是介绍针对不同数据库，获取当前用户所有有权限查看的表，以及表的创建时间、更新时间、注释等信息，表中字段的相关信息(包含分页实现)...，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

给定任意一个数据库的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、passwordprotected 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;}// 获取有权限访问的所有表的总数@Overridepublic 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() 方法，就可以获取表中的所有字段@Overrideprotected 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-resultsetif (sourceType == SourceType.MYSQL || sourceType == SourceType.GBASE) {resultSet = metaData.getColumns(schema, null, table, "%");} else {resultSet = metaData.getColumns(null, schema, table, "%");}while (resultSet.next()) {// TableFieldInfo 是一个POJOTableFieldInfo 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 时，没有办法了，才加了这个方法// 与上面的方法基本一样，该方法可以同时处理多个 schemapublic 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);// 指定系统 schemaexcludeSchema = "('information_schema')";}@Overrideprotected SourceType getSourceType() {return SourceType.MYSQL;}// 获取表的总数的SQL@Overrideprotected 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;}// 获取指定数量的 表的集合，即分页@Overrideprotected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {// mysql的版本需要在 5.7 以上，否则，create_time 和 update_time 可能返回NULLplay.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时，可以直接使用 列名，而不是使用 indexString 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 是一个POJOTableInfo 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);}@Overrideprotected 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')";}@Overrideprotected SourceType getSourceType() {return SourceType.ORACLE;}@Overrideprotected 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;}@Overrideprotected 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);// 系统 schemaexcludeSchema = "('SYSCAT', 'SYSIBM', 'SYSIBMADM', 'SYSPUBLIC', 'SYSSTAT', 'SYSTOOLS')";}@Overrideprotected SourceType getSourceType() {return SourceType.DB2;}@Overrideprotected 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;}@Overrideprotected 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);// 系统 schemaexcludeSchema = "('information_schema', 'pg_catalog', 'gp_toolkit')";}@Overrideprotected SourceType getSourceType() {return SourceType.GREENPLUM;}@Overrideprotected 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;}@Overrideprotected 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);}@Overrideprotected SourceType getSourceType() {return SourceType.HIVE;}@Overridepublic int getTableCount(final BiDataSource dataSource) {// 先获取所有 schema，再获取每个 schema 的所有 tableList<Pair<String, String>> tables = getTables(dataSource, getSchemas(dataSource));return tables.size();}@Overrideprotected String getTableCountSQL() {return null;}@Overrideprotected List getTableInfoList(final BiDataSource dataSource, final int pageSize, final int pageNumber) {// 先获取所有 schema，再获取每个 schema 的所有 tableList<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 valuetableInfo.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 test123last_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)