Oracle8i JPublisher User's Guide Release 2 (8.1.6) Part Number A81357-01 |
Library | Service | Contents | Index

## Understanding Datatype Mappings

When you use the type mapping options (`-builtintypes`, `-lobtypes`, `-numbertypes`, and `-usertypes`), you can specify one of the following datatype mappings:

- `jdbc`

- `objectjdbc` (for `-numbertypes` only)

- `bigdecimal` (for `-numbertypes` only)

- `oracle`

These mappings affect the argument and result types JPublisher uses in the methods it generates.

The class that JPublisher generates for an object type will have `get` and `set` methods for the object's attributes. The class that JPublisher generates for a `varray` or nested table type will have `get` and `set` methods that access the elements of the array or nested table. When you use the option `-methods=true`, the class that JPublisher generates for an object type or PL/SQL package will have wrapper methods that invoke server methods of the object type or package. The mapping options control the argument and result types these methods will use.

- The JDBC mapping maps most numeric database types to standard Java types, including the primitive types short, int, float, and `double`. These types do not protect against null data, floating-point loss of precision, or integer overflow. This might not be a problem, however, if your object types are populated with non-null Java data.

- The Object JDBC mapping affects only numeric types. It is like the JDBC mapping except that JPublisher uses wrapper classes in the `java.lang` package, instead of Java primitive types. For example, JPublisher uses `java.lang.Integer` and `java.lang.Float,` instead of `int` and `float`. This allows null values, but does not protect against loss of precision or integer overflow. This is the default mapping for numeric types.

- The BigDecimal mapping, as the name suggests, maps all numeric database types to java.math.BigDecimal.The BigDecimal class supports null values and very large numbers, but conversions to and from BigDecimal are rather slow. This mapping affects only numeric types.

- The Oracle mapping maps Oracle datatypes to their corresponding oracle.sql classes. All numeric attribute types map to oracle.sql.NUMBER, which supports null values and very large numbers. Non-numeric types map to Oracle types such as oracle.sql.CHAR and oracle.sql.BLOB, and user-defined types map to `CustomDatum` classes.

The JDBC and Object JDBC mappings use familiar Java types that can be manipulated using standard Java operations. If your JDBC program is manipulating Java objects stored in the database as object types, you might prefer the JDBC or Object JDBC mapping.

The Oracle mapping is the most efficient mapping. The oracle.sql types match the Oracle internal database types as closely as possible so that little or no data conversion is required. You do not lose any information and have greater flexibility in how you process and unpack the data. The Oracle mappings for standard SQL types are the most convenient representations if you are manipulating data within the database or moving data (for example, performing `SELECT`s and `INSERT`s from one existing table to another). When data format conversion is necessary, you can use methods in the `oracle.sql.*` classes to convert to Java native types.

When you decide which mapping to use, you should remember that data format conversion is only a part of the cost of transferring data between your program and the server.

### Datatype Mapping Tables

Table 1-3 lists the mappings from SQL and PL/SQL datatypes to Java types using the Oracle and JDBC mappings. You can use all the datatypes listed as supported in Table 1-3 as argument or result types for PL/SQL methods. You can use a subset of the datatypes as object attribute types. Table 1-4 contains a list of these datatypes.

In Table 1-3, the SQL and PL/SQL Datatype column contains all possible datatypes.

The Oracle Mapping Class column contains the corresponding Java types JPublisher uses when all the type mapping options are set to `oracle`. These types are found in the `oracle.sql` package supplied by Oracle, and are designed to minimize the overhead incurred when converting database types to Java types.

The JDBC Mapping Class column contains the corresponding Java types JPublisher uses when all the type mapping options are set to `jdbc.` For standard SQL datatypes, JPublisher uses Java types specified in the JDBC specification. For SQL datatypes that are Oracle extensions, JPublisher uses the `oracle.sql` types. Refer to the Oracle8i JDBC Developer's Guide and Reference for more information on the `oracle.sql.*` package.

A few PL/SQL datatypes are not currently supported by JPublisher, because they are not currently supported by the Oracle JDBC drivers. These are designated in the table by the phrase "not directly supported by JDBC".

