使用结构化对象

注意 :MySQL 和 Java DB 当前不支持用户定义的类型。因此,没有 JDBC 教程示例可用来演示本节中描述的功能。

涵盖以下主题:

结构化类型概述

SQL 结构化类型和DISTINCT类型是用户可以在 SQL 中定义的两种数据类型。它们通常被称为 UDT(用户定义类型),您可以使用 SQL CREATE TYPE语句创建它们。

回到“喝咖啡 Rest 时间”的示例,假设所有者已经取得了超出所有预期的成功,并且正在扩展新的分支机构。所有者决定将STORES表添加到数据库中,其中包含有关每个机构的信息。 STORES将包含四列:

  • STORE_NO用于每个 Store 的标识号

  • LOCATION作为其地址

  • COF_TYPES出售的咖啡

  • MGR代表 StoreManager 的姓名

所有者将列LOCATION设为 SQL 结构化类型,将列COF_TYPES设为 SQL ARRAY,并将列MGR a _ REF(MANAGER)变为MANAGER是 SQL 结构化的类型。

所有者必须首先为地址和 管理 者定义新的结构化类型。 SQL 结构化类型与 Java 编程语言中的结构化类型相似,因为它具有称为* attributes *的成员,该成员可以是任何数据类型。所有者编写以下 SQL 语句以创建新的数据类型ADDRESS

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

在此语句中,新类型ADDRESS具有五个属性,类似于 Java 类中的字段。属性NUMINTEGER,属性STREETVARCHAR(40),属性CITYVARCHAR(40),属性STATECHAR(2),属性ZIPCHAR(5)

以下摘录(其中con是有效的Connection对象)将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);

现在,ADDRESS结构化类型已在数据库中注册为数据类型,所有者可以将其用作表列或结构化类型的属性的数据类型。

在结构化类型中使用 DISTINCT 类型

Manager 的电话 Numbers 是 The Coffee Break 计划的所有者要包含在新结构化类型MANAGER中的属性之一。因为所有者总是将电话 Numbers 列出为 10 位数字(以确保它包含区号),并且永远不会将其作为数字来操作,所以所有者决定定义一个新的PHONE_NO类型,该类型由 10 个字符组成。可以将这种数据类型的 SQL 定义视为只有一个属性的结构化类型,如下所示:

CREATE TYPE PHONE_NO AS CHAR(10);

或者,如前所述,对于某些驱动程序,定义可能如下所示:

CREATE DISTINCT TYPE PHONE_NO AS CHAR(10);

DISTINCT类型始终基于另一种数据类型,该数据类型必须是 预定义的类型。换句话说,DISTINCT类型不能基于用户定义类型(UDT)。要检索或设置DISTINCT类型的值,请对基础类型(基础类型)使用适当的方法。例如,要检索基于CHAR类型的PHONE_NO实例,可以使用getString方法,因为这是用于检索CHAR的方法。

假设类型PHONE_NO的值位于ResultSet对象rs的当前行的第四列中,则以下代码行将其检索:

String phoneNumber = rs.getString(4);

类似地,下面的代码行设置一个 Importing 参数,其类型为PHONE_NO,用于将准备好的语句发送到数据库:

pstmt.setString(1, phoneNumber);

加上上一个代码片段,PHONE_NO的定义将通过以下代码行发送到数据库:

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

在数据库中注册类型PHONE_NO后,所有者可以将其用作表中的列类型或结构化类型的属性的数据类型。以下 SQL 语句中的MANAGER的定义使用PHONE_NO作为属性PHONE的数据类型:

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

重复使用先前定义的stmt,以下代码片段将结构化类型MANAGER的定义发送到数据库:

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

使用对结构化类型的引用

The Coffee Break 的所有者创建了三种新的数据类型,用作数据库中的列类型或属性类型:结构化类型LOCATIONMANAGER,以及DISTINCT类型PHONE_NO。企业家已将PHONE_NO作为新类型MANAGER中的属性PHONE的类型,并将ADDRESS作为表STORES中的列LOCATION的数据类型。 MANAGER类型可以用作MGR列的类型,但企业家更喜欢使用REF(MANAGER)类型,因为企业家通常只有一个人 管理 两个或三个 Store。使用REF(MANAGER)作为列类型可以避免在一个人 管理 多个 Store 时重复MANAGER的所有数据。

使用已创建的结构化类型MANAGER,所有者现在可以创建一个表,其中包含可以引用的MANAGER实例。对MANAGER实例的引用将具有REF(MANAGER)类型。 SQL REF仅仅是指向结构化类型的逻辑指针,因此REF(MANAGER)的实例用作指向MANAGER的实例的逻辑指针。

