JPublisher Examples

Example: Using Classes Generated for Packages

This section illustrates an example of how you can use the classes and method wrappers that JPublisher generates for objects and packages respectively. Suppose that 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 "Using 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, you translate them with SQLJ, then use them in a test file to test the performance of the Rational and RationalP classes.

The sections following the steps list the contents of the files the steps mention.

Create the SQL object type Rational and package RationalP. "Listing of RationalP.sql to Create the Object Type and Package" contains the SQL code for the RationalP.sql file.
Use JPublisher to generate SQLJ classes (RationalP.sqlj and Rational.sqlj files) for the object and package. Use this command line:
```
jpub -props=RationalP.props 
```
where the properties file RationalP.props contains:
```
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 and wrapper methods.

JPublisher produces the files:
```
RationalP.sqlj
Rational.sqlj
 
```
Translate the RationalP.sqlj and Rational.sqlj files:
```
sqlj RationalP.sqlj Rational.sqlj 
```
Write a program, TestRationalP.java that uses the RationalP class.
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:oci8:@
sqlj.driver=oracle.jdbc.driver.OracleDriver
```

Compile, then run TestRationalP.java:

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 to Create the Object Type and Package

This section lists the contents of the file RationalP.sql which creates 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 a couple of Rational objects.

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

sqlj.url=jdbc:oracle:oci8:@
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 oracle.jdbc.driver.OracleConnection;

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()); 
  } 
}