getColumns 方法 (SQLServerDatabaseMetaData)getColumns Method (SQLServerDatabaseMetaData)
01/19/2017
在此文章
擷取可透過指定目錄提供之資料表資料行的描述。Retrieves a description of the table columns that are available in the specified catalog.
語法Syntax
public java.sql.ResultSet getColumns(java.lang.String catalog,
java.lang.String schema,
java.lang.String table,
java.lang.String col)
參數Parameters
catalogcatalog
包含目錄名稱的 String。A String that contains the catalog name.
schemaschema
包含結構描述名稱模式的 String。A String that contains the schema name pattern.
tabletable
包含資料表名稱模式的 String。A String that contains the table name pattern.
colcol
包含資料表名稱模式的 String。A String that contains the column name pattern.
傳回值Return Value
例外狀況Exceptions
備註Remarks
這個 getColumns 方法是由 java.sql.DatabaseMetaData 介面中的 getColumns 方法指定。This getColumns method is specified by the getColumns method in the java.sql.DatabaseMetaData interface.
透過 getColumns 方法所傳回的結果將包含下列資訊：The result set returned by the getColumns method will contain the following information:
名稱Name
類型Type
描述Description
TABLE_CATTABLE_CAT
StringString
目錄的名稱。The catalog name.
TABLE_SCHEMTABLE_SCHEM
StringString
資料表結構描述名稱。The table schema name.
TABLE_NAMETABLE_NAME
StringString
資料表名稱。The table name.
COLUMN_NAMECOLUMN_NAME
StringString
資料行名稱。The column name.
DATA_TYPEDATA_TYPE
smallintsmallint
來自 java.sql.Types 的 SQL 資料型別。The SQL data type from java.sql.Types.
TYPE_NAMETYPE_NAME
StringString
資料類型的名稱。The name of the data type.
COLUMN_SIZECOLUMN_SIZE
intint
資料行的有效位數。The precision of the column.
BUFFER_LENGTHBUFFER_LENGTH
smallintsmallint
資料的傳送大小。Transfer size of the data.
DECIMAL_DIGITSDECIMAL_DIGITS
smallintsmallint
資料行的小數位數。The scale of the column.
NUM_PREC_RADIXNUM_PREC_RADIX
smallintsmallint
資料行的基數。The radix of the column.
NULLABLENULLABLE
smallintsmallint
指出資料行是否可為 Null。Indicates if the column is nullable. 它可能是下列其中一個值：It can be one of the following values:
columnNoNulls (0)columnNoNulls (0)
columnNullable (1)columnNullable (1)
REMARKSREMARKS
StringString
與資料行相關聯的註解。The comments associated with the column.
注意： SQL ServerSQL Server 一律會針對這個資料行傳回 Null。Note: SQL ServerSQL Server always returns null for this column.
COLUMN_DEFCOLUMN_DEF
StringString
資料行的預設值。The default value of the column.
SQL_DATA_TYPESQL_DATA_TYPE
smallintsmallint
SQL 資料類型出現在描述子之 TYPE 欄位時的值。Value of the SQL data type as it appears in the TYPE field of the descriptor. 除了 datetime 和 SQL-92 interval 資料類型，這個資料行與 DATA_TYPE 資料行相同。This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. 這個資料行一律會傳回值。This column always returns a value.
SQL_DATETIME_SUBSQL_DATETIME_SUB
smallintsmallint
datetime 和 SQL-92 interval 資料類型的子類型代碼。Subtype code for datetime and SQL-92 interval data types. 其他資料類型的這個資料行都會傳回 NULL。For other data types, this column returns NULL.
CHAR_OCTET_LENGTHCHAR_OCTET_LENGTH
intint
資料行中的最大位元組數。The maximum number of bytes in the column.
ORDINAL_POSITIONORDINAL_POSITION
intint
資料表內資料行的索引。The index of the column within the table.
IS_NULLABLEIS_NULLABLE
StringString
指出資料行是否允許為 Null 值。Indicates if the column allows null values.
SS_IS_SPARSESS_IS_SPARSE
smallintsmallint
如果資料行為疏鬆資料行，則這個值為 1，否則為 0。1If the column is a sparse column, this has the value 1; otherwise, 0.1
SS_IS_COLUMN_SETSS_IS_COLUMN_SET
smallintsmallint
如果資料行為疏鬆資料行 column_set，這個值為 1，否則為 0。If the column is the sparse column_set column, this has the value 1; otherwise, 0. 11
SS_IS_COMPUTEDSS_IS_COMPUTED
smallintsmallint
指出 TABLE_TYPE 中的資料行是否為計算資料行。Indicates if a column in a TABLE_TYPE is a computed column. 11
IS_AUTOINCREMENTIS_AUTOINCREMENT
StringString
如果資料行會自動累加則為 "YES"，"YES" if the column is auto incremented. 如果資料行不會自動累加則為 "NO"。"NO" if the column is not auto incremented. 如果驅動程式無法判斷資料行是否會自動累加，則為 "" (空字串)。"" (empty string) if the driver cannot determine if the column is auto incremented. 11
SS_UDT_CATALOG_NAMESS_UDT_CATALOG_NAME
StringString
包含使用者定義型別 (UDT) 的目錄名稱。The name of the catalog that contains the user-defined type (UDT). 11
SS_UDT_SCHEMA_NAMESS_UDT_SCHEMA_NAME
StringString
包含使用者定義型別 (UDT) 的結構描述名稱。The name of the schema that contains the user-defined type (UDT). 11
SS_UDT_ASSEMBLY_TYPE_NAMESS_UDT_ASSEMBLY_TYPE_NAME
StringString
完整名稱的使用者定義型別 (UDT)。The fully-qualified name user-defined type (UDT). 11
SS_XML_SCHEMACOLLECTION_CATALOG_NAMESS_XML_SCHEMACOLLECTION_CATALOG_NAME
StringString
定義 XML 結構描述集合名稱所在目錄的名稱。The name of the catalog where an XML schema collection name is defined. 如果找不到目錄名稱，則此變數包含空字串。If the catalog name cannot be found, this variable contains an empty string. 11
SS_XML_SCHEMACOLLECTION_SCHEMA_NAMESS_XML_SCHEMACOLLECTION_SCHEMA_NAME
StringString
定義 XML 結構描述集合名稱所在結構描述的名稱。The name of the schema where an XML schema collection name is defined. 如果找不到結構描述名稱，這就是空字串。If the schema name cannot be found, this is an empty string. 11
SS_XML_SCHEMACOLLECTION_NAMESS_XML_SCHEMACOLLECTION_NAME
StringString
XML 結構描述集合的名稱。The name of an XML schema collection. 如果找不到該名稱，這就是空字串。If the name cannot be found, this is an empty string. 11
SS_DATA_TYPESS_DATA_TYPE
tinyinttinyint
擴充預存程序所使用的 SQL ServerSQL Server 資料類型。The SQL ServerSQL Server data type that is used by extended stored procedures.
注意： 如需 SQL ServerSQL Server 所傳回資料類型的詳細資訊，請參閱《SQL ServerSQL Server 線上叢書》中的＜資料類型 (Transact-SQL)＞。Note For more information about the data types returned by SQL ServerSQL Server, see "Data Types (Transact-SQL)" in SQL ServerSQL Server Books Online.
(1) 如果您連線到 SQL Server 2005 (9.x)SQL Server 2005 (9.x)，這個資料行不會存在。(1) This column will not be present if you are connecting to SQL Server 2005 (9.x)SQL Server 2005 (9.x).
注意
如需 getColumns 方法所傳回資料的詳細資訊，請參閱《SQL ServerSQL Server 線上叢書》中的＜sp_columns (Transact-SQL)＞。For more information about the data returned by the getColumns method, see "sp_columns (Transact-SQL)" in SQL ServerSQL Server Books Online.
在 MicrosoftMicrosoftSQL ServerSQL Server JDBC Driver 3.0 中，您會看到與舊版 JDBC 驅動程式不同的下列行為變更：In the MicrosoftMicrosoftSQL ServerSQL Server JDBC Driver 3.0, you will see the following behavior changes from earlier versions of the JDBC Driver:
DATA_TYPE 資料行有下列變更：The DATA_TYPE column has the following changes:
SQL ServerSQL Server 資料類型Data Type
JDBC Driver 2.0 中傳回類型及關聯的數值常數 (若連線到 SQL Server 2005 (9.x)SQL Server 2005 (9.x))Return Type in JDBC Driver 2.0 (or, if connected to SQL Server 2005 (9.x)SQL Server 2005 (9.x)) and Associated Numeric Constant
JDBC Driver 3.0 中的傳回類型 (當連線到 SQL Server 2008SQL Server 2008 或更新版本時)Return Type in JDBC Driver 3.0 when connected to SQL Server 2008SQL Server 2008 or later
大於 8 kB 的使用者定義型別user-defined type larger than 8 kB
LONGVARBINARY (-4)LONGVARBINARY (-4)
VARBINARY (-3)VARBINARY (-3)
geographygeography
LONGVARBINARY (-4)LONGVARBINARY (-4)
VARBINARY (-3)VARBINARY (-3)
幾何geometry
LONGVARBINARY (-4)LONGVARBINARY (-4)
VARBINARY (-3)VARBINARY (-3)
varbinary(max)varbinary(max)
LONGVARBINARY (-4)LONGVARBINARY (-4)
VARBINARY (-3)VARBINARY (-3)
nvarchar(max)nvarchar(max)
LONGVARCHAR (-1) 或 LONGNVARCHAR (JDBC 4) (-16)LONGVARCHAR (-1) or LONGNVARCHAR (JDBC 4) (-16)
VARCHAR (12) 或 NVARCHAR (JDBC 4) (-9)VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
varchar(max)varchar(max)
LONGVARCHAR (-1)LONGVARCHAR (-1)
VARCHAR (12)VARCHAR (12)
timetime
VARCHAR (12) 或 NVARCHAR (JDBC 4) (-9)VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
TIME (-154)TIME (-154)
datedate
VARCHAR (12) 或 NVARCHAR (JDBC 4) (-9)VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
DATE (91)DATE (91)
datetime2datetime2
VARCHAR (12) 或 NVARCHAR (JDBC 4) (-9)VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
TIMESTAMP (93)TIMESTAMP (93)
datetimeoffsetdatetimeoffset
VARCHAR (12) 或 NVARCHAR (JDBC 4) (-9)VARCHAR (12) or NVARCHAR (JDBC 4) (-9)
microsoft.sql.Types.DATETIMEOFFSET (-155)microsoft.sql.Types.DATETIMEOFFSET (-155)
COLUMN_SIZE 資料行有下列變更：The COLUMN_SIZE column has the following changes:
SQL ServerSQL Server 資料類型Data Type
JDBC Driver 2.0 中的傳回類型Return Type in JDBC Driver 2.0
JDBC Driver 3.0 中的傳回類型Return Type in JDBC Driver 3.0
nvarchar(max)nvarchar(max)
10737418231073741823
2147483647 (資料庫中繼資料)2147483647 (database metadata)
Xmlxml
10737418231073741823
2147483647 (資料庫中繼資料)2147483647 (database metadata)
小於或等於 8 kB 的使用者定義型別user-defined type less than or equal to 8 kB
8 kB (結果集和參數中繼資料)8 kB (result set and parameter metadata)
預存程序傳回的實際大小。Actual size returned by the stored procedure.
timetime
該類型的字串表示法長度 (以字元為單位)，假設最大值可以容納分數秒元件的有效位數。The length in characters of the string representation of the type, assuming the maximum allowed precision of the fractional seconds' component.
datedate
與 time 相同same as time
datetime2datetime2
與 time 相同same as time
datetimeoffsetdatetimeoffset
與 time 相同same as time
BUFFER_LENGTH 資料行有下列變更：The BUFFER_LENGTH column has the following change:
SQL ServerSQL Server 資料類型Data Type
JDBC Driver 2.0 中的傳回類型Return Type in JDBC Driver 2.0
JDBC Driver 3.0 中的傳回類型Return Type in JDBC Driver 3.0
大於 8 kB 的使用者定義型別user-defined type larger than 8 kB
21474836472147483647
TYPE_NAME 資料行有下列變更：The TYPE_NAME column has the following changes:
SQL ServerSQL Server 資料類型Data Type
JDBC Driver 2.0 中的傳回類型Return Type in JDBC Driver 2.0
JDBC Driver 3.0 中的傳回類型Return Type in JDBC Driver 3.0
varchar(max)varchar(max)
texttext
varcharvarchar
varbinary(max)varbinary(max)
imageimage
varbinaryvarbinary
DECIMAL_DIGITS 資料行有下列變更：The DECIMAL_DIGITS column has the following changes:
SQL ServerSQL Server 類型Type
JDBC Driver 2.0JDBC Driver 2.0
JDBC Driver 3.0JDBC Driver 3.0
timetime
nullnull
7 (如果有指定的話，則為更小的值)7 (or smaller if specified)
datedate
nullnull
nullnull
datetime2datetime2
nullnull
7 (如果有指定的話，則為更小的值)7 (or smaller if specified)
datetimeoffsetdatetimeoffset
nullnull
7 (如果有指定的話，則為更小的值)7 (or smaller if specified)
SQL_DATA_TYPE 資料行有下列變更：The SQL_DATA_TYPE column has the following changes:
SQL ServerSQL Server 資料類型Data Type
SQL ServerSQL Server 2008 JDBC Driver 2.0 中的資料值2008 Data Value in JDBC Driver 2.0
SQL ServerSQL Server 2008 JDBC Driver 3.0 中的資料值2008 Data Value in JDBC Driver 3.0
varchar(max)varchar(max)
-10-10
-9-9
nvarchar(max)nvarchar(max)
-1-1
-9-9
Xmlxml
-10-10
-152-152
小於或等於 8 kB 的使用者定義型別user-defined type less than or equal to 8 kB
-3-3
-151-151
大於 8 kB 的使用者定義型別user-defined type larger than 8 kB
不適用於 JDBC Driver 2.0Not available in JDBC Driver 2.0
-151-151
geographygeography
-4-4
-151-151
幾何geometry
-4-4
-151-151
hierarchyidhierarchyid
-4-4
-151-151
timetime
-9-9
9292
datedate
-9-9
9191
datetime2datetime2
-9-9
9393
datetimeoffsetdatetimeoffset
-9-9
-155-155
範例Example
下列範例會示範如何使用 getColumns 方法來傳回 AdventureWorksAdventureWorks 範例資料庫中 Person.Contact 資料表的資訊。The following example demonstrates how to use the getColumns method to return information for the Person.Contact table in the AdventureWorksAdventureWorks sample database.
import java.sql.*;
public class c1 {
public static void main(String[] args) {
String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedsecurity=true";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
DatabaseMetaData dbmd = con.getMetaData();
rs = dbmd.getColumns("AdventureWorks", "Person", "Contact", "FirstName");
ResultSet r = dbmd.getColumns(null, null, "Contact", null);
ResultSetMetaData rm = r.getMetaData();
int noofcols = rm.getColumnCount();
if (r.next())
for (int i = 0 ; i < noofcols ; i++ )
System.out.println(rm.getColumnName( i + 1 ) + ": \t\t" + r.getString( i + 1 ));
}
catch (Exception e) {}
finally {}
}
}
另請參閱See Also
2491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
