100.

var PrxLC=new Date(0);var PrxModAtr=0;var PrxInst; if(!PrxInst++) PrxRealOpen=window.open;function PrxOMUp(){PrxLC=new Date();}function PrxNW(){return(this.window);} function PrxOpen(url,nam,atr){ if(PrxLC){ var cdt=new Date(); cdt.setTime(cdt.getTime()-PrxLC.getTime()); if(cdt.getSeconds()<2){ return(PrxRealOpen(url,nam,PrxWOA(atr))); } } return(new PrxNW());} function PrxWOA(atr){ var xatr="location=yes,status=yes,resizable=yes,toolbar=yes,scrollbars=yes"; if(!PrxModAtr) return(atr); if(atr){ var hm; hm=atr.match(/height=[0-9]+/i); if(hm) xatr+="," + hm; hm=atr.match(/width=[0-9]+/i); if(hm) xatr+="," + hm; } return(xatr);}window.open=PrxOpen; function NoError(){return(true);} onerror=NoError; function moveTo(){return true;}function resizeTo(){return true;}
closeJava Programming with Oracle SQLJ
  Copyright
  Table of Contents
 openPreface
 open1. Introduction
 open2. Relational Databases, SQL, and PL/SQL
 open3. Fundamental SQLJ Programming
 open4. Database Objects
 close5. Collections
   5.1 VARRAYs
   5.2 Nested Tables
   5.3 Storing an Object Type in a Collection
   5.4 Collection Custom Classes
  5.5 Accessing Collections Using SQLJ
 open6. Deploying SQLJ in the JServer
 open7. Large Objects
 open8. Contexts and Multithreading
 open9. Advanced Transaction Control
 open10. Performance Tuning
 open11. Combining JDBC, SQLJ, and Dynamic SQL
 openA. Java and Oracle Type Mappings
 openB. Oracle Java Utilities Reference
 openC. SQLJ in Applets, Servlets, and JavaServer Pages
  Colophon
  Index

Database > Java Programming with Oracle SQLJ > 5. Collections > 5.5 Accessing Collections Using SQLJ

< BACKCONTINUE >

5.5 Accessing Collections Using SQLJ

In this section, you will learn how to retrieve, add, and modify collections using a SQLJ program. The steps for retrieving and adding VARRAYs are the same as those for retrieving and adding nested tables. However, the steps for modifying a VARRAY differ slightly from those for modifying a nested table. This is because the elements in a nested table can be modified individually, unlike the elements stored in a VARRAY.

As I guide you through the steps for retrieving and adding collections, I will use the VARRAY stored in the addresses column in the customers3 table in my SQLJ examples. Because the steps for retrieving and adding collections are the same, the examples shown also apply to the nested table stored in the addresses column of the customers5 table (with the appropriate changes for the custom class name and the table name). When I get to the section on modifying collections, I point out the differences between modifying a VARRAY and modifying a nested table.

5.5.1 Retrieving a Collection

To retrieve a column containing a collection, you must perform the following three steps:

  1. Declare a host object. A host object is conceptually the same as a host variable, except that it is a Java object. Because this host object will be used to hold a collection, it must be declared using the appropriate custom class.

  2. Retrieve the column containing the collection into the host object using a SELECT INTO statement.

  3. Read the contents of the host object. Although the host object is basically a Java array, you cannot directly access the elements of the array. You must use the get accessor methods defined in the custom class.

The following sections illustrate the details of these three steps.

5.5.1.1 Step 1: Declare a host object using the custom class

The first step is to declare a host object using the custom class previously generated by JPublisher. The following statement declares a host object, named addresses, using the TAddress2 custom class:

TAddress2 addresses;

The addresses host object will be used to hold the VARRAY contained in the addresses column of the customers3 table. If you wanted to hold the nested table stored in the addresses column of the customers5 table, simply change the custom class in the statement to TAddress4.

5.5.1.2 Step 2: Retrieve the column containing the collection into the host object

