3
JPublisher Examples

This chapter provides examples of the output JPublisher produces when translating object types and PL/SQL packages. This chapter contains the following sections:

• Example: JPublisher Translations with Different Mappings contains examples of JPublisher output, comparing different outputs where only the values of the datatype mapping parameters are changed.

• Example: JPublisher Object Attribute Mapping illustrates an example of JPublisher output when translating different object types.

• Example: Generating a SQLData Class covers an example of JPublisher output when generating classes that implement the SQLData interface.

• Example: Extending JPublisher Classes presents an example of JPublisher output when generating a class that you will extend.

• Example: Wrappers Generated for Methods in Objects shows an example of JPublisher output when generating method wrappers for object type attributes and methods.

• Example: Wrappers Generated for Methods in Packages shows an example of JPublisher output when generating method wrappers for PL/SQL methods.

• Example: Using Classes Generated for Object Types presents a complete program using the classes that JPublisher generates for object types.

• Example: Using Classes Generated for Packages presents a complete program using the classes and method wrappers that JPublisher generates for objects and packages respectively.

• Example: Using Datatypes Not Supported by JDBC contains an example of how to write anonymous PL/SQL blocks that will let you employ datatypes not supported by JDBC.

Example: JPublisher Translations with Different Mappings

This section presents sample output from JPublisher with the only difference in the translations being the values of the datatype mapping parameters. It uses the SQL type declaration and JPublisher command line presented in "Sample JPublisher Translation" (repeated here for convenience).

Type declaration:

CREATE TYPE employee AS OBJECT
(
    name       VARCHAR2(30),
    empno      INTEGER,
    deptno     NUMBER,
    hiredate   DATE,
    salary     REAL
);

Command line (a single wrap-around line):

jpub -user=scott/tiger -dir=demo -numbertypes=objectjdbc -builtintypes=jdbc 
-package=corp -case=mixed -sql=Employee

In the following two examples, JPublisher translates the types using different datatype mapping options:

• first, with -numbertypes=jdbc and -builtintypes=jdbc

• second, with -numbertypes=oracle and -builtintypes=oracle.

JPublisher Translation with the JDBC Mapping

The SQL program presented in "Sample JPublisher Translation" is translated here by JPublisher with -numbertypes=jdbc. No other changes have been made to the command line.

Because the user requests the JDBC mapping rather than the Object JDBC mapping for numeric types, the getXXX() and setXXX() accessor methods use the type int instead of Integer and the type float instead of Float.

Following are the contents of the Employee.java file. The EmployeeRef.java file is unchanged because it does not depend on the types of the attributes.

Note: The details of method bodies generated by JPublisher might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Employee implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.EMPLOYEE";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    12, 4, 2, 91, 7
  };

  static ORADataFactory[] _factory = new ORADataFactory[5];

  static final Employee _EmployeeFactory = new Employee();
  public static ORADataFactory getORADataFactory()
  {
    return _EmployeeFactory;
  }

  /* constructor */
  protected Employee(boolean init)
  { if(init) _struct = new MutableStruct(new Object[5], _sqlType, _factory);
}
  public Employee()
  { this(true); }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(Employee o, Datum d, int sqlType) throws
SQLException
  {
    if (d == null) return null; 
     if (o == null) o = new Employee(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public String getName() throws SQLException
  { return (String) _struct.getAttribute(0); }

  public void setName(String name) throws SQLException
  { _struct.setAttribute(0, name); }


  public int getEmpno() throws SQLException
  { return ((Integer) _struct.getAttribute(1)).intValue(); }

  public void setEmpno(int empno) throws SQLException
  { _struct.setAttribute(1, new Integer(empno)); }


  public java.math.BigDecimal getDeptno() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(2); }

  public void setDeptno(java.math.BigDecimal deptno) throws SQLException
  { _struct.setAttribute(2, deptno); }


  public java.sql.Timestamp getHiredate() throws SQLException
  { return (java.sql.Timestamp) _struct.getAttribute(3); }

  public void setHiredate(java.sql.Timestamp hiredate) throws SQLException
  { _struct.setAttribute(3, hiredate); }


  public float getSalary() throws SQLException
  { return ((Float) _struct.getAttribute(4)).floatValue(); }

  public void setSalary(float salary) throws SQLException
  { _struct.setAttribute(4, new Float(salary)); }

}

JPublisher Translation with the Oracle Mapping

The SQL program presented in "Sample JPublisher Translation" is translated here by JPublisher with -numbertypes=oracle and -builtintypes=oracle. No other changes have been made to the command line.

Because the user requests Oracle type mappings, the getXXX() and setXXX() accessor methods employ the type oracle.sql.CHAR instead of String, the type oracle.sql.DATE instead of java.sql.Timestamp, and the type oracle.sql.NUMBER instead of Integer, java.math.BigDecimal, and Float.