##### Table 1-3 PL/SQL Datatype to Oracle and Object JDBC Mapping Classes

SQL and PL/SQL Datatype | Oracle Mapping | JDBC Mapping
--- | --- | ---
CHAR, CHARACTER, LONG, STRING, VARCHAR, VARCHAR2 | oracle.sql.CHAR | java.lang.String
NCHAR, NVARCHAR2 | not directly supported by JDBC | not directly supported by JDBC
RAW, LONG RAW | oracle.sql.RAW | byte[]
BINARY_INTEGER, NATURAL, NATURALN, PLS_INTEGER, POSITIVE, POSITIVEN, SIGNTYPE, INT, INTEGER | oracle.sql.NUMBER | int
DEC, DECIMAL, NUMBER, NUMERIC | oracle.sql.NUMBER | java.math.BigDecimal
DOUBLE PRECISION, FLOAT | oracle.sql.NUMBER | double
SMALLINT | oracle.sql.NUMBER | short
REAL | oracle.sql.NUMBER | float
DATE | oracle.sql.DATE | java.sql.Timestamp
ROWID, UROWID | oracle.sql.ROWID | oracle.sql.ROWID
BOOLEAN | not directly supported by JDBC | not directly supported by JDBC
CLOB | oracle.sql.CLOB | java.sql.Clob
BLOB | oracle.sql.BLOB | java.sql.Blob
BFILE | oracle.sql.BFILE | oracle.sql.BFILE
NCLOB | not directly supported by JDBC | not directly supported by JDBC
object type | generated class | generated class
RECORD types | not directly supported by JDBC | not directly supported by JDBC
nested table, varray | generated class implemented using oracle.sql.ARRAY | java.sql.Array
REF to object type | generated class implemented using oracle.sql.REF | java.sql.Ref
REF CURSOR | java.sql.ResultSet | java.sql.ResultSet
index-by tables | not directly supported by JDBC | not directly supported by JDBC
user-defined subtypes | same as for base type | same as for base type

The Object JDBC and BigDecimal mappings, which affect numeric types only, are fully described in "Mappings For Numeric Types (-numbertypes)".

### Allowed Object Attribute Types

You can use a subset of the PL/SQL datatypes listed in Table 1-3 as object attribute types. These datatypes are contained in Table 1-4, and have the same Oracle, Object JDBC, and JDBC mappings as described in Table 1-3 and Table 1-4.

##### Table 1-4 Datatypes That Can be Used as Object Attribute Types

CHAR, VARCHAR, VARCHAR2, CHARACTER
DATE
DECIMAL, DEC, NUMBER, NUMERIC
DOUBLE PRECISION, FLOAT
INTEGER, SMALLINT, INT
REAL
RAW, LONG RAW
CLOB
BLOB
BFILE
object type
nested table, varray
REF to object type

### Using Datatypes Not Supported by JDBC

JPublisher cannot generate wrapper methods for PL/SQL methods that use datatypes not directly supported by JDBC. If you must call a PL/SQL method that uses unsupported data types (such as `NCHAR` or `BOOLEAN`), you have two choices:

- Rewrite the PL/SQL method to avoid using the type.

- Write an anonymous block that converts input types that JDBC supports into the input types used by the PL/SQL method, and converts output types used by the PL/SQL method into output types that JDBC supports. For more information on this technique, see "Example: Using Datatypes not Supported by JDBC".

### Passing OUT Parameters

Stored procedures called through SQLJ do not have the same parameter-passing behavior as ordinary Java methods. This affects the code you write when you call a wrapper method JPublisher generates.

When you call an ordinary Java method, parameters that are Java objects are passed as object references. The method can modify the object.

In contrast, when you call a stored procedure through SQLJ, a copy of each parameter is passed to the stored procedure. If the procedure modifies any parameters, copies of the modified parameters are returned to the caller. Therefore, the "before" and "after" values of a parameter that has been modified appear in separate objects.