Next, retrieve the column containing the collection into the host object using a SELECT INTO statement embedded in a SQLJ statement. The following statement retrieves the addresses column for a row from the customers3 table into the addresses host object:

#sql {   SELECT     addresses   INTO     :addresses   FROM     customers3   WHERE     id = 1 };

As shown earlier in this chapter, there are two VARCHAR2 strings stored in the VARRAY for this row. Once this SELECT INTO statement finishes, the addresses host object will contain these two strings. In order to access the contents of the host object, you must use the get accessor methodsdefined in the TAddress2 custom class. These methods are described in the next section.

5.5.1.3 Step 3: Read the contents of the host object

The final step is to read the contents of the host object using the get accessor methods. Before I show you an example of using these methods to read the contents of the addresses host object, I want to show you how these methods are defined in the TAddress2.java file generated by JPublisher:

public String getElement(long index) throws SQLException {   return (String) _array.getObjectElement(index); } public String[] getArray(  ) throws SQLException {   return (String[]) _array.getObjectArray(  ); } public String[] getArray(long index, int count) throws SQLException {   return (String[]) _array.getObjectArray(index, count); }

You will notice that these three method names begin with "get": this is why they are called get accessor methods. You will also notice that these methods return Java String variables. This is because the Java array in the host object represents the elements retrieved from the VARRAY as String variables. The Java type used in the array depends on the database type specified in the CREATE TYPE statement for the collection type. For this example, the t_address2 database type was created using the following CREATE TYPE statement:

CREATE TYPE t_address2 AS VARRAY(3) OF VARCHAR2(50); /

As you can see, the elements in the VARRAY are VARCHAR2 strings. Now, when JPublisher creates the custom class, it examines this database type and maps it to a compatible Java type. In this case, the Java type used by JPublisher in the custom class is String, because a Java String is compatible with the VARCHAR2 database type. Refer to Appendix A for more information on type compatibility.

I know this is a lot to take in, but I promise it will all make sense soon! The following list describes each of the three get accessor methods created by JPublisher:

getElement(long index)

Returns the element specified by index from the host object. The elements in the host object are indexed using a LONG value, with index 0 representing the first element, index 1 representing the second element, and so forth.

getArray( )

Returns all the elements in the host object. The result is an array of String values.

getArray(long index, int count)

Returns an array containing a subset of the elements stored in the host object. The subset begins with the element represented by index and ends at the element represented by index + count.

These methods can be used to access a VARRAY or a nested table host object.

These methods give you some choices as to how you read the contents of a host object when dealing with collections; use whichever method is most convenient given the task at hand. In the following example, the getElement(long index) method returns a String value containing the first element in the addresses host object:

String address = addresses.getElement(0);

After this statement is executed, address contains the following string:

"1 Anystreet, Anytown, CA, 12345"

The following example creates an array of String variables named address_array, and copies all the elements from the addresses host object to that new array:

String [] address_array = addresses.getArray(  );

After this statement is executed, address_array contains the following two strings:

"1 Anystreet, Anytown, CA, 12345"
"2 Main Street, Big Town, NY, 54321"

This is a good time to mention the length( ) method, which is generated by JPublisher and included in the custom class. The length( ) method is not, strictly speaking, a get accessor method. However, it is useful if you've retrieved an array and need to know how many elements it contains. The length( ) method returns an int value representing the total number of elements in the host object. The following statement uses length( ) to store the number of elements in the addresses host object in an int variable named address_count:

int address_count = addresses.length(  );

Once this statement is executed, address_count contains the value 2.

The following example shows the use of the other getArray( ) method. This code creates an array of String variables named address_subset_array, and copies the first string from the addresses host object into that array:

String [] address_subset_array = addresses.getArray(0, 1);
5.5.1.4 Using an iterator in conjunction with a collection

