4.4 Accessing Database Objects Using SQLJ
Now that you've learned about database objects and the custom Java classes, I'll show you how to add, retrieve, and modify column and row objects using SQLJ. The examples in this section use the customers and customers2 tables. At the end of this section is a complete program illustrating the use of the SQLJ statements in the examples.
4.4.1 Adding Objects
There are two types of database objects: column objects and row objects. First, I'll discuss adding column objects to the customers table using SQLJ statements. Then, I'll discuss adding row objects to the customers2 table, also using SQLJ statements.
4.4.1.1 Adding a column object
You can add a column object to a table using one of two methods:
The following example uses the first method (using the object type's constructor) to add a customer column object to the customers table:
#sql { INSERT INTO customers VALUES ( t_customer(3, 'Jerry', 'Fieldtop', '01-JAN-1965', '650-555-1222', t_address('1 Any Street', 'Any Town', 'CA', '12345') ) ) }; The second method (using host variables) is a bit more involved. To begin with, you need to create a host object. The following statement creates a host object named customer, using the TCustomer custom class:
TCustomer customer = new TCustomer( );
Next, you set the object attributes in the host object using the set mutator methods. The following statements set the attributes for the customer host object:
customer.setId(new BigDecimal(3)); customer.setFirstName("Jerry"); customer.setLastName("Fieldtop"); customer.setDob(new Timestamp(65, 0, 1, 0, 0, 0, 0)); customer.setPhone("12345"); To set the address attribute of the customer object, you must create a host object using the TAddress custom class and then set that object's attributes using the set mutator methods:
TAddress address = new TAddress( ); address.setStreet("1 Any Street"); address.setCity("Any Town"); address.setState("CA"); address.setZip("12345"); customer.setAddress(address); Finally, use your host object in an INSERT statement. In the following example, the customer object is used in an INSERT statement to add the object to the customers table:
#sql { INSERT INTO customers (customer) VALUES ( :customer ) }; 4.4.1.2 Adding a row object
Adding a row object may be performed by setting the object attributes directly in an INSERT statement, just as you would set the columns for a non-object table. The following example adds a row to the customers2 table:
#sql { INSERT INTO customers2 ( id, first_name, last_name, dob, phone, address ) VALUES ( 3, 'Jerry', 'Fieldtop', '01-JAN-1965', '650-555-1222', t_address('1 Any Street', 'Any Town', 'CA', '12345') ) }; The address attribute must still be set using a t_address constructor because it is an embedded object.
Another way to add a row is to use the t_customer constructor; for example:
#sql { INSERT INTO customers2 VALUES ( t_customer(4, 'Nicola', 'Fremont', '04-JUL-1958', '650-555-1223', t_address('4 Market Street', 'Middle Town', 'CA', '12345') ) ) }; You can also add a row object using a host object. In this case, the column list is omitted. The following example adds a row to the customers2 table using the customer host object created earlier:
#sql { INSERT INTO customers2 VALUES ( :customer ) }; 4.4.2 Retrieving Objects
Retrieval of a column or row object from a table may be accomplished using the following steps:
-
Declare a host object using the custom class for the object type that you wish to retrieve.
-
Using a SELECT INTO statement or an iterator, retrieve the column or row object into the host object.
-
Read the host object's attributes using the get accessor methods.
I'll illustrate these steps with examples that retrieve a t_customer column object from the customers table, and a t_customer row object from the customers2 table.
4.4.2.1 Declaring a host object
First, you need to declare a host object to store the column or row object to be retrieved. The following statement declares a host object named customer based on the TCustomer custom class:
TCustomer customer;
Once you have your host object, you can use a SELECT INTO statement to retrieve a column or row object into that host object. The syntax of that SELECT INTO statement depends on whether you are retrieving a column object or a row object.
4.4.2.2 Retrieving a column object
The SELECT INTO statement in the following example retrieves the customer column object for customer #1 from the customers table and places it into the customer host object:
#sql { SELECT c.customer INTO :customer FROM customers c WHERE c.customer.id = 1 }; Notice that this SQL statement resembles any other SQL statement in which you retrieve a column from the database in order to store it in a host variable. In this case, the column being retrieved happens to be an object column, and the "host variable" is really a host object.
4.4.2.3 Retrieving a row object
The following example shows a SELECT INTO statement being used to select the row object from the customers2 table into the customer host object. Notice the use of the VALUE operator, which treats the row as an object:
#sql { SELECT VALUE(c) INTO :customer FROM customers2 c WHERE id = 1 }; Recall that it's also possible to treat object attributes in an object table as separate columns. The following SELECT INTO statement does this, and retrieves attributes for customer #1 into a series of host variables:
#sql { SELECT id, first_name, last_name, dob, address INTO :id, :first_name, :last_name, :dob, :address FROM customers2 WHERE id = 1 }; 4.4.2.4 Referencing object attributes
Once you've retrieved an object, you can access its attributes using the get accessor methods. In the example shown earlier using the customer host object, the customer host object contains all the attributes retrieved from the database. The following example uses the customer object's get accessor methods to display the attributes stored in the customer host object:
System.out.println("id = " + customer.getId( )); System.out.println("first_name = " + customer.getFirstName( )); System.out.println("last_name = " + customer.getLastName( )); System.out.println("dob = " + customer.getDob( )); System.out.println("phone = " + customer.getPhone( )); System.out.println("street = " + customer.getAddress().getStreet( )); System.out.println("city = " + customer.getAddress().getCity( )); System.out.println("state = " + customer.getAddress().getState( )); System.out.println("zip = " + customer.getAddress().getZip( )); It is important to note that calling the get accessor methods does not make another call to the database: the attributes are all retrieved in one go by the SELECT statement.
4.4.2.5 Invoking object methods
To invoke an object method for a column object, you can invoke the method name in a SELECT INTO statement and store the result in a host variable. The following example invokes the get_age( ) object method for the t_customer column object in the customers table:
int customer_age = 0; #sql { SELECT c.customer.get_age( ) INTO :customer_age FROM customers c WHERE c.customer.id = 1 }; When this code is executed, the customer_age host variable will contain the age of the customer in years. The following statement displays the contents of this variable:
System.out.println("age = " + customer_age); You can also invoke object methods using a host object. This statement invokes the getAge( ) method in the customer host object and displays the result:
System.out.println("age = " + customer.getAge( )); 4.4.2.6 Using an iterator with column and row objects
When a query returns more than one row, you need to retrieve column and row objects using an iterator. The following statements declare a named iterator class that contains a TCustomer object, and also declare an iterator object, named cust_iter, that may be used to store TCustomer objects:
#sql private static iterator CustomerIteratorClass(TCustomer customer); CustomerIteratorClass cust_iter;
The cust_iter iterator can be used to retrieve column or row objects from the customers or customers2 tables. This example illustrates how to populate cust_iter with column objects from the customers table:
#sql cust_iter = { SELECT c.customer FROM customers c ORDER BY c.customer.id }; Similarly, the following example selects the row objects from customers2. There are two things to notice: the use of the VALUE operator, and the "customer" column alias required to make the column name match the object name in the named iterator:
#sql cust_iter = { SELECT VALUE(c) customer FROM customers2 c ORDER BY id }; After populating the iterator, you can read the customer attributes. Here, the getId( ) method is used to read the id attribute:
cust_iter.customer().getId( );
You can also read the customer attributes by declaring an object and populating it with the customer object stored in the iterator. Then you read the customer attributes from this object using the get methods. The loop in the following example does this. Each customer object returned by the iterator is retrieved into a host object named TCustomer. The TCustomer object's get methods are then invoked to return the details of the customer:
while (cust_iter.next( )) { TCustomer customer = cust_iter.customer( ); System.out.println("id = " + customer.getId( )); System.out.println("first_name = " + customer.getFirstName( )); System.out.println("last_name = " + customer.getLastName( )); System.out.println("dob = " + customer.getDob( )); System.out.println("phone = " + customer.getPhone( )); System.out.println("street = " + customer.getAddress().getStreet( )); System.out.println("city = " + customer.getAddress().getCity( )); System.out.println("state = " + customer.getAddress().getState( )); System.out.println("zip = " + customer.getAddress().getZip( )); System.out.println("age = " + customer.getAge( )); } 4.4.3 Modifying Object Attributes
You can modify the attributes of a column object or a row object using one of two possible methods:
-
Modify the attributes directly in an UPDATE statement.
-
Modify the attributes through a host object, and write this host object back into the database with an UPDATE statement.
The first method is more direct than the second, but the second method is easier to use when there are many attributes to be modified at the same time.
4.4.3.1 Modifying attributes directly using an UPDATE statement
If you are directly modifying attributes of a column object in an UPDATE statement, you must access that object via a table alias. The UPDATE statement in the following example changes the first_name attribute of a customer object in the customers table. Notice the use of the table alias c in the dot notation leading to the attribute name.
#sql { UPDATE customers c SET c.customer.first_name = :first_name WHERE c.customer.id = 1 }; If you are modifying a row object stored in an object table, you don't need to use a table alias in your UPDATE statement because you can treat the object attributes as regular table columns. The following statement performs the same operation as the previous one, but for the customers2 table. The customers2 table is an object table. Notice that the table alias is omitted from the statement.
#sql { UPDATE customers2 SET first_name = :first_name WHERE id = 1 }; In addition to modifying object attributes directly, you can modify object attributes through a host object, as discussed in the next section.
4.4.3.2 Modifying attributes through a host object
Host objects, declared using the custom classes discussed earlier in this chapter, allow you to work on local copies of database objects. You can even use host objects as the basis for permanently modifying database object attributes. When you do this, you need to follow these four steps:
-
Declare a host object using the custom class.
-
Retrieve the original database object into the host object.
-
Modify the attributes in the host object using the set mutator methods.
-
Use the host object in an UPDATE statement to modify the original database object.
Your first step is to declare a host object based on the appropriate custom class. The following statement declares a host object named customer, using the TCustomer custom class. This is in preparation for modifying objects in the customers and customers2 tables.
TCustomer customer;
Next, retrieve the original database column object or row object into your host object using either a SELECT INTO statement or an iterator. This example uses a SELECT INTO statement to retrieve a customer column object from the customers table:
#sql { SELECT c.customer INTO :customer FROM customers c WHERE c.customer.id = 4 }; Then, you modify the host object attributes using the set mutator methods. The following statement uses the setFirstName( ) method to modify the first_name attribute for the customer host object:
customer.setFirstName("Steve"); Once you have modified the required attributes, use the host object in an UPDATE statement to modify the original database object. The following SQLJ statement uses the customer host object to modify the customer column object:
#sql { UPDATE customers c SET c.customer = :customer WHERE c.customer.id = 4 }; You can also use a host object to modify a row object in an object table. For example, the following statement uses the customer host object to modify a customer row object in the customers2 table:
#sql { UPDATE customers2 c SET VALUE(c) = :customer WHERE id = 4 }; 4.4.4 Example Program: ObjectExample1.sqlj
You have now seen how to write SQLJ statements that retrieve, add, and modify database column and row objects. This section contains a complete program that uses such statements. The program ObjectExample1.sqlj (Example 4-1) performs the following steps:
-
Connects to the object_user schema.
-
Adds a column object to the customers table.
-
Adds a row object to the customers2 table.
-
Creates a host object using the TCustomer custom class, and initializes that object's attributes using the set mutator methods. A host object of the TAddress custom class is also created and initialized, and then used to set the address attribute of the previous TCustomer object. This TCustomer host object is then added to the customers and customers2 tables.
-
Updates the street address attribute for the object added to the customers and customers2 tables in the previous step. These updates are performed by using UPDATE statements to directly modify the attributes.
-
Updates the first_name attribute for the object previously added to the customers and customers2 tables. This time, the update is performed using a host object together with the setFirstName( ) method.
-
Using an iterator, retrieves and displays all the column objects stored in the customers table.
-
Using a SELECT INTO statement, retrieves and displays the row object for customer #4 from the customers2 table.
Example 4-1. ObjectExample1.sqlj
/* The program ObjectExample1.sqlj illustrates how to add, retrieve and modify column and row objects. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import java.math.*; public class ObjectExample1 { #sql private static iterator CustomerIteratorClass(TCustomer customer); public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "object_user", "object_password" ); int id = 3; String first_name = "Jerry"; String last_name = "Fieldtop"; Date dob = new Date(80, 11, 2); String phone = "650-555-1222"; String street = "1 Any Street"; String city = "Any Town"; String state = "CA"; String zip = "12345"; // add a column object to the customers table #sql { INSERT INTO customers (customer) VALUES ( t_customer(:id, :first_name, :last_name, :dob, :phone, t_address(:street, :city, :state, :zip) ) ) }; // add a row object to the customers2 table #sql { INSERT INTO customers2 ( id, first_name, last_name, dob, phone, address ) VALUES ( :id, :first_name, :last_name, :dob, :phone, t_address(:street, :city, :state, :zip) ) }; // create a TCustomer host object and use the set mutator // methods to initialize the attributes of the host object TCustomer customer = new TCustomer( ); id++; customer.setId(new BigDecimal(id)); customer.setFirstName(first_name); customer.setLastName(last_name); customer.setDob(new Timestamp(dob.getYear(), dob.getMonth( ), dob.getDay( ), 0, 0, 0, 0)); customer.setPhone(phone); // create a TAddress host object and use the set mutator // methods to initialize the attributes of the host object TAddress address = new TAddress( ); address.setStreet(street); address.setCity(city); address.setState(state); address.setZip(zip); // set the address attribute of the customer host object customer.setAddress(address); // add the customer object to the customers table #sql { INSERT INTO customers (customer) VALUES ( :customer ) }; // add the customer object to the customers2 table #sql { INSERT INTO customers2 VALUES ( :customer ) }; // update the street attribute in the customers table #sql { UPDATE customers c SET c.customer.address.street = '2 Main Street' WHERE c.customer.id = :id }; // update the street attribute in the customers2 table #sql { UPDATE customers2 c SET c.address.street = '2 Main Street' WHERE c.id = :id }; // update the first_name attribute in the customers table customer.setFirstName("Jason"); #sql { UPDATE customers c SET c.customer = :customer WHERE c.customer.id = :id }; // update the first_name attribute in the customers2 table #sql { UPDATE customers2 c SET VALUE(c) = :customer WHERE id = :id }; // use an iterator to retrieve all the objects in the customers table CustomerIteratorClass cust_iter; #sql cust_iter = { SELECT c.customer FROM customers c ORDER BY c.customer.id }; // display the customers in the iterator System.out.println("The contents of the customers table:"); while (cust_iter.next( )) { displayCustomer(cust_iter.customer( )); } // close the iterator cust_iter.close( ); // retrieve customer #4 from the customers2 table #sql { SELECT VALUE(c) INTO :customer FROM customers2 c WHERE id = :id }; // display the customer System.out.println("Customer #4 from the customers2 table:"); displayCustomer(customer); // 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( ) public static void displayCustomer(TCustomer customer) throws SQLException { System.out.println("id = " + customer.getId( )); System.out.println("first_name = " + customer.getFirstName( )); System.out.println("last_name = " + customer.getLastName( )); System.out.println("dob = " + customer.getDob( )); System.out.println("phone = " + customer.getPhone( )); System.out.println("street = " + customer.getAddress().getStreet( )); System.out.println("city = " + customer.getAddress().getCity( )); System.out.println("state = " + customer.getAddress().getState( )); System.out.println("zip = " + customer.getAddress().getZip( )); System.out.println("age = " + customer.getAge( )); } // end of display_customer( ) }  | If you get a SQL exception when attempting to run this program, go back and read Section 4.3.4 earlier in this chapter. |
|
The output from the program ObjectExample1.sqlj is as follows:
The contents of the customers table: id = 1 first_name = John last_name = Smith dob = 1965-01-01 00:00:00.0 phone = 650-555-1212 street = 1 Anystreet city = Anytown state = CA zip = 12345 age = 36 id = 2 first_name = Fred last_name = Gilroy dob = 1958-07-04 00:00:00.0 phone = 650-555-1212 street = 2 Main Street city = Middle Town state = CA zip = 12345 age = 43 id = 3 first_name = Jerry last_name = Fieldtop dob = 1980-12-02 00:00:00.0 phone = 650-555-1222 street = 1 Any Street city = Any Town state = CA zip = 12345 age = 20 id = 4 first_name = Jason last_name = Fieldtop dob = 1980-12-02 00:00:00.0 phone = 650-555-1222 street = 1 Any Street city = Any Town state = CA zip = 12345 age = 20 Customer #4 from the customers2 table: id = 4 first_name = Jason last_name = Fieldtop dob = 1980-12-02 00:00:00.0 phone = 650-555-1222 street = 1 Any Street city = Any Town state = CA zip = 12345 age = 20
