Using Structured Objects使用结构化对象

Note: MySQL and Java DB currently do not support user-defined types. 注意：MySQL和Java DB目前不支持用户定义的类型。Consequently, no JDBC tutorial example is available to demonstrate the features described in this section.因此，没有JDBC教程示例可用于演示本节中描述的功能。

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

• Overview of Structured Types结构化类型概述
• Using DISTINCT Type in Structured Types在结构化类型中使用独特类型
• Using References to Structured Types使用对结构化类型的引用
• Sample Code for Creating SQL REF Object用于创建SQL REF对象的示例代码
• Using User-Defined Types as Column Values使用用户定义的类型作为列值
• Inserting User-Defined Types into Tables将用户定义的类型插入到表中

Overview of Structured Types结构化类型概述

SQL structured types and DISTINCT types are the two data types that a user can define in SQL. SQL结构化类型和DISTINCT类型是用户可以在SQL中定义的两种数据类型。They are often referred to as UDTs (user-defined types), and you create them with a SQL CREATE TYPE statement.它们通常被称为UDT（用户定义类型），您可以使用SQL CREATE TYPE语句创建它们。

Getting back to the example of The Coffee Break, suppose that the owner has been successful beyond all expectations and has been expanding with new branches. 回到咖啡休息的例子，假设店主的成功超出了所有人的预期，并且一直在扩展新的分支机构。The owner has decided to add a STORES table to the database containing information about each establishment. 所有者已决定向数据库添加一个STORES表，其中包含每个机构的信息。STORES will have four columns:STORES将有四列：

• STORE_NO for each store's identification number用于每个商店的标识号
• LOCATION for its address用于其地址
• COF_TYPES for the coffees it sells用于其销售的咖啡
• MGR for the name of the store manager用于门店经理的姓名

The owner makes the column LOCATION be a SQL structured type, the column COF_TYPES a SQL ARRAY, and the column MGR a REF(MANAGER), with MANAGER being a SQL structured type.所有者将列LOCATION设置为SQL结构化类型，将列COF_TYPES设置为SQLARRAY，将列MGR设置为REF(MANAGER)，将MANAGER设置为SQL结构化类型。

The first thing the owner must define the new structured types for the address and the manager. 所有者首先必须为地址和经理定义新的结构化类型。A SQL structured type is similar to structured types in the Java programming language in that it has members, called attributes, that may be any data type. SQL结构化类型类似于Java编程语言中的结构化类型，因为它具有称为属性的成员，这些成员可以是任何数据类型。The owner writes the following SQL statement to create the new data type ADDRESS:所有者编写以下SQL语句以创建新的数据类型ADDRESS：

CREATE TYPE ADDRESS
(
    NUM INTEGER,
    STREET VARCHAR(40),
    CITY VARCHAR(40),
    STATE CHAR(2),
    ZIP CHAR(5)
);

In this statement, the new type ADDRESS has five attributes, which are analogous to fields in a Java class. 在该语句中，新类型ADDRESS有五个属性，它们类似于Java类中的字段。The attribute NUM is an INTEGER, the attribute STREET is a VARCHAR(40), the attribute CITY is a VARCHAR(40), the attribute STATE is a CHAR(2), and the attribute ZIP is a CHAR(5).属性NUM是INTEGER，属性STREET是VARCHAR(40)，属性CITY是VARCHAR(40)，属性STATE是CHAR(2)，属性ZIP是CHAR(5)。

The following excerpt, in which con is a valid Connection object, sends the definition of ADDRESS to the database:以下摘录（其中con是有效的连接对象）将ADDRESS的定义发送到数据库：

String createAddress =
    "CREATE TYPE ADDRESS " +
    "(NUM INTEGER, STREET VARCHAR(40), " +
    "CITY VARCHAR(40), STATE CHAR(2), ZIP CHAR(5))";
Statement stmt = con.createStatement();
stmt.executeUpdate(createAddress);

Now the ADDRESS structured type is registered with the database as a data type, and the owner can use it as the data type for a table column or an attribute of a structured type.现在，ADDRESS结构化类型作为数据类型在数据库中注册，所有者可以将其用作表列或结构化类型属性的数据类型。

Using DISTINCT Type in Structured Type在结构化类型中使用DISTINCT类型

