Flawed JDO Points the Way to the "Objectbase"
Pages: 1, 2

Inserting a New Instance in the Objectbase

Inserting a Person object into the objectbase is now a simple matter of using a properly-formatted SQL INSERT statement and the same JDBC code that you would typically use to insert an entry into a relational database. In Example 2, the program starts out by creating a new instance of a Person object by using the new operator. Next, it sets the attribute values, using the appropriate setXXX() methods. The program proceeds by creating a prepared statement based upon a SQL INSERT statement that has one argument in its VALUES clause.

Next, the program calls the prepared statement's setObject() method, passing it the Person object. The JDBC driver uses the information the program added to the typemap to map the Person object to the PERSON_TYPE UDT. Then, the program executes the prepared statement using the executeUpdate() method, which returns the number of rows inserted. It then performs the necessary cleanup by closing the prepared statement and committing the transaction. At this point, a new PERSON_TYPE object exists in the PERSON_OBJECT_TABLE.

---

Example 2. A JDBC object-relational demonstration program

import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import oracle.jdbc.driver.*;

/**
A program to demonstrate Oracle object-relational technology
*/
public class DemonstrateOR {
  Connection conn;

  public DemonstrateOR() {
    try {
      DriverManager.registerDriver(
        new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@dssw2k01:1521:orcl",
        "scott",
        "tiger");
      conn.setAutoCommit(false);
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
      System.exit(1);
    }
  }

  public static void main(String[] args)
   throws Exception {
    new DemonstrateOR().process();
  }

  public void process() throws SQLException {
    PreparedStatement pstmt =  null;
    ResultSet         rslt  = null;
    Statement         stmt  = null;
    Person            person = null;

    // Update the connection's type map
    try {
      java.util.Map map  conn.getTypeMap();
      map.put("SCOTT.PERSON_TYPE",
       Class.forName("Person"));
      conn.setTypeMap(map);
    }
    catch (ClassNotFoundException e) {
      System.err.println("Class Not Found Error: " + e.getMessage());
      System.exit(1);
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
      System.exit(1);
    }

    // Insert a person object
    try {
      // create a new instance
      person  = new Person();
      person.setName("Bales, Donald J.");
      person.setAddress("Downers Grove, IL 60516");
      person.setSsn("999-99-9999");
      person.setEmail("balesd@compuserve.com");
      person.setHomePhone("(999) 999-9999");
      person.setWorkPhone("(999) 999-9999");

      pstmt = conn.prepareStatement(
        "insert into PERSON_OBJECT_TABLE values ( ? )");
      pstmt.setObject(1, person);
      int rows = pstmt.executeUpdate();
      pstmt.close();
      pstmt = null;
      System.out.println(rows + " row inserted");
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
      System.exit(1);
    }
    finally {
      if (pstmt !=null)
        try { pstmt.close(); } catch (SQLException ignore) { }
    }

    // Update the object using standard JDBC
    Ref personRef = null;
    try {
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select ref(p), " +
       "       value(p) " +
       "from   PERSON_OBJECT_TABLE p " +
       "where  name = 'Bales, Donald J.'");
      rslt.next();
      personRef = (Ref)   rslt.getObject(1);
      person = (Person)rslt.getObject(2);
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;

      // Let's change my email address
      person.setEmail("don@donaldbales.com");

      pstmt = conn.prepareStatement(
       "update PERSON_OBJECT_TABLE p " +
       "set    value(p)=? " +
       "where  ref(p)=?");
      pstmt.setObject(1, person);
      pstmt.setRef(2, personRef);
      int rows = pstmt.executeUpdate();
      pstmt.close();
      pstmt = null;
      System.out.println(rows + " row updated");
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
      System.exit(1);
    }
    finally {
      if (rslt  !=null)
        try { rslt.close();  } catch (SQLException ignore) { }
      if (stmt  !=null)
        try { stmt.close();  } catch (SQLException ignore) { }
      if (pstmt !=null)
        try { pstmt.close(); } catch (SQLException ignore) { }
    }


