jdbcdatabasemetadata.java

非常棒的java数据库

                    + "GRANTEE, "
                    + "PRIVILEGE_TYPE PRIVILEGE, "
                    + "IS_GRANTABLE "
                    + "FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND TABLE_NAME = ? "
                    + "AND COLUMN_NAME LIKE ? "
                    + "ORDER BY COLUMN_NAME, PRIVILEGE");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schema));
            prep.setString(3, table);
            prep.setString(4, getPattern(columnNamePattern));
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of table privileges. The result set is sorted by
     * TABLE_SCHEM, TABLE_NAME, and PRIVILEGE.
     * 
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 GRANTOR (String) grantor of access </li>
     * <li>5 GRANTEE (String) grantee of access </li>
     * <li>6 PRIVILEGE (String) SELECT, INSERT, UPDATE, DELETE or REFERENCES
     * (only one per row) </li>
     * <li>7 IS_GRANTABLE (String) YES means the grantee can grant access to
     * others </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schemaPattern null (to get all objects) or a schema name
     *            (uppercase for unquoted names)
     * @param tableNamePattern null (to get all objects) or a table name
     *            (uppercase for unquoted names)
     * @return the list of privileges
     * @throws SQLException if the connection is closed
     */
    public ResultSet getTablePrivileges(String catalog, String schemaPattern, String tableNamePattern) throws SQLException {
        try {
            if (debug()) {
                debugCode("getTablePrivileges("
                        +quote(catalog)+", "
                        +quote(schemaPattern)+", "
                        +quote(tableNamePattern)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "GRANTOR, "
                    + "GRANTEE, "
                    + "PRIVILEGE_TYPE PRIVILEGE, "
                    + "IS_GRANTABLE "
                    + "FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND TABLE_NAME LIKE ? "
                    + "ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schemaPattern));
            prep.setString(3, getPattern(tableNamePattern));
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of columns that best identifier a row in a table.
     * The list is ordered by SCOPE.
     *
     * <ul>
     * <li>1 SCOPE (short) scope of result (always bestRowSession)
     * </li><li>2 COLUMN_NAME (String) column name
     * </li><li>3 DATA_TYPE (short) SQL data type, see also java.sql.Types
     * </li><li>4 TYPE_NAME (String) type name
     * </li><li>5 COLUMN_SIZE (int) precision
     * </li><li>6 BUFFER_LENGTH (int) unused
     * </li><li>7 DECIMAL_DIGITS (short) scale
     * </li><li>8 PSEUDO_COLUMN (short) (always bestRowNotPseudo)
     * </li></ul>
     *
     * @param catalog null (to get all objects) or the catalog name
     * @param schema schema name (must be specified)
     * @param tableName table name (must be specified)
     * @param scope ignored
     * @param nullable ignored
     * @return the primary key index
     * @throws SQLException if the connection is closed
     */
    public ResultSet getBestRowIdentifier(String catalog, String schema,
            String tableName, int scope, boolean nullable) throws SQLException {
        try {
            if (debug()) {
                debugCode("getBestRowIdentifier("
                        +quote(catalog)+", "
                        +quote(schema)+", "
                        +quote(tableName)+", "
                        +scope+", "+nullable+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "CAST(? AS SMALLINT) SCOPE, "
                    + "C.COLUMN_NAME, "
                    + "C.DATA_TYPE, "
                    + "C.TYPE_NAME, "
                    + "C.CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, "
                    + "C.CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, "
                    + "CAST(C.NUMERIC_SCALE AS SMALLINT) DECIMAL_DIGITS, "
                    + "CAST(? AS SMALLINT) PSEUDO_COLUMN "
                    + "FROM INFORMATION_SCHEMA.INDEXES I, "
                    +" INFORMATION_SCHEMA.COLUMNS C "
                    + "WHERE C.TABLE_NAME = I.TABLE_NAME "
                    + "AND C.COLUMN_NAME = I.COLUMN_NAME "
                    + "AND C.TABLE_CATALOG LIKE ? "
                    + "AND C.TABLE_SCHEMA LIKE ? "
                    + "AND C.TABLE_NAME = ? "
                    + "AND I.PRIMARY_KEY = TRUE "
                    + "ORDER BY SCOPE");
            prep.setInt(1, DatabaseMetaData.bestRowSession); // SCOPE
            prep.setInt(2, DatabaseMetaData.bestRowNotPseudo); // PSEUDO_COLUMN
            prep.setString(3, getCatalogPattern(catalog));
            prep.setString(4, getSchemaPattern(schema));
            prep.setString(5, tableName);
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Get the list of columns that are update when any value is updated.
     *
     * <ul>
     * <li>1 SCOPE (int) not used
     * </li><li>2 COLUMN_NAME (String) column name
     * </li><li>3 DATA_TYPE (int) SQL data type - see also java.sql.Types
     * </li><li>4 TYPE_NAME (String) data type name
     * </li><li>5 COLUMN_SIZE (int) precision
     * </li><li>6 BUFFER_LENGTH (int) length (bytes)
     * </li><li>7 DECIMAL_DIGITS (int) scale
     * </li><li>8 PSEUDO_COLUMN (int) is this column a pseudo column
     * </li></ul>
     *
     * @param catalog null (to get all objects) or the catalog name
     * @param schema schema name (must be specified)
     * @param tableName table name (must be specified)
     * @return an empty result set
     * @throws SQLException if the connection is closed
     */
    public ResultSet getVersionColumns(String catalog, String schema,
            String tableName) throws SQLException {
        try {
            if (debug()) {
                debugCode("getVersionColumns("
                        +quote(catalog)+", "
                        +quote(schema)+", "
                        +quote(tableName)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "ZERO() SCOPE, "
                    + "COLUMN_NAME, "
                    + "CAST(DATA_TYPE AS INT) DATA_TYPE, "
                    + "TYPE_NAME, "
                    + "NUMERIC_PRECISION COLUMN_SIZE, "
                    + "NUMERIC_PRECISION BUFFER_LENGTH, "
                    + "NUMERIC_PRECISION DECIMAL_DIGITS, "
                    + "ZERO() PSEUDO_COLUMN "
                    + "FROM INFORMATION_SCHEMA.COLUMNS "
                    + "WHERE FALSE");
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of primary key columns that are referenced by a table. The
     * result set is sorted by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME,
     * FK_NAME, KEY_SEQ.
     * 
     * <ul>
     * <li>1 PKTABLE_CAT (String) primary catalog </li>
     * <li>2 PKTABLE_SCHEM (String) primary schema </li>
     * <li>3 PKTABLE_NAME (String) primary table </li>
     * <li>4 PKCOLUMN_NAME (String) primary column </li>
     * <li>5 FKTABLE_CAT (String) foreign catalog </li>
     * <li>6 FKTABLE_SCHEM (String) foreign schema </li>
     * <li>7 FKTABLE_NAME (String) foreign table </li>
     * <li>8 FKCOLUMN_NAME (String) foreign column </li>
     * <li>9 KEY_SEQ (short) sequence number (1, 2, ...) </li>
     * <li>10 UPDATE_RULE (short) action on update (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>11 DELETE_RULE (short) action on delete (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>12 FK_NAME (String) foreign key name </li>
     * <li>13 PK_NAME (String) primary key name </li>
     * <li>14 DEFERRABILITY (short) deferrable or not (always
     * importedKeyNotDeferrable) </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schema the schema name of the foreign table
     * @param tableName the name of the foreign table
     * @return the result set
     * @throws SQLException if the connection is closed
     */
    public ResultSet getImportedKeys(String catalog, String schema, String tableName) throws SQLException {
        try {
            if (debug()) {
                debugCode("getImportedKeys("
                        +quote(catalog)+", "
                        +quote(schema)+", "
                        +quote(tableName)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "PKTABLE_CATALOG PKTABLE_CAT, "
                    + "PKTABLE_SCHEMA PKTABLE_SCHEM, "
                    + "PKTABLE_NAME PKTABLE_NAME, "
                    + "PKCOLUMN_NAME, "
                    + "FKTABLE_CATALOG FKTABLE_CAT, "
                    + "FKTABLE_SCHEMA FKTABLE_SCHEM, "
                    + "FKTABLE_NAME, "
                    + "FKCOLUMN_NAME, "
                    + "ORDINAL_POSITION KEY_SEQ, "
                    + "UPDATE_RULE, "
                    + "DELETE_RULE, "
                    + "FK_NAME, "
                    + "PK_NAME, "
                    + "DEFERRABILITY "
                    + "FROM INFORMATION_SCHEMA.CROSS_REFERENCES "
                    + "WHERE FKTABLE_CATALOG LIKE ? "
                    + "AND FKTABLE_SCHEMA LIKE ? "
                    + "AND FKTABLE_NAME = ? "
                    + "ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, FK_NAME, KEY_SEQ");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schema));
            prep.setString(3, tableName);
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of foreign key columns that reference a table. The result
     * set is sorted by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FK_NAME,
     * KEY_SEQ.
     * 
     * <ul>
     * <li>1 PKTABLE_CAT (String) primary catalog </li>
     * <li>2 PKTABLE_SCHEM (String) primary schema </li>
     * <li>3 PKTABLE_NAME (String) primary table </li>
     * <li>4 PKCOLUMN_NAME (String) primary column </li>
     * <li>5 FKTABLE_CAT (String) foreign catalog </li>
     * <li>6 FKTABLE_SCHEM (String) foreign schema </li>
     * <li>7 FKTABLE_NAME (String) foreign table </li>
     * <li>8 FKCOLUMN_NAME (String) foreign column </li>
     * <li>9 KEY_SEQ (short) sequence number (1,2,...) </li>
     * <li>10 UPDATE_RULE (short) action on update (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>11 DELETE_RULE (short) action on delete (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>12 FK_NAME (String) foreign key name </li>
     * <li>13 PK_NAME (String) primary key name </li>
     * <li>14 DEFERRABILITY (short) deferrable or not (always
     * importedKeyNotDeferrable) </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schema the schema name of the primary table
     * @param tableName the name of the primary table
     * @return the result set
     * @throws SQLException if the connection is closed
     */
    public ResultSet getExportedKeys(String catalog, String schema, String tableName)
            throws SQLException {
        try {
            if (debug()) {
                debugCode("getExportedKeys("
                        +quote(catalog)+", "
                        +quote(schema)+", "
                        +quote(tableName)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "PKTABLE_CATALOG PKTABLE_CAT, "
                    + "PKTABLE_SCHEMA PKTABLE_SCHEM, "
                    + "PKTABLE_NAME PKTABLE_NAME, "
                    + "PKCOLUMN_NAME, "
                    + "FKTABLE_CATALOG FKTABLE_CAT, "
                    + "FKTABLE_SCHEMA FKTABLE_SCHEM, "
                    + "FKTABLE_NAME, "
                    + "FKCOLUMN_NAME, "
                    + "ORDINAL_POSITION KEY_SEQ, "
                    + "UPDATE_RULE, "
                    + "DELETE_RULE, "
                    + "FK_NAME, "
                    + "PK_NAME, "
                    + "DEFERRABILITY "
                    + "FROM INFORMATION_SCHEMA.CROSS_REFERENCES "
                    + "WHERE PKTABLE_CATALOG LIKE ? "
                    + "AND PKTABLE_SCHEMA LIKE ? "
                    + "AND PKTABLE_NAME = ? "
                    + "ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FK_NAME, KEY_SEQ");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schema));
            prep.setString(3, tableName);
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of foreign key columns that references a table, as well as
     * the list of primary key columns that are references by a table. The
     * result set is sorted by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME,
     * FK_NAME, KEY_SEQ.
     * 
     * <ul>
     * <li>1 PKTABLE_CAT (String) primary catalog </li>
     * <li>2 PKTABLE_SCHEM (String) primary schema </li>
     * <li>3 PKTABLE_NAME (String) primary table </li>
     * <li>4 PKCOLUMN_NAME (String) primary column </li>
     * <li>5 FKTABLE_CAT (String) foreign catalog </li>
     * <li>6 FKTABLE_SCHEM (String) foreign schema </li>
     * <li>7 FKTABLE_NAME (String) foreign table </li>
     * <li>8 FKCOLUMN_NAME (String) foreign column </li>
     * <li>9 KEY_SEQ (short) sequence number (1,2,...) </li>
     * <li>10 UPDATE_RULE (short) action on update (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>11 DELETE_RULE (short) action on delete (see
     * DatabaseMetaData.importedKey...) </li>
     * <li>12 FK_NAME (String) foreign key name </li>
     * <li>13 PK_NAME (String) primary key name </li>
     * <li>14 DEFERRABILITY (short) deferrable or not (always
     * importedKeyNotDeferrable) </li>
     * </ul>
     * 
     * @param primaryCatalog ignored
     * @param primarySchema the schema name of the primary table (must be
     *            specified)
     * @param primaryTable the name of the primary table (must be specified)
     * @param foreignCatalog ignored
     * @param foreignSchema the schema name of the foreign table (must be
     *            specified)
     * @param foreignTable the name of the foreign table (must be specified)
     * @return the result set
     * @throws SQLException if the connection is closed
     */
    public ResultSet getCrossReference(String primaryCatalog,