itGalary : Java (J2EE & J2SE)
Author: JijoSubject: Oracle OBJECT s Type and Java
Posted: 31 Jan 08 at 5:20pm
Creating an OBJECT Type in an Oracle Database
In Oracle, you can define a composite data structure called an OBJECT, which consists of one or more basic types. For example, you could define an object called book with a title (VARCHAR) and an price (NUMBER). An OBJECT can also contain other OBJECTs.
This example creates two OBJECT types. object2 contains two fields --- a string and a number while object1 also contains two fields --- a string and a value of type object2.
The example also creates a table to hold object1 values. See Inserting an OBJECT Value into an Oracle Table.
try {
// Create a statement
Statement stmt = connection.createStatement();
// Create the object2 type
stmt.execute("CREATE TYPE object2 AS OBJECT"
+ "(col_string2 VARCHAR(30), col_integer2 NUMBER)");
// Create the object1 type
stmt.execute("CREATE TYPE object1 AS OBJECT"
+ "(col_string1 VARCHAR(30), col_integer2 object2)");
// Create a table with a column to hold a number and the new object1 type
stmt.execute("CREATE TABLE object1_table(col_integer NUMBER, col_object1 object1)");
} catch (SQLException e) {
}
|
Inserting an OBJECT Value into an Oracle Table
This example inserts a row into a table with a column that contains an OBJECT type. The example uses the table and types created in the above example.
try {
// Create a statement
Statement stmt = connection.createStatement();
// Insert a row with values for both the object1 and object2 types
stmt.execute("INSERT INTO object1_table VALUES(1, object1('str1', object2('obj2str1', 123)))");
} catch (SQLException e) {
}
|
Inserting an OBJECT Value into an Oracle Table Using a Prepared Statement
This example inserts Oracle OBJECTs into an Oracle table using a prepared statement. The example uses the OBJECT types and table created in the first example.
try {
// Create an oracle.sql.STRUCT object to hold the values for object2
Object[] object2Values = new Object[]{"str", new BigDecimal(123)};
oracle.sql.StructDescriptor structDesc =
oracle.sql.StructDescriptor.createDescriptor("OBJECT2", connection);
oracle.sql.STRUCT object2 =
new oracle.sql.STRUCT(structDesc, connection, object2Values);
// Create an oracle.sql.STRUCT object to hold the values for object1
Object[] object1Values = new Object[]{"str", object2};
structDesc = oracle.sql.StructDescriptor.createDescriptor("OBJECT1", connection);
oracle.sql.STRUCT object1 =
new oracle.sql.STRUCT(structDesc, connection, object1Values);
// Create a prepared statement for insertion into object1_table
PreparedStatement ps =
connection.prepareStatement("INSERT INTO object1_table VALUES(?,?)");
// Set the values to insert
ps.setInt(1, 123);
ps.setObject(2, object1);
// Insert the new row
ps.execute();
} catch (SQLException e) {
}
|
Getting an OBJECT Value from an Oracle Table
This example retrieves values contained in an Oracle OBJECT type. The example uses the table and types created in the above examples.
try {
// Create a statement
Statement stmt = connection.createStatement();
// Select rows from object1_table
ResultSet resultSet = stmt.executeQuery("SELECT * FROM object1_table");
// Get the OBJECT values from each row
while (resultSet.next()) {
// Get the integer from the first column col_integer of the row
int i = resultSet.getInt(1);
// Get the object1 value from the second column col_object1
oracle.sql.STRUCT object1 = (oracle.sql.STRUCT)resultSet.getObject(2);
// Get the object1 values from each row
Object[] object1Values = object1.getAttributes();
// Get the first value of object1, which is a string
String str = (String)object1Values[0];
// Get the second value of object1, which is of the type object2
oracle.sql.STRUCT object2 = (oracle.sql.STRUCT)object1Values[1];
// Get the values of object2
Object object2Values[] = object2.getAttributes();
str = (String)object2Values[0];
BigDecimal num = (BigDecimal)object2Values[1];
}
} catch (SQLException e) {
}
|
Deleting an OBJECT Type from an Oracle Table
This example deletes the OBJECTs and tables created in above example.
try {
// Create a statement
Statement stmt = connection.createStatement();
// Drop table object1_table and types object1 and object2
stmt.execute("DROP TABLE object1_table");
stmt.execute("DROP TYPE object1 FORCE");
stmt.execute("DROP TYPE object2 FORCE");
} catch (SQLException e) {
// A drop statement will throw an exception if the table or type does not exist
}
|
