使用结构化对象
注意 :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 类中的字段。属性NUM
是INTEGER
,属性STREET
是VARCHAR(40)
,属性CITY
是VARCHAR(40)
,属性STATE
是CHAR(2)
,属性ZIP
是CHAR(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 的所有者创建了三种新的数据类型,用作数据库中的列类型或属性类型:结构化类型LOCATION
和MANAGER
,以及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_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
);
以下内容遍历每列,并将值插入其中。
STORE_NO: 100001
此列的类型为INTEGER
,数字100001
为INTEGER
类型,类似于之前在表COFFEES
和SUPPLIERS
中进行的 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)
实例,请选择表MANAGERS
中MGR_ID
为100001
的行中的列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);
}
}
}
}