< Prev - Examples - Next >

Subclassed Objects

The object orientated programming in stored procedures is a little bit restricted. This example shows how to work with derived object type and how they are represented as Java transfer object classes.

Example Description

User Defined Object Specification of Parent Object

Object Type Specification : BASE_OBJECT
/**
 * Parent object containing one member function for inspect information.
 */
create or replace type base_object force as object (
  d           date,
  ts          timestamp,
  instance    varchar2(100),
  db_version  number(9),
  db_release  number(9),
  member function val return varchar2  
) not final;

User Defined Object Body of Parent Object

Object Type Body : BASE_OBJECT
create or replace type body base_object
as

member function val return varchar2
is
begin
  return 'd:'||to_char(d, 'yyyy/mm/dd')||'/ts:'||to_char(ts, 'yyyy/mm/dd hh:mi:ss')||'/instance:'||instance||'/db_version:'||db_version||'/db_release:'||db_release; 
end val;

end;

Generated Transfer Object Type from User Defined Object 'BASE_OBJECT'

Java Transfer Object : BaseObject.java
/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, Germany, Hamburg, 2015-2018. All rights reserved.
 *
 * THIS FILE IS NOT INTENDED TO BE MODIFIED - IT WILL BE OVERWRITTEN ON EVERY RUN OF THE CONNECTOR BUILDER
 */
package transferobject;

import java.io.Serializable;
import java.sql.Date;
import java.sql.Timestamp;

/**
 * Transfer object of user defined type <em>BASE_OBJECT</em>.
 */
public class BaseObject implements Serializable {
  private static final long serialVersionUID = 1L;
  public Date d;
  public Timestamp ts;
  public String instance;
  public int dbVersion;
  public int dbRelease;
}

User Defined Object Specification of Subclassed Object

Object Type Specification : DERIVED_OBJECT
/**
 * Derived object containing overriding member function for inspect information.
 */
create or replace type derived_object force under base_object (
  new_s    varchar2(100),
  new_ts   timestamp,
  new_d    date,
  new_n    number(9),
  overriding member function val return varchar2
);

User Defined Object Body of Subclassed Object

Object Type Body : DERIVED_OBJECT
create or replace type body derived_object
as

overriding member function val return varchar2
is
begin
  return (self as base_object).val || '/new_s:'||new_s||'/new_ts:'||to_char(new_ts, 'yyyy/mm/dd hh:mi:ss')||'/new_d:'||to_char(new_d, 'yyyy/mm/dd')||'/new_n:'||new_n; 
end val;

end;

Generated Transfer Object Type from User Defined Object 'DERIVED_OBJECT'

Java Transfer Object : DerivedObject.java
/*
 * This file is generated by PL/SQL Enterprise Workbench Connector Builder.
 * PL/SQL Enterprise Workbench Copyright (c) Jan Richter, Germany, Hamburg, 2015-2018. All rights reserved.
 *
 * THIS FILE IS NOT INTENDED TO BE MODIFIED - IT WILL BE OVERWRITTEN ON EVERY RUN OF THE CONNECTOR BUILDER
 */
package transferobject;

import java.sql.Date;
import java.sql.Timestamp;

/**
 * Transfer object of user defined type <em>DERIVED_OBJECT</em>.
 */
final public class DerivedObject extends BaseObject {
  private static final long serialVersionUID = 1L;
  public String newS;
  public Timestamp newTs;
  public Date newD;
  public int newN;
}

PL/SQL Package Specification

PL/SQL Package Specification : SUBCLASSED_OBJECTS
create or replace package subclassed_objects
/**
 * Demonstration of calling a stored procedure with in/put parameter of collections of scalar element types.
 *
 * @param i_col_number Table-Collection of integer input values.
 * @param io_col_timestamp Table-Collection of timestamp value.
 * @param o_col_varchar Varray(5)-Collection of varchar2 out put values.
 */
as

/**
 * Generating an object of type base_object.
 *
 * @return Object of type base_object.
 */
function get_base_object return base_object;

/**
 * Generating an object of type derived_object.
 *
 * @param i_diff Offset of field values of type derived_object.
 * @return Object of type derived_object.
 */
function get_derived_object(i_diff in number) return base_object;

/**
 * Calling member function 'val' of base_object or derived_object.
 *
 * @param base_object Object value to inspect.
 * @return Result of member function 'val'.
 */
function inspect_object(i_object in base_object) return varchar2;

end subclassed_objects;

PL/SQL Package Body

PL/SQL Package Body : SUBCLASSED_OBJECTS
create or replace package body subclassed_objects
as

function get_base_object return base_object
is
  o base_object;
begin
  o := base_object(null, null, null, null, null);
  o.d := sysdate;
  o.ts := systimestamp;
  select sys_context('USERENV','INSTANCE_NAME')
    into o.instance
    from dual;
  o.db_version := DBMS_DB_VERSION.VERSION;
  o.db_release := DBMS_DB_VERSION.RELEASE;
  return o;
end get_base_object;

function get_derived_object(i_diff in number) return base_object
is
  o base_object;
begin
  select derived_object(sysdate, 
                        systimestamp, 
                        sys_context('USERENV','INSTANCE_NAME'), 
                        DBMS_DB_VERSION.VERSION, 
                        DBMS_DB_VERSION.RELEASE, 
                        dbms_random.string('A', 100), 
                        systimestamp+i_diff, 
                        sysdate-i_diff, 
                        DBMS_RANDOM.value(0,999999999))
    into o
    from dual;  
  return o; 
end get_derived_object;

function inspect_object(i_object in base_object) return varchar2
is
  result varchar2(1000);
