getIndexInfo
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException
-
Retrieves a description of the given table's indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
Each index column description has the following columns:
- TABLE_CAT String => table catalog (may be
null
) - TABLE_SCHEM String => table schema (may be
null
) - TABLE_NAME String => table name
- NON_UNIQUE boolean => Can index values be non-unique. false when TYPE is tableIndexStatistic
- INDEX_QUALIFIER String => index catalog (may be
null
);null
when TYPE is tableIndexStatistic - INDEX_NAME String => index name;
null
when TYPE is tableIndexStatistic - TYPE short => index type:
- tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions
- tableIndexClustered - this is a clustered index
- tableIndexHashed - this is a hashed index
- tableIndexOther - this is some other style of index
- ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
- COLUMN_NAME String => column name;
null
when TYPE is tableIndexStatistic - ASC_OR_DESC String => column sort sequence, "A" => ascending, "D" => descending, may be
null
if sort sequence is not supported;null
when TYPE is tableIndexStatistic - CARDINALITY int => When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index.
- PAGES 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.
- FILTER_CONDITION String => Filter condition, if any. (may be
null
)
- TABLE_CAT String => table catalog (may be
-
-
Parameters:
-
catalog
- a catalog name; must match the catalog name as it is stored in this database; "" retrieves those without a catalog;null
means that the catalog name should not be used to narrow the search -
schema
- a schema name; must match the schema name as it is stored in this database; "" retrieves those without a schema;null
means that the schema name should not be used to narrow the search -
table
- a table name; must match the table name as it is stored in this database -
unique
- when true, return only indices for unique values; when false, return indices regardless of whether unique or not -
approximate
- when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate
Returns:
-
ResultSet
- each row is an index column description
Throws:
-
检索给定表的索引和统计信息的描述。它们根据 NON_UNIQUE、TYPE、INDEX_NAME 和 ORDINAL_POSITION 进行排序。
每个索引列描述都有以下列:
- TABLE_CAT String => 表类别(可为
null
) - TABLE_SCHEM String => 表模式(可为
null
) - TABLE_NAME String => 表名称
- NON_UNIQUE boolean => 索引值是否可以不惟一。TYPE 为 tableIndexStatistic 时索引值为 false
- INDEX_QUALIFIER String => 索引类别(可为
null
);TYPE 为 tableIndexStatistic 时索引类别为null
- INDEX_NAME String => 索引名称;TYPE 为 tableIndexStatistic 时索引名称为
null
- TYPE short => 索引类型:
- tableIndexStatistic - 此标识与表的索引描述一起返回的表统计信息
- tableIndexClustered - 此为集群索引
- tableIndexHashed - 此为散列索引
- tableIndexOther - 此为某种其他样式的索引
- ORDINAL_POSITION short => 索引中的列序列号;TYPE 为 tableIndexStatistic 时该序列号为零
- COLUMN_NAME String => 列名称;TYPE 为 tableIndexStatistic 时列名称为
null
- ASC_OR_DESC String => 列排序序列,"A" => 升序,"D" => 降序,如果排序序列不受支持,可能为
null
;TYPE 为 tableIndexStatistic 时排序序列为null
- CARDINALITY int => TYPE 为 tableIndexStatistic 时,它是表中的行数;否则,它是索引中惟一值的数量。
- PAGES int => TYPE 为 tableIndexStatisic 时,它是用于表的页数,否则它是用于当前索引的页数。
- FILTER_CONDITION String => 过滤器条件,如果有的话。(可能为
null
)
- TABLE_CAT String => 表类别(可为
-
-
参数:
-
catalog
- 类别名称,因为存储在此数据库中,所以它必须匹配类别名称。该参数为 "" 则检索没有类别的描述,为null
则表示该类别名称不应用于缩小搜索范围 -
schema
- 模式名称,因为存储在此数据库中,所以它必须匹配模式名称。该参数为 "" 则检索那些没有模式的描述,为null
则表示该模式名称不应用于缩小搜索范围 -
table
- 表名称,因为存储在此数据库中,所以它必须匹配表名称 -
unique
- 该参数为 true 时,仅返回惟一值的索引;该参数为 false 时,返回所有索引,不管它们是否惟一 -
approximate
- 该参数为 true 时,允许结果是接近的数据值或这些数据值以外的值;该参数为 false 时,要求结果是精确结果
返回:
-
ResultSet
- 每一行都是一个索引列描述
抛出:
-
SQLException
- 如果发生数据库访问错误 -
-
SQLException
- if a database access error occurs
getIndexInfo
ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException
注意:这个函数返回的每一行都是一个索引的一个字段,如果一个索引有n个字段,那么对这个索引就要返回n行;如果一个表有3个索引,每个索引有2个字段,那么,对这些索引,至少要返回3*2=6行。COLUMN_NAME是允许重复的。
统计该表有多少个索引:
ResultSet rsIndex = meta.getIndexInfo(null, schemaPtrn, tableName, false, false); int nIndex = 0; while(rsIndex.next()){ //column sequence number within index short nPos = rsIndex.getShort("ORDINAL_POSITION"); if(nPos == 1){ nIndex ++; } }
-