One of the attributes the owner of The Coffee Break plans to include in the new structured type MANAGER is the manager's telephone number. 咖啡休息时间的所有者计划在新的结构化类型MANAGER中包括的属性之一是经理的电话号码。Because the owner will always list the telephone number as a 10-digit number (to be sure it includes the area code) and will never manipulate it as a number, the owner decides to define a new type called PHONE_NO that consists of 10 characters. 由于所有者将始终将电话号码列为10位数字（以确保其包含区号），并且不会将其作为数字进行操作，因此所有者决定定义一个名为PHONE_NO的新类型，该类型由10个字符组成。The SQL definition of this data type, which can be thought of as a structured type with only one attribute, looks like this:此数据类型的SQL定义可以看作是一个只有一个属性的结构化类型，如下所示：

CREATE TYPE PHONE_NO AS CHAR(10);

Or, as noted earlier, for some drivers the definition might look like this:或者，如前所述，对于某些驱动程序，定义可能如下所示：

CREATE DISTINCT TYPE PHONE_NO AS CHAR(10);

A DISTINCT type is always based on another data type, which must be a predefined type. DISTINCT类型始终基于另一个数据类型，该数据类型必须是预定义的类型。In other words, a DISTINCT type cannot be based on a user-defined type (UDT). 换句话说，DISTINCT类型不能基于用户定义类型（UDT）。To retrieve or set a value that is a DISTINCT type, use the appropriate method for the underlying type (the type on which it is based). 要检索或设置DISTINCT类型的值，请对基础类型（它所基于的类型）使用适当的方法。For example, to retrieve an instance of PHONE_NO, which is based on a CHAR type, you would use the method getString because that is the method for retrieving a CHAR.例如，要检索基于CHAR类型的PHONE_NO实例，可以使用getString方法，因为这是检索CHAR的方法。

Assuming that a value of type PHONE_NO is in the fourth column of the current row of the ResultSet object rs, the following line of code retrieves it:假设一个PHONE_NO类型的值位于ResultSet对象rs当前行的第四列中，下面的代码行将检索该值：

String phoneNumber = rs.getString(4);

Similarly, the following line of code sets an input parameter that has type PHONE_NO for a prepared statement being sent to the database:类似地，以下代码行设置了一个输入参数，该参数的类型为PHONE_NO，用于发送到数据库的准备语句：

pstmt.setString(1, phoneNumber);

Adding on to the previous code fragment, the definition of PHONE_NO will be sent to the database with the following line of code:在前面的代码片段的基础上，PHONE_NO的定义将通过以下代码行发送到数据库：

stmt.executeUpdate(
    "CREATE TYPE PHONE_NO AS CHAR(10)");

After registering the type PHONE_NO with the database, the owner can use it as a column type in a table or as the data type for an attribute in a structured type. 在向数据库注册类型PHONE_NO后，所有者可以将其用作表中的列类型或结构化类型中属性的数据类型。The definition of MANAGER in the following SQL statement uses PHONE_NO as the data type for the attribute PHONE:以下SQL语句中MANAGER的定义使用PHONE_NO作为属性PHONE的数据类型：

CREATE TYPE MANAGER
(
    MGR_ID INTEGER,
    LAST_NAME VARCHAR(40),
    FIRST_NAME VARCHAR(40),
    PHONE PHONE_NO
);

Reusing stmt, defined previously, the following code fragment sends the definition of the structured type MANAGER to the database:使用前面定义的stmt，以下代码片段将结构化类型MANAGER的定义发送到数据库：

String createManager =
    "CREATE TYPE MANAGER " +
    "(MGR_ID INTEGER, LAST_NAME " +
    "VARCHAR(40), " +
    "FIRST_NAME VARCHAR(40), " +
    "PHONE PHONE_NO)";
  stmt.executeUpdate(createManager);

Using References to Structured Types使用对结构化类型的引用