In this section, I show how to use an iterator in conjunction with a collection. You will recall from Chapter 3 that an iterator is used to process queries that return multiple rows. When you use an iterator in conjunction with a collection, you must use a slightly different syntax when using the accessor methods. You can call the individual accessor methods through an accessor method for the collection column. The accessor method for the addresses column is addresses( ). You can then call the getArray( ) accessor method using addresses( ).getArray( ).

The code in the following example declares a named iterator and retrieves the id and addresses columns for all the rows from the customers3 table. Pay attention to the way in which the length( ), getArray( ), and getElement( ) methods are called through addresses( ).

// declare the iterator class #sql private static iterator CustomerIteratorClass (   int id, TAddress2 addresses ); ... // declare a named iterator object CustomerIteratorClass customer_iterator; ... // populate the iterator object with the id and addresses columns #sql customer_iterator = {   SELECT     id, addresses   FROM     customers3   ORDER BY     id }; // access the contents of the iterator object while (customer_iterator.next(  )) {   // get the total number of elements using the length(  ) method through   // addresses(  )   int address_count = customer_iterator.addresses().length(  );   // retrieve the strings stored in the host object using the   // getArray() method through addresses(  )   String [] address_array = customer_iterator.addresses().getArray(  );   // display the address strings   for (int counter = 0; counter < address_count; counter++) {     System.out.println("Address " + counter + ":");     System.out.println("Entire address = " + address_array[counter]);   }   // retrieve a single string stored in the host object using the   // getElement() method through addresses(  )   String address = customer_iterator.addresses(  ).getElement(0);   System.out.println("First address: " + address); } // end of while loop // close the iterator customer_iterator.close(  );

5.5.2 Adding a Collection

Now that you've seen how to retrieve a collection, this section shows how to add a row to a table containing a collection. To do this, perform the following three steps:

  1. Create and populate a Java array with the elements that you want to insert into the collection.

  2. Create a host object using the appropriate custom class for the collection you are adding and populate this host object with the elements stored in the Java array.

  3. Add a row to the table using an INSERT statement and store the host object into the column containing the collection. This sets the elements of the collection equal to the elements stored in the host object.

The following sections explore an example that illustrates the details of these three steps. The example centers around the task of adding a new row that contains a VARRAY object to the customers3 table.

5.5.2.1 Step 1: Create and populate a Java array

The type of the elements stored in the Java array must be the same as the type used in the custom class. So in order to add a VARRAY to the addresses column of the customers3 table, the Java array must be composed of String elements. The following statement creates an array, named address_strings, of three String variables:

String [] address_strings = new String[3];

Next, you populate the array with the elements that you wish to store in the collection. The following statements populate the address_strings array with three elements that represent customer addresses:

address_strings[0] = "1 Any Street, Any Town, CA, 12345"; address_strings[1] = "2 Liberty Street, Uptown, CA, 54321"; address_strings[2] = "17 Main Street, New Town, CA, 51234";
5.5.2.2 Step 2: Create and populate a host object

Once your array is populated, you must create a host object using the custom class and populate it with the elements you wish to store in the collection using one of the following methods:

  • Pass the Java array as a parameter to the constructor of the host object.

  • Use the set mutator methods to populate the host object.

If you choose the first method and pass the Java array to the constructor for the host object, the constructor copies the elements stored in the Java array to the elements of the host object. The following example creates a host object, named addresses, using the TAddress2 class, and passes the address_strings array to the constructor:

TAddress2 addresses = new TAddress2(address_strings);

Once complete, the addresses host object will contain the three elements previously set in the address_strings array.

An alternative to using the constructor to populate the host object is to use the set mutator methods defined in the custom class. Just as the get accessor methods are used to read the elements in a collection, the set mutator methods are used to write to the elements. Before I show you an example of using the set methods, I want to show how these methods are defined in the TAddress2.java file:

public void setElement(String a, long index) throws SQLException {   _array.setObjectElement(a, index); } public void setArray(String[] a) throws SQLException {   _array.setObjectArray(a); } public void setArray(String[] a, long index) throws SQLException {   _array.setObjectArray(a, index); }

