4.5 Object Identifiers and REF Columns
Every row in an object table is assigned a unique object identifier (OID), which is a hexadecimal number generated by Oracle. An object reference, represented using an Oracle REF type, can store an OID and is used as a pointer to a row in an object table. An object reference is similar to a pointer in the C programming language and is the object table version of a foreign key.
In this section, you will learn how to retrieve the OID for row objects, how to define a column in a table using the REF type, how to populate and modify a REF column, and how to dereference a REF column. Then, I describe REF custom class files and how to access REF columns using SQLJ. Finally, this section contains a complete SQLJ program that illustrates how to access REF columns.
To demonstrate the use of OIDs and REF columns, I guide you through creating and populating an object table, named products, that will be used to store product details. I show you how to retrieve the OID for a row in the products table, and how to create a table, named purchases, that will store OIDs from the products and customers2 tables. This purchases table will be used to track a customer's purchases.
You don't need to type any of the DDL or DML statements shown in this section: they are contained in the object_user_schema.sql script, which you or your DBA should have run already.
4.5.1 Create and Populate the Products Table
First, you need an object type and table to hold product details. The following statements create an object type named t_product and an object table named products:
CREATE TYPE t_product AS OBJECT ( id NUMBER, name VARCHAR2(50), description VARCHAR2(500), price NUMBER(10, 2) ); / CREATE TABLE products OF t_product;
With this table in place, you can add information about products as follows:
INSERT INTO products (id, name, description, price) VALUES (1, 'Beyond Understanding', 'The frontiers of human knowledge', 19.95);
Every row in an object table contains a unique OID, and you can retrieve this OID using the REF operator in a SELECT statement. The SELECT statement in the following example retrieves the OID for the row added to the products table in the previous example. Notice the use of the REF operator to obtain the OID.
SQL> SELECT REF(p) 2 FROM products p 3 WHERE c.id = 1; REF(P) -------------------------------------------------------------------------------------- 000028020997B2C958868A11D490FB0010A4E7AE8A97B2C953868A11D490FB0010A4E7AE8A00407D120000
4.5.2 Create and Populate the Purchases Table
You can define a column in a table as a REF to a particular object type, and populate such a column with a REF to a row. The column then acts as a pointer to a row object. In this way, a REF column is the object table equivalent of a foreign key relationship. The following example creates a table, named purchases, that will be used to record customer purchases. Notice that the purchased_by and product columns are each defined using a REF to the appropriate object type:
CREATE TABLE purchases ( id NUMBER CONSTRAINT purchases_pk PRIMARY KEY, purchased_by REF t_customer SCOPE IS customers2, product REF t_product SCOPE IS products, quantity NUMBER );
The SCOPE IS clause restricts a REF to point to a row in a specific object table. Recall from the previous sections that the customers2 table is an object table containing t_customer objects. So in the example, a value stored in the purchased_by column is a pointer to a t_customer row object stored in the customers2 table, while a value stored in the product column is a pointer to a t_product row object stored in the products table.
To add a row to the purchases table, you must use an INSERT statement containing the OIDs for row objects from the customers2 and products tables. You can obtain these OIDs with nested queries that use the REF operator. For example, if you wanted to record that customer #1 purchased one unit of product #1, then your INSERT statement could be as follows:
INSERT INTO purchases ( id, purchased_by, product, quantity ) VALUES ( 3, (SELECT REF(c) FROM customers2 c WHERE c.id = 1), (SELECT REF(p) FROM products p WHERE p.id = 1), 1 );
Notice that the nested queries obtain the OIDs for the row objects from the customers2 and products tables, and that these are stored in the purchased_by and product REF columns of the purchases table. After the row is added to the purchases table, the purchased_by column contains a REF that points to customer #1 in the customers2 table, and the product column contains a REF that points to product #1 in the products table.
Using an UPDATE statement, you can modify the row pointed to by a REF contained in a column. The following example modifies the row previously added to the purchases table so that the purchased_by REF points to customer #2 of the customers2 table instead of to customer #1. Notice that the nested query obtains the new REF.
UPDATE purchases SET purchased_by = (SELECT REF(c) FROM customers2 c WHERE c.id =2) WHERE id = 1;
4.5.3 Dereferencing REF Columns
Using the DEREF operator, you can retrieve the row pointed to by a REF contained in a column. The process for doing so is known as dereferencing the REF column. For example, to dereference the values in the purchased_by and product columns, you would use the following SELECT statement:
SELECT DEREF(purchased_by), DEREF(product) FROM purchases;
The results of this SELECT statement would consist of two objects: a t_customer object and a t_product object.
4.5.4 REF Custom Class Files
To access the REF columns stored in the purchases table from SQLJ, you need a Java custom class for the t_product object type (the custom class files for the t_customer object type were generated earlier in the chapter). In this section, I show how to create the custom class files for the t_product object type, and finally explain what the TCustomerRef.java class file previously generated by JPublisher is all about. To get JPublisher to generate class files for the t_product object type, run the following command:
jpub -user=object_user/object_password -sql=t_product:TProduct
JPublisher then generates two files:
-
TProduct.sqlj
-
TProductRef.java
The first file defines a class named TProduct that allows you to declare a host object in which you can store t_product database objects. The second file defines a class named TProductRef that you can use to create an object in which to store a reference to a t_product row object. Similarly, the TCustomerRef.java file created earlier in this chapter allows you to declare a host object in which to store a reference to a t_customer row object.
Now, to access a REF column using SQLJ, you must use the getValue( ) and setValue( ) methods contained in the custom class file. The getValue( ) and setValue( ) methods defined in the TProductRef.java file are as follows:
public TProduct getValue( ) throws SQLException public void setValue(TProduct c) throws SQLException
The getValue( ) method returns the actual TProduct object pointed to by the REF column, and is equivalent to the DEREF operator. The setValue( ) method accepts a TProduct object and is used to copy the host object to the database.
4.5.5 Accessing REF Columns Using SQLJ
In this section, I show how to add, modify, and retrieve rows containing REF columns using SQLJ. I also show how to use the getValue( ) and setValue( ) methods. The examples in this section add, modify, and retrieve rows from the purchases table.
4.5.5.1 Adding a row containing REF columns
For the purpose of explaining how to add a row containing REF values to the database, I'll add a row to the purchases table containing a reference to customer #1 from the customers2 table and product #1 from the products table. (Those tables have already been populated with the necessary rows for this example by the object_user_schema.sql script.) To do this, there are three steps that must be performed:
-
Declare two host objects using the TCustomerRef and TProductRef classes.
-
Retrieve the references for customer #1 and product #1 into the respective host objects using a SELECT INTO statement.
-
Add the row to the purchases table using an INSERT statement. Set the purchased_by and product columns of that row equal to the respective host objects.
First, you need to declare two host objects using the TCustomerRef and TProductRef classes. In addition, you need two host variables for the id and quantity columns that are also part of the purchases table. The following Java statements declare these two host objects and create the variables:
TCustomerRef customer_ref; TProductRef product_ref; int id = 3; int quantity = 1;
Next, retrieve the REF values from the database. The following examples retrieve references to customer #1 from the customers2 table and to product #1 from the products table. These references are retrieved into the customer_ref and product_ref host objects, respectively.
#sql { SELECT REF(c) INTO :customer_ref FROM customers2 c WHERE c.id = 1 }; #sql { SELECT REF(p) INTO :product_ref FROM products p WHERE p.id = 1 }; Finally, having retrieved the necessary references from the database, you can add the new row to the purchases table. The following SQLJ statement does this. Notice the use of the customer_ref and product_ref host objects to set the purchased_by and product columns:
#sql { INSERT INTO purchases ( id, purchased_by, product, quantity ) VALUES ( :id, :customer_ref, :product_ref, :quantity ) }; After executing this statement, you will have a row in the purchases table that points to a customer object and to a product object, but does not duplicate those objects.
4.5.5.2 Dereferencing REF columns
This section describes how to retrieve REF columns, and how to use the DEREF operator to dereference the REF columns to retrieve the actual row objects to which these columns point. There are two ways to dereference row objects using SQLJ:
-
Use the DEREF operator to retrieve the object to which a REF column points into a host object of the REF custom class.
-
Retrieve the REF column into a host object, and then use the getValue( ) method to retrieve the actual object that is referenced.
The following examples show how to retrieve the purchased_by and product columns using both these methods. For the first method, you need to declare two host objects in which to store the row objects from the customers2 and products tables:
TCustomer customer; TProduct product;
You can then use the DEREF operator to retrieve the row objects pointed to by the purchased_by and product columns into the customer and product host objects, respectively:
#sql { SELECT DEREF(purchased_by), DEREF(product) INTO :customer, :product FROM purchases WHERE id = :id }; The DEREF operator retrieves the objects pointed to by the two REF columns. Once you get the objects into host variables, you can use the usual get accessor methods to read their attributes. In this case, the host objects are customer and product, so you would use those objects' get methods.
For the second method, you retrieve each REF column into a host object of the REF custom class. You then use those REF objects' getValue( ) methods to retrieve the actual objects in questions. This method involves the following three steps:
-
Declare host objects using the REF custom class.
-
Select the REF columns into the host objects using a SELECT INTO statement or an iterator.
-
Use the getValue( ) method to access the row objects pointed to by the REFs.
To begin, you must declare two host objects in which to store the REF columns that you retrieve from the database:
TCustomerRef customer_ref; TProductRef product_ref;
Next, you retrieve the REF values from the database and place them into the two host objects that you declared. In the following example, the purchased_by and product columns are retrieved into the customer_ref and product_ref host objects:
#sql { SELECT purchased_by, product INTO :customer_ref, :product_ref FROM purchases WHERE id = :id }; Once you have retrieved the REF values into the appropriate host objects, you can use the getValue( ) method to obtain the actual row object pointed to by each REF. For example, the following statement uses the getValue( ) method to obtain the row object from the customers2 table:
customer = customer_ref.getValue( );
Now that you have the object itself in a host object, you can use the get accessor methods to obtain the object's attribute values. For example, the following statement uses the getFirstName( ) method to display the first_name attribute for the customer host object:
System.out.println("First name = " + customer.getFirstName( )); 4.5.5.3 Using the setValue( ) method to modify a REF column
Before showing you a complete example program, I want to show how the setValue( ) method may be used to directly modify a row in the database. The setValue( ) method copies the attributes from a host object in your Java program to a database object. The examples in this section show how to modify the first_name attribute of customer #1 in the customers2 table. Five steps are required to perform this change:
-
Declare two host objects: one, named customer, to store a customer object, and another, named customer_ref, to store a reference to the customer object.
-
Retrieve a reference to customer #1 into the customer_ref host object.
-
Use the customer_ref host object's getValue( ) method to populate the customer host object.
-
Modify the first_name attribute of the customer host object using the setFirstName( ) method.
-
Pass the customer host object into a call to the customer_ref host object's setValue( ) method. This modifies the actual row in the customers2 table that is referenced by customer_ref.
The following statements perform these five steps:
// step 1: declare the two host objects TCustomer customer; TCustomerRef customer_ref; // step 2: retrieve customer #1 from customers2 into customer #sql { SELECT REF(c) INTO :customer_ref FROM customers2 c WHERE c.id = 1 }; // step 3: use the getValue( ) method to populate the customer host object customer = customer_ref.getValue( ); // step 4: Modify the first_name attribute of the customer host object customer.setFirstName("Jason"); // step 5: Pass the customer host object into a call to the // setValue( ) method using customer_ref customer_ref.setValue(customer); All these steps take place within the context of a transaction. If you then perform a commit using SQLJ, the change is permanently recorded in the table with no explicit UPDATE statement involved; the setValue( ) method implicitly performs a database UPDATE. I know that these five steps seem overly complex just to modify a single column, but if your SQLJ program was structured such that you had the customer reference in memory already, you would only need to perform Steps 4 and 5 to modify the row. This is a neat feature that you might want to use in your programs.
4.5.6 Example Program: ObjectExample2.sqlj
This section contains a complete program that illustrates how to add, retrieve, and modify REF columns using the customers2, products, and purchases tables. The program ObjectExample2.sqlj (Example 4-2) performs the following steps:
-
Declares two host objects, named customer_ref and product_ref, using the TCustomerRef and TProductRef custom classes.
-
Retrieves REFs for customer #1 and product #1 into the customer_ref and product_ref host objects.
-
Adds a row to the purchases table using the customer_ref and product_ref host objects.
-
Declares two host objects, named customer and product, using the TCustomer and TProduct custom classes.
-
Retrieves the purchased_by and product columns from the purchases table into the customer and product host objects, using the DEREF operator.
-
Displays the product name and customer first name.
-
Uses the customer_ref and product_ref objects' getValue( ) methods to obtain the actual customer and product row objects to which the REFs point.
-
Uses the setValue( ) method to modify the first_name attribute of the customer_ref host object.
Example 4-2. ObjectExample2.sqlj
/* The program ObjectExample2.sqlj illustrates how to add and retrieve REF columns. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; public class ObjectExample2 { public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "object_user", "object_password" ); // declare the host objects and create the variables TCustomerRef customer_ref; TProductRef product_ref; int id = 3; int quantity = 1; // retrieve the customer REF into the customer_ref host object #sql { SELECT REF(c) INTO :customer_ref FROM customers2 c WHERE c.id = 1 }; // retrieve the product REF into the product_ref host object #sql { SELECT REF(p) INTO :product_ref FROM products p WHERE p.id = 1 }; // add row to the purchases table using the host objects #sql { INSERT INTO purchases ( id, purchased_by, product, quantity ) VALUES ( :id, :customer_ref, :product_ref, :quantity ) }; // declare the customer and product host objects TCustomer customer; TProduct product; // retrieve the purchased_by and product columns into // the customer and product host objects using the DEREF // operator #sql { SELECT DEREF(purchased_by), DEREF(product) INTO :customer, :product FROM purchases WHERE id = :id }; System.out.println(product.getName( ) + " was purchased by " + customer.getFirstName( )); // retrieve the purchased_by and product columns into the // customer_ref and product_ref host objects #sql { SELECT purchased_by, product INTO :customer_ref, :product_ref FROM purchases WHERE id = :id }; // use the getValue( ) method to obtain the customer and // product objects customer = customer_ref.getValue( ); product = product_ref.getValue( ); System.out.println(product.getName( ) + " was purchased by " + customer.getFirstName( )); // use the setFirstName() and setValue( ) methods to modify // the customer object customer.setFirstName("Jason"); customer_ref.setValue(customer); System.out.println("First name is now " + customer.getFirstName( )); // undo the changes and disconnect from the database #sql { ROLLBACK }; Oracle.close( ); } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) } You should compile TProduct.sqlj and TProductRef.java before compiling ObjectExample2.sqlj, using the technique described earlier in Section 4.3.4. If you get a SQL exception when attempting to run the program, it means you haven't compiled the custom classes before compiling ObjectExample2.sqlj.
The output from the program ObjectExample2.sqlj is as follows:
Beyond Understanding was purchased by John Beyond Understanding was purchased by John First name is now Jason