    try {
      // Create and execute the object sql statement
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
        "select value(p) from PERSON_OBJECT_TABLE p");
      while (rslt.next()) {
        // Cast the object
        person = (Person)rslt.getObject(1);
        System.out.println("name:       " +
         person.getName());
        System.out.println("address:    " +
         person.getAddress());
        System.out.println("ssn:        " +
         person.getSsn());
        System.out.println("email:      " +
         person.getEmail());
        System.out.println("home phone: " +
          person.getHomePhone());
        System.out.println("work_phone: " +
          person.getWorkPhone());
      }
      rslt.close();
      rslt = null;
      stmt.close();
      stmt  = null;
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
      System.exit(1);
    }
    finally {
      if (rslt !=null)
        try { rslt.close();  } catch (SQLException ignore) { }
      if (stmt !=null)
        try { stmt.close();  } catch (SQLException ignore) { }
    }

    // Delete the person
    try {
      stmt  = conn.createStatement();
      int rows = stmt.executeUpdate(
        "delete PERSON_OBJECT_TABLE " +
        "where  ssn = '999-99-9999'");
      stmt.close();
      stmt = null;
      System.out.println(rows + " row deleted");
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
      System.exit(1);
    }
    finally {
      if (stmt != null)
        try { stmt.close(); } catch (SQLException ignore) { }
    }

  }

  protected void finalize()
   throws Throwable {
    if (conn != null)
      try { conn.close(); } catch (SQLException ignore) { }
    super.finalize();
  }
}

---

Updating an Instance from the Objectbase

The next step in the demonstration is to update the Person object the program just stored in the objectbase. To do this, it retrieves the object's reference and an instance of the object. To accomplish this, it utilizes the objectbase's functions ref() and value() in the SQL SELECT statement. I could have just used unique information about the object's attributes to update it (the reference is not necessary), but I wanted to show that a reference is similar in concept to one in Java, in that it is a unique identifier for an object.

First, the program creates a Statement object. Next, it retrieves a ResultSet object by executing the SQL SELECT statement. It forwards to the first row of the result set by calling the next() method. Then, it stores the REF object in a Ref variable by calling getObject() and casting the returned Object to a Ref. Next, it performs a similar call for the Person object. Now that the program has a copy of the Person object, I change my email address and proceed to save it to the objectbase. To do so, the program starts out by creating a properly-formatted SQL UPDATE statement that once again utilizes the value() and ref() objectbase functions. Next, it calls the prepared statement's setObject() method twice; the first time to set the Person object, and the second time to set its reference. Then, the program tidies things up by closing the prepared statement and committing the transaction. Now the objectbase contains an updated entry with my new email address.

Selecting an Instance from the Objectbase

Retrieving an object is straightforward. If you were paying close attention, you'll have realized that the program just did it when it updated the Person object. This time, the program retrieves the Person object and then echoes its attributes to the screen. Proof that the program actually works. Wow.

Deleting an Instance from the Objectbase

Finally, the demonstration program executes a basic SQL DELETE statement, using JDBC, to delete the Person object from the objectbase. By doing so, the program cleans up after itself, so you can try the program over and over again to your heart's content.

Transforming a Relational Database using Object Views

As you have just seen in the demonstration program, using object-relational technology with JDBC is fairly simple and uncomplicated. The real beauty behind Oracle's implementation of OR technology is that you can have your cake and eat it too. You can use Oracle8i or Oracle9i as an objectbase and then use the SQL TABLE operator to create relational views for your non-object-oriented development or third-party software tools. Or you can put an object face on a legacy relational database by creating object views, so you can access it with your OOD tools. This means that you don't have to wait for a new project to start leveraging OR technology; you can start using it immediately on any Oracle8i or Oracle9i server.

Conclusion

As you can see from Example 2, using JDBC with an object-relational technology is far simpler than using JDO. You can continue to leverage the strengths of SQL and at the same time migrate an existing relational database to an objectbase. Yet, the most important point of using OR technology is that the object model exists in the objectbase, not just in a set of Java programs. This means that the object model can be leveraged by any object-oriented programming language or OR-capable tool. With Oracle, it also means that you can continue to use your favorite relational tools as well.

Because I've provided a somewhat apples-to-apples comparison of JDBC and JDO, the example program does not completely demonstrate the capabilities of JDBC with OR technology. For example, it does not show the use of arrays (or collections, as they are also called) or methods. I do, however, cover these topics in-depth in my book Java Programming with Oracle JDBC. I urge you to bypass the extraneous layers of abstraction that JDO and similar object/relational mapping tools create, and address the fundamental problem: you need to use an objectbase for your object-oriented software solutions. I look forward to your feedback.

Donald Bales is a Systems Architect, Computer Applications Consultant, and Business Analyst specializing in the analysis, design, and programming of web-based, client-server, and distributed applications, the internationalization of existing applications, systems integration, and data warehousing.

---

Return to ONJava.com.