A wrapper method JPublisher generates contains SQLJ code to call a stored procedure. The parameters to the stored procedure, as declared in your CREATE TYPE or CREATE PACKAGE declaration, have three possible parameter modes: IN, OUT, and IN OUT. The IN OUT and OUT parameters of the stored procedure are returned to the wrapper method in newly crated objects. These new values must be returned to the wrapper method's caller somehow, but assignment to the formal parameter within the wrapper method does not affect the actual parameter visible to the caller.

Passing Parameters Other Than the "this" Parameter

The simplest way to solve the problem described in the previous section is to pass an OUT or IN OUT parameter to the wrapper method in a single-element array. The array is a sort of container that holds the parameter.

- You assign the "before" value of the parameter to element 0 of an array.

- You pass the array to your wrapper method.

- The wrapper method assigns the "after" value of the parameter to element 0 of the array.

- After executing the method, you extract the "after" value from the array.

In the following example, you have an initialized variable `p` of class `Person`, and `x` is an object belonging to a JPublisher-generated class that has a wrapper method `f` taking an `IN` `OUT` `Person` argument. You create the array and pass the parameter as follows:

    Person [] pa = {p};
    x.f(pa);
    p = pa[0];

Unfortunately, this technique for passing `OUT` or `IN` `OUT` parameters requires you to add a few extra lines of code to your program for each parameter. If your stored program has many OUT or IN OUT parameters, you might prefer to call it directly using SQLJ code, rather than a wrapper method.

#### Passing the "this" Parameter

Similar problems arise when the `this` object of an instance method is modified.

The `this` object is an additional parameter that is passed in a different way. Its mode, as declared in the CREATE TYPE statement, may be IN or IN OUT. If you do not explicitly declare the mode of `this`, its mode is IN OUT if the stored procedure does not return a result, or IN if it does.

If the mode of the `this` object is IN OUT, the wrapper method must return the new value of `this`. The code generated by JPublisher processes this in two different ways:

- If the stored procedure does not return a result (in PL/SQL terminology, it is a procedure rather than a function) the new value of `this` is returned as the function result of the wrapper method. This is the usual case, because the default mode for `this` in a stored procedure that returns a result is IN.

- If the stored procedure returns a result (in PL/SQL terminology, it is a function), the wrapper method returns the result of the stored procedure as its result. The new value of `this` is returned in a single-element array, passed as an extra argument to the wrapper method and is the last argument of the wrapper method.

### Translating Overloaded Methods

PL/SQL, as with Java, lets you create overloaded methods: two or more methods with the same name, but different signatures. If you use JPublisher to generate wrapper methods for PL/SQL methods, it is possible that two overloaded methods with different signatures in PL/SQL might have identical signatures in Java. If this occurs, JPublisher changes the names of the methods to avoid generating two or more methods with the identical signature. For example, consider a PL/SQL package or object type that includes these functions:

    FUNCTION f(x INTEGER, y INTEGER) RETURN INTEGER

and

    FUNCTION f(xx FLOAT, yy FLOAT) RETURN INTEGER

In PL/SQL, these functions have different argument types. However, once translated to Java with `-mapping=oracle`, this difference disappears (both `INTEGER` and `FLOAT` map to `oracle.sql.NUMBER`).

Suppose that JPublisher generates a class for the package or object type with the command line settings `-methods=true` and `-mapping=oracle`. JPublisher responds by generating code similar to this:

      public oracle.sql.NUMBER f_1 (
        oracle.sql.NUMBER x,
        oracle.sql.NUMBER y)
      throws SQLException
      {
        /* body omitted */
      }

      public oracle.sql.NUMBER f_4 (
        oracle.sql.NUMBER xx,
        oracle.sql.NUMBER yy)
      throws SQLException
      {
        /* body omitted */
      }

Note that in this example, JPublisher names the first function `f_1` and the second function `f_4`. Each function name ends with `_<`nn`>`, where `<`nn`>` is a number assigned by JPublisher. The number has no significance of its own, but JPublisher uses it to guarantee that the names of functions with identical parameter types will be unique.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index