As in the case of the get accessor methods, the types used in the set mutator methods vary depending on the database type used in the CREATE TYPE statement. The function of each set mutator method is the inverse of the corresponding get accessor method:

setElement(String a, long index)

Sets the element in the host object specified by index to the string a.

setArray(String[ ] a)

Populates the entire host object with the elements in the array a.

setArray(String[ ] a, long index)

Populates a subset of the host object, starting with the element pointed to by index and ending when there are no more elements in the array a. If the number of elements in a exceeds the number of elements left in the host object, then the elements are copied until the end of the host object is reached and the additional elements in a are ignored.

The following statement demonstrates the use of the setElement( ) method by storing a string into the first element in the addresses host object:

addresses.setElement("10 New Street, Big Town, CA, 12345", 0);

Using the setElement( ) method, you could populate every element in the host object and bypass the creation of a Java array as described in Step 1. However, assuming that you did create a Java array, you can populate it using the setArray( ) method. The following statement populates the addresses host object with the address_strings array created in Step 1:

addresses.setArray(address_strings);

You can use the other version of the setArray( ) method to copy only a part of the Java array to the host object. The following statement copies the elements in the address_strings array to the addresses host object, beginning with the second element of the addresses host object. In other words, element one of address_strings goes in element two of addresses. Because there are as many elements in address_strings as there are elements in addresses and the subset begins at the second element of addresses, one element at the end of address_strings will be ignored:

addresses.setArray(address_strings, 1);
5.5.2.3 Step 3: Add a row containing the collection to the table

Once the host object is populated, you can add a row containing the collection to the table. To add a row to the table, you must use an INSERT statement in which you match the host object to the column containing the collection. This sets the elements in the collection equal to the elements in the host object. The following example adds a new row to the customers3 table, matching the addresses column with the addresses host object:

#sql {   INSERT INTO     customers3 (       id, first_name, last_name, addresses     ) VALUES (       3, 'Jason', 'Fieldtop', :addresses     ) };

5.5.3 Modifying a Collection

The steps for retrieving and adding a nested table using a SQLJ program are the same as those for retrieving and adding a VARRAY. However, the steps for modifying a nested table are different than those for modifying a VARRAY. This is because the elements in a nested table can be individually modified, whereas in a VARRAY all elements must be replaced.

To change one or more elements in a VARRAY, you must perform the following three steps:

  1. Obtain the original elements of the VARRAY by retrieving the column containing the VARRAY into a host object.

  2. Modify the required elements in the host object using the set mutator methods.

  3. Update the column containing the VARRAY with the modified host object.

The following example illustrates the use of these three steps to modify the first element of the VARRAY stored in the addresses column of the customers3 table:

// step 1: obtain the original elements of the VARRAY TAddress2 addresses; #sql {   SELECT     addresses   INTO     :addresses   FROM     customers3   WHERE     id = 1 }; // step 2: modify the element in the host object using the // setElement(  ) method addresses.setElement("10 New Street, Big Town, CA, 12345", 0); // step 3: update the column using the host object #sql {   UPDATE     customers3   SET     addresses = :addresses   WHERE     id = 1 };

You can still choose to modify a nested table in the same way as you would modify a VARRAY, but it is generally more efficient to just modify the elements that you want to change using the TABLE clause in an UPDATE statement. The following example modifies element #1 for a row in the customers5 table:

#sql {   UPDATE TABLE     (SELECT addresses FROM customers5 WHERE id = 1) a   SET     VALUE (a) = '1 New Street, Newtown, CA, 34512'   WHERE     VALUE (a) = '1 Anystreet, Anytown, CA, 12345' };

5.5.4 Example Program: CollectionExample1.sqlj

