Make the Java-Oracle9i connection

Put Oracle9i's object-oriented features to work

1 2 Page 2
Page 2 of 2

Mapping Java objects to Oracle types

Oracle's object-relational facilities are tightly integrated with JDBC and SQLJ. This tight integration allows seamless integration between Java code and PL/SQL types in methods. Objects, through a form of serialization, persist their state to the database. Objects can be mapped to C++, Java, or XML. In this article, we only explore techniques for mapping objects to Java via JDBC.


SQLJ is a popular alternative to JDBC. SQLJ is an Oracle technology that simplifies mapping from Java to SQL by alleviating the developer from the task of object serialization. SQLJ allows strong typing through the use of oracle.sql.CustomData and weak typing through oracle.sql.STRUCT. SQLJ uses the oracle.sql package. The remainder of this article focuses on the JDBC approach to O/R mapping because many good resources about SQLJ already exist.


The JDBC API provides a programming bridge between Java programs and the Oracle database. JDBC allows access to database entities through dynamic SQL. Through customizable mappings, JDBC also provides Java with a way to access Oracle object types as if they are Java classes. When JDBC calls encounter an Oracle object, the database driver looks up the Oracle-to-Java mapping and instantiates the Java object based upon the Oracle object's state. This process also works in the Java-to-Oracle direction, where object parameters in a stored procedure convert to their Oracle corollaries.

This process of mapping Java objects to Oracle objects back to Java objects is a variation on serialization. In the JDBC scenario, the state is marshaled between the Oracle and the Java environments (e.g., JVM).

