Using Advanced Data Types使用高级数据类型

The advanced data types introduced in this section give a relational database more flexibility in what can be used as a value for a table column. 本节中介绍的高级数据类型为关系数据库提供了更大的灵活性，使其可以用作表列的值。For example, a column can be used to store BLOB (binary large object) values, which can store very large amounts of data as raw bytes. 例如，一列可用于存储BLOB（二进制大对象）值，该值可将大量数据存储为原始字节。A column can also be of type CLOB (character large object), which is capable of storing very large amounts of data in character format.列也可以是CLOB（字符大对象）类型，它能够以字符格式存储大量数据。

The latest version of the ANSI/ISO SQL standard is commonly referred to as SQL:2003. ANSI/ISO SQL标准的最新版本通常称为SQL:2003。This standard specifies the following data types:本标准规定了以下数据类型：

• SQL92 built-in types, which consist of the familiar SQL column types such as CHAR, FLOAT, and DATESQL92内置类型，由熟悉的SQL列类型组成，如CHAR、FLOAT和DATE

• SQL99 built-in types, which consist of types added by SQL99:SQL99内置类型，由SQL99添加的类型组成：

  • BOOLEAN: Boolean (true or false) value：布尔值（真或假）

  • BLOB: Binary large Bobject：二进制大对象

  • CLOB: Character large object：字符大对象

• New built-in types added by SQL:2003:SQL:2003添加的新内置类型：

  • XML: XML object：XML对象

• User defined types:用户定义的类型：

  • Structured type: User-defined type; for example:结构化类型：用户自定义类型；例如：

    CREATE TYPE PLANE_POINT
    AS (X FLOAT, Y FLOAT) NOT FINAL

  • DISTINCT type: User-defined type based on a built-in type; for example:类型：基于内置类型的用户定义类型；例如：

    CREATE TYPE MONEY
    AS NUMERIC(10,2) FINAL

• Constructed types: New types based on a given base type:构造类型：基于给定基类型的新类型：

  • REF(structured-type): Pointer that persistently denotes an instance of a structured type that resides in the database：持久表示驻留在数据库中的结构化类型的实例的游标

  • base-type ARRAY[n]: Array of n base-type elements：n个基类型元素的数组

• Locators: Entities that are logical pointers to data that resides on the database server. 定位器：是指向驻留在数据库服务器上的数据的逻辑游标的实体。A locator exists in the client computer and is a transient, logical pointer to data on the server. A locator typically refers to data that is too large to materialize on the client, such as images or audio. (Materialized views are query results that have been stored or "materialized" in advance as schema objects.) There are operators defined at the SQL level to retrieve randomly accessed pieces of the data denoted by the locator:

  • LOCATOR(structured-type): Locator to a structured instance in the server：服务器中结构化实例的定位器

  • LOCATOR(array): Locator to an array in the server：服务器中数组的定位器

  • LOCATOR(blob): Locator to a binary large object in the server：服务器中二进制大对象的定位器

  • LOCATOR(clob): Locator to a character large object in the server：服务器中字符大对象的定位器

• Datalink: Type for managing data external to the data source. Datalink values are part of SQL MED (Management of External Data), a part of the SQL ANSI/ISO standard specification.

Mapping Advanced Data Types映射高级数据类型

The JDBC API provides default mappings for advanced data types specified by the SQL:2003 standard. JDBCAPI为SQL:2003标准指定的高级数据类型提供默认映射。The following list gives the data types and the interfaces or classes to which they are mapped:下表给出了数据类型及其映射到的接口或类：

• BLOB: Blob interface
• CLOB: Clob interface
• NCLOB: NClob interface
• ARRAY: Array interface
• XML: SQLXML interface
• Structured types: Struct interface
• REF(structured type): Ref interface
• ROWID: RowId interface
• DISTINCT: Type to which the base type is mapped. For example, a DISTINCT value based on a SQL NUMERIC type maps to a java.math.BigDecimal type because NUMERIC maps to BigDecimal in the Java programming language.
• DATALINK: java.net.URL object

Using Advanced Data Types使用高级数据类型

You retrieve, store, and update advanced data types the same way you handle other data types. 检索、存储和更新高级数据类型的方式与处理其他数据类型的方式相同。You use either ResultSet.getDataType or CallableStatement.getDataType methods to retrieve them, PreparedStatement.setDataType methods to store them, and ResultSet.updateDataType methods to update them. (The variable DataType is the name of a Java interface or class mapped to an advanced data type.) Probably 90 percent of the operations performed on advanced data types involve using the getDataType, setDataType, and updateDataType methods. The following table shows which methods to use:下表显示了要使用的方法：

Note: The DISTINCT data type behaves differently from other advanced SQL data types. Being a user-defined type that is based on an already existing built-in types, it has no interface as its mapping in the Java programming language. Consequently, you use the method that corresponds to the Java type on which the DISTINCT data type is based. See Using DISTINCT Data Type for more information.

For example, the following code fragment retrieves a SQL ARRAY value. For this example, suppose that the column SCORES in the table STUDENTS contains values of type ARRAY. The variable stmt is a Statement object.

ResultSet rs = stmt.executeQuery(
    "SELECT SCORES FROM STUDENTS " +
    "WHERE ID = 002238");
rs.next();
Array scores = rs.getArray("SCORES");

The variable scores is a logical pointer to the SQL ARRAY object stored in the table STUDENTS in the row for student 002238.变量scores是一个逻辑游标，指向存储在student 002238行中STUDENTS表中的SQLARRAY对象。

If you want to store a value in the database, you use the appropriate set method. 如果要在数据库中存储值，请使用适当的set方法。For example, the following code fragment, in which rs is a ResultSet object, stores a Clob object:例如，以下代码片段（rs是ResultSet对象）存储Clob对象：

Clob notes = rs.getClob("NOTES");
PreparedStatement pstmt =
    con.prepareStatement(
        "UPDATE MARKETS SET COMMENTS = ? " +
        "WHERE SALES < 1000000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();

This code sets notes as the first parameter in the update statement being sent to the database. 此代码将notes设置为发送到数据库的update语句中的第一个参数。The Clob value designated by notes will be stored in the table MARKETS in column COMMENTS in every row where the value in the column SALES is less than one million.notes指定的Clob值将存储在表MARKETS中SALES列值小于一百万的每一行的COMMENTS列中。