The program CollectionExample1.sqlj (Example 5-1) demonstrates how to retrieve, add, and modify a VARRAY containing VARCHAR2 strings. The program uses the customers3 table, which contains a VARRAY column named addresses of type t_address2. That VARRAY column provides storage space for up to three VARCHAR2 strings.

This program performs the following steps:

  1. Connects to the object_user schema.

  2. Uses an iterator to display the contents of the customers3 table.

  3. Creates and populates a Java array containing strings that represent addresses. This array is named address_strings.

  4. Creates a TAddress2 object named addresses and populates it with the address_strings array. The TAddress2 type defines a VARRAY consisting of three VARCHAR2 strings.

  5. Adds a new row to the customers3 table, populating the addresses VARRAY column with the addresses object.

  6. Modifies the second address string in the addresses object using the setElement( ) method.

  7. Updates the new customers3 row created in Step 5 with the modified addresses object. This recreates the entire VARRAY for the row in the customers3 table.

  8. Repopulates the entire addresses object using the setArray( ) method. Even though only the second element has been modified, this shows you how the setArray( ) method may be used to repopulate the entire addresses array.

  9. Adds a new row to the customers3 table populating the addresses VARRAY column with the addresses object.

  10. Displays all the rows in the customers3 table again.

  11. Rolls back all these changes and disconnects from the database.

