java反射查询数据库_JAVA反射数据库表信息

需求

建设数仓时，需要根据关系型数据库表，生成hive创建语句，且需要保留表、字段的原始注释

实现

使用JAVA通过JDBC读取数据库信息，拼接字符串实现；以下代码会省略拼接字符串部分。

模糊查询相关表

/**

* 模糊查询数据库表

*/

public ArrayList queryTables(String tableName) throws Exception {

String dbIp = "10.10.10.10";

String port = "数据库端口";

String dataBase = "test_db";

/**

* 如果是ORACLE，如果表不是当前访问用户的，需要加上所属用户的名字

* MYSQL则不需要

*/

String userSchema = "test_user";

String userName = "query_user";

String passWord = "query_user_pwd";

String driverClassName = "";

String url = "";

String schema = null;

/**

* 构造访问URL等

*/

if (DatabaseType.ORACLE.getType().equals("ORACLE")) {

driverClassName = "oracle.jdbc.driver.OracleDriver";

url = "jdbc:oracle:thin:@" + dbIp + ":" + port + ":" + dataBase;

schema = userSchema;

} else if (DatabaseType.MYSQL.getType().equals("MYSQL")) {

driverClassName = "com.mysql.jdbc.Driver";

url = "jdbc:mysql://" + dbIp + ":" + port + "/" + dataBase

+ "?characterEncoding=utf8&&useInformationSchema=true";

schema = null;

}

/**

* 创建连接

*/

Class.forName(driverClassName);

Properties props = new Properties();

props.setProperty("user", userName);

props.setProperty("password", passWord);

//显示表备注

props.put("remarksReporting", "true");

Connection conn = DriverManager.getConnection(url, props);

conn.setReadOnly(true);

Statement stmt = conn.createStatement();

//查询具有可读权限的表信息，模糊匹配用%

DatabaseMetaData metadata = conn.getMetaData();

ResultSet rs = metadata.getTables(null, schema, "%" + tableName + "%",new String[]{"TABLE", "VIEW"});

ArrayList tables = new ArrayList<>();

while (rs.next()) {

String v_tableName = rs.getString("TABLE_NAME");

String tableType = rs.getString("TABLE_TYPE");

String tableComment = rs.getString("REMARKS");

TableInfo tableInfo = new TableInfo(v_tableName, tableType, tableComment);

tables.add(tableInfo);

}

// 关闭连接

conn.close();

stmt.close();

return tables;

}

获取单张表结构

创建连接参考上面代码片段，这里只展示如何获取表结构

DatabaseMetaData metadata = conn.getMetaData();

ResultSet rs = metadata.getTables(null, schema, tableName, new String[]{"TABLE", "VIEW"});

ResultSet cloumnRs = conn.getMetaData().getColumns(null, schema, tableName, "%");

ArrayList colList = new ArrayList<>();

while (cloumnRs.next()) {

//列名

String colName = cloumnRs.getString("COLUMN_NAME");

//备注

String remarks = cloumnRs.getString("REMARKS");

//类型

String colType = cloumnRs.getString("TYPE_NAME");

}

// 关闭连接

conn.close();

stmt.close();