Using Cloudscape's Java Extensions
Page 5 of 12

Retrieving and Updating Objects

Serialization has some implications for an object. Objects stored in a Cloudscape database do not reside in your application's memory until you retrieve them. You can alter objects stored in a database only by retrieving them, altering them, and then storing the complete objects again. This section includes the following topics:

Retrieving Objects

Once an object is stored in a Cloudscape database, you can retrieve the object into a Java application using JDBC methods to execute an SQL-J statement that selects the object. When assigning the retrieved object to a variable in your application, you must cast the result as an instance of the particular class you are working with. Once you retrieve the object into your Java application, you can then execute its methods directly in the application, or alter it and then update the database.

The following example shows how you would retrieve an instance of the class JBMSTours.serializabletypes.City from a table called City, where the column City is defined to serialize the class JBMSTours.serializabletypes.City:

ResultSet rs = stmt.executeQuery("SELECT city " +
    "FROM Cities " +
    "WHERE city_id = 1");
rs.next();
//here is the cast
mycity = (City) rs.getObject(1);
//close the result set 
rs.close();
//You can work with the object in your application
mycity.getName());

Updating Objects

The following example shows how you can modify a retrieved object and then update it in the database:

// retrieve the object into memory
ResultSet rs = stmt.executeQuery(
   "SELECT city
    FROM Cities
    WHERE city_id = 1");
/* Expecting only one row*/
rs.next();
mycity = (City) rs.getObject(1);
//close the result set 
rs.close();
// modify the object in memory
mycity.setTemperatures(38, 52, 69, 56);
// update the database with the new object
PreparedStatement ptstmt2 = conn.executeUpdate(
    "UPDATE Cities SET City = ? WHERE city_id = ?");
ptstmt2.setObject(1, mycity);
ptstmt2.setInt(2, 1);
ptstmt2.executeUpdate();
ptstmt2.close();

It is also possible to include methods in the class that alter the object and then return the object itself. With such methods, you can update the object with a single SQL-J statement. In the following example, the method setPassportNumberReturnSelf returns the (altered) object itself:

-- Adult is a class alias for JBMSTours.serializabletypes.Adult
-- without a class alias you would have to use the CLASS
--keyword
UPDATE PEOPLE
SET person = CAST (person AS Adult).setPassportNumberReturnSelf('ABCD')
WHERE person_id = 2