因为 SQL REF值需要与其引用的结构化类型的实例永久关联,所以它与其关联的实例一起存储在特殊表中。程序员不会直接创建REF类型,而是会创建一个表,该表将存储可以引用的特定结构化类型的实例。每个要引用的结构化类型都有自己的表。当您将结构类型的实例插入表中时,数据库会自动创建一个REF实例。例如,为了包含MANAGER可以引用的实例,所有者使用 SQL 创建了以下特殊表:

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

该语句使用特殊列OID创建一个表,该表存储类型REF(MANAGER)的值。每次将MANAGER的实例插入表中时,数据库将生成REF(MANAGER)的实例并将其存储在OID列中。隐式地,另外一列还存储了已插入表中的MANAGER的每个属性。例如,以下代码片段显示了企业家如何创建三个MANAGER结构化类型的实例来表示三个 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'
   );

现在,表MANAGERS将具有三行,至此为止插入的每个 Manager 一行。列OID将包含三个类型为REF(MANAGER)的唯一对象标识符,每个对象标识符MANAGER.实例一个。这些对象标识符由数据库自动生成,并将永久存储在表MANAGERS中。隐式地,附加列存储MANAGER的每个属性。例如,在表MANAGERS中,一行包含引用 Alfredo Montoya 的REF(MANAGER),另一行包含引用 Margaret Haskins 的REF(MANAGER),第三行包含引用 Helen Chen 的REF(MANAGER)

要访问REF(MANAGER)实例,请从其表中选择它。例如,所有者使用以下代码片段检索了对 ID 为 000001 的 Alfredo Montoya 的引用:

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

现在,变量manager可用作引用 Alfredo Montoya 的列值。

用于创建 SQL REF 对象的示例代码

下面的代码示例创建表MANAGERS,可引用的结构化类型MANAGER的实例的表,并将MANAGER的三个实例插入到表中。该表中的OID列将存储REF(MANAGER)的实例。执行此代码后,MANAGERS表将为插入的三个MANAGER对象的每一行都有一行,并且OID列中的值将是REF(MANAGER)类型,用于标识存储在该行中的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);
        }
    }
}

使用用户定义的类型作为列值

现在,我们的企业家拥有创建表STORES所需的 UDT。结构化类型ADDRESS是列LOCATION的类型,类型REF(MANAGER)是列MGR的类型。

UDT COF_TYPES基于 SQL 数据类型ARRAY,并且是列COF_TYPES的类型。下面的代码行将类型COF_ARRAY创建为具有 10 个元素的ARRAY值。 COF_ARRAY的基本类型是VARCHAR(40)

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

使用定义的新数据类型,以下 SQL 语句创建表STORES

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

将用户定义的类型插入表中

下面的代码片段在STORES表中插入一行,并按 Sequences 为STORE_NOLOCATIONCOF_TYPESMGR列提供值:

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
  );

以下内容遍历每列,并将值插入其中。

STORE_NO: 100001

此列的类型为INTEGER,数字100001INTEGER类型,类似于之前在表COFFEESSUPPLIERS中进行的 Importing。

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

该列的类型是结构化类型ADDRESS,而该值是ADDRESS实例的构造函数。当我们将ADDRESS的定义发送到数据库时,它所做的一件事就是为新类型创建一个构造函数。括号中的逗号分隔值是ADDRESS类型的属性的初始化值,并且它们必须以与ADDRESS类型的定义中列出的属性相同的 Sequences 出现。 888是属性NUM的值,它是INTEGER的值。 "Main_Street"STREET的值,"Rancho_Alegre"CITY的值,两个属性的类型均为VARCHAR(40)。属性STATE的值为"CA"(类型为CHAR(2)),属性ZIP的值为"94049"(类型为CHAR(5))。

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

COF_TYPES的类型为COF_ARRAY,基本类型为VARCHAR(40),并且括号之间的逗号分隔值是作为数组元素的String对象。所有者将类型COF_ARRAY定义为最多包含 10 个元素。该数组包含 5 个元素,因为企业家仅为其提供了 5 个String个对象。

MGR: SELECT OID FROM MANAGERS
    WHERE MGR_ID = 000001

MGR列是REF(MANAGER)类型,这意味着该列中的值必须是对结构化类型MANAGER的引用。 MANAGER的所有实例都存储在表MANAGERS中。 REF(MANAGER)的所有实例也存储在此表的OID列中。在此表行中描述的 Store 的 Manager 是 Alfredo Montoya,他的信息存储在MANAGER实例中,该实例的属性MGR_ID具有100001。要获取与 Alfredo Montoya 的MANAGER对象关联的REF(MANAGER)实例,请选择表MANAGERSMGR_ID100001的行中的列OID。将存储在STORES表的MGR列中的值(REF(MANAGER)值)是 DBMS 生成的用于唯一标识MANAGER结构化类型的实例的值。

使用以下代码片段将前面的 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);

但是,由于要发送多个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);
            }
        }
    }
}