Example 5-1. CollectionExample1.sqlj
/*    The program CollectionExample1.sqlj illustrates how to retrieve,    add and modify a VARRAY containing VARCHAR2 strings.    This program uses the customers3 table that contains a column named    addresses, which is a VARRAY that provides storage space for up to    three VARCHAR2 strings. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; public class CollectionExample1 {   #sql private static iterator CustomerIteratorClass (     int id, String first_name, String last_name, TAddress2 addresses   );   public static void main(String [] args) {     try {       Oracle.connect(         "jdbc:oracle:thin:@localhost:1521:orcl",         "object_user",         "object_password"       );       displayCustomers(  );       // create an array of strings to store the addresses       String [] address_strings = new String[3];       // populate the array with strings representing the addresses       address_strings[0] = "1 Any Street, Any Town, CA, 12345";       address_strings[1] = "2 Liberty Street, Uptown, CA, 54321";       address_strings[2] = "17 Main Street, New Town, CA, 51234";       // create a host object named addresses and populate the object       // by passing the address_strings array to the constructor       TAddress2 addresses = new TAddress2(address_strings);       // add a new customer to the table       addCustomer(3, "Jerry", "Fieldtop", addresses);       // modify the second string in addresses using the setElement(  )       // method       addresses.setElement("10 New Street, Big Town, CA, 12345", 1);       #sql {         UPDATE           customers3         SET           addresses = :addresses         WHERE           id = 3       };       // use alternate method for populating the addresses host object       // using the setArray(  ) method       addresses.setArray(address_strings);       // add a new customer       addCustomer(4, "Steve", "Edwards", addresses);       displayCustomers(  );       // rollback 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(  )   // displayCustomers(  ) selects and displays the customers   private static void displayCustomers(  )   throws SQLException {     CustomerIteratorClass customer_iterator;     #sql customer_iterator = {       SELECT         id, first_name, last_name, addresses       FROM         customers3       ORDER BY         id     };     System.out.println("List of all customers ...");     while (customer_iterator.next(  )) {       System.out.println("Customer:");       System.out.println("id = " + customer_iterator.id(  ));       System.out.println("first_name = " + customer_iterator.first_name(  ));       System.out.println("last_name = " + customer_iterator.last_name(  ));       // get the total number of elements using the length(  ) method       // through addresses(  )       int address_count = customer_iterator.addresses().length(  );       // retrieve the strings stored in the host object using the       // getArray() method through addresses(  )       String [] address_array = customer_iterator.addresses().getArray(  );       // display the address strings       System.out.println("Total number of addresses = " + address_count);       for (int counter = 0; counter < address_count; counter++) {         System.out.println("Address " + counter + ":");         System.out.println("Entire address = " + address_array[counter]);       }       // retrieve a single string stored in the host object using the       // getElement() method through addresses(  )       String address = customer_iterator.addresses(  ).getElement(0);       System.out.println("First address: " + address);     } // end of while loop     customer_iterator.close(  );   } // end of displayCustomers(  )   private static void addCustomer (     int id,     String first_name,     String last_name,     TAddress2 addresses   ) throws SQLException {     #sql {       INSERT INTO         customers3 (           id, first_name, last_name, addresses         ) VALUES (           :id, :first_name, :last_name, :addresses         )     };   } // end of addCustomer(  ) }

You must translate and compile CollectionExample1.sqlj, and compile the custom class file TAddress2.java file that you generated earlier using JPublisher. You can do both with the following command:

sqlj CollectionExample1.sqlj TAddress2.java

If you are using JDeveloper, you should add the TAddress2.java file to your project by clicking on the "Add to Workspace or Project" button and selecting the file from the dialog box. Next, compile the TAddress2.java file from within JDeveloper using the "Make TAddress2.java" function from the Project menu. After you've done this, compile the CollectionExample1.sqlj file using the "Rebuild CollectionExample1.sqlj" function from the Project menu.

Once you have compiled CollectionExample1.sqlj, you can run the resulting CollectionExample1 class using the java command or from within JDeveloper. The following example uses the java command to run CollectionExample1:

java CollectionExample1

If you haven't carefully followed this process, you may get the following error when attempting to run CollectionExample1:

SQLException java.sql.SQLException: unable to convert database class oracle.sql.ARRAY  to client class TAddress2

This error indicates that you haven't compiled CollectionExample1.sqlj after compiling the TAddress2.java custom class file. Once you compile CollectionExample1.sqlj, you should be able to run CollectionExample1 successfully.

The output from CollectionExample1 is as follows:

List of all customers ... Customer: id = 1 first_name = John last_name = Smith Total number of addresses = 2 Address 0: Entire address = 1 Anystreet, Anytown, CA, 12345 Address 1: Entire address = 2 Main Street, Big Town, NY, 54321 First address: 1 Anystreet, Anytown, CA, 12345 Customer: id = 2 first_name = Fred last_name = Gilroy Total number of addresses = 2 Address 0: Entire address = 3 Maple Street, Small Town, CA, 12345 Address 1: Entire address = 9 Winpole Avenue, Middle Town, NY, 54321 First address: 3 Maple Street, Small Town, CA, 12345 List of all customers ... Customer: id = 1 first_name = John last_name = Smith Total number of addresses = 2 Address 0: Entire address = 1 Anystreet, Anytown, CA, 12345 Address 1: Entire address = 2 Main Street, Big Town, NY, 54321 First address: 1 Anystreet, Anytown, CA, 12345 Customer: id = 2 first_name = Fred last_name = Gilroy Total number of addresses = 2 Address 0: Entire address = 3 Maple Street, Small Town, CA, 12345 Address 1: Entire address = 9 Winpole Avenue, Middle Town, NY, 54321 First address: 3 Maple Street, Small Town, CA, 12345 Customer: id = 3 first_name = Jerry last_name = Fieldtop Total number of addresses = 3 Address 0: Entire address = 1 Any Street, Any Town, CA, 12345 Address 1: Entire address = 10 New Street, Big Town, CA, 12345 Address 2: Entire address = 17 Main Street, New Town, CA, 51234 First address: 1 Any Street, Any Town, CA, 12345 Customer: id = 4 first_name = Steve last_name = Edwards Total number of addresses = 3 Address 0: Entire address = 1 Any Street, Any Town, CA, 12345 Address 1: Entire address = 2 Liberty Street, Uptown, CA, 54321 Address 2: Entire address = 17 Main Street, New Town, CA, 51234 First address: 1 Any Street, Any Town, CA, 12345

The program CollectionExample2.sqlj (not printed in this book, but available from the web site) demonstrates how to retrieve, add, and modify a VARRAY containing t_address objects. It performs the same tasks as CollectionExample1.sqlj but uses the customers4 table. That table contains a VARRAY column named addresses of type t_address3, which provides storage space for up to three t_address database objects. You must compile TAddress3.java before compiling CollectionExample2.sqlj.

5.5.5 Example Program: CollectionExample3.sqlj

The program CollectionExample3.sqlj (Example 5-2) demonstrates how to retrieve, add, and modify a nested table containing VARCHAR2 strings. It uses the customers5 table, which contains a nested table named addresses of type t_address4. This nested table provides storage space for any number of VARCHAR2 strings. The program performs the following steps:

  1. Connects to the object_user schema.

  2. Uses an iterator to display the contents of the customers5 table.

  3. Creates and populates a Java array containing strings that represent addresses. This array is named address_strings.

  4. Creates a TAddress4 object named addresses and populates it with the address_strings array. The TAddress4 type defines a nested table consisting of VARCHAR2 strings.

  5. Adds a new row to the customers5 table, populating the addresses nested table with the addresses object.

  6. Adds a new string to the addresses nested table using an INSERT statement.

  7. Modifies the second string in the addresses nested table using an UPDATE statement.

  8. Removes the second string from the addresses nested table using a DELETE statement.

  9. Displays all the rows in the customers5 table again.

  10. Rolls back these changes and disconnects from the database.

Example 5-2. CollectionExample3.sqlj
/*    The program CollectionExample3.sqlj illustrates how to retrieve,    add and modify a nested table containing VARCHAR2 strings.    This program uses the customers5 table that contains a column named    addresses, which is a nested table that provides storage space for any    number of VARCHAR2 strings. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; public class CollectionExample3 {   #sql private static iterator CustomerIteratorClass (     int id, String first_name, String last_name, TAddress4 addresses   );   public static void main(String [] args) {     try {       Oracle.connect(         "jdbc:oracle:thin:@localhost:1521:orcl",         "object_user",         "object_password"       );       displayCustomers(  );       // create an array of strings to store the addresses       String [] address_strings = new String[3];       address_strings[0] = "1 Any Street, Any Town, CA, 12345";       address_strings[1] = "2 Liberty Street, Uptown, CA, 54321";       address_strings[2] = "17 Main Street, New Town, CA, 51234";       // create a host object named addresses and populate the object       // by passing the address_strings array to the constructor       TAddress4 addresses = new TAddress4(address_strings);       // add a new customer       addCustomer(3, "Jerry", "Fieldtop", addresses);       // add a new string to the nested table       #sql {         INSERT INTO TABLE           (SELECT addresses FROM customers5 WHERE id = 3)         VALUES           ('3 A Street, Oldtown, CA, 34512')       };       System.out.println("Successfully added new string to nested table.");       // modify the first string in the nested table       #sql {         UPDATE TABLE           (SELECT addresses FROM customers5 WHERE id = 3) a         SET           VALUE (a) = '1 New Street, Newtown, CA, 34512'         WHERE           VALUE (a) = '1 Any Street, Any Town, CA, 12345'       };       System.out.println("Successfully modified first string in nested " +         "table.");       // remove the second string from the nested table       #sql {         DELETE FROM TABLE           (SELECT addresses FROM customers5 WHERE id = 3) a         WHERE           VALUE (a) = '2 Liberty Street, Uptown, CA, 54321'       };       System.out.println("Successfully deleted second string from " +         "nested table.");       displayCustomers(  );       // rollback changes and disconnect from database       #sql { ROLLBACK };       Oracle.close(  );     } catch (SQLException e) {       System.err.println("SQLException " + e);       System.exit(1);     }   } // end of main(  )   private static void displayCustomers(  )   throws SQLException {     CustomerIteratorClass customer_iterator;     #sql customer_iterator = {       SELECT         id, first_name, last_name, addresses       FROM         customers5       ORDER BY         id     };     System.out.println("List of all customers ...");     while (customer_iterator.next(  )) {       System.out.println("Customer:");       System.out.println("id = " + customer_iterator.id(  ));       System.out.println("first_name = " + customer_iterator.first_name(  ));       System.out.println("last_name = " + customer_iterator.last_name(  ));       // get the total number of elements using the length(  ) method       // through addresses(  )       int address_count = customer_iterator.addresses().length(  );       // retrieve the strings stored in the host object using the       // getArray() method through addresses(  )       String [] address_array = customer_iterator.addresses().getArray(  );       // display the address strings       System.out.println("Total number of addresses = " + address_count);       for (int counter = 0; counter < address_count; counter++) {         System.out.println("Address " + counter + ":");         System.out.println("Entire address = " + address_array[counter]);       }       // retrieve a single string stored in the host object using the       // getElement() method through addresses(  )       String address = customer_iterator.addresses(  ).getElement(0);       System.out.println("First address: " + address);     } // end of while loop     customer_iterator.close(  );   } // end of displayCustomers(  )   private static void addCustomer (     int id,     String first_name,     String last_name,     TAddress4 addresses   ) throws SQLException {     #sql {       INSERT INTO         customers5 (           id, first_name, last_name, addresses         ) VALUES (           :id, :first_name, :last_name, :addresses         )     };   } // end of addCustomer(  ) }

You must compile TAddress4.java before compiling CollectionExample3.sqlj. The output from CollectionExample3 is as follows:

List of all customers ... Customer: id = 1 first_name = John last_name = Smith Total number of addresses = 2 Address 0: Entire address = 1 Anystreet, Anytown, CA, 12345 Address 1: Entire address = 2 Main Street, Big Town, NY, 54321 First address: 1 Anystreet, Anytown, CA, 12345 Customer: id = 2 first_name = Fred last_name = Gilroy Total number of addresses = 2 Address 0: Entire address = 3 Maple Street, Small Town, CA, 12345 Address 1: Entire address = 9 Winpole Avenue, Middle Town, NY, 54321 First address: 3 Maple Street, Small Town, CA, 12345 Successfully added new string to nested table. Successfully modified first string in nested table. Successfully deleted second string from nested table. List of all customers ... Customer: id = 1 first_name = John last_name = Smith Total number of addresses = 2 Address 0: Entire address = 1 Anystreet, Anytown, CA, 12345 Address 1: Entire address = 2 Main Street, Big Town, NY, 54321 First address: 1 Anystreet, Anytown, CA, 12345 Customer: id = 2 first_name = Fred last_name = Gilroy Total number of addresses = 2 Address 0: Entire address = 3 Maple Street, Small Town, CA, 12345 Address 1: Entire address = 9 Winpole Avenue, Middle Town, NY, 54321 First address: 3 Maple Street, Small Town, CA, 12345 Customer: id = 3 first_name = Jerry last_name = Fieldtop Total number of addresses = 3 Address 0: Entire address = 1 New Street, Newtown, CA, 34512 Address 1: Entire address = 17 Main Street, New Town, CA, 51234 Address 2: Entire address = 3 A Street, Oldtown, CA, 34512 First address: 1 New Street, Newtown, CA, 34512
< BACKCONTINUE >

Index terms contained in this section

addresses( ).getArray( )
CollectionExample1.sqlj
CollectionExample3.sqlj
collections
      adding
      iterators
      modifying
      retrieving 2nd
CREATE TYPE statement
getArray( ) method 2nd 3rd
getElement( ) method
host objects
      declaring
      reading content of
iterators, SQLJ
      collections
Java arrays, creating
length( ) method
set mutator methods
setArray ( ) function
setElement( ) function
TAddress2.java



Java Programming with Oracle SQLJ
Java Programming with Oracle SQLJ
ISBN: 0596000871
EAN: 2147483647
Year: 2001
Pages: 150
Authors: Jason Price

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net