Make the Java-Oracle9i connection

Put Oracle9i's object-oriented features to work

By Terry Camerlengo and Carl Johnson, JavaWorld.com, 06/13/03

One of Java's most appealing features is platform neutrality: Java gives you the ability to port applications to numerous operating systems without tie-ins to a particular application server, IDE, or even database vendor. This flexibility is the cornerstone of Java's appeal and presents a strong foundation for software development. At the very least, such platform independence is an admirable goal to work towards. But under certain circumstances, commitments are necessary.

A team of Java developers might be forced to rationalize their commitment to a particular database vendor for numerous reasons, but invariably, one of the strongest motivators is performance. Leveraging a database's inherent single processes for executing stored procedures is a well-known technique to improve an application's scalability and speed. As an aside, many critics of the .Net Pet Shop application complained that the better performance achieved by that Pet Shop version was largely due to its reliance on stored procedures (our purpose isn't to argue for or against .Net or even comment on the merits of its architecture; our goal is to draw attention to the benefits of stored procedures).

In addition, a very common design pattern in Enterprise JavaBeans (EJB) development is the Fast Lane Reader, or Data Access Object (DAO) pattern. This pattern is meant to expedite read-only requests by avoiding the entity bean layer altogether and going directly to the database. In a way, the techniques described in this article extend that approach.

In this article, we examine the relationship between Java and Oracle9i. More precisely, we demonstrate some basic techniques for utilizing Oracle9i's new object-oriented features in stored procedure development. We show how the stored procedure development process can be easily and seamlessly accomplished, taking into account the complexities of modern object-oriented software development with Java.

Note: You can downloaded this article's source code from Resources.

Oracle types and inheritance

Both Oracle and Java support single inheritance, and the basics are similar for both. Oracle type inheritance, as in a Java class hierarchy, allows for ever-increasing specificity in subtypes. By expanding a type hierarchy, new columns can be added to subtypes while inheriting the basic derived features (both attributes and behavior) from the parent.

However, the syntax of how this is accomplished differs slightly between Java and Oracle's PL (Procedural Language)/SQL. In Java, object inheritance involves the keyword extends, whereas Oracle's PL/SQL uses the keyword under. PL/SQL creates object types with the create type phrase. To derive from this type, the keyword NOT FINAL must be used.

The table below compares Oracle PL/SQL and Java syntax.

Oracle PL/SQL syntax and Java syntax compared

Traditionally, the migration from a class hierarchy in Java to a relational model required an O/R (object relational) mapping layer. Many products, such as Thought Inc.'s CocoBase Enterprise O/R, were designed to avoid manually coding this O/R layer. In addition, container-managed persistence (CMP) strives to accomplish this mapping, but, by specifying the relations in XML, does so with considerable overhead. However, when trying to employ straight Java Database Connectivity (JDBC) and SQL via the Fast Lane Reader pattern to achieve optimal performance, these approaches are not an option.

Fortunately, Oracle9i allows a developer to map existing class hierarchies or create new ones without modifying relational data and/or create a mapping translation layer from the object to relational domains. This ability to map Java objects directly to Oracle objects, foregoing any translation layer (often referred to as impedance mismatch), is an extremely useful feature. Without the need to create and manage a translation layer, development time can be greatly reduced, and reliance on third-party middleware products can be mitigated. With Oracle9i and JDBC, developers can now place mapping information in the object itself.

Oracle object types are abstractions upon Oracle relational types. Underneath, the Oracle database might still deal with the data as a group of relational entities (tables, columns, rows, etc.), but developers can think of the object types in aggregate as real-world entities. This allows Java-Oracle PL/SQL developers to cast aside the detailed inner workings of the relational structures and safely move about in the realm of objects. The utility of this object perspective cannot be overstated from a developer's vantage point. And just like any other database element, object types are similarly managed alongside other database schema elements. Therefore, database administrators do not need to alter their approach to accommodate Oracle object types.

Let's look at our example. A few basic types are derived from the Vehicle superclass. The abstract Java Vehicle class has a direct descendant, PassengerVehicle. PassengerVehicle inherits all of the key attributes from Vehicle, such as vehicle ID, make, model, etc.

We have a basic Vehicle type and can derive PassengerVehicle, CommercialVehicle, and EnduranceVehicle types from it. Each subtype becomes more specialized than its parent, and if the need arises, you can later add more subtypes to the hierarchy, such as EconomyVehicle or FuelHybrid, to further qualify the PassengerVehicle type.

The Java base class Vehicle shown below contains our hierarchy's essential attributes. Vehicle contains a vehicleID (e.g., a vehicle identification number) used as a primary key and some descriptive information such as the make and model. The fuelTank and odometer attributes refer to types defined elsewhere in our domain. The ORACLE_ARRAY_NAME attribute identifies the Oracle type in which the Java class is mapped (VEHICLE_TBL_T in Vehicle's case):