Following are the contents of the Employee.java file. The EmployeeRef.java file is unchanged, because it does not depend on the types of the attributes.

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Employee implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.EMPLOYEE";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    12, 4, 2, 91, 7
  };

  static ORADataFactory[] _factory = new ORADataFactory[5];

  static final Employee _EmployeeFactory = new Employee();
  public static ORADataFactory getORADataFactory()
  {
    return _EmployeeFactory;
  }

  /* constructor */
  protected Employee(boolean init)
  { if(init) _struct = new MutableStruct(new Object[5], _sqlType, _factory); }
  public Employee()
  { this(true); }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(Employee o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null; 
    if (o == null) o = new Employee(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public oracle.sql.CHAR getName() throws SQLException
  { return (oracle.sql.CHAR) _struct.getOracleAttribute(0); }

  public void setName(oracle.sql.CHAR name) throws SQLException
  { _struct.setOracleAttribute(0, name); }


  public oracle.sql.NUMBER getEmpno() throws SQLException
  { return (oracle.sql.NUMBER) _struct.getOracleAttribute(1); }

  public void setEmpno(oracle.sql.NUMBER empno) throws SQLException
  { _struct.setOracleAttribute(1, empno); }


  public oracle.sql.NUMBER getDeptno() throws SQLException
  { return (oracle.sql.NUMBER) _struct.getOracleAttribute(2); }

  public void setDeptno(oracle.sql.NUMBER deptno) throws SQLException
  { _struct.setOracleAttribute(2, deptno); }


  public oracle.sql.DATE getHiredate() throws SQLException
  { return (oracle.sql.DATE) _struct.getOracleAttribute(3); }

  public void setHiredate(oracle.sql.DATE hiredate) throws SQLException
  { _struct.setOracleAttribute(3, hiredate); }


  public oracle.sql.NUMBER getSalary() throws SQLException
  { return (oracle.sql.NUMBER) _struct.getOracleAttribute(4); }

  public void setSalary(oracle.sql.NUMBER salary) throws SQLException
  { _struct.setOracleAttribute(4, salary); }

}

Example: JPublisher Object Attribute Mapping

This section provides examples of JPublisher output for a variety of object attribute types, demonstrating the various datatype mappings that JPublisher creates.

The example defines an address object (address) and then uses it as the basis of the definition of an address array (Addr_Array). The alltypes object definition also uses the address and address array objects to demonstrate the mappings that JPublisher creates for object references and arrays (see attr17, attr18, and attr19 in the alltypes object definition below).

CONNECT SCOTT/TIGER;

CREATE OR REPLACE TYPE address AS object
(
  street varchar2(50),
  city   varchar2(50),
  state  varchar2(30),
  zip    number
);

CREATE OR REPLACE TYPE Addr_Array AS varray(10) OF address;
CREATE OR REPLACE TYPE ntbl AS table OF Integer;
CREATE TYPE alltypes AS object (
  attr1  bfile,
  attr2  blob,
  attr3  char(10),
  attr4  clob,
  attr5  date,
  attr6  decimal,
  attr7  double precision,
  attr8  float,
  attr9  integer,
  attr10 number,
  attr11 numeric,
  attr12 raw(20),
  attr13 real,
  attr14 smallint,
  attr15 varchar(10),
  attr16 varchar2(10),
  attr17 address,
  attr18 ref address,
  attr19 Addr_Array,
  attr20 ntbl);

In this example, JPublisher was invoked with the following command line (a single wrap-around line):

jpub -user=scott/tiger -input=demoin -dir=demo -package=corp -mapping=objectjdbc 
-methods=false

Note: The -mapping option, while deprecated, is still supported so is therefore demonstrated. The -mapping=objectjdbc setting is equivalent to the combination of -builtintypes=jdbc, -numbertypes=objectjdbc, -lobtypes=oracle, and -usertypes=oracle. See "Mappings for All Types (-mapping)" for more information.

It is not necessary to create the demo and corp directories in advance. JPublisher will create the directories for you.

The demoin file contains these declarations:

SQL ADDRESS AS Address 
SQL ALLTYPES AS all.Alltypes 

JPublisher generates declarations of the types Alltypes and Address, because demoin explicitly lists them. It also generates declarations of the types ntbl and AddrArray, because the Alltypes type requires them.

Additionally, JPublisher generates declarations of the types AlltypesRef and AddressRef, because it generates a declaration of a reference type for each object type. A reference type is in the same package as the corresponding object type. Reference types are not listed in the INPUT file or on the command line. The Address and AddressRef types are in package corp, because -package=corp appears on the command line. The Alltypes and AlltypesRef types are in package all, because the all in all.Alltypes overrides -package=corp. The remaining types were not explicitly mentioned, so they go in package corp, which was specified on the command line.

Therefore, JPublisher creates the following files in package corp:

./demo/corp/Address.java 
./demo/corp/AddressRef.java 
./demo/corp/Ntbl.java 
./demo/corp/AddrArray.java

and the following files in package all:

./demo/all/Alltypes.java 
./demo/all/AlltypesRef.java 

Listing and Description of Address.java Generated by JPublisher

The file ./demo/corp/Address.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Address implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.ADDRESS";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    12, 12, 12, 2
  };

  static ORADataFactory[] _factory = new ORADataFactory[4];

  static final Address _AddressFactory = new Address();
  public static ORADataFactory getORADataFactory()
  {
    return _AddressFactory;
  }

  /* constructor */
  protected Address(boolean init)
  { if(init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
  public Address()
  { this(true); }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(Address o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null; 
     if (o == null) o = new Address(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public String getStreet() throws SQLException
  { return (String) _struct.getAttribute(0); }

  public void setStreet(String street) throws SQLException
  { _struct.setAttribute(0, street); }


  public String getCity() throws SQLException
  { return (String) _struct.getAttribute(1); }

  public void setCity(String city) throws SQLException
  { _struct.setAttribute(1, city); }


  public String getState() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setState(String state) throws SQLException
  { _struct.setAttribute(2, state); }


  public java.math.BigDecimal getZip() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(3); }

  public void setZip(java.math.BigDecimal zip) throws SQLException
  { _struct.setAttribute(3, zip); }

}

The Address.java file illustrates several points about Java source files. JPublisher-generated files begin with a package declaration whenever the generated class is in a named package. Note that you can specify a package in any of these ways:

• a -package parameter that you specify on the command line or in the properties file

• the AS <Java_identifier> clause in the INPUT file, where Java_identifier includes a package name

Import declarations for specific classes and interfaces mentioned by the Address class follow the package declaration.

The class definition follows the import declarations. All classes JPublisher generates are declared public.

SQLJ uses the _SQL_NAME and _SQL_TYPECODE strings to identify the SQL type matching the Address class.

The no-argument constructor is used to create the _AddressFactory object, which will be returned by getORADataFactory(). For efficiency, JPublisher also generates a protected boolean constructor for Address objects. This can be used in subclasses of Address to create uninitialized Address objects. Other Address objects are constructed by the create() method. The protected create(...,...,...) method is used to encapsulate details of the JPublisher implementation in the JPublisher-generated Address class, and to simplify the writing of user-provided subclasses. Implementation details, such as generation of the static _factory field and the _struct field, are implementation-specific and should not be referenced or exploited by any subclass of Address. (In this implementation, the _factory field is an array of factories for attributes of Address, but in this case the factories are null because none of the attribute types of Address require a factory. The _struct field holds the object data and is a MutableStruct instance.)

The toDatum() method converts an Address object to a Datum object (in this case, a STRUCT object). JDBC requires the connection argument, although it might not be logically necessary.

The getXXX() and setXXX() accessor methods use the objectjdbc mapping for numeric attributes and the jdbc mapping for other attributes. The method names are in mixed case because -case=mixed is the default.

Listing of AddressRef.java Generated by JPublisher

The file ./demo/corp/AddressRef.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class AddressRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "SCOTT.ADDRESS";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

  static final AddressRef _AddressRefFactory = new AddressRef();
  public static ORADataFactory getORADataFactory()
  {
    return _AddressRefFactory;
  }

  /* constructor */
  public AddressRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    AddressRef r = new AddressRef();
    r._ref = (REF) d;
    return r;
  }

  public Address getValue() throws SQLException
  {
     return (Address) Address.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(Address c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

The getValue() method in the AddressRef class returns the address referenced by an AddressRef object, with its proper type. The setValue() method copies the contents of the Address argument into the database Address object to which the AddressRef object refers.

Listing of Alltypes.java Generated by JPublisher

The file ./demo/all/Alltypes.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package all;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Alltypes implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.ALLTYPES";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    -13, 2004, 1, 2005, 91, 3, 8, 6, 4, 2,
    3, -2, 7, 5, 12, 12, 2002, 2006, 2003, 2003
  };

  static ORADataFactory[] _factory = new ORADataFactory[20];
  static
  {
    _factory[16] = corp.Address.getORADataFactory();
    _factory[17] = corp.AddressRef.getORADataFactory();
    _factory[18] = corp.AddrArray.getORADataFactory();
    _factory[19] = corp.Ntbl.getORADataFactory();
  }

  static final Alltypes _AlltypesFactory = new Alltypes();
  public static ORADataFactory getORADataFactory()
  {
    return _AlltypesFactory;
  }

  /* constructor */
  protected Alltypes(boolean init)
  { if(init) _struct = new MutableStruct(new Object[20], _sqlType, _factory); }
  public Alltypes()
  { this(true); }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(Alltypes o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null; 
    if (o == null) o = new Alltypes(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public oracle.sql.BFILE getAttr1() throws SQLException
  { return (oracle.sql.BFILE) _struct.getOracleAttribute(0); }

  public void setAttr1(oracle.sql.BFILE attr1) throws SQLException
  { _struct.setOracleAttribute(0, attr1); }


  public oracle.sql.BLOB getAttr2() throws SQLException
  { return (oracle.sql.BLOB) _struct.getOracleAttribute(1); }

  public void setAttr2(oracle.sql.BLOB attr2) throws SQLException
  { _struct.setOracleAttribute(1, attr2); }


  public String getAttr3() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setAttr3(String attr3) throws SQLException
  { _struct.setAttribute(2, attr3); }


  public oracle.sql.CLOB getAttr4() throws SQLException
  { return (oracle.sql.CLOB) _struct.getOracleAttribute(3); }

  public void setAttr4(oracle.sql.CLOB attr4) throws SQLException
  { _struct.setOracleAttribute(3, attr4); }


  public java.sql.Timestamp getAttr5() throws SQLException
  { return (java.sql.Timestamp) _struct.getAttribute(4); }

  public void setAttr5(java.sql.Timestamp attr5) throws SQLException
  { _struct.setAttribute(4, attr5); }


  public java.math.BigDecimal getAttr6() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(5); }

  public void setAttr6(java.math.BigDecimal attr6) throws SQLException
  { _struct.setAttribute(5, attr6); }


  public Double getAttr7() throws SQLException
  { return (Double) _struct.getAttribute(6); }

  public void setAttr7(Double attr7) throws SQLException
  { _struct.setAttribute(6, attr7); }


  public Double getAttr8() throws SQLException
  { return (Double) _struct.getAttribute(7); }

  public void setAttr8(Double attr8) throws SQLException
  { _struct.setAttribute(7, attr8); }


  public Integer getAttr9() throws SQLException
  { return (Integer) _struct.getAttribute(8); }

  public void setAttr9(Integer attr9) throws SQLException
  { _struct.setAttribute(8, attr9); }


  public java.math.BigDecimal getAttr10() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(9); }

  public void setAttr10(java.math.BigDecimal attr10) throws SQLException
  { _struct.setAttribute(9, attr10); }


  public java.math.BigDecimal getAttr11() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(10); }

  public void setAttr11(java.math.BigDecimal attr11) throws SQLException
  { _struct.setAttribute(10, attr11); }


  public byte[] getAttr12() throws SQLException
  { return (byte[]) _struct.getAttribute(11); }

  public void setAttr12(byte[] attr12) throws SQLException
  { _struct.setAttribute(11, attr12); }


  public Float getAttr13() throws SQLException
  { return (Float) _struct.getAttribute(12); }

  public void setAttr13(Float attr13) throws SQLException
  { _struct.setAttribute(12, attr13); }


  public Integer getAttr14() throws SQLException
  { return (Integer) _struct.getAttribute(13); }

  public void setAttr14(Integer attr14) throws SQLException
  { _struct.setAttribute(13, attr14); }


  public String getAttr15() throws SQLException
  { return (String) _struct.getAttribute(14); }

  public void setAttr15(String attr15) throws SQLException
  { _struct.setAttribute(14, attr15); }


  public String getAttr16() throws SQLException
  { return (String) _struct.getAttribute(15); }

  public void setAttr16(String attr16) throws SQLException
  { _struct.setAttribute(15, attr16); }


  public corp.Address getAttr17() throws SQLException
  { return (corp.Address) _struct.getAttribute(16); }

  public void setAttr17(corp.Address attr17) throws SQLException
  { _struct.setAttribute(16, attr17); }


  public corp.AddressRef getAttr18() throws SQLException
  { return (corp.AddressRef) _struct.getAttribute(17); }

  public void setAttr18(corp.AddressRef attr18) throws SQLException
  { _struct.setAttribute(17, attr18); }


  public corp.AddrArray getAttr19() throws SQLException
  { return (corp.AddrArray) _struct.getAttribute(18); }

  public void setAttr19(corp.AddrArray attr19) throws SQLException
  { _struct.setAttribute(18, attr19); }


  public corp.Ntbl getAttr20() throws SQLException
  { return (corp.Ntbl) _struct.getAttribute(19); }

  public void setAttr20(corp.Ntbl attr20) throws SQLException
  { _struct.setAttribute(19, attr20); }

}

When a declared class requires user-defined classes from another package, JPublisher generates import declarations for those user-defined classes following the import declaration for the oracle.sql package. In this case, JDBC requires the Address and AddressRef classes from package corp.

The attributes with types Address, AddressRef, AddrArray, and Ntbl require the construction of factories. The static block puts the correct factories in the _factory array.

Note: Notice that the SMALLINT SQL type for attr14 maps to the Java type short, but this maps to Integer in -numbertypes=objectjdbc mapping. This was a JPublisher implementation decision. See "Mappings For Numeric Types (-numbertypes)" for related information.

Listing of AlltypesRef.java Generated by JPublisher

