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 SELECTs and INSERTs 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.

---