The owner of The Coffee Break has created three new data types used as column types or attribute types in the database: The structured types LOCATION and MANAGER, and the DISTINCT type PHONE_NO. Coffee Break的所有者创建了三种新的数据类型，用作数据库中的列类型或属性类型：结构化类型LOCATION和MANAGER和DISTINCT类型PHONE_NO。The entrepreneur has used PHONE_NO as the type for the attribute PHONE in the new type MANAGER, and ADDRESS as the data type for the column LOCATION in the table STORES. 企业家在新的类型MANAGER中将PHONE_NO用作属性PHONE的类型，将ADDRESS用作表STORES中列LOCATION的数据类型。The MANAGER type could be used as the type for the column MGR, but instead the entrepreneur prefers to use the type REF(MANAGER) because the entrepreneur often has one person manage two or three stores. MANAGER类型可以用作列MGR的类型，但企业家更喜欢使用类型REF(MANAGER)，因为企业家通常由一个人管理两个或三个店铺。Using REF(MANAGER) as a column type avoids repeating all the data for MANAGER when one person manages more than one store.使用REF(MANAGER)作为列类型可以避免在一个人管理多个存储时重复MANAGER的所有数据。

With the structured type MANAGER already created, the owner can now create a table containing instances of MANAGER that can be referenced. 在已创建结构化类型MANAGER的情况下，所有者现在可以创建一个包含可引用的MANAGER实例的表。A reference to an instance of MANAGER will have the type REF(MANAGER). 对MANAGER实例的引用的类型为REF(MANAGER)。A SQL REF is nothing more than a logical pointer to a structured type, so an instance of REF(MANAGER) serves as a logical pointer to an instance of MANAGER.SQL REF只不过是指向结构化类型的逻辑指针，因此REF(MANAGER)的实例充当指向MANAGER实例的逻辑指针。

Because a SQL REF value needs to be permanently associated with the instance of the structured type that it references, it is stored in a special table together with its associated instance. 因为SQL REF值需要与它引用的结构化类型的实例永久关联，所以它与其关联的实例一起存储在一个特殊的表中。A programmer does not create REF types directly but rather creates the table that will store instances of a particular structured type that can be referenced. 程序员不直接创建REF类型，而是创建一个表来存储可引用的特定结构化类型的实例。Every structured type that is to be referenced will have its own table. When you insert an instance of the structured type into the table, the database automatically creates a REF instance. 要引用的每个结构化类型都有自己的表。在表中插入结构化类型的实例时，数据库会自动创建一个REF实例。For example, to contain instances of MANAGER that can be referenced, the owner created the following special table using SQL:例如，为了包含可引用的MANAGER实例，所有者使用SQL创建了以下特殊表：

CREATE TABLE MANAGERS OF MANAGER
  (OID REF(MANAGER)
  VALUES ARE SYSTEM GENERATED);

This statement creates a table with the special column OID, which stores values of type REF(MANAGER). 此语句创建一个具有特殊列OID的表，该列存储REF(MANAGER)类型的值。Each time an instance of MANAGER is inserted into the table, the database will generate an instance of REF(MANAGER) and store it in the column OID. 每次将MANAGER实例插入表中时，数据库将生成一个REF(MANAGER)实例并将其存储在OID列中。Implicitly, an additional column stores each attribute of MANAGER that has been inserted into the table, as well. 另一列隐式地存储插入表中的MANAGER的每个属性。For example, the following code fragment shows how the entrepreneur created three instances of the MANAGER structured type to represent three managers:例如，以下代码片段显示了企业家如何创建MANAGER结构化类型的三个实例来表示三个经理：

INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000001,
    'MONTOYA',
    'ALFREDO',
    '8317225600'
  );

  INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000002,
    'HASKINS',
    'MARGARET',
    '4084355600'
  );

  INSERT INTO MANAGERS (
    MGR_ID, LAST_NAME,
    FIRST_NAME, PHONE) VALUES
  (
    000003,
    'CHEN',
    'HELEN',
    '4153785600'
   );

The table MANAGERS will now have three rows, one row for each manager inserted so far. 表MANAGERS中现在有了三行数据，每行针对一个刚插入的经理。The column OID will contain three unique object identifiers of type REF(MANAGER), one for each instance of MANAGER. 列OID将包含三个REF(MANAGER)类型的唯一对象标识符，每个MANAGER实例一个。These object identifiers were generated automatically by the database and will be permanently stored in the table MANAGERS. 这些对象标识符由数据库自动生成，并将永久存储在表MANAGERS中。Implicitly, an additional column stores each attribute of MANAGER. 隐式地，另一列存储MANAGER的每个属性。For example, in the table MANAGERS, one row contains a REF(MANAGER) that references Alfredo Montoya, another row contains a REF(MANAGER) that references Margaret Haskins, and a third row contains a REF(MANAGER) that references Helen Chen.例如，在表MANAGERS中，一行包含引用Alfredo Montoya的REF(MANAGER)，另一行包含引用Margaret Haskins的REF(MANAGER)，第三行包含引用Helen Chen的REF(MANAGER)。