The file ./demo/corp/all/AlltypesRef.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package all;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class AlltypesRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "SCOTT.ALLTYPES";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

  static final AlltypesRef _AlltypesRefFactory = new AlltypesRef();
  public static ORADataFactory getORADataFactory()
  {
    return _AlltypesRefFactory;
  }

  /* constructor */
  public AlltypesRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    AlltypesRef r = new AlltypesRef();
    r._ref = (REF) d;
    return r;
  }

  public Alltypes getValue() throws SQLException
  {
     return (Alltypes) Alltypes.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(Alltypes c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

Listing of Ntbl.java Generated by JPublisher

The file ./demo/corp/Ntbl.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;

public class Ntbl implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.NTBL";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;

  MutableArray _array;

  static final Ntbl _NtblFactory = new Ntbl();
  public static ORADataFactory getORADataFactory()
  {
    return _NtblFactory;
  }

  /* constructors */
  public Ntbl()
  {
    this((Integer[])null);
  }

  public Ntbl(Integer[] a)
  {
    _array = new MutableArray(4, a, null);
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _array.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    Ntbl a = new Ntbl();
    a._array = new MutableArray(4, (ARRAY) d, null);
    return a;
  }

  public int length() throws SQLException
  {
    return _array.length();
  }

  public int getBaseType() throws SQLException
  {
    return _array.getBaseType();
  }

  public String getBaseTypeName() throws SQLException
  {
    return _array.getBaseTypeName();
  }

  public ArrayDescriptor getDescriptor() throws SQLException
  {
    return _array.getDescriptor();
  }

  /* array accessor methods */
  public Integer[] getArray() throws SQLException
  {
    return (Integer[]) _array.getObjectArray();
  }

  public void setArray(Integer[] a) throws SQLException
  {
    _array.setObjectArray(a);
  }

  public Integer[] getArray(long index, int count) throws SQLException
  {
    return (Integer[]) _array.getObjectArray(index, count);
  }

  public void setArray(Integer[] a, long index) throws SQLException
  {
    _array.setObjectArray(a, index);
  }

  public Integer getElement(long index) throws SQLException
  {
    return (Integer) _array.getObjectElement(index);
  }

  public void setElement(Integer a, long index) throws SQLException
  {
    _array.setObjectElement(a, index);
  }

}

Listing of AddrArray.java Generated by JPublisher

JPublisher generates declarations of the type AddrArray because they are required by the Alltypes type. The file ./demo/corp/AddrArray.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jpub.runtime.MutableArray;

public class AddrArray implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.ADDR_ARRAY";
  public static final int _SQL_TYPECODE = OracleTypes.ARRAY;

  MutableArray _array;

  static final AddrArray _AddrArrayFactory = new AddrArray();
  public static ORADataFactory getORADataFactory()
  {
    return _AddrArrayFactory;
  }

  /* constructors */
  public AddrArray()
  {
    this((Address[])null);
  }

  public AddrArray(Address[] a)
  {
    _array = new MutableArray(2002, a, Address.getORADataFactory());
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _array.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    AddrArray a = new AddrArray();
    a._array = new MutableArray(2002, (ARRAY) d, Address.getORADataFactory());
    return a;
  }

  public int length() throws SQLException
  {
    return _array.length();
  }

  public int getBaseType() throws SQLException
  {
    return _array.getBaseType();
  }

  public String getBaseTypeName() throws SQLException
  {
    return _array.getBaseTypeName();
  }

  public ArrayDescriptor getDescriptor() throws SQLException
  {
    return _array.getDescriptor();
  }

  /* array accessor methods */
  public Address[] getArray() throws SQLException
  {
    return (Address[]) _array.getObjectArray(
      new Address[_array.length()]);
  }

  public void setArray(Address[] a) throws SQLException
  {
    _array.setObjectArray(a);
  }

  public Address[] getArray(long index, int count) throws SQLException
  {
    return (Address[]) _array.getObjectArray(index,
      new Address[_array.sliceLength(index, count)]);
  }

  public void setArray(Address[] a, long index) throws SQLException
  {
    _array.setObjectArray(a, index);
  }

  public Address getElement(long index) throws SQLException
  {
    return (Address) _array.getObjectElement(index);
  }

  public void setElement(Address a, long index) throws SQLException
  {
    _array.setObjectElement(a, index);
  }

}

Example: Generating a SQLData Class

This example is identical to the previous one, except that JPublisher generates a SQLData class rather than an ORAData class. The command line for this example is:

jpub -user=scott/tiger -input=demoin -dir=demo -package=corp -mapping=objectjdbc 
-usertypes=jdbc -methods=false

(This is a single wrap-around command line.)

Note: The -mapping option, while deprecated, is still supported so is therefore demonstrated. The -mapping=objectjdbc setting is equivalent to the combination of -builtintypes=jdbc, -numbertypes=objectjdbc, -lobtypes=oracle, and -usertypes=oracle; however, this command line overrides the -usertypes=oracle setting with a -usertypes=jdbc setting. See "Mappings for All Types (-mapping)" for more information about the -mapping option.

The option -usertypes=jdbc instructs JPublisher to generate classes that implement the SQLData interface. The SQLData interface supports reference and collection classes generically, using the generic types java.sql.Ref and java.sql.Array rather than using custom classes. Therefore, JPublisher generates only two classes:

./demo/corp/Address.java 
./demo/all/Alltypes.java 

Listing of Address.java Generated by JPublisher

Because we specified -usertypes=jdbc in this example, the Address class implements the java.sql.SQLData interface rather than the oracle.sql.ORAData interface. The file ./demo/corp/Address.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import java.sql.SQLData;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Address implements SQLData
{
  public static final String _SQL_NAME = "SCOTT.ADDRESS";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  private String m_street;
  private String m_city;
  private String m_state;
  private java.math.BigDecimal m_zip;

  /* constructor */
  public Address()
  {
  }

  public void readSQL(SQLInput stream, String type)
  throws SQLException
  {
      setStreet(stream.readString());
      setCity(stream.readString());
      setState(stream.readString());
      setZip(stream.readBigDecimal());
  }

  public void writeSQL(SQLOutput stream)
  throws SQLException
  {
      stream.writeString(getStreet());
      stream.writeString(getCity());
      stream.writeString(getState());
      stream.writeBigDecimal(getZip());
  }

  public String getSQLTypeName() throws SQLException
  {
    return _SQL_NAME;
  }

  /* accessor methods */
  public String getStreet()
  { return m_street; }

  public void setStreet(String street)
  { m_street = street; }


  public String getCity()
  { return m_city; }

  public void setCity(String city)
  { m_city = city; }


  public String getState()
  { return m_state; }

  public void setState(String state)
  { m_state = state; }


  public java.math.BigDecimal getZip()
  { return m_zip; }

  public void setZip(java.math.BigDecimal zip)
  { m_zip = zip; }

}

Listing of Alltypes.java Generated by JPublisher

Because -usertypes=jdbc was specified in this example, the Alltypes class implements the java.sql.SQLData interface rather than the oracle.sql.ORAData interface. Although the SQLData interface is a vendor-neutral standard, there is Oracle-specific code in the Alltypes class because it uses Oracle-specific types such as oracle.sql.BFILE and oracle.sql.CLOB.

The file ./demo/corp/Alltypes.java reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

package all;

