databasemetadatausinginfoschema.java

用于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;