begin
  if (i_object is of(only base_object)) then
    result := 'base_object ['|| i_object.val ||']';
  elsif (i_object is of(only derived_object)) then
    result := 'derived_object ['|| i_object.val ||']';
  else 
    result := 'null or unknown object';
  end if;
  
  return result;
end inspect_object;

end subclassed_objects;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.
Java Calling Class : SubclassedObjectsFactoryApi.java
package plsql_workbench_examples.factoryapi;

import java.sql.Date;
import java.sql.Timestamp;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import factory.ExamplesRPCFactory;
import service.SubclassedObjectsService;
import transferobject.BaseObject;
import transferobject.DerivedObject;

public class SubclassedObjectsFactoryApi {
  public static void main(String[] args) {
    System.setProperty("baseURL", "http://localhost:9090/embeddedTomcat");

    // Initialize factory class once - after that all services are accessible calling throw the static factory api.
    try (AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(ExamplesRPCFactory.class)) {
      runDemo();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void runDemo() throws Exception {
    int diff = 10;
    SubclassedObjectsService service = ExamplesRPCFactory.getSubclassedObjectsService();

    // create a base object from the database function
    BaseObject baseObject = service.getBaseObject();

    System.out.format("java-inspect: %s [d:%s/ts:%s/instance:%s/version:%d.%d]%n",
                      baseObject.getClass(),
                      baseObject.d,
                      baseObject.ts,
                      baseObject.instance,
                      baseObject.dbVersion,
                      baseObject.dbRelease);

    System.out.println("pl/sql inspect : " + service.inspectObject(baseObject));

    // create an derived object from the database function
    baseObject = service.getDerivedObject(diff);
    DerivedObject derivedObject = (DerivedObject) baseObject;
    System.out.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]%n",
                      derivedObject.getClass(),
                      derivedObject.d,
                      derivedObject.ts,
                      derivedObject.instance,
                      derivedObject.dbVersion,
                      derivedObject.dbRelease,
                      derivedObject.newS,
                      derivedObject.newTs,
                      derivedObject.newD,
                      derivedObject.newN);

    System.out.println("pl/sql inspect : " + service.inspectObject(baseObject));

    // inspect of object instantiated in java
    derivedObject = new DerivedObject();
    derivedObject.d = new Date(System.currentTimeMillis());
    derivedObject.instance = "any string";
    derivedObject.ts = new Timestamp(System.currentTimeMillis());
    derivedObject.dbVersion = 88;
    derivedObject.dbRelease = 99;
    derivedObject.newD = new Date(0);
    derivedObject.newN = 1234567890;
    derivedObject.newS = "another string";
    derivedObject.newTs = new Timestamp((long) (Math.random() * 1000L * 3600L * 24L * 365L * 50L));
    System.out.println("pl/sql inspect () : " + service.inspectObject(derivedObject));
  }
}

Spring API : Calling the PL/SQL package

Using Spring annotation to inject the service and call the remote service.
Java Calling Class : SubclassedObjectsSpringApi.java
package plsql_workbench_examples.springapi;

import java.sql.Date;
import java.sql.Timestamp;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.stereotype.Component;

import service.SubclassedObjectsService;
import transferobject.BaseObject;
import transferobject.DerivedObject;

@Component
public class SubclassedObjectsSpringApi {
  @Autowired
  private SubclassedObjectsService subclassedObjectsService;

  public static void main(String[] args) {
    System.setProperty("baseURL", "http://localhost:9090/embeddedTomcat");

    // Register Spring Beans, Spring Context and call demo method 
    try (AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext("factory")) {
      ctx.register(SubclassedObjectsSpringApi.class);

      SubclassedObjectsSpringApi demo = ctx.getBean(SubclassedObjectsSpringApi.class);
      demo.runDemo();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }

  private void runDemo() {
    try {
      int diff = 10;

      // create a base object from the database function
      BaseObject baseObject = subclassedObjectsService.getBaseObject();

      System.out.format("java-inspect: %s [d:%s/ts:%s/instance:%s/version:%d.%d]%n",
                        baseObject.getClass(),
                        baseObject.d,
                        baseObject.ts,
                        baseObject.instance,
                        baseObject.dbVersion,
                        baseObject.dbRelease);

      System.out.println("pl/sql inspect : " + subclassedObjectsService.inspectObject(baseObject));

      // create an derived object from the database function
      baseObject = subclassedObjectsService.getDerivedObject(diff);
      DerivedObject derivedObject = (DerivedObject) baseObject;
      System.out.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]%n",
                        derivedObject.getClass(),
                        derivedObject.d,
                        derivedObject.ts,
                        derivedObject.instance,
                        derivedObject.dbVersion,
                        derivedObject.dbRelease,
                        derivedObject.newS,
                        derivedObject.newTs,
                        derivedObject.newD,
                        derivedObject.newN);

      System.out.println("pl/sql inspect : " + subclassedObjectsService.inspectObject(baseObject));

      // inspect of object instantiated in java
      derivedObject = new DerivedObject();
      derivedObject.d = new Date(System.currentTimeMillis());
      derivedObject.instance = "any string";
      derivedObject.ts = new Timestamp(System.currentTimeMillis());
      derivedObject.dbVersion = 88;
      derivedObject.dbRelease = 99;
      derivedObject.newD = new Date(0);
      derivedObject.newN = 1234567890;
      derivedObject.newS = "another string";
      derivedObject.newTs = new Timestamp((long) (Math.random() * 1000L * 3600L * 24L * 365L * 50L));
      System.out.println("pl/sql inspect () : " + subclassedObjectsService.inspectObject(derivedObject));
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

< Prev - Examples - Next >