< Prev - Examples - Next >

Inherited Objects

The object orientated programming in stored procedures is a little bit restricted. This example shows how to work with inherited 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
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 : INHERITED_OBJECT
create or replace type inherited_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 : INHERITED_OBJECT
create or replace type body inherited_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 'INHERITED_OBJECT'

Java Transfer Object : InheritedObject.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>INHERITED_OBJECT</em>.
 */
final public class InheritedObject 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 : INHERITED_OBJECTS
create or replace package inherited_objects
as

function get_base_object return base_object;

function get_inherited_object(i_diff in number) return base_object;

function inspect_object(i_object in base_object) return varchar2;

end inherited_objects;

PL/SQL Package Body

PL/SQL Package Body : INHERITED_OBJECTS
create or replace package body inherited_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_inherited_object(i_diff in number) return base_object
is
  o base_object;
begin
  select inherited_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_inherited_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 inherited_object)) then
    result := 'inherited_object ['|| i_object.val ||']';
  else 
    result := 'null or unknown object';
  end if;
  
  return result;
end inspect_object;

end inherited_objects;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.
Java Calling Class : InheritedObjectsFactoryApi.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.InheritedObjectsService;
import transferobject.BaseObject;
import transferobject.InheritedObject;

public class InheritedObjectsFactoryApi {
  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;
      InheritedObjectsService service = ExamplesRPCFactory.getInheritedObjectsService();

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

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

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

      // create an inherited object from the database function
      baseObject = service.getInheritedObject(diff);
      InheritedObject inheritedObject = (InheritedObject) baseObject;
      System.out.println(String.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]",
                                       inheritedObject.getClass(),
                                       inheritedObject.d,
                                       inheritedObject.ts,
                                       inheritedObject.instance,
                                       inheritedObject.dbVersion,
                                       inheritedObject.dbRelease,
                                       inheritedObject.newS,
                                       inheritedObject.newTs,
                                       inheritedObject.newD,
                                       inheritedObject.newN));

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

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

Spring API : Calling the PL/SQL package

Using Spring annotation to inject the service and call the remote service.
Java Calling Class : InheritedObjectsSpringApi.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.InheritedObjectsService;
import transferobject.BaseObject;
import transferobject.InheritedObject;

@Component
public class InheritedObjectsSpringApi {
  @Autowired
  private InheritedObjectsService inheritedObjectsService;

  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(InheritedObjectsSpringApi.class);

      InheritedObjectsSpringApi demo = ctx.getBean(InheritedObjectsSpringApi.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 = inheritedObjectsService.getBaseObject();

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

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

      // create an inherited object from the database function
      baseObject = inheritedObjectsService.getInheritedObject(diff);
      InheritedObject inheritedObject = (InheritedObject) baseObject;
      System.out.println(String.format("java-inspect : %s [d:%s/ts:%s/instance:%s/version:%d.%d/newS:%s/newTs:%s/newD:%s/newN:%d]",
                                       inheritedObject.getClass(),
                                       inheritedObject.d,
                                       inheritedObject.ts,
                                       inheritedObject.instance,
                                       inheritedObject.dbVersion,
                                       inheritedObject.dbRelease,
                                       inheritedObject.newS,
                                       inheritedObject.newTs,
                                       inheritedObject.newD,
                                       inheritedObject.newN));

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

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