Using Array Objects使用数组对象

Note: MySQL and Java DB currently do not support the ARRAY SQL data type. 注意：MySQL和Java DB目前不支持数组SQL数据类型。Consequently, no JDBC tutorial example is available to demonstrate the Array JDBC data type.因此，没有JDBC教程示例可用于演示ArrayJDBC数据类型。

The following topics are covered:涵盖以下主题：

• Creating Array Objects创建数组对象
• Retrieving and Accessing Array Values in ResultSet检索和访问ResultSet中的数组值
• Storing and Updating Array Objects存储和更新数组对象
• Releasing Array Resources释放阵列资源

Creating Array Objects创建阵列对象

Use the method Connection.createArrayOf to create Array objects.使用方法Connection.createArrayOf创建Array对象。

For example, suppose your database contains a table named REGIONS, which has been created and populated with the following SQL statements; note that the syntax of these statements will vary depending on your database:例如，假设您的数据库包含一个名为REGIONS的表，该表是用以下SQL语句创建和填充的；请注意，这些语句的语法因数据库而异：

create table REGIONS
    (REGION_NAME varchar(32) NOT NULL,
    ZIPS varchar32 ARRAY[10] NOT NULL,
    PRIMARY KEY (REGION_NAME));

insert into REGIONS values(
    'Northwest',
    '{"93101", "97201", "99210"}');
insert into REGIONS values(
    'Southwest',
    '{"94105", "90049", "92027"}');

Connection con = DriverManager.getConnection(url, props);
String [] northEastRegion = { "10022", "02110", "07399" };
Array anArray = con.createArrayOf("VARCHAR", northEastRegion);

The Oracle Database JDBC driver implements the java.sql.Array interface with the oracle.sql.ARRAY class.

Retrieving and Accessing Array Values in ResultSet检索和访问ResultSet中的数组值

As with the JDBC 4.0 large object interfaces (Blob, Clob, NClob), you can manipulate Array objects without having to bring all of their data from the database server to your client computer. An Array object materializes the SQL ARRAY it represents as either a result set or a Java array.

The following excerpt retrieves the SQL ARRAY value in the column ZIPS and assigns it to the java.sql.Array object z object. The excerpt retrieves the contents of z and stores it in zips, a Java array that contains objects of type String. The excerpt iterates through the zips array and checks that each postal (zip) code is valid. This code assumes that the class ZipCode has been defined previously with the method isValid returning true if the given zip code matches one of the zip codes in a master list of valid zip codes:

ResultSet rs = stmt.executeQuery(
    "SELECT region_name, zips FROM REGIONS");

while (rs.next()) {
    Array z = rs.getArray("ZIPS");
    String[] zips = (String[])z.getArray();
    for (int i = 0; i < zips.length; i++) {
        if (!ZipCode.isValid(zips[i])) {
            // ...
            // Code to display warning
        }
    }
}

In the following statement, the ResultSet method getArray returns the value stored in the column ZIPS of the current row as the java.sql.Array object z:

Array z = rs.getArray("ZIPS");

The variable z contains a locator, which is a logical pointer to the SQL ARRAY on the server; it does not contain the elements of the ARRAY itself. Being a logical pointer, z can be used to manipulate the array on the server.

In the following line, getArray is the Array.getArray method, not the ResultSet.getArray method used in the previous line. Because the Array.getArray method returns an Object in the Java programming language and because each zip code is a String object, the result is cast to an array of String objects before being assigned to the variable zips.

String[] zips = (String[])z.getArray();

The Array.getArray method materializes the SQL ARRAY elements on the client as an array of String objects. Because, in effect, the variable zips contains the elements of the array, it is possible to iterate through zips in a for loop, looking for zip codes that are not valid.

Storing and Updating Array Objects存储和更新数组对象

Use the methods PreparedStatement.setArray and PreparedStatement.setObject to pass an Array value as an input parameter to a PreparedStatement object.

The following example sets the Array object anArray (created in a previous example) as the second parameter to the PreparedStatement pstmt:

PreparedStatement pstmt = con.prepareStatement(
    "insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
pstmt.setString(1, "NorthEast");
pstmt.setArray(2, anArray);
pstmt.executeUpdate();

Similarly, use the methods PreparedStatement.updateArray and PreparedStatement.updateObject to update a column in a table with an Array value.

Releasing Array Resources释放阵列资源

Array objects remain valid for at least the duration of the transaction in which they are created. Array对象至少在创建它们的事务期间保持有效。This could potentially result in an application running out of resources during a long running transaction. 这可能会导致应用程序在长时间运行的事务中耗尽资源。Applications may release Array resources by invoking their free method.应用程序可以通过调用其free方法来释放Array资源。

In the following excerpt, the method Array.free is called to release the resources held for a previously created Array object.在下面的摘录中，调用方法Array.free来释放为先前创建的Array对象保留的资源。

Array aArray = con.createArrayOf("VARCHAR", northEastRegionnewYork);
// ...
aArray.free();