databasemetadatausinginfoschema.java

用于JAVA数据库连接.解压就可用,方便得很

		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}

	}

	private String generateOptionalRefContraintsJoin() {
		return ((this.hasReferentialConstraintsView) ? "JOIN " 
				+ "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R "
				+ "ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME "
				+ "AND R.TABLE_NAME = B.TABLE_NAME AND "
				+ "R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) " : "");
	}

	private String generateDeleteRuleClause() {
		return ((this.hasReferentialConstraintsView) ? 
				"CASE WHEN R.DELETE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) 
				+ " WHEN R.DELETE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull)  
				+ " WHEN R.DELETE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) 
				+ " WHEN R.DELETE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict)
				+ " WHEN R.DELETE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction)
				+ " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict));
	}

	private String generateUpdateRuleClause() {
		return ((this.hasReferentialConstraintsView) ? 
				"CASE WHEN R.UPDATE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) 
				+ " WHEN R.UPDATE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull)  
				+ " WHEN R.UPDATE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) 
				+ " WHEN R.UPDATE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict)
				+ " WHEN R.UPDATE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction)
				+ " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict));
	}

	/**
	 * Get a description of the primary key columns that are referenced by a
	 * table's foreign key columns (the primary keys imported by a table). They
	 * are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
	 * <P>
	 * Each primary key column description has the following columns:
	 * <OL>
	 * <li> <B>PKTABLE_CAT</B> String => primary key table catalog being
	 * imported (may be null) </li>
	 * <li> <B>PKTABLE_SCHEM</B> String => primary key table schema being
	 * imported (may be null) </li>
	 * <li> <B>PKTABLE_NAME</B> String => primary key table name being imported
	 * </li>
	 * <li> <B>PKCOLUMN_NAME</B> String => primary key column name being
	 * imported </li>
	 * <li> <B>FKTABLE_CAT</B> String => foreign key table catalog (may be
	 * null) </li>
	 * <li> <B>FKTABLE_SCHEM</B> String => foreign key table schema (may be
	 * null) </li>
	 * <li> <B>FKTABLE_NAME</B> String => foreign key table name </li>
	 * <li> <B>FKCOLUMN_NAME</B> String => foreign key column name </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 name (may be null) </li>
	 * <li> <B>PK_NAME</B> String => primary key name (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 primary key column description
	 * @throws SQLException
	 *             if a database access error occurs
	 * @see #getExportedKeys
	 */
	public java.sql.ResultSet getImportedKeys(String catalog, String schema,
			String table) throws SQLException {
		if (table == null) {
			throw SQLError.createSQLException("Table not specified.",
					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
		}

		if (catalog == null) {
			if (this.conn.getNullCatalogMeansCurrent()) {
				catalog = this.database;
			}
		}

		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 "
				+ "(CONSTRAINT_NAME, TABLE_NAME) "
				+ generateOptionalRefContraintsJoin()
				+ "WHERE "
				+ "B.CONSTRAINT_TYPE = 'FOREIGN KEY' "
				+ "AND A.TABLE_SCHEMA LIKE ? "
				+ "AND A.TABLE_NAME=? "
				+ "AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL "
				+ "ORDER BY "
				+ "A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_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;
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * Get a description of a table's indices and statistics. They are ordered
	 * by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
	 * <P>
	 * Each index column description has the following columns:
	 * <OL>
	 * <li> <B>TABLE_CAT</B> String => table catalog (may be null) </li>
	 * <li> <B>TABLE_SCHEM</B> String => table schema (may be null) </li>
	 * <li> <B>TABLE_NAME</B> String => table name </li>
	 * <li> <B>NON_UNIQUE</B> boolean => Can index values be non-unique? false
	 * when TYPE is tableIndexStatistic </li>
	 * <li> <B>INDEX_QUALIFIER</B> String => index catalog (may be null); null
	 * when TYPE is tableIndexStatistic </li>
	 * <li> <B>INDEX_NAME</B> String => index name; null when TYPE is
	 * tableIndexStatistic </li>
	 * <li> <B>TYPE</B> short => index type:
	 * <UL>
	 * <li> tableIndexStatistic - this identifies table statistics that are
	 * returned in conjuction with a table's index descriptions </li>
	 * <li> tableIndexClustered - this is a clustered index </li>
	 * <li> tableIndexHashed - this is a hashed index </li>
	 * <li> tableIndexOther - this is some other style of index </li>
	 * </ul>
	 * </li>
	 * <li> <B>ORDINAL_POSITION</B> short => column sequence number within
	 * index; zero when TYPE is tableIndexStatistic </li>
	 * <li> <B>COLUMN_NAME</B> String => column name; null when TYPE is
	 * tableIndexStatistic </li>
	 * <li> <B>ASC_OR_DESC</B> String => column sort sequence, "A" =>
	 * ascending, "D" => descending, may be null if sort sequence is not
	 * supported; null when TYPE is tableIndexStatistic </li>
	 * <li> <B>CARDINALITY</B> int => When TYPE is tableIndexStatisic then this
	 * is the number of rows in the table; otherwise it is the number of unique
	 * values in the index. </li>
	 * <li> <B>PAGES</B> int => When TYPE is tableIndexStatisic then this is
	 * the number of pages used for the table, otherwise it is the number of
	 * pages used for the current index. </li>
	 * <li> <B>FILTER_CONDITION</B> String => Filter condition, if any. (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
	 * @param unique
	 *            when true, return only indices for unique values; when false,
	 *            return indices regardless of whether unique or not
	 * @param approximate
	 *            when true, result is allowed to reflect approximate or out of
	 *            data values; when false, results are requested to be accurate
	 * @return ResultSet each row is an index column description
	 * @throws SQLException
	 *             DOCUMENT ME!
	 */
	public ResultSet getIndexInfo(String catalog, String schema, String table,
			boolean unique, boolean approximate) throws SQLException {
		StringBuffer sqlBuf = new StringBuffer("SELECT "
				+ "TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM,"
				+ "TABLE_NAME," + "NON_UNIQUE,"
				+ "TABLE_SCHEMA AS INDEX_QUALIFIER," + "INDEX_NAME,"
				+ tableIndexOther + " AS TYPE,"
				+ "SEQ_IN_INDEX AS ORDINAL_POSITION," + "COLUMN_NAME,"
				+ "COLLATION AS ASC_OR_DESC," + "CARDINALITY,"
				+ "NULL AS PAGES," + "NULL AS FILTER_CONDITION "
				+ "FROM INFORMATION_SCHEMA.STATISTICS WHERE "
				+ "TABLE_SCHEMA LIKE ? AND " + "TABLE_NAME LIKE ?");

		if (unique) {
			sqlBuf.append(" AND NON_UNIQUE=0 ");
		}

		sqlBuf.append("ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX");

		PreparedStatement pStmt = null;

		try {
			if (catalog == null) {
				if (this.conn.getNullCatalogMeansCurrent()) {
					catalog = this.database;
				}
			}
			
			pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());

			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("", "TABLE_CAT", Types.CHAR, 255),
					new Field("", "TABLE_SCHEM", Types.CHAR, 0),
					new Field("", "TABLE_NAME", Types.CHAR, 255),
					new Field("", "NON_UNIQUE", Types.CHAR, 4),
					new Field("", "INDEX_QUALIFIER", Types.CHAR, 1),
					new Field("", "INDEX_NAME", Types.CHAR, 32),
					new Field("", "TYPE", Types.CHAR, 32),
					new Field("", "ORDINAL_POSITION", Types.SMALLINT, 5),
					new Field("", "COLUMN_NAME", Types.CHAR, 32),
					new Field("", "ASC_OR_DESC", Types.CHAR, 1),
					new Field("", "CARDINALITY", Types.INTEGER, 10),
					new Field("", "PAGES", Types.INTEGER, 10),
					new Field("", "FILTER_CONDITION", Types.CHAR, 32) });

			return rs;
		} finally {
			if (pStmt != null) {
				pStmt.close();
			}
		}
	}

	/**
	 * Get a description of a table's primary key columns. They are ordered by
	 * COLUMN_NAME.
	 * <P>
	 * Each column description has the following columns:
	 * <OL>