import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import java.sql.SQLData;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Alltypes implements SQLData
{
  public static final String _SQL_NAME = "SCOTT.ALLTYPES";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  private oracle.sql.BFILE m_attr1;
  private oracle.sql.BLOB m_attr2;
  private String m_attr3;
  private oracle.sql.CLOB m_attr4;
  private java.sql.Timestamp m_attr5;
  private java.math.BigDecimal m_attr6;
  private Double m_attr7;
  private Double m_attr8;
  private Integer m_attr9;
  private java.math.BigDecimal m_attr10;
  private java.math.BigDecimal m_attr11;
  private byte[] m_attr12;
  private Float m_attr13;
  private Integer m_attr14;
  private String m_attr15;
  private String m_attr16;
  private corp.Address m_attr17;
  private java.sql.Ref m_attr18;
  private java.sql.Array m_attr19;
  private java.sql.Array m_attr20;

  /* constructor */
  public Alltypes()
  {
  }

  public void readSQL(SQLInput stream, String type)
  throws SQLException
  {
      setAttr1((oracle.sql.BFILE) 
((oracle.sql.OracleJdbc2SQLInput)stream).readOracleObject());
      setAttr2((oracle.sql.BLOB) 
((oracle.sql.OracleJdbc2SQLInput)stream).readOracleObject());
      setAttr3(stream.readString());
      setAttr4((oracle.sql.CLOB) 
((oracle.sql.OracleJdbc2SQLInput)stream).readOracleObject());
      setAttr5(stream.readTimestamp());
      setAttr6(stream.readBigDecimal());
      setAttr7(new Double(stream.readDouble()));
      if (stream.wasNull()) setAttr7(null);
      setAttr8(new Double(stream.readDouble()));
      if (stream.wasNull()) setAttr8(null);
      setAttr9(new Integer(stream.readInt()));
      if (stream.wasNull()) setAttr9(null);
      setAttr10(stream.readBigDecimal());
      setAttr11(stream.readBigDecimal());
      setAttr12(stream.readBytes());
      setAttr13(new Float(stream.readFloat()));
      if (stream.wasNull()) setAttr13(null);
      setAttr14(new Integer(stream.readInt()));
      if (stream.wasNull()) setAttr14(null);
      setAttr15(stream.readString());
      setAttr16(stream.readString());
      setAttr17((corp.Address) stream.readObject());
      setAttr18(stream.readRef());
      setAttr19(stream.readArray());
      setAttr20(stream.readArray());
  }

  public void writeSQL(SQLOutput stream)
  throws SQLException
  {
      ((oracle.sql.OracleSQLOutput)stream).writeOracleObject(getAttr1());
      ((oracle.sql.OracleSQLOutput)stream).writeOracleObject(getAttr2());
      stream.writeString(getAttr3());
      ((oracle.sql.OracleSQLOutput)stream).writeOracleObject(getAttr4());
      stream.writeTimestamp(getAttr5());
      stream.writeBigDecimal(getAttr6());
      if (getAttr7() == null)
        stream.writeBigDecimal(null);
      else
        stream.writeDouble(getAttr7().doubleValue());
      if (getAttr8() == null)
        stream.writeBigDecimal(null);
      else
        stream.writeDouble(getAttr8().doubleValue());
      if (getAttr9() == null)
        stream.writeBigDecimal(null);
      else
        stream.writeInt(getAttr9().intValue());
      stream.writeBigDecimal(getAttr10());
      stream.writeBigDecimal(getAttr11());
      stream.writeBytes(getAttr12());
      if (getAttr13() == null)
        stream.writeBigDecimal(null);
      else
        stream.writeFloat(getAttr13().floatValue());
      if (getAttr14() == null)
        stream.writeBigDecimal(null);
      else
        stream.writeInt(getAttr14().intValue());
      stream.writeString(getAttr15());
      stream.writeString(getAttr16());
      stream.writeObject(getAttr17());
      stream.writeRef(getAttr18());
      stream.writeArray(getAttr19());
      stream.writeArray(getAttr20());
  }

  public String getSQLTypeName() throws SQLException
  {
    return _SQL_NAME;
  }

  /* accessor methods */
  public oracle.sql.BFILE getAttr1()
  { return m_attr1; }

  public void setAttr1(oracle.sql.BFILE attr1)
  { m_attr1 = attr1; }


  public oracle.sql.BLOB getAttr2()
  { return m_attr2; }

  public void setAttr2(oracle.sql.BLOB attr2)
  { m_attr2 = attr2; }


  public String getAttr3()
  { return m_attr3; }

  public void setAttr3(String attr3)
  { m_attr3 = attr3; }


  public oracle.sql.CLOB getAttr4()
  { return m_attr4; }

  public void setAttr4(oracle.sql.CLOB attr4)
  { m_attr4 = attr4; }


  public java.sql.Timestamp getAttr5()
  { return m_attr5; }

  public void setAttr5(java.sql.Timestamp attr5)
  { m_attr5 = attr5; }


  public java.math.BigDecimal getAttr6()
  { return m_attr6; }

  public void setAttr6(java.math.BigDecimal attr6)
  { m_attr6 = attr6; }


  public Double getAttr7()
  { return m_attr7; }

  public void setAttr7(Double attr7)
  { m_attr7 = attr7; }


  public Double getAttr8()
  { return m_attr8; }

  public void setAttr8(Double attr8)
  { m_attr8 = attr8; }


  public Integer getAttr9()
  { return m_attr9; }

  public void setAttr9(Integer attr9)
  { m_attr9 = attr9; }


  public java.math.BigDecimal getAttr10()
  { return m_attr10; }

  public void setAttr10(java.math.BigDecimal attr10)
  { m_attr10 = attr10; }


  public java.math.BigDecimal getAttr11()
  { return m_attr11; }

  public void setAttr11(java.math.BigDecimal attr11)
  { m_attr11 = attr11; }


  public byte[] getAttr12()
  { return m_attr12; }

  public void setAttr12(byte[] attr12)
  { m_attr12 = attr12; }


  public Float getAttr13()
  { return m_attr13; }

  public void setAttr13(Float attr13)
  { m_attr13 = attr13; }


  public Integer getAttr14()
  { return m_attr14; }

  public void setAttr14(Integer attr14)
  { m_attr14 = attr14; }


  public String getAttr15()
  { return m_attr15; }

  public void setAttr15(String attr15)
  { m_attr15 = attr15; }


  public String getAttr16()
  { return m_attr16; }

  public void setAttr16(String attr16)
  { m_attr16 = attr16; }


  public corp.Address getAttr17()
  { return m_attr17; }

  public void setAttr17(corp.Address attr17)
  { m_attr17 = attr17; }


  public java.sql.Ref getAttr18()
  { return m_attr18; }

  public void setAttr18(java.sql.Ref attr18)
  { m_attr18 = attr18; }


  public java.sql.Array getAttr19()
  { return m_attr19; }

  public void setAttr19(java.sql.Array attr19)
  { m_attr19 = attr19; }


  public java.sql.Array getAttr20()
  { return m_attr20; }

  public void setAttr20(java.sql.Array attr20)
  { m_attr20 = attr20; }

}

Example: Extending JPublisher Classes

Here is an example of the scenario described in "Extending JPublisher-Generated Classes".

The following is the code that you have written for the class MyAddress.java and stored in the directory demo/corp.

Note: There is a way to code the ORADataFactory create() method to be more efficient than shown here, to ensure that an object instance is not needlessly created (if the data object is null) or needlessly re-initialized (if the data object is non-null). This is discussed in "Writing the Class that Extends the Generated Class".

package corp;

import java.sql.SQLException;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class MyAddress extends JAddress
{
  /* _SQL_NAME inherited from JAddress */
  /* _SQL_TYPECODE inherited from JAddress */

  static final MyAddress _MyAddressFactory = new MyAddress();
  public static ORADataFactory getORADataFactory()
  {
    return _MyAddressFactory;
  }

  /* constructor */
  public MyAddress()
  {
    super();
  }

  /* ORAData interface */
  /* toDatum() inherited from JAddress */

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    return create(new MyAddress(), d, sqlType);
  }

  /* accessor methods inherited from JAddress */

  /* Additional methods go here. These additional methods (not shown)
     are the reason that JAddress was extended.
  */
}
 

To have JPublisher generate code for the JAddress class, recognizing that MyAddress extends JAddress, enter this command line:

jpub -user=scott/tiger -input=demoin -dir=demo -package=corp 
 

where the demoin file includes the following:

SQL ADDRESS GENERATE JAddress AS MyAddress  

JPublisher will generate these files:

demo/corp/JAddress.java 
demo/corp/MyAddressRef.java 
 

Because an ADDRESS object will be represented in the Java program as a MyAddress instance, JPublisher generates the class MyAddressRef rather than JAddressRef.

Here is a listing of the demo/corp/JAddress.java class file generated by JPublisher:

Note: The details of method bodies that JPublisher generates might change in future releases.

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class JAddress implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.ADDRESS";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    12, 12, 12, 2
  };

  static ORADataFactory[] _factory = new ORADataFactory[4];

  static final JAddress _JAddressFactory = new JAddress();
  public static ORADataFactory getORADataFactory()
  {
    return _JAddressFactory;
  }

  /* constructor */
  protected JAddress(boolean init)
  { if(init) _struct = new MutableStruct(new Object[4], _sqlType, _factory); }
  public JAddress()
  { this(true); }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  protected ORAData create(JAddress o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null; 
    if (o == null) o = new JAddress(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public String getStreet() throws SQLException
  { return (String) _struct.getAttribute(0); }

  public void setStreet(String street) throws SQLException
  { _struct.setAttribute(0, street); }


  public String getCity() throws SQLException
  { return (String) _struct.getAttribute(1); }

  public void setCity(String city) throws SQLException
  { _struct.setAttribute(1, city); }

  public String getState() throws SQLException
  { return (String) _struct.getAttribute(2); }

  public void setState(String state) throws SQLException
  { _struct.setAttribute(2, state); }

  public java.math.BigDecimal getZip() throws SQLException
  { return (java.math.BigDecimal) _struct.getAttribute(3); }

  public void setZip(java.math.BigDecimal zip) throws SQLException
  { _struct.setAttribute(3, zip); }

}

Here is a listing of the demo/corp/MyAddressRef.java class file generated by JPublisher:

package corp;

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class MyAddressRef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "SCOTT.ADDRESS";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

  static final MyAddressRef _MyAddressRefFactory = new MyAddressRef();
  public static ORADataFactory getORADataFactory()
  {
    return _MyAddressRefFactory;
  }

  /* constructor */
  public MyAddressRef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    MyAddressRef r = new MyAddressRef();
    r._ref = (REF) d;
    return r;
  }

  public MyAddress getValue() throws SQLException
  {
     return (MyAddress) MyAddress.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(MyAddress c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

Example: Wrappers Generated for Methods in Objects

Note: The wrapper methods that JPublisher generates to invoke stored procedures are generated in SQLJ code; therefore, JPublisher-generated classes that contain wrapper methods must be processed by the SQLJ translator.

This section describes an example of JPublisher output given the definition below of a SQL type containing methods. The example defines a type Rational with numerator and denominator attributes and the following functions and procedures:

• MEMBER FUNCTION toReal: Given two integers, this function converts a rational number to a real number and returns a real number.

• MEMBER PROCEDURE normalize: Given two integers, representing a numerator and a denominator, this procedure reduces a fraction by dividing the numerator and denominator by their greatest common divisor.

• STATIC FUNCTION gcd: Given two integers, this function returns their greatest common divisor.

• MEMBER FUNCTION plus: This function adds two rational numbers and returns the result.

The code for rational.sql follows:

CREATE TYPE Rational AS OBJECT (
 numerator INTEGER,
 denominator INTEGER,
 MAP MEMBER FUNCTION toReal RETURN REAL,
 MEMBER PROCEDURE normalize,
 STATIC FUNCTION gcd(x INTEGER,
                     y INTEGER) RETURN INTEGER,
 MEMBER FUNCTION plus ( x Rational) RETURN Rational
);

CREATE TYPE BODY Rational AS

MAP MEMBER FUNCTION toReal RETURN REAL IS
-- convert rational number to real number
BEGIN
  RETURN numerator / denominator;
END toReal;

MEMBER PROCEDURE normalize IS
 g INTEGER;
BEGIN
 g := Rational.gcd(numerator, denominator);
 numerator := numerator / g;
 denominator := denominator / g;
END normalize;

STATIC FUNCTION gcd(x INTEGER,
                    y INTEGER) RETURN INTEGER IS
-- find greatest common divisor of x and y
ans INTEGER;
z INTEGER;
BEGIN
IF x < y THEN
   ans := Rational.gcd(y, x);
ELSIF (x MOD y = 0) THEN
   ans := y;
ELSE
   z := x MOD y;
   ans := Rational.gcd(y, z);
END IF;
RETURN ans;
END gcd;

MEMBER FUNCTION plus (x Rational) RETURN Rational IS
BEGIN
 return Rational(numerator * x.denominator + x.numerator * denominator,
                 denominator * x.denominator);
END plus;
END;

In this example, JPublisher was invoked with the following command line:

jpub -user=scott/tiger -sql=Rational -methods=true

The -user parameter directs JPublisher to login to the database as user scott with password tiger. The -methods parameter directs JPublisher to generate wrappers for the methods contained in the type Rational. You can omit this parameter, because -methods=true is the default.

Listing and Description of Rational.sqlj Generated by JPublisher

JPublisher generates the file Rational.sqlj. This file reads as follows:

Notes: The details of method bodies that JPublisher generates might change in future releases. Notice the release() calls, which are to avoid memory leaks related to SQLJ connection contexts. See "Releasing Connection Context Resources" for more information.

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public class Rational implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.RATIONAL";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  /* connection management */
  protected DefaultContext __tx = null;
  protected Connection __onn = null;
  public void setConnectionContext(DefaultContext ctx) throws SQLException
  { release(); __tx = ctx; }
  public DefaultContext getConnectionContext() throws SQLException
  { if (__tx==null)
    { __tx = (__onn==null) ? DefaultContext.getDefaultContext() : new 
DefaultContext(__onn); }
    return __tx;
  };
  public Connection getConnection() throws SQLException
  { return (__onn==null) ? ((__tx==null) ? null : __tx.getConnection()) : __onn; 
}
  public void release() throws SQLException
  { if (__tx!=null && __onn!=null) 
__tx.close(ConnectionContext.KEEP_CONNECTION);
    __onn = null; __tx = null;
  }

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    4, 4
  };

  static ORADataFactory[] _factory = new ORADataFactory[2];

  static final Rational _RationalFactory = new Rational(false);
  public static ORADataFactory getORADataFactory()
  {
    return _RationalFactory;
  }

  /* constructors */
  protected Rational(boolean init)
  { if (init) _struct = new MutableStruct(new Object[2], _sqlType, _factory); }
  public Rational()
  { this(true); __tx = DefaultContext.getDefaultContext(); }
  public Rational(DefaultContext c) throws SQLException
  { this(true); __tx = c; }
  public Rational(Connection c) throws SQLException
  { this(true); __onn = c; }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    if (__tx!=null && __onn!=c) release();
    __onn = c;
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  public void setFrom(Rational o) throws SQLException
  { release(); _struct = o._struct; __tx = o.__tx; __onn = o.__onn; }
  protected void setValueFrom(Rational o) { _struct = o._struct; }
  protected ORAData create(Rational o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) { if (o!=null) { o.release(); }; return null; }
    if (o == null) o = new Rational(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    o.__onn = ((STRUCT) d).getJavaSqlConnection();
    return o;
  }

  /* accessor methods */
  public Integer getNumerator() throws SQLException
  { return (Integer) _struct.getAttribute(0); }

  public void setNumerator(Integer numerator) throws SQLException
  { _struct.setAttribute(0, numerator); }


  public Integer getDenominator() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setDenominator(Integer denominator) throws SQLException
  { _struct.setAttribute(1, denominator); }


  public Integer gcd (
    Integer x,
    Integer y)
  throws SQLException
  {
    Integer __jPt_result;
    #sql [getConnectionContext()] __jPt_result = { VALUES(RATIONAL.GCD(
      :x,
      :y)) };
    return __jPt_result;
  }

  public Rational normalize ()
  throws SQLException
  {
    Rational __jPt_temp = this;
    #sql [getConnectionContext()] {
      BEGIN
      :INOUT __jPt_temp.NORMALIZE();
      END;
    };
    return __jPt_temp;
  }

  public Rational plus (
    Rational x)
  throws SQLException
  {
    Rational __jPt_temp = this;
    Rational __jPt_result;
    #sql [getConnectionContext()] {
      BEGIN
      :OUT __jPt_result := :__jPt_temp.PLUS(
      :x);
      END;
    };
    return __jPt_result;
  }

  public Float toreal ()
  throws SQLException
  {
    Rational __jPt_temp = this;
    Float __jPt_result;
    #sql [getConnectionContext()] {
      BEGIN
      :OUT __jPt_result := :__jPt_temp.TOREAL();
      END;
    };
    return __jPt_result;
  }
}

