Using Oracle Object-Relational Features with JDBC

So far, we've seen in this chapter and in the previous one how we can use Java with relational data. We've recommended putting JDBC and SQL functionality in their own classes as a separate layer to isolate the rest of the application from the details of how data is stored. But the rest of the application still has to deal with the fact that the data is not represented as objects.

We've also seen that Oracle will allow us to define our own object types. If we're using objects in Oracle, it's easy to "flatten" them to look like standard relational objects by using the appropriate SQL queries, but this is really defeating the purpose of Oracle's object-relational features though there may be situations where this is expedient.

It would be really convenient to store and retrieve Oracle objects and use them as objects in Java. There are, in fact, two ways to do this. The first is to use the weakly typed default Java class for a database object, Struct. The second is to create strongly typed custom classes in Java for Oracle objects.

Weakly Typed Objects

Structs are more efficient than are custom classes and are far easier to set up; their disadvantage is that they can't be fully integrated into your application architecture because they are defined dynamically you might think of them as second-class classes.

Selecting Structs

Retrieving an Oracle object as a weakly typed Struct is as simple as using the ResultSet's getObject() method and assigning it either the standard JDBC Struct type or Oracle's STRUCT type.

Our first example shows the steps necessary for retrieving an object using the standard Java Struct. First, we'll create an object type using SQL*Plus.

 SQL> CREATE TYPE ELEMENT_TYPE AS OBJECT (   2    ATOMIC_NUMBER NUMBER, --  can't name a column NUMBER   3    SYMBOL VARCHAR2(2),   4    NAME   VARCHAR2(20),   5    MASS   NUMBER   6  );   7  / Type created. 

We'll create a table that includes the object type.

 SQL> CREATE TABLE PERIODIC_TABLE (   2    PERIOD         NUMBER,   3    COLUMN_GROUP   NUMBER,  -- can't name a column GROUP   4    ELEMENT ELEMENT_TYPE   5  ); Table created. 

And we'll insert some data.

 SQL> INSERT INTO PERIODIC_TABLE   2    VALUES (1,1, ELEMENT_TYPE(1, 'H', 'Hydrogen', 1.008)); 1 row created. SQL> INSERT INTO PERIODIC_TABLE   2    VALUES (1,8, ELEMENT_TYPE(2, 'He', 'Helium', 4.003)); 1 row created. SQL> INSERT INTO PERIODIC_TABLE   2    VALUES (2,1, ELEMENT_TYPE(3, 'Li', 'Lithium', 6.941)); 1 row created. 

Back in Java, assuming we have valid Statement object, stmt, we can query the table like this:

 ResultSet rs = stmt.executeQuery(   "SELECT SELECT PERIOD, COLUMN_GROUP, ELEMENT" +   "FROM PERIODIC_TABLE"); 

We can retrieve the first two columns, which are a standard SQL type, using getInt().

 int period = rs.getInt(1); int group = rs.getInt(2); 

The third column, which is the ELEMENT_TYPE, we retrieve using getObject(), but we need to cast it to a STRUCT (or Struct) and assign it to a STRUCT object.

 STRUCT elementStruct = (STRUCT) rs.getObject(3); 

We can retrieve the object attributes also known as the fields or properties of the object from a STRUCT in the form of an Object array by calling the Struct getAttributes() method.

 Object elementAttr[] = elementStruct.getAttributes(); 

The getAttributes() method effectively retrieves each of the object's attributes and maps it to an appropriate Java type, as defined by the connection's type map table, if it has one; otherwise, the default SQL to Java mappings are used. When we read an attribute from the Object array, we need to cast the attribute to its actual type. Table 9-1 lists the essential type mappings.

Table 9-1. Basic SQL to Java Type Mappings

Oracle SQL type

Java type

VARCHAR2,CHAR

String

NUMBER

BigDecimal

DATE

Date

The NUMBER type mapping to BigDecimal is necessary to hold any possible value of NUMBER, but it's rarely the most appropriate Java type. We usually have to perform a conversion, based on our knowledge of the data, by calling one of BigDecimal's xxxValue() methods, such as intValue() or doubleValue(), to get an appropriate Java primitive type.

The first attribute, atomic weight, is a NUMBER in the database. We can call BigDecimal's intValue() method to convert it to int the most appropriate Java type for atomic weight.

 int atomic_number = ((BigDecimal)elementAttr[0]).intValue(); 

The second and third attributes, atomic symbol and name, are both VARCHAR2, so they will automatically be Strings.

 String symbol =  (String) elementAttr[1]; String name =    (String) elementAttr[2]; 

graphics/note_icon.gif

If a String is retrieved from an object array by casting an element to a string and it looks like hexadecimal gibberish when you print it out, for example you probably need to add [ORACLE_HOME]/jdbc/lib/nls_charset12.zip to your CLASSPATH.

The fourth parameter, atomic mass, is also an SQL NUMBER, which we'll get as a BigDecimal. The most appropriate Java type is float, so we'll use the BigDecimal floatValue() method.

 float mass = ((BigDecimal) elementAttr[3]).floatValue(); 

An alternative to using the getAttributes() method is to use the getOracleAttributes() method. This returns the attributes as Oracle JDBC datatypes, such as NUMBER and CHAR. These have conversion methods to standard Java types.

 Object elementAttr[] = elementStruct.getOracleAttributes(); int atomic_number = ((NUMBER) elementAttr[0]).intValue(); String symbol =     ((CHAR)   elementAttr[1]).toString(); String name =       ((CHAR)   elementAttr[2]).toString(); float mass =        ((NUMBER) elementAttr[3]).floatValue(); 

Performing the conversion from Oracle JDBC types to Java types ourselves has the advantage that we avoid unnecessary intermediate conversions.

Inserting and Updating Object Types

Unfortunately, the standard Java Struct doesn't provide a way to create a new Struct we can obtain one only by selecting an object type from the database. Fortunately, Oracle's STRUCT has additional functionality, including a means of creating a new STRUCT that we can insert into the database. Because we're working closely with Oracle-specific object-relational features anyway, there's little benefit to be gained by choosing the standard option and lots of benefit to be gained from Oracle's extensions, so we'll use only the Oracle STRUCT in these examples.

When we retrieve an object type from the database into a STRUCT, the JDBC driver obtains the information it needs to create the STRUCT and the attributes array from the database, behind the scenes. If we want to create a STRUCT object from scratch and insert it into the database, we need to obtain the same information about the object type from the database ourselves this information comes in the form of an object called a StructDescriptor, which is unique to Oracle's implementation.

To use STRUCT and the StructDescriptor classes, we need to import the Oracle JDBC classes at the top of our source file.

 import oracle.sql.*; 

To get a StructDescriptor, we call a static method, createDescriptor(), with the name of the object type and a valid connection to the database. Here is how we obtain the descriptor for our ELEMENT_TYPE:

 String elemTypeName = "ELEMENT_TYPE"; StructDescriptor elemDesc =    StructDescriptor.createDescriptor(elemTypeName, conn); 

StructDescriptor has a number of interesting methods. One of these, getMetaData(), returns metadata about the object type in the form of a ResultSetMetaData object.

 ResultSetMetaData elemMetaData = elemDesc.getMetaData(); 

We can obtain information about the object's attributes by calling ResultMetaData methods, such as getColumnCount(), getColumnName(), getColumnType(), and getColumnTypeName().

 int columns = elemMetaData.getColumnCount(); System.out.println("Number of attributes: " + columns); System.out.println("Attributes: "); for(int i = 1; i <= columns; i++) {    System.out.print(elemMetaData.getColumnName(i)+"    ");    System.out.println(elemMetaData.getColumnTypeName(i)); } 

Unlike the standard Java Struct, Oracle's STRUCT has a public constructor that we can call. It requires a StructDescriptor, a Connection, and an array of objects for the attributes. First, we'll create and populate an attributes array.

 Object [] elemAttr = new Object [4]; elemAttr[0] = new BigDecimal(4);    // Atomic number elemAttr[1] = "Be";                 // symbol elemAttr[2] = "Beryllium";          // name elemAttr[3] = new BigDecimal(9.012);// mass 

Now we have all the parts we need to call the STRUCT constructor.

 STRUCT elemStruct = new STRUCT(elemDesc, conn, elemAttr); 

We can use this STRUCT to perform an INSERT into the PERIODIC _TABLE table in two ways. We can use it with PreparedStatement.

 PreparedStatement ps = conn.prepareStatement(     "INSERT INTO PERIODIC_TABLE VALUES(?, ?, ?)"); ps.setInt(1, 2); // period ps.setInt(2, 2); // group ps.setObject(3, elemStruct); int rows = ps.executeUpdate(); 

Alternatively, if we have an updateable result set, we can use that to perform an insert. We'll add another record this way. To obtain an updateable result set, you may remember we need to set type and concurrency when we create our Statement.

 Statement stmt = conn.createStatement(   ResultSet.TYPE_SCROLL_INSENSITIVE,   ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(   "SELECT SELECT PERIOD, COLUMN_GROUP, ELEMENT" +   "FROM PERIODIC_TABLE"); 

We'll need to create a new STRUCT but because the StructDescriptor is associated with the object type and not any particular table or object, we can reuse it. We'll keep the same attributes array, but we'll assign new values to it.

 elemAttr[0] = new BigDecimal(5);   // Atomic number elemAttr[1] = "B";                 // symbol elemAttr[2] = "Boron";             // name elemAttr[3] = new BigDecimal(10.81);// mass elemStruct = new STRUCT(elemDesc, conn, elemAttr); 

To perform the insert using the ResultSet, we move to the insert row, update each of the fields using the appropriate updateXXX() method update-Object() is the one we need for our STRUCT then we call insertRow().

 rs.moveToInsertRow(); rs.updateInt(1,2);  // period rs.updateInt(2,13); // group rs.updateObject(3, elemStruct); rs.insertRow(); 

Strongly Typed Objects

STRUCT is a one-size-fits-all, easy-to-use class that will accommodate any Oracle object type. But if you want a strongly typed class of your own design that fits into your application's design, you can do that, too, by implementing either the SQLData or the ORAData interface in the class definition. Once we register our SQLData or ORAData class with the JDBC driver's type map, we can read and write objects of this class automatically, using standard JDBC calls.

Implementing Custom Classes with the SQLData Interface

We'll take a look at the Java standard SQLData interface first. The SQLData interface requires that we implement three methods:

 getSQLTypeName(); readSQL(SQLInput stream, String typeName); writeSQL(SQLOutput stream); 

In addition, we need to provide a public, parameterless constructor that the JDBC driver can call.

 public classname() { } 

For this example, we'll use the ELEMENT_TYPE object type we created above. Each row of the table must correspond to an object in Java; to do this we need to make a table with a single column of type ELEMENT_TYPE an object table won't work, interestingly enough, because it appears to be a relational table with columns corresponding to the object attributes.

 SQL> CREATE TABLE ELEMENTS (ELEMENT ELEMENT_TYPE); Table created. 

Let's create a Java class, Element, to correspond to the Oracle ELEMENT_TYPE. We'll include the Oracle typename, as well as private instance variables to hold the object's attributes.

 public class Element implements SQLData {   private static final String typeName="ELEMENT_TYPE";   private int    number;   private String symbol;   private String name;   private float  weight; 

The first SQLData method that we'll implement, getSQLTypeName(), is trivial. Because we associate our class with the Oracle object by hard-coding the object's SQL type name, we just need to return that string.

 public String getSQLTypeName() {   return typeName; } 

The next method that we'll implement, readSQL(), requires us to read a stream from the database, calling the appropriate readXXX() method for each attribute of ELEMENT_TYPE, in the order of their appearance in the object type, to populate our instance variables.

 public void readSQL(SQLInput inStream, String typeName) throws SQLException {  number = inStream.readInt();  symbol = inStream.readString();  name   = inStream.readString();  weight = inStream.readFloat(); } 

The last method we need to implement, writeSQL(), is similar to readSQL() it requires us to write to a stream, calling the appropriate writeXXX() method for each attribute of ELEMENT_TYPE in the proper order.

 public void writeSQL(SQLOutput outStream) throws SQLException {   outStream.writeInt(number);   outStream.writeString(symbol);   outStream.writeString(name);   outStream.writeFloat(weight); } 

Finally, we need to provide this constructor:

 public Element() { } 

That is all that's required to create the custom class, but as we'll see, we may want to create another constructor and other methods to make it easier to use the Element class and to hide some of the database details from client classes.

In order for it to use our class, we need to let the JDBC driver know that it exists and that it corresponds to the Oracle ELEMENT_TYPE. By default, when we retrieve an object from a ResultSet, it will map it to a STRUCT. To get ELEMENT_TYPE objects mapped to the Element class, we need to create or extend the JDBC connection's type map.

Updating the SQL-Java Type Map

The JDBC type map is an implementation of the Map interface. It maps a key in this case, an SQL typename to a value in this case, a Java class. There is a type map automatically associated with a connection. We can retrieve it like this:

 Map typeMap = conn.getTypeMap(); 

As long as it implements the Map interface, the actual class of the type map isn't important, but as it happens, it is a Hashtable. (And, until we add something to it, it is initially empty.) To add the mapping for ELEMENT _TYPE, we call the Map put() method with the typename and the Java class; we get the Java class by calling the Class.forName() method.

 typeMap.put("ELEMENT_TYPE", Class.forName("Element"); 

This map entry causes the JDBC driver to map the Oracle object type ELEMENT_TYPE to the Java Element type when we retrieve the object from the database.

Using Custom Classes

Once we've mapped our class to the corresponding object type, we can retrieve an object from the ResultSet using the getObject() method. The JDBC driver will create a class object by calling our parameterless constructor and will populate it by calling our readSQL() class. In this example, we use a PreparedStatement to select a particular element by atomic number.

 PreparedStatement ps = conn.prepareStatement(      "SELECT * FROM ELEMENTS E " +      "WHERE E.ELEMENT.ATOMIC_NUMBER = ?"); ps.setInt(1, number); ResultSet rs = executePSQuery(ps); ResultSet rs = ps.executeQuery(); Element e = null; if(rs.next()) {       e = (Element) rs.getObject(1); } 

To insert an Element object into the database, we first need to create it. We could call the default constructor, then set the attributes, but we'll create another constructor, for convenience, instead.

 public Element(String typeName, int number, String symbol,      String name, float weight) throws SQLException {     // this.typeName = typeName;     this.number = number;     this.symbol = symbol;     this.name = name;     this.weight = weight; } 

In the following example, we'll call this constructor, then insert it by using a PreparedStatement:

 e = new Element("ELEMENT_TYPE",     number, symbol, name, weight); getConnection(); PreparedStatement ps = conn.prepareStatement(   "INSERT INTO ELEMENTS VALUES (?)"); ps.setObject(1, e); ps.executeQuery(); ps.close(); 

In this case, the JDBC driver calls our getSQLTypeName() method to determine the Oracle type, then calls our writeSQL() to get our class's attributes.

The examples above may suggest that the client class accesses the database to read and write Element objects that is one possible implementation. Below is a more complete example that encapsulates all the database access in the Element class itself. A client application is expected to create and get an Element object using the static factory methods getElement() and createElement(). However, there is nothing except good manners to prevent it from calling the default constructor, which must be public though this will allow it to insert only an empty object.

 // Element.java import java.sql.*; import oracle.jdbc.pool.OracleDataSource; import oracle.jdbc.driver.*; import oracle.sql.*; import java.math.BigDecimal; import java.util.*; public class Element implements SQLData {   private static Connection conn = null;   private static boolean typeMapSet = false;   private static final String typeName="DAVID.ELEMENT_TYPE";   private int    number;   private String symbol;   private String name;   private float  weight;   /* Constructors */   public Element()   {   }   private Element(int number, String symbol,      String name, float weight)   throws SQLException   {      // this.typeName = typeName;      this.number = number;      this.symbol = symbol;      this.name = name;      this.weight = weight;    }   // Interface implementation ...    public String getSQLTypeName()    {      return typeName;    }      public void readSQL(SQLInput inStream, String typeName)      throws SQLException      {      // typeName = typeName; already set      number = inStream.readInt();      symbol = inStream.readString();      name   = inStream.readString();      weight = inStream.readFloat();      }    public void writeSQL(SQLOutput outStream)    throws SQLException    {      outStream.writeInt(number);      outStream.writeString(symbol);      outStream.writeString(name);      outStream.writeFloat(weight);    }   // utility methods   public static Connection getConnection()   throws SQLException   {     if(conn==null)     {       OracleDataSource ods = new OracleDataSource();       ods.setDriverType("thin");       ods.setServerName("noizmaker");       ods.setNetworkProtocol("tcp");       ods.setDatabaseName("osiris");       ods.setPortNumber(1521);       ods.setUser("david");       ods.setPassword("bigcat");       conn = ods.getConnection();     }     setTypeMap();     return conn;   }   private static void setTypeMap()    throws SQLException   {     if(!typeMapSet)     {       Map typeMap = conn.getTypeMap();       try       {          typeMap.put(typeName, Class.forName("Element"));          typeMapSet = true;       }       catch (ClassNotFoundException e)       {         System.out.println("Caught: " + e);       }     }   }    // Access methods       // Create new Element   public static Element createElement(int number, String symbol,     String name, float weight)   throws SQLException   {     getConnection();     Element e = null;     if((e = getElement(number)) == null)     {       e = new Element(number, symbol, name, weight);       PreparedStatement ps = conn.prepareStatement(         "INSERT INTO ELEMENTS VALUES (?)");       ps.setObject(1, e);       ps.executeQuery();       ps.close();     }     return e;   }   public static Element getElement(int number)   throws SQLException   {     getConnection();     PreparedStatement ps = conn.prepareStatement(          "SELECT * FROM ELEMENTS E " +          "WHERE E.ELEMENT.ATOMIC_NUMBER = ?");     ps.setInt(1, number);     return executePSQuery(ps);   }   public static Element getElement(String symbol)   throws SQLException   {     getConnection();     PreparedStatement ps = conn.prepareStatement(          "SELECT * FROM ELEMENTS E " +          "WHERE E.ELEMENT.SYMBOL = ?");     ps.setString(1, symbol);     return executePSQuery(ps);   }   private static Element executePSQuery(PreparedStatement ps)   throws SQLException   {     Element e = null;     ResultSet rs = ps.executeQuery();     while(rs.next())     {       e = (Element) rs.getObject(1);     }     rs.close();     ps.close();     return e;   }   int getNumber()   {     return number;   }   String getSymbol()   {     return symbol;   }   String getName()   {     return name;   }   float getWeight()   {     return weight;   }   int getNeutrons()   {     return Math.round(weight) - number;   } } 

This is a trivial example of how a client application could use the Element class:

 // Molecule.java - uses Element import java.sql.*; public class Molecule {   public static void main(String [] args)   {     try     {      Element.createElement(         1, "H", "Hydrogen", 1.008F);       Element.createElement(         8, "O", "Oxygen", 15.999F);       Element hydrogen = Element.getElement("H");       Element oxygen   = Element.getElement("O");       System.out.println("Elem: " + oxygen.getName());       System.out.println("  Neutrons (avg): "          + oxygen.getNeutrons());       System.out.println("Elem: " + hydrogen.getName());       System.out.println("  Neutrons (avg): "          + hydrogen.getNeutrons());     }     catch (SQLException e)     {       System.out.println("Caught: " + e);     }   } } 
Implementing Custom Classes with the ORAData Interface

Oracle's proprietary ORAData interface offers performance advantages over the SQLData interface because it does not automatically perform conversions from SQL types to Java types.

Another advantage is that we do not have to update the connection's type map, because the object is passed to and from our class as an Oracle STRUCT. (The method signatures actually specify Datum, but that is a superclass of STRUCT.) Our class is responsible for conversion of the STRUCT's attributes to and from Java types. An alternative is to keep the attributes in their SQL format and let the client class perform the conversion, if the client class is database-savvy. If calculations need to be performed on the attributes, it may be more efficient and accurate to defer conversion.

Creating a custom class using the ORAData interface actually requires that we implement two interfaces: ORAData and ORADataFactory. Each of these requires that we implement one method. The ORADataFactory interface requires that we implement a create() method that takes a Datum and returns a populated instance of our class.

 public ORAData create(Datum d, int sqlType) throws SQLException {   if(d==null)   {     return null;   }   Object [] attributes = ((STRUCT) d).getOracleAttributes();   System.out.println("Datum is" + d.getClass().getName());   return new OraDataElement(      ((NUMBER) attributes[0]).intValue(),      ((CHAR)attributes[1]).toString(),      ((CHAR)attributes[2]).toString(),      ((NUMBER) attributes[3]).floatValue()); } 

Converting the Datum is identical to what we did when we used STRUCTs directly as weakly typed classes. After casting the Datum to a STRUCT, we obtain the attributes in an object array by calling the getAttributes() or getOracleAttributes() method, depending on whether we want Java types or Oracle JDBC datatypes.

The ORAData interface requires that we implement a toDatum() method that converts the attributes of our class and returns them as a STRUCT. Creating a STRUCT, you may remember, requires us to get a StructDescriptor and stuff our attributes into an object array.

 public Datum toDatum(Connection conn) throws SQLException   {     StructDescriptor sd =       StructDescriptor.createDescriptor("ELEMENT_TYPE", conn);       Object [] attributes = {         new NUMBER(number),         new CHAR(symbol, CHAR.DEFAULT_CHARSET),         new CHAR(name, CHAR.DEFAULT_CHARSET),         new NUMBER(weight)};       return new STRUCT(sd, conn, attributes);   } 

graphics/note_icon.gif

It doesn't matter in this case whether we provide Oracle JDBC types or Java types, like we did before i.e., BigDecimal in place of NUMBER and String in place of CHAR the driver will determine and perform the appropriate conversion in either case.

In addition to these interface requirements, we also must provide a way for the client application to obtain an instance of our class that implements the ORADataFactory class. (This can be a separate class from our ORAData class, but here, our custom class implements both interfaces.) This is commonly done by having a static instance in our class and providing a method to return it.

 static final OraDataElement oraDataElementFactory =     new OraDataElement(); public static ORADataFactory getFactory() {    return oraDataElementFactory; } 

To facilitate creating an object to insert into the database, we'll provide a constructor that a client application can use.

 public OraDataElement (int number, String symbol,     String name, float weight)   {     this.number = number;     this.symbol = symbol;     this.name = name;     this.weight = weight;   } 

To use the ORAData class to insert an object, we create the object by calling the constructor, then use an OraclePreparedStatement (not just a PreparedStatement) to insert it.

 OraDataElement ee = new OraDataElement(16,"S","Sulfur", 32.06F); OraclePreparedStatement ps = (OraclePreparedStatement)    conn.prepareStatement("INSERT INTO ELEMENTS VALUES(?)"); ps.setORAData(1, ee); ps.execute(); ps.close(); 

To use the ORAData class to read objects, we select into an Oracle-ResultSet (not just a ResultSet) and call the getORAData() method. Notice that the getORAData() method requires the getFactory() method that we defined above.

 Statement stmt = conn.createStatement(); OracleResultSet rs = (OracleResultSet) stmt.executeQuery(      "SELECT * FROM ELEMENTS"); while(rs.next()) {     OraDataElement e = (OraDataElement) rs.getORAData(1,     OraDataElement.getFactory());     System.out.println("Name:" + e.name); } 

Finally, just as we did with the SQLData example, we could encapsulate all the database functionality in the ORAData class to hide it from the client class.



Java Oracle Database Development
Java Oracle Database Development
ISBN: 0130462187
EAN: 2147483647
Year: 2002
Pages: 71

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