The Vehicle base class's readSQL() method is responsible for transforming Oracle data into Java attribute values (i.e., when receiving an Oracle stored procedure's return value). The writeSQL() method translates Java attribute data into Oracle data (i.e., when a Java program supplies a stored procedure's parameters). These methods are not called directly by the developer but are instead internally invoked by the Connection object:

protected void readSQL(SQLInput sqlInput)
        throws SQLException
        vehicleId = sqlInput.readInt();
        make = sqlInput.readString();
        model = sqlInput.readString();
        range = sqlInput.readInt();
        fuelTank = (FuelTank)sqlInput.readObject();
        odometer = (Odometer)sqlInput.readObject();
        tripOdometer = (Odometer)sqlInput.readObject();
    public void writeSQL(SQLOutput sqlOutput)
        throws SQLException

The run() method below demonstrates the invocation of a stored procedure from our sample Java program. The example highlights connection type map usage, the passing of procedure parameters, array descriptor usage, and finally the CallableStatement class's various methods:

    public void run(String[] makes)
        throws SQLException
        Connection connection = null;
        CallableStatement callableStatement = null;
            connection = 
(HOST=" + host + ")(PORT=" + port + ")))(CONNECT_DATA=(SERVICE_NAME=" + database + ")))", username, password);
            Map typeMap = connection.getTypeMap();
            typeMap.put(Odometer.ORACLE_OBJECT_NAME, Odometer.class);
            typeMap.put(FuelTank.ORACLE_OBJECT_NAME, FuelTank.class);
            typeMap.put(Vehicle.ORACLE_ARRAY_NAME, Vehicle[].class);
            callableStatement = connection.prepareCall("{? = call 
            ArrayDescriptor arrayDescriptor = 
ArrayDescriptor.createDescriptor("MAKE_TBL_T", connection);
            ARRAY array = new ARRAY(arrayDescriptor, connection, makes);
            callableStatement.setObject(2, array);
            callableStatement.registerOutParameter(1, OracleTypes.ARRAY, 
            Object[] objects = 
            for (int i = 0; i < objects.length; i++)
                if (callableStatement != null)
            catch(SQLException e)
                System.out.println("Exception encountered closing callable 
statement: " + e.getMessage());
                if (connection != null)
            catch(SQLException e)
                System.out.println("Exception encountered closing 
connection: " + e.getMessage());


SQLData provides a mechanism to specify explicit mappings between Oracle object types and Java classes. The java.sql package contains the SQLData class.

The explicit mapping is entered in a type map, which must be provided to the Connection object. The programmer simply supplies the Oracle type name, the Java class name, and the field mappings. This is automatically done in SQLJ and O/R mapping tools. But with JDBC, we must specify the mapping instructions directly.

Cast and MultiSet functions

Pseudo functions were first introduced in Oracle8i and enable relational tables to be treated as if they were a collection of user-defined types. In our example, we use the two pseudo functions, Cast and MultiSet, to transform between user-defined Oracle objects and relational data.

MultiSet converts a table into a collection, while Cast converts among collections of different types. Together they form a nice one-two punch.

The following code demonstrates how Vehicle_manager uses Cast and MultiSet together. MultiSet and Cast are extensively used in the example to convert a series of SQL queries into the vehicle_info_tbl_t type, which is selected into a ref cursor:

create or replace package body vehicle_manager is
  type ref_cur_t is ref cursor;
  function search(make_tbl in make_tbl_t) return vehicle_tbl_t is
    vehicle_info_cur ref_cur_t;
    vehicle_info_tbl vehicle_info_tbl_t;
    vehicles vehicle_tbl_t := vehicle_tbl_t();
    open vehicle_info_cur for select cast ( multiset (
      select v.vehicle_id
           , v.make
           , v.model
           , v.mpg
           , cast ( multiset (
               select ft.fuel_tank_id, ft.capacity from fuel_tank ft where 
v.fuel_tank_id = ft.fuel_tank_id
             ) as fuel_tank_tbl_t )
           , cast ( multiset (
               select ft.fuel_tank_id, ft.capacity from fuel_tank ft where 
v.auxillary_fuel_tank_id = ft.fuel_tank_id
             ) as fuel_tank_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.odometer_id = o.odometer_id
             ) as odometer_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.trip_odometer_id = o.odometer_id
             ) as odometer_tbl_t )
           , cast ( multiset (
               select o.odometer_id, o.mileage from odometer o where 
v.maintenance_odometer_id = o.odometer_id
             ) as odometer_tbl_t )
        from vehicle v where make in (select * from table ( make_tbl ) )
    ) as vehicle_info_tbl_t ) from dual;
    if vehicle_info_cur%isopen then
      fetch vehicle_info_cur into vehicle_info_tbl;
      if vehicle_info_tbl.count > 0 then
        for i in vehicle_info_tbl.first .. vehicle_info_tbl.last
          vehicles(i) := vehicle_info_tbl(i).get_vehicle();
        end loop;
      end if;
    end if;
    return vehicles;
end vehicle_manager;

Run the sample and its output

To run this article's example, first download the code from Resources. Then run the script JavaWorldExample.sql in SQLPlus, or within any active Oracle environment, to create the tables and types, and populate sample data into the tables. Make sure you have the appropriate create permissions within your schema to create entities such as objects and tables. You will need to modify the attributes host, database, username, and password on to reflect your development environment's specifics. Next, compile all of the .java files (javac *.java) with a J2SE (Java 2 Platform, Standard Edition) 1.3 compiler or greater. Finally, you should be able to run the JavaWorldExample by typing java JavaWorldExample into the command-line prompt. Figure 2 is a screen shot of the desired output.

Figure 2. JavaWorldExample screen shot

Java and Oracle in unison

Oracle's object features are an extremely useful facility for Java developers in overcoming the dreaded impedance mismatch between Java applications and relational databases. This article explained and demonstrated features such as type inheritance, polymorphism, and type maps. Java programmers using Oracle9i can apply this article's concepts without remodeling their domains since Oracle object types can be neatly built upon existing relational models. We showed how Java developers can take advantage of Oracle's object features by using stored procedures and JDBC. Although this may reduce your application's portability, the performance pay-off often justifies the cost.

Terry Camerlengo is a software developer in Columbus, Ohio with more than six years of experience designing and implementing enterprise software solutions. He has numerous certifications and is coauthor of The Sun Certified Java Developer Exam with J2SE 1.4 (APress, 2002; ISBN: 1590590309) Carl Johnson has fourteen years of experience designing and implementing both commercial and enterprise software solutions in C, C++, and Java.

Learn more about this topic

1 2 Page 2
Page 2 of 2