All the methods that JPublisher generates invoke the corresponding PL/SQL methods executing in the server.

JPublisher declares the sql_name for the object to be SCOTT.RATIONAL and its sql_type_code to be OracleTypes.STRUCT. By default it uses the SQLJ connection context class sqlj.runtime.ref.DefaultContext. It creates accessor methods getNumerator(), setNumerator(), getDenominator(), and setDenominator() for the object attributes numerator and denominator.

JPublisher generates source code for the gcd static function, which takes two Integer values as input and returns an Integer result. This gcd function invokes the RATIONAL.GCD stored function with IN host variables :x and :y.

JPublisher generates source code for the normalize member procedure, which defines a PL/SQL block containing an IN OUT parameter inside the SQLJ statement. The this parameter passes the values to the PL/SQL block.

JPublisher generates source code for the plus member function, which takes an object x of type Rational and returns an object of type Rational. It defines a PL/SQL block inside the SQLJ statement. The IN host variables are :x and a copy of this. The result of the function is an OUT host variable.

JPublisher generates source code for the toReal member function, which returns a Float value. It defines a host OUT variable that is assigned the value returned by the function. A copy of the this object is an IN parameter.

Example: Wrappers Generated for Methods in Packages

Note: The wrapper methods that JPublisher generates to invoke stored procedures are generated in SQLJ code; therefore, JPublisher-generated classes that contain wrapper methods must be processed by the SQLJ translator.

This section describes an example of JPublisher output given the definition below of a PL/SQL package containing methods. The example defines the package RationalP with the following functions and procedures, which manipulate the numerators and denominators of fractions.

• FUNCTION toReal: Given two integers, this function converts a rational number to a real number and returns a real number.

• PROCEDURE normalize: Given two integers (representing a numerator and a denominator), this procedure reduces a fraction by dividing the numerator and denominator by their greatest common divisor.

• FUNCTION gcd: Given two integers, this function returns their greatest common divisor.

• PROCEDURE plus: Adds two rational numbers and returns the result.

The code for RationalP.sql follows:

CREATE PACKAGE RationalP AS

 FUNCTION toReal(numerator   INTEGER,
                 denominator INTEGER) RETURN REAL;

 PROCEDURE normalize(numerator   IN OUT INTEGER,
                     denominator IN OUT INTEGER);

 FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER;

 PROCEDURE plus (n1 INTEGER, d1 INTEGER,
                 n2 INTEGER, d2 INTEGER,
                 n3 OUT INTEGER, d3 OUT INTEGER);
END rationalP;

/

CREATE PACKAGE BODY rationalP AS

  FUNCTION toReal(numerator INTEGER,
                  denominator INTEGER) RETURN real IS
  -- convert rational number to real number
  BEGIN
    RETURN numerator / denominator;
  END toReal;

  FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER IS
  -- find greatest common divisor of x and y
  ans INTEGER;
  BEGIN
  IF x < y THEN
     ans := gcd(y, x);
  ELSIF (x MOD y = 0) THEN
     ans := y;
  ELSE
     ans := gcd(y, x MOD y);
  END IF;
  RETURN ans;
  END gcd;

  PROCEDURE normalize( numerator IN OUT INTEGER,
                       denominator IN OUT INTEGER) IS
   g INTEGER;
   BEGIN
   g := gcd(numerator, denominator);
   numerator := numerator / g;
   denominator := denominator / g;
   END normalize;

  PROCEDURE plus (n1 INTEGER, d1 INTEGER,
                  n2 INTEGER, d2 INTEGER,
                  n3 OUT INTEGER, d3 OUT INTEGER) IS
  BEGIN
  n3 := n1 * d2 + n2 * d1;
  d3 := d1 * d2;
  END plus;

END rationalP;

In this example, JPublisher was invoked with the following command line:

jpub -user=scott/tiger -sql=RationalP -methods=true

The -user parameter directs JPublisher to login to the database as user scott with password tiger. The -methods parameter directs JPublisher to generate wrappers for the methods in the package RationalP. You can omit this parameter, because -methods=true is the default.

Listing and Description of RationalP.sqlj Generated by JPublisher

JPublisher generates the file RationalP.sqlj, which reads as follows:

Note: The details of method bodies that JPublisher generates might change in future releases.

import java.sql.SQLException;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public class RationalP
{

  /* connection management */
  protected DefaultContext __tx = null;
  protected Connection __onn = null;
  public void setConnectionContext(DefaultContext ctx) throws SQLException
  { release(); __tx = ctx; }
  public DefaultContext getConnectionContext() throws SQLException
  { if (__tx==null)
    { __tx = (__onn==null) ? DefaultContext.getDefaultContext() : new 
DefaultContext(__onn); }
    return __tx;
  };
  public Connection getConnection() throws SQLException
  { return (__onn==null) ? ((__tx==null) ? null : __tx.getConnection()) : __onn; 
}
  public void release() throws SQLException
  { if (__tx!=null && __onn!=null) 
__tx.close(ConnectionContext.KEEP_CONNECTION);
    __onn = null; __tx = null;
  }


  /* constructors */
  public RationalP() throws SQLException
  { __tx = DefaultContext.getDefaultContext(); }
  public RationalP(DefaultContext c) throws SQLException
  { __tx = c; }
  public RationalP(Connection c) throws SQLException
  {__onn = c; __tx = new DefaultContext(c); }

  public Integer gcd (
    Integer x,
    Integer y)
  throws SQLException
  {
    Integer __jPt_result;
    #sql [getConnectionContext()] __jPt_result = { VALUES(RATIONALP.GCD(
      :x,
      :y)) };
    return __jPt_result;
  }

  public void normalize (
    Integer numerator[],
    Integer denominator[])
  throws SQLException
  {
    #sql [getConnectionContext()] { CALL RATIONALP.NORMALIZE(
      :INOUT (numerator[0]),
      :INOUT (denominator[0])) };
  }

  public void plus (
    Integer n1,
    Integer d1,
    Integer n2,
    Integer d2,
    Integer n3[],
    Integer d3[])
  throws SQLException
  {
    #sql [getConnectionContext()] { CALL RATIONALP.PLUS(
      :n1,
      :d1,
      :n2,
      :d2,
      :OUT (n3[0]),
      :OUT (d3[0])) };
  }

  public Float toreal (
    Integer numerator,
    Integer denominator)
  throws SQLException
  {
    Float __jPt_result;
    #sql [getConnectionContext()] __jPt_result = { VALUES(RATIONALP.TOREAL(
      :numerator,
      :denominator)) };
    return __jPt_result;
  }
}