To access a REF(MANAGER) instance, you select it from its table. 要访问REF(MANAGER)实例，请从其表中选择它。For example, the owner retrieved the reference to Alfredo Montoya, whose ID number is 000001, with the following code fragment:例如，所有者检索到对Alfredo Montoya的引用，其ID号为000001，代码片段如下：

String selectMgr =
    "SELECT OID FROM MANAGERS " +
    "WHERE MGR_ID = 000001";
  ResultSet rs = stmt.executeQuery(selectMgr);
  rs.next();
  Ref manager = rs.getRef("OID");

Now the variable manager can be used as a column value that references Alfredo Montoya.现在，变量manager可以用作引用Alfredo Montoya的列值。

Sample Code for Creating SQL REF Object用于创建SQL REF对象的示例代码

The following code example creates the table MANAGERS, a table of instances of the structured type MANAGER that can be referenced, and inserts three instances of MANAGER into the table. 下面的代码示例创建表MANAGERS，即可以引用的结构化类型MANAGER实例表，并将MANAGER的三个实例插入表中。The column OID in this table will store instances of REF(MANAGER). 此表中的列OID将存储REF(MANAGER)的实例。After this code is executed, the MANAGERS table will have a row for each of the three MANAGER objects inserted, and the value in the OID column will be the REF(MANAGER) type that identifies the instance of MANAGER stored in that row.执行此代码后，MANAGERS表将针对插入的三个MANAGER对象中的每一个都有一行，OID列中的值将是标识存储在该行中的管理器实例的REF(MANAGER)类型。

package com.oracle.tutorial.jdbc;

import java.sql.*;

public class CreateRef {

    public static void main(String args[]) {

        JDBCTutorialUtilities myJDBCTutorialUtilities;
        Connection myConnection = null;

        if (args[0] == null) {
            System.err.println("Properties file not specified " +
                               "at command line");
            return;
        } else {
            try {
                myJDBCTutorialUtilities = new JDBCTutorialUtilities(args[0]);
            } catch (Exception e) {
                System.err.println("Problem reading properties " +
                                   "file " + args[0]);
                e.printStackTrace();
                return;
            }
        }

        Connection con = null;
        Statement stmt = null;

        try {
            String createManagers =
                "CREATE TABLE " +
                "MANAGERS OF MANAGER " +
                "(OID REF(MANAGER) " +
                "VALUES ARE SYSTEM " +
                "GENERATED)";

            String insertManager1 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000001, 'MONTOYA', " +
                "'ALFREDO', " +
                "'8317225600')";

            String insertManager2 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000002, 'HASKINS', " +
                "'MARGARET', " +
                "'4084355600')";

            String insertManager3 =
                "INSERT INTO MANAGERS " +
                "(MGR_ID, LAST_NAME, " +
                "FIRST_NAME, PHONE) " +
                "VALUES " +
                "(000003, 'CHEN', 'HELEN', " +
                "'4153785600')";
  
            con = myJDBCTutorialUtilities.getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();
            stmt.executeUpdate(createManagers);

            stmt.addBatch(insertManager1);
            stmt.addBatch(insertManager2);
            stmt.addBatch(insertManager3);
            int [] updateCounts = stmt.executeBatch();

            con.commit();

            System.out.println("Update count for:  ");
            for (int i = 0; i < updateCounts.length; i++) {
                System.out.print("    command " + (i + 1) + " = ");
                System.out.println(updateCounts[i]);
            }
        } catch(BatchUpdateException b) {
            System.err.println("-----BatchUpdateException-----");
            System.err.println("Message:  " + b.getMessage());
            System.err.println("SQLState:  " + b.getSQLState());
            System.err.println("Vendor:  " + b.getErrorCode());
            System.err.print("Update counts for " + "successful commands:  ");
            int [] rowsUpdated = b.getUpdateCounts();
            for (int i = 0; i < rowsUpdated.length; i++) {
                System.err.print(rowsUpdated[i] + "   ");
            }
            System.err.println("");
        } catch(SQLException ex) {
            System.err.println("------SQLException------");
            System.err.println("Error message:  " + ex.getMessage());
            System.err.println("SQLState:  " + ex.getSQLState());
            System.err.println("Vendor:  " + ex.getErrorCode());
        } finally {
            if (stmt != null) { stmt.close(); }
              JDBCTutorialUtilities.closeConnection(con);
        }
    }
}

