oracledatabasemetadata.java

数据库连接包括连接池。很有用的资料

      // null catalog.  This means return info about both packaged and
      // standalone objects.
      finalQuery = baseQuery + columnWhere + orderBy;
      connection.trace("getProcedureColumns Final SQL statement:\n" +
		       finalQuery);
      s = connection.prepareStatement (finalQuery);

      s.setString(1, schemaBind);
      s.setString(2, procedureNameBind);
      s.setString(3, columnNameBind);
    }
    else if (catalog.equals(""))
    {
      // Empty string passed in for catalog.  This means return info only
      // about standalone objects.
      finalQuery = baseQuery + catalogEmptyWhere + columnWhere + orderBy;
      connection.trace("getProcedureColumns Final SQL statement:\n" +
		       finalQuery);

      s = connection.prepareStatement (finalQuery);

      s.setString(1, schemaBind);
      s.setString(2, procedureNameBind);
      s.setString(3, columnNameBind);
    }
    else
    {
      // Catalog specified.  Use it.
      finalQuery = baseQuery + catalogSpecifiedWhere + columnWhere + orderBy;
      connection.trace("getProcedureColumns Final SQL statement:\n" +
		       finalQuery);

      s = connection.prepareStatement (finalQuery);

      s.setString(1, schemaBind);
      s.setString(2, procedureNameBind);
      s.setString(3, catalog);
      s.setString(4, columnNameBind);
    }

    // Execute the query and return the ResultSet.
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery ();
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * COLUMN_TYPE - nobody knows.
   */
  int procedureColumnUnknown = 0;

  /**
   * COLUMN_TYPE - IN parameter.
   */
  int procedureColumnIn = 1;

  /**
   * COLUMN_TYPE - INOUT parameter.
   */
  int procedureColumnInOut = 2;

  /**
   * COLUMN_TYPE - OUT parameter.
   */
  int procedureColumnOut = 4;
  /**
   * COLUMN_TYPE - procedure return value.
   */
  int procedureColumnReturn = 5;

  /**
   * COLUMN_TYPE - result column in ResultSet.
   */
  int procedureColumnResult = 3;

  /**
   * TYPE NULLABLE - does not allow NULL values.
   */
  int procedureNoNulls = 0;

  /**
   * TYPE NULLABLE - allows NULL values.
   */
  int procedureNullable = 1;

  /**
   * TYPE NULLABLE - nullability unknown.
   */
  int procedureNullableUnknown = 2;


  /**
   * Get a description of tables available in a catalog.
   *
   * <P>Only table descriptions matching the catalog, schema, table
   * name and type criteria are returned.  They are ordered by
   * TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
   *
   * <P>Each table description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>TABLE_TYPE</B> String => table type.  Typical types are "TABLE",
   *			"VIEW",	"SYSTEM TABLE", "GLOBAL TEMPORARY", 
   *			"LOCAL TEMPORARY", "ALIAS", "SYNONYM".
   *	<LI><B>TABLE_REMARKS</B> String => explanatory comment on the table
   *  </OL>
   *
   * <P><B>Note:</B> Some databases may not return information for
   * all tables.
   *
   * @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 types a list of table types to include; null returns all types 
   * @return ResultSet each row is a table description
   * @see #getSearchStringEscape 
   */
  public synchronized
  ResultSet getTables (String catalog, String schemaPattern,
		       String tableNamePattern, String types[])
    throws SQLException
  {
    String type_select = "";
    if (types != null)
    {
      int i;
      type_select = "  AND c.table_type IN (";
      for (i = 0; i < types.length - 1; i++)
	type_select = type_select + "'" + types [i] + "', ";
      type_select = type_select + "'" + types [i] + "')\n";
    }

    String query;
    if (connection.report_remarks)
      query =
"SELECT NULL AS table_cat,\n" +
"       c.owner AS table_schem,\n" +
"       c.table_name AS table_name,\n" +
"       c.table_type AS table_type,\n" +
"       k.comments AS table_remarks\n" +
"FROM all_catalog c, all_tab_comments k\n" + 
"WHERE c.owner LIKE ? escape '/'\n" +
"  AND c.table_name LIKE ? escape '/'\n" +
type_select +
"  AND c.owner = k.owner\n" +
"  AND c.table_name = k.table_name (+)\n" +
"ORDER BY table_type, table_schem, table_name\n";
    else
      query =
"SELECT NULL AS table_cat,\n" +
"       c.owner AS table_schem,\n" +
"       c.table_name AS table_name,\n" +
"       c.table_type AS table_type,\n" +
"       null AS table_remarks\n" +
"FROM all_catalog c\n" + 
"WHERE c.owner LIKE ? escape '/'\n" +
"  AND c.table_name LIKE ? escape '/'\n" +
type_select +
"ORDER BY table_type, table_schem, table_name\n";

    PreparedStatement s = connection.prepareStatement (query);

    s.setString (1, schemaPattern == null ? "%" : schemaPattern);
    s.setString (2, tableNamePattern == null ? "%" : tableNamePattern);
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery ();
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * Get the schema names available in this database.  The results
   * are ordered by schema name.
   *
   * <P>The schema column is:
   *  <OL>
   *	<LI><B>TABLE_SCHEM</B> String => schema name
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * schema name 
   */
  public ResultSet getSchemas ()
    throws SQLException
  {
    Statement s = connection.createStatement ();
    String basic_query = 
"SELECT username AS table_schem FROM all_users ORDER BY table_schem";
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery (basic_query);
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * Get the catalog names available in this database.  The results
   * are ordered by catalog name.
   *
   * <P>The catalog column is:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => catalog name
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * catalog name 
   */
  public ResultSet getCatalogs() 
    throws SQLException
  {
    connection.trace ("getCatalogs");
    Statement s = connection.createStatement ();
    String query = "select 'nothing' as table_cat from dual where 1 = 2";
    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery (query);
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * Get the table types available in this database.  The results
   * are ordered by table type.
   *
   * <P>The table type is:
   *  <OL>
   *	<LI><B>TABLE_TYPE</B> String => table type.  Typical types are "TABLE",
   *			"VIEW",	"SYSTEM TABLE", "GLOBAL TEMPORARY", 
   *			"LOCAL TEMPORARY", "ALIAS", "SYNONYM".
   *  </OL>
   *
   * @return ResultSet each row has a single String column that is a
   * table type 
   */
  public ResultSet getTableTypes() 
    throws SQLException
  {
    Statement s = connection.createStatement ();
    String query =
"select 'TABLE' as table_type from dual\n" +
"union select 'VIEW' as table_type from dual\n" +
"union select 'SYNONYM' as table_type from dual\n";

    OracleResultSetImpl rs = (OracleResultSetImpl)s.executeQuery (query);
    rs.close_statement_on_close = true;
    return rs;
  }

  /**
   * Get a description of table columns available in a catalog.
   *
   * <P>Only column descriptions matching the catalog, schema, table
   * and column name criteria are returned.  They are ordered by
   * TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
   *
   * <P>Each column description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
   *	<LI><B>TYPE_NAME</B> String => Data source dependent type name
   *	<LI><B>COLUMN_SIZE</B> int => column size.  For char or date
   *	    types this is the maximum number of characters, for numeric or
   *	    decimal types this is precision.
   *	<LI><B>BUFFER_LENGTH</B> is not used.
   *	<LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
   *	<LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
   *	<LI><B>NULLABLE</B> int => is NULL allowed?
   *      <UL>
   *      <LI> columnNoNulls - might not allow NULL values
   *      <LI> columnNullable - definitely allows NULL values
   *      <LI> columnNullableUnknown - nullability unknown
   *      </UL>
   *	<LI><B>REMARKS</B> String => comment describing column (may be null)
   * 	<LI><B>COLUMN_DEF</B> String => default value (may be null)
   *	<LI><B>SQL_DATA_TYPE</B> int => unused
   *	<LI><B>SQL_DATETIME_SUB</B> int => unused
   *	<LI><B>CHAR_OCTET_LENGTH</B> int => for char types the 
   *       maximum number of bytes in the column
   *	<LI><B>ORDINAL_POSITION</B> int	=> index of column in table 
   *      (starting at 1)
   *	<LI><B>IS_NULLABLE</B> String => "NO" means column definitely 
   *      does not allow NULL values; "YES" means the column might 
   *      allow NULL values.  An empty string means nobody knows.
   *  </OL>
   *
   * @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_nullable\n";

    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 '/' OR\n" +
"       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_name\n" +
"  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_position\n";

    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;
  }

  /**
   * COLUMN NULLABLE - might not allow NULL values.
   */
  int columnNoNulls = 0;

  /**
   * COLUMN NULLABLE - definitely allows NULL values.
   */
  int columnNullable = 1;

  /**
   * COLUMN NULLABLE - nullability unknown.
   */
  int columnNullableUnknown = 2;

  /**
   * Get a description of the access rights for a table's columns.
   *
   * <P>Only privileges matching the column name criteria are
   * returned.  They are ordered by COLUMN_NAME and PRIVILEGE.
   *
   * <P>Each privilige description has the following columns:
   *  <OL>
   *	<LI><B>TABLE_CAT</B> String => table catalog (may be null)
   *	<LI><B>TABLE_SCHEM</B> String => table schema (may be null)
   *	<LI><B>TABLE_NAME</B> String => table name
   *	<LI><B>COLUMN_NAME</B> String => column name
   *	<LI><B>GRANTOR</B> => grantor of access (may be null)
   *	<LI><B>GRANTEE</B> String => grantee of access
   *	<LI><B>PRIVILEGE</B> String => name of access (SELECT, 
   *      INSERT, UPDATE, REFRENCES, ...)
   *	<LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted 
   *      to grant to others; "NO" if not; null if unknown 
   *  </OL>
   *
   * @param catalog a catalog name; "" retrieves those without a
   * catalog; null means drop catalog name from the selection criteria
   * @param schema a schema name; "" retrieves those without a schema
   * @param table a table name
   * @param columnNamePattern a column name pattern 
   * @return ResultSet each row is a column privil