All the methods that JPublisher generates invoke the corresponding PL/SQL methods executing in the server.

By default, JPublisher uses the existing SQLJ connection context class sqlj.runtime.ref.DefaultContext and associates an instance of it with the RationalP package.

JPublisher generates source code for the gcd function, which takes two BigDecimal values--x and y--and returns a BigDecimal result. This gcd function invokes the stored function RATIONALP.GCD with IN host variables :x and :y.

JPublisher generates source code for the normalize procedure, which takes two BigDecimal values--numerator and denominator. This normalize procedure invokes the stored procedure call RATIONALP.NORMALIZE with IN OUT host variables :numerator and :denominator. Because these are IN OUT parameters, JPublisher passes their values as the first element of an array.

JPublisher generates source code for the plus procedure, which takes four BigDecimal IN parameters and two BigDecimal OUT parameters. This plus procedure invokes the stored procedure call RATIONALP.PLUS, with IN host variables :n1, :d1, :n2, and :d2. It also defines the OUT host variables :n3 and :d3. Because these are OUT variables, JPublisher passes each of their values as the first element of an array.

JPublisher generates source code for the toReal function, which takes two BigDecimal values--numerator and denominator--and returns a BigDecimal result. This toReal function invokes the stored function call RATIONALP.TOREAL, with IN host variables :numerator and :denominator.

Example: Using Classes Generated for Object Types

This section illustrates an example of how you can use the classes that JPublisher generates for object types. Suppose you have defined a SQL object type that contains attributes and methods. You use JPublisher to generate a <name>.sqlj file and a <name>Ref.java file for the object type. To enhance the functionality of the Java class generated by JPublisher, you can extend the class. After translating (if applicable) and compiling the classes, you can use them in a program. For more information on this topic, see "Use of Classes JPublisher Generates for Object Types".

The following steps demonstrate the scenario described above. In this case, define a RationalO SQL object type that contains numerator and denominator attributes and several methods to manipulate rational numbers. After using JPublisher to generate the JPubRationalO.sqlj and a RationalORef.java files, provide a file, RationalO.java, that enhances the functionality of the JPubRationalO class by extending it. After compiling the necessary files, use the classes in a test file to test the performance of the RationalO.java class.

Here are the steps, followed by listings of the files:

1. Create the SQL object type RationalO. "Listing of RationalO.sql (Definition of Object Type)" contains the code for the RationalO.sql file.

2. Use JPublisher to generate Java classes (a JPubRationalO.sqlj file and a RationalORef.java file) for the object. Use this command line:

   jpub -props=RationalO.props

   Assume the properties file RationalO.props contains the following:

   jpub.user=scott/tiger
jpub.sql=RationalO:JPubRationalO:RationalO
jpub.methods=true

   According to the properties file, JPublisher will log into the database with user name scott and password tiger. The sql parameter directs JPublisher to translate the object type RationalO (declared by RationalO.sql) and generate JPubRationalO as RationalO, where the second RationalO indicates a class that you have written (RationalO.java) that extends the functionality of the original RationalO. The value of the methods parameter indicates that JPublisher will generate classes for PL/SQL packages and wrapper methods.

   JPublisher produces the following files:

   JPubRationalO.sqlj
RationalORef.java

   See "Listing of JPubRationalO.sqlj Generated by JPublisher" and "Listing of RationalORef.java Generated by JPublisher" for listings of the JPubRationalO.sqlj and RationalORef.java files.

3. Write a file RationalO.java that enhances the functionality of JPubRationalO.sqlj by extending it. In RationalO.java, everything is inherited from the superclass except the following items. Add code to do the following:
   • Declare a factory object, _JPubRationalO.

   • Implement a getORADataFactory() method.

   • Implement a create() method.

   • Implement the constructors by calling the constructors in the superclass.

   • Add a toString() method, which is used in the last two System.out.println() calls in the test program TestRationalO.java (described in "Listing of TestRationalO.java Written by User").

   "Listing of RationalO.java Written by User" contains the code for the RationalO.java file.

4. Use SQLJ to compile/translate the necessary files. Enter the following:

   sqlj JPubRationalO.sqlj RationalO.java

   This translates and compiles JPubRationalO.sqlj and compiles the RationalO.java file.

5. Write a program TestRationalO.java that uses the RationalO class. "Listing of TestRationalO.java Written by User" contains the code.

6. Create the file connect.properties, which TestRationalO uses to determine how to connect to the database. The file reads as follows:

   sqlj.user=scott
sqlj.password=tiger
sqlj.url=jdbc:oracle:oci:@
sqlj.driver=oracle.jdbc.driver.OracleDriver

7. Compile and run TestRationalO:

   javac TestRationalO.java
java TestRationalO

   The program produces the following output:

   gcd: 5
real value: 0.5
sum: 100/100
sum: 1/1

Listing of RationalO.sql (Definition of Object Type)

This section contains the code that defines the RationalO SQL object type.

CREATE TYPE RationalO AS OBJECT (
   numerator INTEGER,
   denominator INTEGER,
   MAP MEMBER FUNCTION toReal RETURN REAL,
   MEMBER PROCEDURE normalize,
   STATIC FUNCTION gcd(x INTEGER,
                       y INTEGER) RETURN INTEGER,
   MEMBER FUNCTION plus ( x RationalO) RETURN RationalO
);

CREATE TYPE BODY RationalO AS

  MAP MEMBER FUNCTION toReal RETURN REAL IS
  -- convert rational number to real number
  BEGIN
    RETURN numerator / denominator;
  END toReal;

  MEMBER PROCEDURE normalize IS
   g BINARY_INTEGER;
  BEGIN
   g := RationalO.gcd(numerator, denominator);
   numerator := numerator / g;
   denominator := denominator / g;
  END normalize;

  STATIC FUNCTION gcd(x INTEGER,
                      y INTEGER) RETURN INTEGER IS
  -- find greatest common divisor of x and y
  ans BINARY_INTEGER;
  BEGIN
  IF x < y THEN
     ans := RationalO.gcd(y, x);
  ELSIF (x MOD y = 0) THEN
     ans := y;
  ELSE
     ans := RationalO.gcd(y, x MOD y);
  END IF;
  RETURN ans;
  END gcd;

  MEMBER FUNCTION plus (x RationalO) RETURN RationalO IS
  BEGIN
   return RationalO(numerator * x.denominator + x.numerator * denominator,
                   denominator * x.denominator);
  END plus;
END;

Listing of JPubRationalO.sqlj Generated by JPublisher

This section lists the code in JPubRationalO.java that JPublisher generates.

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public class JPubRationalO implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.RATIONALO";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  /* connection management */
  protected DefaultContext __tx = null;
  protected Connection __onn = null;
  public void setConnectionContext(DefaultContext ctx) throws SQLException
  { release(); __tx = ctx; }
  public DefaultContext getConnectionContext() throws SQLException
  { if (__tx==null)
    { __tx = (__onn==null) ? DefaultContext.getDefaultContext() : new 
DefaultContext(__onn); }
    return __tx;
  };
  public Connection getConnection() throws SQLException
  { return (__onn==null) ? ((__tx==null) ? null : __tx.getConnection()) : __onn; 
}
  public void release() throws SQLException
  { if (__tx!=null && __onn!=null) 
__tx.close(ConnectionContext.KEEP_CONNECTION);
    __onn = null; __tx = null;
  }

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    4, 4
  };

  static ORADataFactory[] _factory = new ORADataFactory[2];

  static final JPubRationalO _JPubRationalOFactory = new JPubRationalO(false);
  public static ORADataFactory getORADataFactory()
  {
    return _JPubRationalOFactory;
  }

  /* constructors */
  protected JPubRationalO(boolean init)
  { if (init) _struct = new MutableStruct(new Object[2], _sqlType, _factory); }
  public JPubRationalO()
  { this(true); __tx = DefaultContext.getDefaultContext(); }
  public JPubRationalO(DefaultContext c) throws SQLException
  { this(true); __tx = c; }
  public JPubRationalO(Connection c) throws SQLException
  { this(true); __onn = c; }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    if (__tx!=null && __onn!=c) release();
    __onn = c;
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  public void setFrom(JPubRationalO o) throws SQLException
  { release(); _struct = o._struct; __tx = o.__tx; __onn = o.__onn; }
  protected void setValueFrom(JPubRationalO o) { _struct = o._struct; }
  protected ORAData create(JPubRationalO o, Datum d, int sqlType) throws 
SQLException
  {
    if (d == null) { if (o!=null) { o.release(); }; return null; }
    if (o == null) o = new JPubRationalO(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    o.__onn = ((STRUCT) d).getJavaSqlConnection();
    return o;
  }

  /* accessor methods */
  public Integer getNumerator() throws SQLException
  { return (Integer) _struct.getAttribute(0); }

  public void setNumerator(Integer numerator) throws SQLException
  { _struct.setAttribute(0, numerator); }


  public Integer getDenominator() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setDenominator(Integer denominator) throws SQLException
  { _struct.setAttribute(1, denominator); }


  public Integer gcd (
    Integer x,
    Integer y)
  throws SQLException
  {
    Integer __jPt_result;
    #sql [getConnectionContext()] __jPt_result = { VALUES(RATIONALO.GCD(
      :x,
      :y)) };
    return __jPt_result;
  }

  public RationalO normalize ()
  throws SQLException
  {
    RationalO __jPt_temp = (RationalO) this;
    #sql [getConnectionContext()] {
      BEGIN
      :INOUT __jPt_temp.NORMALIZE();
      END;
    };
    return __jPt_temp;
  }

  public RationalO plus (
    RationalO x)
  throws SQLException
  {
    JPubRationalO __jPt_temp = this;
    RationalO __jPt_result;
    #sql [getConnectionContext()] {
      BEGIN
      :OUT __jPt_result := :__jPt_temp.PLUS(
      :x);
      END;
    };
    return __jPt_result;
  }

  public Float toreal ()
  throws SQLException
  {
    JPubRationalO __jPt_temp = this;
    Float __jPt_result;
    #sql [getConnectionContext()] {
      BEGIN
      :OUT __jPt_result := :__jPt_temp.TOREAL();
      END;
    };
    return __jPt_result;
  }
}