Using User-Defined Types as Column Values使用用户定义的类型作为列值

Our entrepreneur now has the UDTs required to create the table STORES. 我们的企业家现在拥有创建表STORES所需的UDT。The structured type ADDRESS is the type for the column LOCATION, and the type REF(MANAGER) is the type for the column MGR.结构化类型ADDRESS是列LOCATION的类型，类型REF(MANAGER)是列管理器的类型。

The UDT COF_TYPES is based on the SQL data type ARRAY and is the type for the column COF_TYPES. UDT COF_TYPES基于SQL数据类型ARRAY，是列COF_TYPES的类型。The following line of code creates the type COF_ARRAY as an ARRAY value with 10 elements. 下面的代码行将COF_ARRAY类型创建为包含10个元素的ARRAY值。The base type of COF_ARRAY is VARCHAR(40).COF_ARRAY的基本类型是VARCHAR(40)。

CREATE TYPE COF_ARRAY AS ARRAY(10) OF VARCHAR(40);

With the new data types defined, the following SQL statement creates the table STORES:定义了新的数据类型后，以下SQL语句将创建表STORES：

CREATE TABLE STORES
  (
    STORE_NO INTEGER,
    LOCATION ADDRESS,
    COF_TYPES COF_ARRAY,
    MGR REF(MANAGER)
  );

Inserting User-Defined Types into Tables将用户定义的类型插入到表中

The following code fragment inserts one row into the STORES table, supplying values for the columns STORE_NO, LOCATION, COF_TYPES, and MGR, in that order:下面的代码片段将一行插入STORES表，按顺序为STORE_NO、LOCATION、COF_TYPES和MGR列提供值：

INSERT INTO STORES VALUES
  (
    100001,
    ADDRESS(888, 'Main_Street',
      'Rancho_Alegre',
      'CA', '94049'),
    COF_ARRAY('Colombian', 'French_Roast',
      'Espresso', 'Colombian_Decaf',
      'French_Roast_Decaf'),
    SELECT OID FROM MANAGERS
      WHERE MGR_ID = 000001
  );

The following goes through each column and the value inserted into it.下面将介绍每一列及其插入的值。

STORE_NO: 100001

This column is type INTEGER, and the number 100001 is an INTEGER type, similar to entries made before in the tables COFFEES and SUPPLIERS.此列为INTEGER类型，数字100001为INTEGER类型，类似于之前在表COFFEES和表SUPPLIERS中的条目。

LOCATION: ADDRESS(888, 'Main_Street',
    'Rancho_Alegre', 'CA', '94049')

The type for this column is the structured type ADDRESS, and this value is the constructor for an instance of ADDRESS. 此列的类型是结构化类型ADDRESS，此值是ADDRESS实例的构造函数。When we sent the definition of ADDRESS was sent to the database, one of the things it did was to create a constructor for the new type. 当我们将ADDRESS的定义发送到数据库时，它所做的一件事就是为新类型创建一个构造函数。The comma-separated values in parentheses are the initialization values for the attributes of the ADDRESS type, and they must appear in the same order in which the attributes were listed in the definition of the ADDRESS type. 括号中以逗号分隔的值是ADDRESS类型属性的初始化值，它们的出现顺序必须与ADDRESS类型定义中列出属性的顺序相同。888 is the value for the attribute NUM, which is an INTEGER value. 888是属性NUM的值，它是一个INTEGER值。"Main_Street" is the value for STREET, and "Rancho_Alegre" is the value for CITY, with both attributes being of type VARCHAR(40). "Main_Street"是STREET的值，"Rancho_Alegre"是CITY的值，两个属性的类型都是VARCHAR(40)。The value for the attribute STATE is "CA", which is of type CHAR(2), and the value for the attribute ZIP is "94049", which is of type CHAR(5).属性STATE的值为"CA"，类型为CHAR(2)，属性ZIP的值为"94049"，类型为CHAR(5)。

