?? databasemetadatausinginfoschema.java
字號:
* <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be
* null) </li>
* <li> <B>PKTABLE_NAME</B> String => primary key table name </li>
* <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li>
* <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
* null) being exported (may be null) </li>
* <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
* null) being exported (may be null) </li>
* <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported
* </li>
* <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being
* exported </li>
* <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
* <li> <B>UPDATE_RULE</B> short => What happens to foreign key when
* primary is updated:
* <UL>
* <li> importedKeyCascade - change imported key to agree with primary key
* update </li>
* <li> importedKeyRestrict - do not allow update of primary key if it has
* been imported </li>
* <li> importedKeySetNull - change imported key to NULL if its primary key
* has been updated </li>
* </ul>
* </li>
* <li> <B>DELETE_RULE</B> short => What happens to the foreign key when
* primary is deleted.
* <UL>
* <li> importedKeyCascade - delete rows that import a deleted key </li>
* <li> importedKeyRestrict - do not allow delete of primary key if it has
* been imported </li>
* <li> importedKeySetNull - change imported key to NULL if its primary key
* has been deleted </li>
* </ul>
* </li>
* <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li>
* <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li>
* </ol>
* </p>
*
* @param primaryCatalog
* a catalog name; "" retrieves those without a catalog
* @param primarySchema
* a schema name pattern; "" retrieves those without a schema
* @param primaryTable
* a table name
* @param foreignCatalog
* a catalog name; "" retrieves those without a catalog
* @param foreignSchema
* a schema name pattern; "" retrieves those without a schema
* @param foreignTable
* a table name
* @return ResultSet each row is a foreign key column description
* @throws SQLException
* if a database access error occurs
*/
public java.sql.ResultSet getCrossReference(String primaryCatalog,
String primarySchema, String primaryTable, String foreignCatalog,
String foreignSchema, String foreignTable) throws SQLException {
if (primaryTable == null) {
throw SQLError.createSQLException("Table not specified.",
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
if (primaryCatalog == null) {
if (this.conn.getNullCatalogMeansCurrent()) {
primaryCatalog = this.database;
}
}
if (foreignCatalog == null) {
if (this.conn.getNullCatalogMeansCurrent()) {
foreignCatalog = this.database;
}
}
Field[] fields = new Field[14];
fields[0] = new Field("", "PKTABLE_CAT", Types.CHAR, 255);
fields[1] = new Field("", "PKTABLE_SCHEM", Types.CHAR, 0);
fields[2] = new Field("", "PKTABLE_NAME", Types.CHAR, 255);
fields[3] = new Field("", "PKCOLUMN_NAME", Types.CHAR, 32);
fields[4] = new Field("", "FKTABLE_CAT", Types.CHAR, 255);
fields[5] = new Field("", "FKTABLE_SCHEM", Types.CHAR, 0);
fields[6] = new Field("", "FKTABLE_NAME", Types.CHAR, 255);
fields[7] = new Field("", "FKCOLUMN_NAME", Types.CHAR, 32);
fields[8] = new Field("", "KEY_SEQ", Types.SMALLINT, 2);
fields[9] = new Field("", "UPDATE_RULE", Types.SMALLINT, 2);
fields[10] = new Field("", "DELETE_RULE", Types.SMALLINT, 2);
fields[11] = new Field("", "FK_NAME", Types.CHAR, 0);
fields[12] = new Field("", "PK_NAME", Types.CHAR, 0);
fields[13] = new Field("", "DEFERRABILITY", Types.INTEGER, 2);
String sql = "SELECT "
+ "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
+ "NULL AS PKTABLE_SCHEM,"
+ "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,"
+ "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,"
+ "A.TABLE_SCHEMA AS FKTABLE_CAT,"
+ "NULL AS FKTABLE_SCHEM,"
+ "A.TABLE_NAME AS FKTABLE_NAME, "
+ "A.COLUMN_NAME AS FKCOLUMN_NAME, "
+ "A.ORDINAL_POSITION AS KEY_SEQ,"
+ generateUpdateRuleClause()
+ " AS UPDATE_RULE,"
+ generateDeleteRuleClause()
+ " AS DELETE_RULE,"
+ "A.CONSTRAINT_NAME AS FK_NAME,"
+ "(SELECT CONSTRAINT_NAME FROM"
+ " INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
+ " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND"
+ " TABLE_NAME = REFERENCED_TABLE_NAME AND"
+ " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)"
+ " AS PK_NAME,"
+ importedKeyNotDeferrable
+ " AS DEFERRABILITY "
+ "FROM "
+ "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN "
+ "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
+ "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) "
+ generateOptionalRefContraintsJoin()
+ "WHERE "
+ "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
+ "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
+ "AND A.TABLE_SCHEMA LIKE ? AND A.TABLE_NAME=? " + "ORDER BY "
+ "A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
PreparedStatement pStmt = null;
try {
pStmt = prepareMetaDataSafeStatement(sql);
if (primaryCatalog != null) {
pStmt.setString(1, primaryCatalog);
} else {
pStmt.setString(1, "%");
}
pStmt.setString(2, primaryTable);
if (foreignCatalog != null) {
pStmt.setString(3, foreignCatalog);
} else {
pStmt.setString(3, "%");
}
pStmt.setString(4, foreignTable);
ResultSet rs = executeMetadataQuery(pStmt);
((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] {
new Field("", "PKTABLE_CAT", Types.CHAR, 255),
new Field("", "PKTABLE_SCHEM", Types.CHAR, 0),
new Field("", "PKTABLE_NAME", Types.CHAR, 255),
new Field("", "PKCOLUMN_NAME", Types.CHAR, 32),
new Field("", "FKTABLE_CAT", Types.CHAR, 255),
new Field("", "FKTABLE_SCHEM", Types.CHAR, 0),
new Field("", "FKTABLE_NAME", Types.CHAR, 255),
new Field("", "FKCOLUMN_NAME", Types.CHAR, 32),
new Field("", "KEY_SEQ", Types.SMALLINT, 2),
new Field("", "UPDATE_RULE", Types.SMALLINT, 2),
new Field("", "DELETE_RULE", Types.SMALLINT, 2),
new Field("", "FK_NAME", Types.CHAR, 0),
new Field("", "PK_NAME", Types.CHAR, 0),
new Field("", "DEFERRABILITY", Types.INTEGER, 2) });
return rs;
} finally {
if (pStmt != null) {
pStmt.close();
}
}
}
/**
* Get a description of a foreign key columns that reference a table's
* primary key columns (the foreign keys exported by a table). They are
* ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.
* <P>
* Each foreign key column description has the following columns:
* <OL>
* <li> <B>PKTABLE_CAT</B> String => primary key table catalog (may be
* null) </li>
* <li> <B>PKTABLE_SCHEM</B> String => primary key table schema (may be
* null) </li>
* <li> <B>PKTABLE_NAME</B> String => primary key table name </li>
* <li> <B>PKCOLUMN_NAME</B> String => primary key column name </li>
* <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
* null) being exported (may be null) </li>
* <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
* null) being exported (may be null) </li>
* <li> <B>FKTABLE_NAME</B> String => foreign key table name being exported
* </li>
* <li> <B>FKCOLUMN_NAME</B> String => foreign key column name being
* exported </li>
* <li> <B>KEY_SEQ</B> short => sequence number within foreign key </li>
* <li> <B>UPDATE_RULE</B> short => What happens to foreign key when
* primary is updated:
* <UL>
* <li> importedKeyCascade - change imported key to agree with primary key
* update </li>
* <li> importedKeyRestrict - do not allow update of primary key if it has
* been imported </li>
* <li> importedKeySetNull - change imported key to NULL if its primary key
* has been updated </li>
* </ul>
* </li>
* <li> <B>DELETE_RULE</B> short => What happens to the foreign key when
* primary is deleted.
* <UL>
* <li> importedKeyCascade - delete rows that import a deleted key </li>
* <li> importedKeyRestrict - do not allow delete of primary key if it has
* been imported </li>
* <li> importedKeySetNull - change imported key to NULL if its primary key
* has been deleted </li>
* </ul>
* </li>
* <li> <B>FK_NAME</B> String => foreign key identifier (may be null) </li>
* <li> <B>PK_NAME</B> String => primary key identifier (may be null) </li>
* </ol>
* </p>
*
* @param catalog
* a catalog name; "" retrieves those without a catalog
* @param schema
* a schema name pattern; "" retrieves those without a schema
* @param table
* a table name
* @return ResultSet each row is a foreign key column description
* @throws SQLException
* if a database access error occurs
* @see #getImportedKeys
*/
public java.sql.ResultSet getExportedKeys(String catalog, String schema,
String table) throws SQLException {
// TODO: Can't determine actions using INFORMATION_SCHEMA yet...
if (table == null) {
throw SQLError.createSQLException("Table not specified.",
SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
if (catalog == null) {
if (this.conn.getNullCatalogMeansCurrent()) {
catalog = this.database;
}
}
//CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
String sql = "SELECT "
+ "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
+ "NULL AS PKTABLE_SCHEM,"
+ "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, "
+ "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, "
+ "A.TABLE_SCHEMA AS FKTABLE_CAT,"
+ "NULL AS FKTABLE_SCHEM,"
+ "A.TABLE_NAME AS FKTABLE_NAME,"
+ "A.COLUMN_NAME AS FKCOLUMN_NAME, "
+ "A.ORDINAL_POSITION AS KEY_SEQ,"
+ generateUpdateRuleClause()
+ " AS UPDATE_RULE,"
+ generateDeleteRuleClause()
+ " AS DELETE_RULE,"
+ "A.CONSTRAINT_NAME AS FK_NAME,"
+ "(SELECT CONSTRAINT_NAME FROM"
+ " INFORMATION_SCHEMA.TABLE_CONSTRAINTS"
+ " WHERE TABLE_SCHEMA = REFERENCED_TABLE_SCHEMA AND"
+ " TABLE_NAME = REFERENCED_TABLE_NAME AND"
+ " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)"
+ " AS PK_NAME,"
+ importedKeyNotDeferrable
+ " AS DEFERRABILITY "
+ "FROM "
+ "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN "
+ "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B "
+ "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) "
+ generateOptionalRefContraintsJoin()
+ "WHERE "
+ "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
+ "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? "
+ "ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION";
PreparedStatement pStmt = null;
try {
pStmt = prepareMetaDataSafeStatement(sql);
if (catalog != null) {
pStmt.setString(1, catalog);
} else {
pStmt.setString(1, "%");
}
pStmt.setString(2, table);
ResultSet rs = executeMetadataQuery(pStmt);
((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] {
new Field("", "PKTABLE_CAT", Types.CHAR, 255),
new Field("", "PKTABLE_SCHEM", Types.CHAR, 0),
new Field("", "PKTABLE_NAME", Types.CHAR, 255),
new Field("", "PKCOLUMN_NAME", Types.CHAR, 32),
new Field("", "FKTABLE_CAT", Types.CHAR, 255),
new Field("", "FKTABLE_SCHEM", Types.CHAR, 0),
new Field("", "FKTABLE_NAME", Types.CHAR, 255),
new Field("", "FKCOLUMN_NAME", Types.CHAR, 32),
new Field("", "KEY_SEQ", Types.SMALLINT, 2),
new Field("", "UPDATE_RULE", Types.SMALLINT, 2),
new Field("", "DELETE_RULE", Types.SMALLINT, 2),
new Field("", "FK_NAME", Types.CHAR, 255),
new Field("", "PK_NAME", Types.CHAR, 0),
new Field("", "DEFERRABILITY", Types.INTEGER, 2) });
return rs;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -