databasemetadatausinginfoschema.java

开发MySql数据库的最新JDBC驱动。

	 * <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.ResultSet) 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>
	 * <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>COLUMN_NAME</B> String => column name </li>
	 * <li> <B>KEY_SEQ</B> short => sequence number within primary key </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
	 *             DOCUMENT ME!
	 */
	public java.sql.ResultSet getPrimaryKeys(String catalog, String schema,
			String table) throws SQLException {

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

		if (table == null) {
			throw SQLError.createSQLException("Table not specified.",
					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
		}

		String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, "
				+ "COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS "
				+ "WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND "
				+ "INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX";

		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.ResultSet) 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("", "COLUMN_NAME", Types.CHAR, 32),
					new Field("", "KEY_SEQ", Types.SMALLINT, 5),
					new Field("", "PK_NAME", Types.CHAR, 32) });

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

	/**
	 * Get a description of stored procedures available in a catalog.
	 * <P>
	 * Only procedure descriptions matching the schema and procedure name
	 * criteria are returned. They are ordered by PROCEDURE_SCHEM, and
	 * PROCEDURE_NAME.
	 * </p>
	 * <P>
	 * Each procedure description has the the following columns:
	 * <OL>
	 * <li> <B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
	 * </li>
	 * <li> <B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
	 * </li>
	 * <li> <B>PROCEDURE_NAME</B> String => procedure name </li>
	 * <li> reserved for future use </li>
	 * <li> reserved for future use </li>
	 * <li> reserved for future use </li>
	 * <li> <B>REMARKS</B> String => explanatory comment on the procedure </li>
	 * <li> <B>PROCEDURE_TYPE</B> short => kind of procedure:
	 * <UL>
	 * <li> procedureResultUnknown - May return a result </li>
	 * <li> procedureNoResult - Does not return a result </li>
	 * <li> procedureReturnsResult - Returns a result </li>
	 * </ul>
	 * </li>
	 * </ol>
	 * </p>
	 * 
	 * @param catalog
	 *            a catalog name; "" retrieves those without a catalog
	 * @param schemaPattern
	 *            a schema name pattern; "" retrieves those without a schema
	 * @param procedureNamePattern
	 *            a procedure name pattern
	 * @return ResultSet each row is a procedure description
	 * @throws SQLException
	 *             if a database access error occurs
	 * @see #getSearchStringEscape
	 */
	public ResultSet getProcedures(String catalog, String schemaPattern,
			String procedureNamePattern) throws SQLException {

		if ((procedureNamePattern == null)
				|| (procedureNamePattern.length() == 0)) {
			if (this.conn.getNullNamePatternMatchesAll()) {
				procedureNamePattern = "%";
			} else {
				throw SQLError.createSQLException(
						"Procedure name pattern can not be NULL or empty.",
						SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
			}
		}

		String db = null;

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

		String sql = "SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, "
				+ "NULL AS PROCEDURE_SCHEM, "
				+ "ROUTINE_NAME AS PROCEDURE_NAME, " + "NULL AS RESERVED_1, "
				+ "NULL AS RESERVED_2, " + "NULL AS RESERVED_3, "
				+ "ROUTINE_COMMENT AS REMARKS, "
				+ "CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN "
				+ procedureNoResult + " WHEN ROUTINE_TYPE='FUNCTION' THEN "
				+ procedureReturnsResult + " ELSE " + procedureResultUnknown
				+ " END AS PROCEDURE_TYPE "
				+ "FROM INFORMATION_SCHEMA.ROUTINES WHERE "
				+ "ROUTINE_SCHEMA LIKE ? AND ROUTINE_NAME LIKE ? "
				+ "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";

		PreparedStatement pStmt = null;

		try {
			pStmt = prepareMetaDataSafeStatement(sql);
			
			if (db != null) {
				pStmt.setString(1, db);
			} else {
				pStmt.setString(1, "%");
			}
			
			pStmt.setString(2, procedureNamePattern);

			ResultSet rs = executeMetadataQuery(pStmt);
			((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
					new Field("", "PROCEDURE_CAT", Types.CHAR, 0),
					new Field("", "PROCEDURE_SCHEM", Types.CHAR, 0),
					new Field("", "PROCEDURE_NAME", Types.CHAR, 0),
					new Field("", "reserved1", Types.CHAR, 0),
					new Field("", "reserved2", Types.CHAR, 0),
					new Field("", "reserved3", Types.CHAR, 0),
					new Field("", "REMARKS", Types.CHAR, 0),
					new Field("", "PROCEDURE_TYPE", Types.SMALLINT, 0) });

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

	/**
	 * 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>
	 * <P>
	 * Each table 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>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
	 * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS",
	 * "SYNONYM". </li>
	 * <li> <B>REMARKS</B> String => explanatory comment on the table </li>
	 * </ol>
	 * </p>
	 * <P>
	 * <B>Note:</B> Some databases may not return information for all tables.
	 * </p>
	 * 
	 * @param catalog
	 *            a catalog name; "" retrieves those without a catalog
	 * @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
	 * @throws SQLException
	 *             DOCUMENT ME!
	 * @see #getSearchStringEscape
	 */
	public ResultSet getTables(String catalog, String schemaPattern,
			String tableNamePattern, String[] types) throws SQLException {
		if (catalog == null) {
			if (this.conn.getNullCatalogMeansCurrent()) {
				catalog = this.database;
			}
		}

		if (tableNamePattern == null) {
			if (this.conn.getNullNamePatternMatchesAll()) {
				tableNamePattern = "%";
			} else {
				throw SQLError.createSQLException(
						"Table name pattern can not be NULL or empty.",
						SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
			}
		}

		PreparedStatement pStmt = null;

		String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, "
				+ "NULL AS TABLE_SCHEM, TABLE_NAME, "
				+ "CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'TABLE' WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, "
				+ "TABLE_COMMENT AS REMARKS "
				+ "FROM INFORMATION_SCHEMA.TABLES WHERE "
				+ "TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND TABLE_TYPE IN (?,?,?) "
				+ "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME";
		try {
			pStmt = prepareMetaDataSafeStatement(sql);
			
			if (catalog != null) {
				pStmt.setString(1, catalog);
			} else {
				pStmt.setString(1, "%");
			}
			
			pStmt.setString(2, tableNamePattern);

			// This overloading of IN (...) allows us to cache this
			// prepared statement
			if (types == null || types.length == 0) {
				pStmt.setString(3, "BASE TABLE");
				pStmt.setString(4, "VIEW");
				pStmt.setString(5, "TEMPORARY");
			} else {
				pStmt.setNull(3, Types.VARCHAR);
				pStmt.setNull(4, Types.VARCHAR);
				pStmt.setNull(5, Types.VARCHAR);

				for (int i = 0; i < types.length; i++) {
					if ("TABLE".equalsIgnoreCase(types[i])) {
						pStmt.setString(3, "BASE TABLE");
					}

					if ("VIEW".equalsIgnoreCase(types[i])) {
						pStmt.setString(4, "VIEW");
					}

					if ("LOCAL TEMPORARY".equalsIgnoreCase(types[i])) {
						pStmt.setString(5, "TEMPORARY");
					}
				}
			}

			ResultSet rs = executeMetadataQuery(pStmt);

			((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
					new Field("", "TABLE_CAT", java.sql.Types.VARCHAR,
							(catalog == null) ? 0 : catalog.length()),
					new Field("", "TABLE_SCHEM", java.sql.Types.VARCHAR, 0),
					new Field("", "TABLE_NAME", java.sql.Types.VARCHAR, 255),
					new Field("", "TABLE_TYPE", java.sql.Types.VARCHAR, 5),
					new Field("", "REMARKS", java.sql.Types.VARCHAR, 0) });

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

	private PreparedStatement prepareMetaDataSafeStatement(String sql)
			throws SQLException {
		// Can't use server-side here as we coerce a lot of types to match
		// the spec.
		PreparedStatement pStmt = this.conn.clientPrepareStatement(sql);

		if (pStmt.getMaxRows() != 0) {
			pStmt.setMaxRows(0);
		}

		pStmt.setHoldResultsOpenOverClose(true);

		return pStmt;
	}
}