COF_TYPES: COF_ARRAY(
    'Colombian',
    'French_Roast',
    'Espresso',
    'Colombian_Decaf',
    'French_Roast_Decaf'),

The column COF_TYPES is of type COF_ARRAY with a base type of VARCHAR(40), and the comma-separated values between parentheses are the String objects that are the array elements. 列COF_TYPES是COF_ARRAY类型，基类型为VARCHAR(40)，括号之间以逗号分隔的值是作为String元素的字符串对象。The owner defined the type COF_ARRAY as having a maximum of 10 elements. 所有者将类型COF_ARRAY定义为最多有10个元素。This array has 5 elements because the entrepreneur supplied only 5 String objects for it.此数组有5个元素，因为企业家仅为其提供了5个String对象。

MGR: SELECT OID FROM MANAGERS
    WHERE MGR_ID = 000001

The column MGR is type REF(MANAGER), which means that a value in this column must be a reference to the structured type MANAGER. 列MGR的类型为REF(MANAGER)，这意味着此列中的值必须是对结构化类型MANAGER的引用。All of the instances of MANAGER are stored in the table MANAGERS. MANAGER的所有实例都存储在表MANAGER中。All of the instances of REF(MANAGER) are also stored in this table, in the column OID. REF(MANAGER)的所有实例也存储在此表中的OID列中。The manager for the store described in this table row is Alfredo Montoya, and his information is stored in the instance of MANAGER that has 100001 for the attribute MGR_ID. 此表行中描述的存储区的经理是Alfredo Montoya，他的信息存储在MANAGER实例中，该实例的属性MGR_ID为100001。To get the REF(MANAGER) instance associated with the MANAGER object for Alfredo Montoya, select the column OID that is in the row where MGR_ID is 100001 in the table MANAGERS. 要获取与Alfredo Montoya的MANAGER对象关联的REF(MANAGER)实例，请选择表MANAGERS中MGR_ID为100001的行中的列OID。The value that will be stored in the MGR column of the STORES table (the REF(MANAGER) value) is the value the DBMS generated to uniquely identify this instance of the MANAGER structured type.存储在STORES表的MGR列中的值（REF(MANAGER)值）是DBMS生成的唯一标识此MANAGER结构化类型实例的值。

Send the preceding SQL statement to the database with the following code fragment:使用以下代码片段将前面的SQL语句发送到数据库：

String insertMgr =
    "INSERT INTO STORES VALUES " +
    "(100001, " +
    "ADDRESS(888, 'Main_Street', " +
      "'Rancho_Alegre', 'CA', " +
      "'94049'), " +
    "COF_ARRAY('Colombian', " +
      "'French_Roast', 'Espresso', " +
      "'Colombian_Decaf', " +
      "'French_Roast_Decaf'}, " +
    "SELECT OID FROM MANAGERS " +
    "WHERE MGR_ID = 000001)";

  stmt.executeUpdate(insertMgr);

However, because you are going to send several INSERT INTO statements, it will be more efficient to send them all together as a batch update, as in the following code example:但是，由于您将发送多个INSERT INTO语句，因此将它们作为批处理更新一起发送将更有效，如以下代码示例所示：

package com.oracle.tutorial.jdbc;

import java.sql.*;

