Make the Java-Oracle9i connection

Put Oracle9i's object-oriented features to work

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

FeatureOracle PL/SQL syntaxJava syntax
Type specification or definitionCreate type <type name> (e.g., create type Student)<access specifier> class <name> public class Student
Implementation inheritanceunderextends
Abstract typesNot instantiableAbstract
SubtypingNot final; Oracle types are not extendable by defaultNot applicable; Java objects can be extended by default
Method overriding/polymorphismOverridingSubtypes override supertype methods by default; to explicitly call the supertype method, use the keyword super
Standard outputDBMS_OUTPUT.PUT_LINE()System.out.println()

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 = 
    public PassengerVehicle()

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,
  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.

Figure 1. Object model for Vehicle example. Click on thumbnail to view full-size image.

Dynamic dispatch (polymorphism)

Commonly referred to as polymorphism, dynamic dispatch behavior in Oracle allows stored procedures or functions to override supertype methods and directly invoke the subtype methods. Subtypes can override nonfinal methods derived from supertypes. Oracle9i also supports substitutability—another consequence of an object-oriented language—that allows the substitution of a subtype where the supertype is required. The combination of dynamic dispatch and substitutability enables procedures to invoke inherited class methods on any object instance regardless of its location in a class hierarchy.

Polymorphism in a stored procedure is demonstrated below. The offroad_vehicle_obj_t overrides the set_range() method's inherited behavior. The offroad_vehicle_obj_t version of set_range() takes into account the existence of an auxiliary fuel tank, whereas the supertype vehicle_obj_t uses the default fuel tank capacity to calculate the range:

member procedure set_range(mpg in number) is
     range := mpg * fuel_tank.capacity;
create or replace type body offroad_vehicle_obj_t is
  overriding member procedure set_range(mpg in number) is
     range := mpg * (fuel_tank.capacity + auxillary_fuel_tank.capacity);

Type evolution

Another useful feature is type evolution. Through type evolution, a developer can modify the type's definition while simultaneously cascading the changes to all of the type's dependents. You can use type evolution to add, modify, or drop methods and attributes on an object type as long as the change is not to a final or noninstantiable type. However, if need be, a noninstantiable or final type can be evolved to an instantiable type. When evolving a type, the type's dependencies are checked and validated by recompilation. If the dependency can be recompiled, then the change commits; otherwise, the change rolls back, and the type is not evolved.

The syntax for type evolution is the ALTER TYPE phrase, but type evolution does not require the ALTER TYPE statement. A developer can drop all of the objects, make the necessary type change (the evolution), and then manually recreate the dependent types one by one. However, type evolution gives you the ability to quickly cascade simple changes throughout a type hierarchy without elaborate groups of drop and create statements. We do not demonstrate type evolution in this article, but the functionality is useful on mature projects where object models change frequently.

Custom constructors

Every user-defined Oracle type has a system default constructor. In Java terms, this is often referred to as the default constructor or no-arg constructor. Oracle provides the same initialization feature with its object types, except the default constructor requires the inclusion of all type members as parameters—the exact opposite from Java and other object-oriented languages.

The key is that the attribute order must match the order in which they are defined. Noninstantiable types (equivalent to an abstract class) do not have defined constructors. Functions can also be declared as noninstantiable so long as they are in a noninstantiable type. Ironically, a no-arg constructor in an Oracle type would involve a custom user-defined constructor. Here's an example of a user-defined constructor in PL/SQL and how you invoke it:

--the definition
constructor function passenger_vehicle_obj_t(vehicle_id in number, make in 
varchar2, model in varchar2, mpg in number, odometer in odometer_obj_t, 
trip_odometer in odometer_obj_t, fuel_tank in fuel_tank_obj_t) return self 
as result
--the invocation
vehicle_obj := passenger_vehicle_obj_t(vehicle_id, make, model, mpg, 
odometer(1), trip_odometer(1), fuel_tank(1));
1 2 Page 1
Page 1 of 2