Listing of RationalORef.java Generated by JPublisher

This section lists the code in RationalORef.java that JPublisher generates.

Note: The details of method bodies that JPublisher generates might change in future releases.

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.REF;
import oracle.sql.STRUCT;

public class RationalORef implements ORAData, ORADataFactory
{
  public static final String _SQL_BASETYPE = "SCOTT.RATIONALO";
  public static final int _SQL_TYPECODE = OracleTypes.REF;

  REF _ref;

  static final RationalORef _RationalORefFactory = new RationalORef();
  public static ORADataFactory getORADataFactory()
  {
    return _RationalORefFactory;
  }

  /* constructor */
  public RationalORef()
  {
  }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    return _ref;
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    RationalORef r = new RationalORef();
    r._ref = (REF) d;
    return r;
  }

  public RationalO getValue() throws SQLException
  {
     return (RationalO) RationalO.getORADataFactory().create(
       _ref.getSTRUCT(), OracleTypes.REF);
  }

  public void setValue(RationalO c) throws SQLException
  {
    _ref.setValue((STRUCT) c.toDatum(_ref.getJavaSqlConnection()));
  }
}

Listing of RationalO.java Written by User

This section lists the code for the user-written file, RationalO.java, that extends the class JPubRationalO.sqlj. Note that this program accomplishes the following:

• It declares a factory object, _JPubRationalO.

• It implements a getORADataFactory() method.

• It implements a create() method.

• It implements the constructors by calling the constructors in the superclass.

• It adds a toString() method, which is used in the last two System.out.println() calls in TestRationalO.java (described in "Listing of TestRationalO.java Written by User").

Note: There is a way to code the ORADataFactory create() method to be more efficient than shown here, to ensure that an object instance is not needlessly created (if the data object is null) or needlessly re-initialized (if the data object is non-null). This is discussed in "Writing the Class that Extends the Generated Class".

import java.sql.SQLException;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;
import sqlj.runtime.ref.DefaultContext;
import java.sql.Connection;

public class RationalO extends JPubRationalO
                      implements ORAData, ORADataFactory
{
  /* _SQL_NAME inherited from JPubRationalO */

  /* _SQL_TYPECODE inherited from JPubRationalO */

  static final RationalO _RationalOFactory = new RationalO();
  public static ORADataFactory getORADataFactory()
  {
    return _RationalOFactory;
  }

  /* constructors */
  public RationalO()
  {
    super();
  }

  public RationalO(DefaultContext c) throws SQLException  
  {
    super(c);
  }
  public RationalO(Connection c) throws SQLException  
  {
    super(c);
  }

  /* ORAData interface */
  /* toDatum() inherited from JPubRationalO */

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  {
    return create(new RationalO(), d, sqlType);
  }

  /* accessor methods inherited from JPubRationalO */

  /* additional method not in base class */
  public String toString()
  {
    try
    {
       return getNumerator().toString() + "/" + getDenominator().toString();
    }
    catch (SQLException e)
    {
       return null;
    }
  }
}

Listing of TestRationalO.java Written by User

This section lists the contents of a user-written file, TestRationalO.java, that tests the performance of the RationalO class, given initial values for numerator and denominator. Note that the TestRationalO.java file also demonstrates how to do the following:

• Connect to the database by calling the Oracle.connect() method.

• Declare a Java object representing a SQL object type and initialize it by setting its attributes.

• Use the object to call server methods.

import oracle.sqlj.runtime.Oracle;
import oracle.sql.Datum;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Driver;

public class TestRationalO
{
  public static void main(String[] args)
  throws java.sql.SQLException
  {
    Oracle.connect(new TestRationalO().getClass(),
                   "connect.properties");

    RationalO r = new RationalO();
    
    Integer n = new Integer(5);
    Integer d = new Integer(10);

    r.setNumerator(n);
    r.setDenominator(d);

    Integer g = r.gcd(n, d);
    System.out.println("gcd: " + g);

    Float f = r.toreal();
    System.out.println("real value: " + f);

    RationalO s = r.plus(r); 
    System.out.println("sum: " + s);

    s = s.normalize();
    System.out.println("sum: " + s);
  } 
}

Example: Using Classes Generated for Packages

This section provides an example of how you can use the classes and method wrappers that JPublisher generates for objects and packages, respectively. Suppose you have defined a SQL object type that contains attributes and a package with methods. You use JPublisher to generate a <name>.sqlj files for the object and the package. After translating the classes you can use them in a program. For more information on this topic, see "Use of SQLJ Classes JPublisher Generates for PL/SQL Packages".

The following steps demonstrate the scenario described above. In this case, you define a Rational SQL object type that contains numerator and denominator integer attributes and a package RationalP that contains methods to manipulate rational numbers. After using JPublisher to generate the Rational.sqlj and RationalP.sqlj files, translate them with SQLJ, then use them in a test file to test the performance of the Rational and RationalP classes.

Here are the steps, followed by listings of the files:

1. Create the SQL object type Rational and package RationalP. "Listing of RationalP.sql (Definition of the Object Type and Package)" contains the SQL code for the RationalP.sql file.

2. Use JPublisher to generate a Java class and a SQLJ class (Rational.java and RationalP.sqlj) for the object and package, respectively. Use this command line:

   jpub -props=RationalP.props

   Assume the properties file RationalP.props contains the following:

   jpub.user=scott/tiger
jpub.sql=RationalP,Rational
jpub.mapping=oracle
jpub.methods=true

   According to the properties file, JPublisher will log into the database with user name scott and password tiger. The sql parameter directs JPublisher to translate the object type Rational and package RationalP (declared in RationalP.sql). JPublisher will translate the type and package according to the oracle mapping. The value of the methods parameter indicates that JPublisher will generate classes for PL/SQL packages, including wrapper methods. Since the object type Rational does not have any member functions, JPublisher will translate it into a .java file, not a .sqlj file. By using the -methods=always setting for JPublisher, however, you could have requested the generation of a .sqlj file regardless. See "Generate Classes for Packages and Wrapper Methods for Methods (-methods)" for more information.

   JPublisher produces the following files:

   Rational.java
RationalP.sqlj

3. Translate the RationalP.sqlj and Rational.java files:

   sqlj RationalP.sqlj Rational.java

4. Write a program, TestRationalP.java, that uses the RationalP class.

5. Write the file connect.properties, which TestRationalP.java uses to determine how to connect to the database. The file reads as follows:

   sqlj.user=scott
sqlj.password=tiger
sqlj.url=jdbc:oracle:oci:@
sqlj.driver=oracle.jdbc.driver.OracleDriver

6. Compile and run TestRationalP:

   javac TestRationalP.java
java TestRationalP

   The program produces the following output:

   gcd: 5
real value: 0.5
sum: 100/100
sum: 1/1

Listing of RationalP.sql (Definition of the Object Type and Package)

This section lists the contents of the file RationalP.sql, which defines the Rational SQL object type and the RationalP package.

CREATE TYPE Rational AS OBJECT (
   numerator INTEGER,
   denominator INTEGER
);
/
CREATE PACKAGE RationalP AS

 FUNCTION toReal(r Rational) RETURN REAL;

 PROCEDURE normalize(r IN OUT Rational);

 FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER;

 FUNCTION plus (r1 Rational, r2 Rational) RETURN Rational;

END rationalP;
/
CREATE PACKAGE BODY rationalP AS

  FUNCTION toReal(r Rational) RETURN real IS
  -- convert rational number to real number
  BEGIN
    RETURN r.numerator / r.denominator;
  END toReal;

  FUNCTION gcd(x INTEGER, y INTEGER) RETURN INTEGER IS
  -- find greatest common divisor of x and y
  result INTEGER;
  BEGIN
  IF x < y THEN
     result := gcd(y, x);
  ELSIF (x MOD y = 0) THEN
     result := y;
  ELSE
     result := gcd(y, x MOD y);
  END IF;
  RETURN result;
  END gcd;

  PROCEDURE normalize( r IN OUT Rational) IS
   g INTEGER;
   BEGIN
   g := gcd(r.numerator, r.denominator);
   r.numerator := r.numerator / g;
   r.denominator := r.denominator / g;
   END normalize;

  FUNCTION plus (r1 Rational,
                 r2 Rational) RETURN Rational IS
  n INTEGER;
  d INTEGER;
  result Rational;
  BEGIN
  n := r1.numerator * r2.denominator + r2.numerator * r1.denominator;
  d := r1.denominator * r2.denominator;
  result := Rational(n, d);
  RETURN result;
  END plus;

END rationalP;
/

Listing of TestRationalP.java Written by a User

The test program, TestRationalP.java, uses the package RationalP and the object type Rational, which does not have methods. The test program creates an instance of package RationalP and two Rational objects.

TestRationalP connects to the database in SQLJ style, using the Oracle.connect() method. In this example, the Oracle.connect() call specifies the file connect.properties, which contains these connection properties:

sqlj.url=jdbc:oracle:oci:@
sqlj.user=scott
sqlj.password=tiger

Following is a listing of TestRationalP.java:

import oracle.sql.Datum;
import oracle.sql.NUMBER;
import java.math.BigDecimal;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;
import java.sql.Connection;

public class TestRationalP
{

