请教一条Sql语句！！在线等候！！__String_finalQuery_connection_table_name_include_synonyms_

当前位置: 技术问答>java相关

请教一条Sql语句！！在线等候！！

来源: 互联网发布时间：2015-04-28

本文导语: 怎么得到一个table的所有column的名称？？在线等候！！！ | ResultSet rs = stmt.executeQuery("SELECT * FROM TABLE1"); ResultSetMetaData rsmd = rs.getMetaData(); for(int i=0;i table catalog (may be null) ...

怎么得到一个table的所有column的名称？？在线等候！！！

ResultSet rs = stmt.executeQuery("SELECT * FROM TABLE1");
ResultSetMetaData rsmd = rs.getMetaData();
for(int i=0;i table catalog (may be null)
*

TABLE_SCHEM String => table schema (may be null)
*

TABLE_NAME String => table name
*

COLUMN_NAME String => column name
*

DATA_TYPE short => SQL type from java.sql.Types
*

TYPE_NAME String => Data source dependent type name
*

COLUMN_SIZE int => column size.  For char or date
   *     types this is the maximum number of characters, for numeric or
   *     decimal types this is precision.
   *

BUFFER_LENGTH is not used.
*

DECIMAL_DIGITS int => the number of fractional digits
*

NUM_PREC_RADIX int => Radix (typically either 10 or 2)
*

NULLABLE int => is NULL allowed?
*

columnNoNulls - might not allow NULL values
*
columnNullable - definitely allows NULL values
*
columnNullableUnknown - nullability unknown
*

REMARKS String => comment describing column (may be null)
*

COLUMN_DEF String => default value (may be null)
*

SQL_DATA_TYPE int => unused
*

SQL_DATETIME_SUB int => unused
*

CHAR_OCTET_LENGTH int => for char types the
* maximum number of bytes in the column
*

ORDINAL_POSITION int => index of column in table
* (starting at 1)
*

IS_NULLABLE String => "NO" means column definitely
   *      does not allow NULL values; "YES" means the column might
   *      allow NULL values.  An empty string means nobody knows.
   *
   *
   * @param catalog a catalog name; "" retrieves those without a
   * catalog; null means drop catalog name from the selection criteria
   * @param schemaPattern a schema name pattern; "" retrieves those
   * without a schema
   * @param tableNamePattern a table name pattern
   * @param columnNamePattern a column name pattern
   * @return ResultSet each row is a column description
   * @see #getSearchStringEscape
   */
  public synchronized
    ResultSet getColumns(String catalog, String schemaPattern,
String tableNamePattern, String columnNamePattern)

    throws SQLException
  {
    /* Bug 798299:
     * We need to build the query dynamically, based on the values of
     * include_synonyms and report_remarks.  If include_synonyms is true,
     * we need to do an outer join with all_synonyms.  If report_remarks is
     * true, we need to do an outer join with all_col_comments.  Both of these
     * are in addition to the base query against all_tab_columns.
     */
    String queryPart1 =
"SELECT NULL AS table_cat,n";

    String tableName =
"       t.owner AS table_schem,n" +
"       t.table_name AS table_name,n";

    String synonymName =
"       DECODE(s.table_owner, NULL, t.owner, s.table_owner)n" +
"              AS table_schem,n" +
"       DECODE(s.synonym_name, NULL, t.table_name, s.synonym_name)n" +
"              AS table_name,n";

    String queryPart2 =
"       t.column_name AS column_name,n" +
"       DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,n" +
"               'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4, 1111)n" +
"              AS data_type,n" +
"       t.data_type AS type_name,n" +
"       DECODE (t.data_precision, null, t.data_length, t.data_precision)n" +
"              AS column_size,n" +
"       0 AS buffer_length,n" +
"       t.data_scale AS decimal_digits,n" +
"       10 AS num_prec_radix,n" +
"       DECODE (t.nullable, 'N', 0, 1) AS nullable,n";

    String remarks =
"       c.comments AS remarks,n";

    String noRemarks =
"       NULL AS remarks,n";

    String queryPart3 =
"       t.data_default AS column_def,n" +
"       0 AS sql_data_type,n" +
"       0 AS sql_datetime_sub,n" +
"       t.data_length AS char_octet_length,n" +
"       t.column_id AS ordinal_position,n" +
"       DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullablen";

    String fromClause =
"FROM all_tab_columns t";

    String synonymFrom = ", all_synonyms s";
    String remarksFrom = ", all_col_comments c";

    String whereClause =
"WHERE t.owner LIKE ? ESCAPE '/'n" +
"  AND t.table_name LIKE ? ESCAPE '/'n" +
"  AND t.column_name LIKE ? ESCAPE '/'n";

    String synonymWhereClause =
"WHERE t.owner LIKE ? ESCAPE '/'n" +
"  AND (t.table_name LIKE ? ESCAPE '/' ORn" +
"       s.synonym_name LIKE ? ESCAPE '/')n" +
"  AND t.column_name LIKE ? ESCAPE '/'n";

    String remarksWhere =
"  AND t.owner = c.owner (+)n" +
"  AND t.table_name = c.table_name (+)n" +
"  AND t.column_name = c.column_name (+)n";

    String synonymWhere =
"  AND s.table_name (+) = t.table_namen" +
"  AND DECODE(s.owner, t.owner, 'OK',n" +
"                      'PUBLIC', 'OK',n" +
"                      NULL, 'OK',n" +
"                      'NOT OK') = 'OK'";

    String orderBy =
"ORDER BY table_schem, table_name, ordinal_positionn";

    String finalQuery;

    // Assemble the final query based on report_remarks and
    // include_synonyms.
    finalQuery = queryPart1;

    if (connection.include_synonyms)
      finalQuery += synonymName;
    else
      finalQuery += tableName;

    finalQuery += queryPart2;

    if (connection.report_remarks)
      finalQuery += remarks;
    else
      finalQuery += noRemarks;

    finalQuery += queryPart3 + fromClause;

    if (connection.report_remarks)
      finalQuery += remarksFrom;

    if (connection.include_synonyms)
      finalQuery += synonymFrom;

    if (connection.include_synonyms)
      finalQuery += "n" + synonymWhereClause;
    else
      finalQuery += "n" + whereClause;

    if (connection.report_remarks)
      finalQuery += remarksWhere;

    if (connection.include_synonyms)
      finalQuery += synonymWhere;

    finalQuery += orderBy;

    connection.trace("getColumns final SQL statement is:n" + finalQuery);

    // And finally, prepare, execute, and return the result set.
    PreparedStatement s = connection.prepareStatement (finalQuery);

    if (connection.include_synonyms)
    {
      s.setString (1, schemaPattern == null ? "%" : schemaPattern);
      s.setString (2, tableNamePattern == null ? "%" : tableNamePattern);
      s.setString (3, tableNamePattern == null ? "%" : tableNamePattern);
      s.setString (4, columnNamePattern == null ? "%" : columnNamePattern);
    }
    else
    {
      s.setString (1, schemaPattern == null ? "%" : schemaPattern);
      s.setString (2, tableNamePattern == null ? "%" : tableNamePattern);
      s.setString (3, columnNamePattern == null ? "%" : columnNamePattern);
    }
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery ();
    rs.close_statement_on_close = true;
    return rs;
  }

如果你指的是JDBC的话,可以用如下的代码:
.........//SQL:select A,B,C from test;
ResultSetMetaData Rsm=resultset.getMetaData();
int i;
for (i=1;i