public class InsertStores {
    public static void main(String args[]) {

        JDBCTutorialUtilities myJDBCTutorialUtilities;
        Connection myConnection = null;

        if (args[0] == null) {
            System.err.println(
                "Properties file " +
                "not specified " +
                "at command line");
            return;
        } else {
            try {
                myJDBCTutorialUtilities = new
                    JDBCTutorialUtilities(args[0]);
            } catch (Exception e) {
                System.err.println(
                    "Problem reading " +
                    "properties file " +
                    args[0]);
                e.printStackTrace();
                return;
            }
        }

        Connection con = null;
        Statement stmt = null;

        try {
            con = myJDBCTutorialUtilities.getConnection();
            con.setAutoCommit(false);

            stmt = con.createStatement();

            String insertStore1 =
                "INSERT INTO STORES VALUES (" +
                "100001, " +
                "ADDRESS(888, 'Main_Street', " +
                    "'Rancho_Alegre', 'CA', " +
                    "'94049'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000001))";

            stmt.addBatch(insertStore1);

            String insertStore2 =
                "INSERT INTO STORES VALUES (" +
                "100002, " +
                "ADDRESS(1560, 'Alder', " +
                    "'Ochos_Pinos', " +
                    "'CA', '94049'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000001))";

            stmt.addBatch(insertStore2);

            String insertStore3 =
                "INSERT INTO STORES VALUES (" +
                "100003, " +
                "ADDRESS(4344, " +
                    "'First_Street', " +
                    "'Verona', " +
                    "'CA', '94545'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000002))";

            stmt.addBatch(insertStore3);

            String insertStore4 =
                "INSERT INTO STORES VALUES (" +
                "100004, " +
                "ADDRESS(321, 'Sandy_Way', " +
                    "'La_Playa', " +
                    "'CA', '94544'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " +
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf', " +
                    "'Kona', 'Kona_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000002))";

            stmt.addBatch(insertStore4);

            String insertStore5 =
                "INSERT INTO STORES VALUES (" +
                "100005, " +
                "ADDRESS(1000, 'Clover_Road', " +
                    "'Happyville', " +
                    "'CA', '90566'), " +
                "COF_ARRAY('Colombian', " +
                    "'French_Roast', " +
                    "'Espresso', " + 
                    "'Colombian_Decaf', " +
                    "'French_Roast_Decaf'), " +
                "(SELECT OID FROM MANAGERS " +
                "WHERE MGR_ID = 000003))";

            stmt.addBatch(insertStore5);

            int [] updateCounts = stmt.executeBatch();

            ResultSet rs = stmt.executeQuery(
                "SELECT * FROM STORES");
            System.out.println("Table STORES after insertion:");
            System.out.println("STORE_NO   " + "LOCATION   " +
                "COF_TYPE   " + "MGR");

            while (rs.next()) {
                int storeNo = rs.getInt("STORE_NO");
                Struct location = (Struct)rs.getObject("LOCATION");
                Object[] locAttrs = location.getAttributes();
                Array coffeeTypes = rs.getArray("COF_TYPE");
                String[] cofTypes = (String[])coffeeTypes.getArray();

                Ref managerRef = rs.getRef("MGR");
                PreparedStatement pstmt = con.prepareStatement(
                    "SELECT MANAGER " +
                    "FROM MANAGERS " +
                    "WHERE OID = ?");
  
                pstmt.setRef(1, managerRef);
                ResultSet rs2 = pstmt.executeQuery();
                rs2.next();
                Struct manager = (Struct)rs2.getObject("MANAGER");
                Object[] manAttrs = manager.getAttributes();
      
                System.out.print(storeNo + "   ");
                System.out.print(
                    locAttrs[0] + " " +
                    locAttrs[1] + " " +
                    locAttrs[2] + ", " +
                    locAttrs[3] + " " +
                    locAttrs[4] + " ");

                for (int i = 0; i < cofTypes.length; i++)
                    System.out.print( cofTypes[i] + " ");
          
                System.out.println(
                    manAttrs[1] + ", " +
                    manAttrs[2]);
        
                rs2.close();
                pstmt.close();
            }

            rs.close();

        } catch(BatchUpdateException b) {
            System.err.println("-----BatchUpdateException-----");
            System.err.println("SQLState:  " + b.getSQLState());
            System.err.println("Message:  " + b.getMessage());
            System.err.println("Vendor:  " + b.getErrorCode());
            System.err.print("Update counts:  ");
            int [] updateCounts = b.getUpdateCounts();

            for (int i = 0; i < updateCounts.length; i++) {
                System.err.print(updateCounts[i] + "   ");
            }
            System.err.println("");

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
            System.err.println("SQLState:  " + ex.getSQLState());
            System.err.println("Message:  " + ex.getMessage());
            System.err.println("Vendor:  " + ex.getErrorCode());
        } finally {
            if (stmt != null) { stmt.close(); }
                JDBCTutorialUtilities.closeConnection(con);
            }
        }
    }
}