JAVA反射数据库表信息

需求

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

实现

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

模糊查询相关表

/**
   * 模糊查询数据库表
   */
  public ArrayList<TableInfo> 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<TableInfo> 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<TableColumn> 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();

转载于:https://my.oschina.net/u/3457546/blog/2998186