  public static void main(String[] args)
  throws java.sql.SQLException
  {
    
    Oracle.connect(new TestRationalP().getClass(),
                   "connect.properties");

    RationalP p = new RationalP();

    NUMBER n = new NUMBER(5);
    NUMBER d = new NUMBER(10);
    Rational r = new Rational();
    r.setNumerator(n);
    r.setDenominator(d);
    
    NUMBER f = p.toreal(r);
    System.out.println("real value: " + f.stringValue());

    NUMBER g = p.gcd(n, d);
    System.out.println("gcd: " + g.stringValue());

    Rational s = p.plus(r, r); 
    System.out.println("sum: " + s.getNumerator().stringValue() +
                           "/" + s.getDenominator().stringValue());

    Rational[] sa = {s};
    p.normalize(sa);
    s = sa[0];
    System.out.println("sum: " + s.getNumerator().stringValue() +
                           "/" + s.getDenominator().stringValue()); 
  } 
}

Example: Using Datatypes Not Supported by JDBC

One technique you can employ to use datatypes not supported by JDBC is to write an anonymous PL/SQL block that converts input types that JDBC supports into the input types that the PL/SQL method uses. Then convert the output types that the PL/SQL method uses into output types that JDBC supports. For more information on this topic, see "Using Datatypes Not Supported by JDBC".

The following steps offer a general outline of how you would do this. The steps assume that you used JPublisher to translate an object type with methods that contain argument types not supported by JDBC. The steps describe the changes you must make. You could make changes by extending the class or modifying the generated files. Extending the classes is generally a better technique; however, in this example, the generated files are modified.

1. In Java, convert each IN or IN OUT argument having a type that JDBC does not support to a Java type that JDBC does support.

2. Pass each IN or IN OUT argument to a PL/SQL block.

3. In the PL/SQL block, convert each IN or IN OUT argument to the correct type for the PL/SQL method.

4. Call the PL/SQL method.

5. Convert each OUT argument or IN OUT argument or function result from the type that JDBC does not support to the corresponding type that JDBC does support in PL/SQL.

6. Return each OUT argument, IN OUT argument, or function result from the PL/SQL block.

7. In Java, convert each OUT argument, IN OUT argument, or function result from the type JDBC does support to the type it does not support.

Here is an example of how to handle an argument type not directly supported by JDBC. The example converts from/to a type that JDBC does not support (Boolean/BOOLEAN) to/from one that JDBC does support (String/VARCHAR2).

The following .sql file defines an object type with methods that use boolean arguments. The methods this program uses are very simple; they serve only to demonstrate that arguments are passed correctly.

CREATE TYPE BOOLEANS AS OBJECT (
 iIn     INTEGER,
 iInOut  INTEGER,
 iOut    INTEGER,
 
 MEMBER PROCEDURE p(i1 IN BOOLEAN,
                    i2 IN OUT BOOLEAN,
                    i3 OUT BOOLEAN),

 MEMBER FUNCTION f(i1 IN BOOLEAN) RETURN BOOLEAN
);

CREATE TYPE BODY BOOLEANS AS

MEMBER PROCEDURE p(i1 IN BOOLEAN,
                   i2 IN OUT BOOLEAN,
                   i3 OUT BOOLEAN) IS
BEGIN
  iOut := iIn;

  IF iInOut IS NULL THEN
    iInOut := 0;
  ELSIF iInOut = 0 THEN
    iInOut := 1;
  ELSE
    iInOut := NULL;
  END IF;

  i3 := i1; 
  i2 := NOT i2;
END;

MEMBER FUNCTION f(i1 IN BOOLEAN) RETURN BOOLEAN IS
BEGIN
  return i1 = (iIn = 1);
END;

END;

The following .sqlj file was first generated by JPublisher and then modified by a user, according to the steps above. The wrapper methods accomplish the following:

• Convert each argument from Boolean to String in Java.

• Pass each argument into a PL/SQL block.

• Convert the argument from VARCHAR2 to BOOLEAN in PL/SQL.

• Call the PL/SQL method.

• Convert each OUT argument, IN OUT argument, or function result from BOOLEAN to VARCHAR2 in PL/SQL.

• Return each OUT argument, IN OUT argument, or function result from the PL/SQL block.

• Finally, convert each OUT argument, IN OUT argument, or function result.

Here is the code:

import java.sql.SQLException;
import java.sql.Connection;
import oracle.jdbc.OracleTypes;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ConnectionContext;
import java.sql.Connection;

public class Booleans implements ORAData, ORADataFactory
{
  public static final String _SQL_NAME = "SCOTT.BOOLEANS";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  /* connection management */
  protected DefaultContext __tx = null;
  protected Connection __onn = null;
  public void setConnectionContext(DefaultContext ctx) throws SQLException
  { release(); __tx = ctx; }
  public DefaultContext getConnectionContext() throws SQLException
  { if (__tx==null)
    { __tx = (__onn==null) ? DefaultContext.getDefaultContext() : new 
DefaultContext(__onn); }
    return __tx;
  };
  public Connection getConnection() throws SQLException
  { return (__onn==null) ? ((__tx==null) ? null : __tx.getConnection()) : __onn; 
}
  public void release() throws SQLException
  { if (__tx!=null && __onn!=null) 
__tx.close(ConnectionContext.KEEP_CONNECTION);
    __onn = null; __tx = null;
  }

  protected MutableStruct _struct;

  static int[] _sqlType =
  {
    4, 4, 4
  };

  static ORADataFactory[] _factory = new ORADataFactory[3];

  static final Booleans _BooleansFactory = new Booleans(false);
  public static ORADataFactory getORADataFactory()
  {
    return _BooleansFactory;
  }

  /* constructors */
  protected Booleans(boolean init)
  { if (init) _struct = new MutableStruct(new Object[3], _sqlType, _factory); }
  public Booleans()
  { this(true); __tx = DefaultContext.getDefaultContext(); }
  public Booleans(DefaultContext c) throws SQLException
  { this(true); __tx = c; }
  public Booleans(Connection c) throws SQLException
  { this(true); __onn = c; }

  /* ORAData interface */
  public Datum toDatum(Connection c) throws SQLException
  {
    if (__tx!=null && __onn!=c) release();
    __onn = c;
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* ORADataFactory interface */
  public ORAData create(Datum d, int sqlType) throws SQLException
  { return create(null, d, sqlType); }
  public void setFrom(Booleans o) throws SQLException
  { release(); _struct = o._struct; __tx = o.__tx; __onn = o.__onn; }
  protected void setValueFrom(Booleans o) { _struct = o._struct; }
  protected ORAData create(Booleans o, Datum d, int sqlType) throws SQLException
  {
    if (d == null) { if (o!=null) { o.release(); }; return null; }
     if (o == null) o = new Booleans(false);
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    o.__onn = ((STRUCT) d).getJavaSqlConnection();
    return o;
  }

  /* accessor methods */
  public Integer getIin() throws SQLException
  { return (Integer) _struct.getAttribute(0); }

  public void setIin(Integer iin) throws SQLException
  { _struct.setAttribute(0, iin); }


  public Integer getIinout() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setIinout(Integer iinout) throws SQLException
  { _struct.setAttribute(1, iinout); }


  public Integer getIout() throws SQLException
  { return (Integer) _struct.getAttribute(2); }

  public void setIout(Integer iout) throws SQLException
  { _struct.setAttribute(2, iout); }



/* Unable to generate method "f"
   because it uses a type that is not supported

  public <unsupported type> f (
    <unsupported type> i1)
  throws SQLException
  {
    Booleans __jPt_temp = this;
    <unsupported type> __jPt_result;
    #sql [getConnectionContext()] {
      BEGIN
      :OUT __jPt_result := :__jPt_temp.F(
      :i1);
      END;
    };
    return __jPt_result;
  } */

  public Boolean f (
    Boolean i1)
  throws SQLException
  {
    Booleans _temp = this;
    String _i1 = null;
    String _result = null;

    if (i1 != null) _i1 = i1.toString();

    #sql [getConnectionContext()] {
      DECLARE
      i1_ BOOLEAN;
      result_ BOOLEAN;
      t_ VARCHAR2(5);

      BEGIN
      i1_ := :_i1 = 'true';

      result_ := :_temp.F(i1_);

      IF result_ THEN
        t_ := 'true';
      ELSIF NOT result_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _result := t_;

      END;
    };

    if (_result == null)
       return null;
    else
       return new Boolean(_result.equals("true"));
  }


/* Unable to generate method "p"
   because it uses a type that is not supported

  public Booleans p (
    <unsupported type> i1,
    <unsupported type> i2[],
    <unsupported type> i3[])
  throws SQLException
  {
    Booleans __jPt_temp = this;
    #sql [getConnectionContext()] {
      BEGIN
      :INOUT __jPt_temp.P(
      :i1,
      :INOUT (i2[0]),
      :OUT (i3[0]));
      END;
    };
    return __jPt_temp;
  } */

  public Booleans p (
    Boolean i1,
    Boolean i2[],
    Boolean i3[])
  throws SQLException
  {
    String _i1 = (i1 == null) ? null
                              : i1.toString();

    String _i2 = (i2[0] == null) ? null
                                 : i2[0].toString();

    String _i3 = (i3[0] == null) ? null
                                 : i3[0].toString();

    Booleans _temp = this;

    #sql [getConnectionContext()] {
      DECLARE
      i1_ BOOLEAN;
      i2_ BOOLEAN;
      i3_ BOOLEAN;
      t_ VARCHAR2(5);

      BEGIN
      i1_ := :_i1 = 'true';
      i2_ := :_i2 = 'true';

      :INOUT _temp.P( i1_, i2_, i3_);

      IF i2_ THEN
        t_ := 'true';
      ELSIF NOT i2_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _i2 := t_;

      IF i3_ THEN
        t_ := 'true';
      ELSIF NOT i3_ THEN
        t_ := 'false';
      ELSE
        t_ := NULL;
      END IF;
      :OUT _i3 := t_;

      END;
    };

    i2[0] = (_i2 == null) ? null
                          : new Boolean(_i2.equals("true"));
    i3[0] = (_i3 == null) ? null
                          : new Boolean(_i3.equals("true"));
    return _temp;
  }
}

Note: Because of the semantics of SQLJ parameters, it is necessary to assign to each output parameter exactly once within the block.