public abstract class Vehicle
    extends AbstractSQLData
{
    public static final String ORACLE_ARRAY_NAME = "VEHICLE_TBL_T";
    protected int vehicleId;
    protected String make;
    protected String model;
    protected int range;
    protected FuelTank fuelTank;
    protected Odometer odometer;
    protected Odometer tripOdometer;
......

The PassengerVehicle is a subtype for the Vehicle class. It shares all of the base Vehicle class's attributes, via inheritance, differing only in the Oracle type it maps to the Oracle type, PASSENGER_VEHICLE_OBJ_T. The setSQLTypeName() method is inherited from the base class AbstractSQLData from which all Vehicle types derive. AbstractSQLData is merely a wrapper for the java.sql.SQLData class. SQLData plays a central role in custom mappings between Oracle and Java:

public class PassengerVehicle
    extends Vehicle
{
    public static final String ORACLE_OBJECT_NAME = 
"PASSENGER_VEHICLE_OBJ_T";
    public PassengerVehicle()
    {
        setSQLTypeName(ORACLE_OBJECT_NAME);
    }
...

The class hierarchy is further extended with the introduction of two more classes, OffRoadVehicle and CommercialVehicle. This hierarchy is interesting because the Java classes map to Oracle types that rely on a single database table named VEHICLE. Thus, the Oracle types mentioned above are abstractions on a single relational entity. Whether your design involves a one-to-one mapping of Oracle types to tables or a more complex representation, the ability to do either greatly improves the flexibility of migration between application layers.

This code lists the VEHICLE table:

create table VEHICLE
(
  VEHICLE_ID              NUMBER(10) not null,
  MAKE                    VARCHAR2(80) not null,
  MODEL                   VARCHAR2(80) not null,
  ODOMETER_ID             NUMBER(10) not null,
  TRIP_ODOMETER_ID        NUMBER(10) not null,
  FUEL_TANK_ID            NUMBER(10) not null,
  AUXILLARY_FUEL_TANK_ID  NUMBER(10),
  MAINTENANCE_ODOMETER_ID NUMBER(10),
  MPG                     NUMBER(2) not null
);

Another nice feature is the object view. As an extension to traditional views in relational databases, an object view is a virtual object table. Just as a view is a virtual relational table that simply references a group of actual tables, an object view is also a reference to a group of relational entities but constructed as an object type. Object views are useful in situations where a developer must create a set of object-oriented services upon an existing relational store without the need or desire to modify the actual relational data. In this article's example, we do not use object views since there is no preexisting relational model to build upon. Rather we start from scratch with pure object types. But in practice, dealing with object views is similar to dealing with the actual object type. Figure 1 shows the Vehicle example's object model.

Free Download - 5 Minute Product Review. When slow equals Off: Manage the complexity of Web applications - Symphoniq

Free Download - 5 Minute Product Review. Realize the benefits of real user monitoring in less than an hour. - Symphoniq