Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5
A64684-01

Library
Product
Contents
Index

Prev Chap Top Next

Object, Collection, and CustomDatum Samples

This section has examples that feature support of user-defined objects and collections and general use of the oracle.sql.CustomDatum interface. (This interface is discussed in "About Custom Java Classes and the CustomDatum Interface".)

The object and collection samples are located in the following directory: 

[Oracle Home]/sqlj/demo/Objects

Definition of Object and Collection Types

The following SQL script defines Oracle object types, Oracle collection types, and tables used in the object and collection sample applications below. In particular, it defines the following:

Oracle Objects--ObjectDemo.sqlj

Following is the ObjectDemo.sqlj source code. This uses definitions from the SQL script in "Definition of Object and Collection Types".

Use of objects is discussed in "Strongly Typed Objects and References in SQLJ Executable Statements". 

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

public class ObjectDemo
{

/* Global variables */

static String uid      = "scott";              /* user id */
static String password = "tiger";              /* password */
static String url      = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */

public static void main(String [] args)
{

  System.out.println("*** SQLJ OBJECT DEMO ***");

  try {

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(ObjectDemo.class, "connect.properties");


  /* DML operations on single objects */

        selectAttributes(); /* Select Person attributes */
        updateAttributes(); /* Update Address attributes */

        selectObject();     /* Select a person object */
        insertObject();     /* Insert a new person object */
        updateObject();     /* Update an address object */

        selectRef();        /* Select Person objects via REFs */
        updateRef();        /* Update Person objects via REFs */

        #sql { rollback work };

  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ OBJECT DEMO ***");
}

/**
Iterator for selecting a person's data.
*/

#sql static iterator PData (String name, String address, int ssn);


/**
Selecting individual attributes of objects 
*/
static void selectAttributes()

{
  /*
   Select individual scalar attributes of a person object 
   into host types such as int, String
   */

        String name;
        String address;
        int ssn;

        PData iter;

        System.out.println("Selecting person attributes.");
  try {
    #sql iter =
    {
    select p.name as "name", p.ssn as "ssn", 
           p.addr.street || ', ' || p.addr.city 
                         || ', ' || p.addr.state 
                         || ', ' || p.addr.zip_code as "address"
    from persons p
    where p.addr.state = 'AU' OR p.addr.state = 'CA' };

    while (iter.next())
    {
        System.out.println("Selected person attributes:\n" +
        "name = "    + iter.name() + "\n" +
        "ssn = "     + iter.ssn()  + "\n" +
        "address = " + iter.address() );
    }
  } catch (SQLException exn) {
    System.out.println("SELECT failed with "+exn);
  }
}


/**
Updating individual attributes of an object
*/

static void updateAttributes()
{

  /* 
   * Update a person object to have a new address.  This example
   * illustrates the use of constructors in SQL to create object types
   * from scalars.
   */

  String name       = "Ludwig van Beethoven";
  String new_street = "New Street";
  String new_city   = "New City";
  String new_state  = "WA";
  String new_zip    = "53241";
  
  System.out.println("Updating person attributes..");

  try { #sql {

        update persons 
        set addr = Address(:new_street, :new_city, :new_state, :new_zip)
        where name = :name };

        System.out.println("Updated address attribute of person."); 

      } catch (SQLException exn) {

        System.out.println("UPDATE failed with "+exn);
  }
}


/**
Selecting an object 
*/

static void selectObject()
{
    /*
     * When selecting an object from a typed table like persons 
     * (as opposed to an object column in a relational table, e.g.,
     * office_addr in table employees), you have to use the VALUE
     * function with a table alias.
     */

    Person p;
    System.out.println("Selecting the Ludwig van Beethoven person object.");

    try { #sql {
            select value(p) into :p
               from persons p
            where p.addr.state = 'WA'  AND p.name = 'Ludwig van Beethoven' };

    printPersonDetails(p);

    /* 
     * Memory for the person object was automatically allocated,
     * and it will be automatically garbage collected when this
     * method returns.
     */

   } catch (SQLException exn) { 
     System.out.println("SELECT failed with "+exn);
   }
   catch (Exception exn)
   {
      System.out.println("An error occurred");
      exn.printStackTrace();
   }
}

/**
Inserting an object
*/

static void insertObject() 
{

        String new_name   = "NEW PERSON";
        int    new_ssn    = 987654;
        String new_street = "NEW STREET";
        String new_city   = "NEW CITY";
        String new_state  = "NS";
        String new_zip    = "NZIP";

  /*
   * Insert a new person object into the persons table
   */
         try {
           #sql {
                   insert into persons
                   values (person(:new_name, :new_ssn,
                   address(:new_street, :new_city, :new_state, :new_zip)))
            };

            System.out.println("Inserted person object NEW PERSON."); 

          } catch (SQLException exn) { 
            System.out.println("INSERT failed with "+exn); } 
}

/**
Updating an object 
*/

static void updateObject() 
{

        Address addr;
        Address new_addr;
        int empno = 1001;

try {
     #sql {
         select office_addr
         into :addr
         from employees
         where empnumber = :empno };
  System.out.println("Current office address of employee 1001:");

  printAddressDetails(addr);

     /* Now update the street of address */

        String street ="100 Oracle Parkway";
        addr.setStreet(street);

     /* Put updated object back into the database */

   try
   {
      #sql {
         update employees
         set office_addr = :addr
         where empnumber = :empno };
     
       System.out.println
           ("Updated employee 1001 to new address at Oracle Parkway.");

      /* Select new address to verify update */
      
      try
      {
       #sql {
            select office_addr
            into :new_addr
            from employees
            where empnumber = :empno };
      
        System.out.println("New office address of employee 1001:");
        printAddressDetails(new_addr);

      } catch (SQLException exn) {
          System.out.println("Verification SELECT failed with "+exn);
      }
      
   } catch (SQLException exn) {
       System.out.println("UPDATE failed with "+exn); 
   }

} catch (SQLException exn) {
    System.out.println("SELECT failed with "+exn);
}


  /* No need to free anything explicitly. */

}

/**
Selecting an object via a REF
*/

static void selectRef()
{ 

  String name = "Ludwig van Beethoven";
  Person mgr;

  System.out.println("Selecting manager of "+name+" via a REF.");

  try { 
     #sql {
      select deref(manager)
      into :mgr
      from employees e
      where e.person_data.name = :name
  } ;

     System.out.println("Current manager of "+name+":");
     printPersonDetails(mgr);

  } catch (SQLException exn) {
     System.out.println("SELECT REF failed with "+exn); }
}


/**
Updating a REF to an object
*/

static void updateRef()
{

  int empno = 1001;
  String new_manager = "NEW PERSON";

  System.out.println("Updating manager REF."); 
  try {
    #sql {
      update employees
      set manager = (select ref(p) from persons p where p.name = :new_manager)
      where empnumber = :empno };

    System.out.println("Updated manager of employee 1001. Selecting back");

  } catch (SQLException exn) {
    System.out.println("UPDATE REF failed with "+exn);
  }

  /* Select manager back to verify the update */
  Person manager;

  try { 
     #sql {
      select deref(manager)
      into :manager
      from employees e
      where empnumber = :empno
  } ;

     System.out.println("Current manager of "+empno+":");
     printPersonDetails(manager);

  } catch (SQLException exn) {
     System.out.println("SELECT REF failed with "+exn); }

}

/**
Utility functions
*/

/**** Print the attributes of a person object ****/
   static void printPersonDetails(Person p) throws SQLException

   {
       if (p == null) {
       System.out.println("NULL Person");
       return;

       }

       System.out.print("Person ");
       System.out.print( (p.getName()==null) ? "NULL name" : p.getName() );
       System.out.print( ", SSN=" + ((p.getSsn()==null) ? "-1" :  
                     p.getSsn().toString()) );
       System.out.println(":");
       printAddressDetails(p.getAddr());
   }

/**** Print the attributes of an address object ****/

   static void printAddressDetails(Address a) throws SQLException
   { 

       if (a == null)  {
         System.out.println("No Address available.");
         return;
       }

     String street = ((a.getStreet()==null) ? "NULL street" : a.getStreet()) ;
     String city = (a.getCity()==null) ? "NULL city" : a.getCity();
     String state = (a.getState()==null) ? "NULL state" : a.getState();
     String zip_code = (a.getZipCode()==null) ? "NULL zip" : a.getZipCode();

     System.out.println("Street: '" + street + "'\n" +
                        "City:   '" + city   + "'\n" +
                        "State:  '" + state  + "'\n" +
                        "Zip:    '" + zip_code + "'" );
}

/**** Populate a person object with data ****/

static Person createPersonData(int i) throws SQLException
{
   Person p = new Person();

   /* create and load the dummy data into the person */
   p.setName("Person " + i);
   p.setSsn(new BigDecimal(100000 + 10 * i));

   Address a = new Address();
   p.setAddr(a);
   a.setStreet("Street " + i);
   a.setCity("City " + i);
   a.setState("S" + i);
   a.setZipCode("Zip"+i);

  /* Illustrate NULL values for objects and individual attributes */

  if (i == 2)
    {
      /* Pick this person to have a NULL ssn and a NULL address */
      p.setSsn(null); 
      p.setAddr(null);
    }
  return p;
}

}

Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj

Following is the source code for NestedDemo1.sqlj and NestedDemo2.sqlj. These use definitions from the SQL script in "Definition of Object and Collection Types".

Use of nested tables is discussed in "Strongly Typed Collections in SQLJ Executable Statements".

NestedDemo1.sqlj

// --------------Begin of NestedDemo1.sqlj -------------------------

// Import Useful classes 

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import oracle.sql.* ;
import oracle.sqlj.runtime.Oracle;

public class NestedDemo1
{
 //  The Nested Table is accessed using the ModuleIter 
 //  The ModuleIter is defined as Named Iterator 

  #sql public static iterator ModuleIter(int moduleId , 
                                         String moduleName , 
                                         String moduleOwner);

 // Get the Project Details using the ProjIter defined as 
 // Named Iterator. Notice the use of ModuleIter below:

  #sql public static iterator ProjIter(int id, 
                                       String name, 
                                       String owner, 
                                       Date start_date, 
                                       ModuleIter modules);

   public static void main(String[] args) 
   {
     try {
       /* if you're using a non-Oracle JDBC Driver, add a call here to
          DriverManager.registerDriver() to register your Driver
       */

       /* Connect to the database */
       Oracle.connect(NestedDemo1.class, "connect.properties");
       
       listAllProjects();  // uses named iterator
     } catch (Exception e) { 
          System.err.println( "Error running ProjDemo: " + e );
     }
   }
   
   
   public static void listAllProjects() throws SQLException
   {
     System.out.println("Listing projects...");

     // Instantiate and initilaise the iterators 

     ProjIter projs = null;
     ModuleIter  mods = null;
     #sql projs = {SELECT a.id, 
                          a.name, 
                          initcap(a.owner.ename) as "owner", 
                          a.start_date,
                          CURSOR (
                          SELECT b.module_id AS "moduleId",
                                 b.module_name AS "moduleName",
                                 initcap(b.module_owner.ename) AS "moduleOwner"
                           FROM TABLE(a.modules) b) AS "modules"  
                    FROM projects a };
   
     // Display Project Details 
   
     while (projs.next()) {
       System.out.println( "\n'" + projs.name() + "' Project Id:" 
                + projs.id() + " is owned by " +"'"+ projs.owner() +"'"
                + " start on "  
                + projs.start_date());
              
    // Notice below the modules from the Projiter are assigned to the module
    // iterator variable 

       mods = projs.modules() ;

       System.out.println ("Modules in this Project are : ") ;

    // Display Module details 

       while(mods.next()) { 
         System.out.println ("  "+ mods.moduleId() + " '"+ 
                              mods.moduleName() + "' owner is '" +
                              mods.moduleOwner()+"'" ) ;
       }                    // end of modules 
       mods.close();
     }                      // end of projects 
     projs.close();
   }
}

NestedDemo2.sqlj

// --------------Begin of NestedDemo2.sqlj -------------------------
// Demonstrate DML on Nested Tables in SQLJ 
// Import Useful classes 

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import oracle.sql.*;
import oracle.sqlj.runtime.Oracle;


public class NestedDemo2
{
   #sql public static iterator ModIter(int, String, String) ;

   static ModuletblT mymodules=null;

   public static void main(String[] args) 
   {
     try { 
       /* if you're using a non-Oracle JDBC Driver, add a call here to
          DriverManager.registerDriver() to register your Driver
       */

       /* get connect to the database */
       Oracle.connect(NestedDemo2.class, "connect.properties");
       
       cleanupPreviousRuns();
       /* 
       // insert new project into Projects table 
       // get the owner details from 'participant'
       */

       String ProjName ="My project";
       int projid = 123;
       String Owner = "MARY TURNER";
       insertProject(projid, ProjName, Owner); // insert new project

       /* 
       // Insert another Project 
       // Both project details and Nested table details are inserted 
       */ 
       projid = 600;
       insertProject2(projid);        

       /* Insert a new module for the above project  */ 
       insertModules(projid); 

       /* Update the nested table row */
       projid=600;
       String moduleName = "Module 1";
       String setownerto = "JULIE ADAMS";
       assignModule(projid, moduleName, setownerto); 

      /* delete all the modules for the given project  
      // which are unassigned 
      */ 
        
       projid=600;
       deleteUnownedModules(projid);
 
      /* Display Modules  for 500 project */

       getModules(500) ;

       // Example to use nested table as host variable using a
       // JPub-generated SQL 'Array' type

       getModules2(600);


     } catch (Exception e) { 
          System.err.println( "Error running ProjDemo: " + e );
     }
   }
   
  /* insertProject 
  // inserts  into projects table 
  */

  public static void insertProject(int id, String projectName, String ownerName) 
        throws SQLException 
  {
    System.out.println("Inserting Project '" + id + " "+projectName +
         "'  owner is '" + ownerName + "'");

    try {
      #sql { INSERT INTO Projects(id, name,owner,start_date,duration) 
             SELECT :id, :projectName, ref(p), '12-JAN-97', 30 
             FROM participants p WHERE ename = :ownerName };

    } catch ( Exception e) {
      System.out.println("Error:insertProject");
      e.printStackTrace();
    }
  }

  /* insert Project 2
  // Insert Nested table details along with master details 
  */

  public static void insertProject2(int id)  throws Exception 
  {
    System.out.println("Inserting Project with Nested Table details..");
    try {
      #sql { INSERT INTO Projects(id,name,owner,start_date,duration, modules) 
             VALUES ( 600, 'Ruby', null, '10-MAY-98',  300, 
            moduletbl_t(module_t(6001, 'Setup ', null, '01-JAN-98', 100),
                        module_t(6002, 'BenchMark', null, '05-FEB-98',20) ,
                        module_t(6003, 'Purchase', null, '15-MAR-98', 50),
                        module_t(6004, 'Install', null, '15-MAR-98',44),
                        module_t(6005, 'Launch', null,'12-MAY-98',34))) };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2");
      e.printStackTrace();
    }

    // Assign project owner to this project 

    try {
      #sql { UPDATE Projects pr
          SET owner=(SELECT ref(pa) FROM participants pa WHERE pa.empno = 7698)
             WHERE pr.id=600 };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2:update");
      e.printStackTrace();
    }
  }

  /* insertModules 
  // Illustrates accessing the nested table using the TABLE construct 
  */
  public static void insertModules(int projId)  throws Exception 
  {
    System.out.println("Inserting Module 6009 for Project " + projId); 
    try {
      #sql { INSERT INTO TABLE(SELECT modules FROM projects  
                             WHERE id = :projId) 
             VALUES (6009,'Module 1', null, '12-JAN-97', 10)};

    } catch(Exception e) {
      System.out.println("Error:insertModules");
      e.printStackTrace();
    }
  }

  /* assignModule 
  // Illustrates accessing the nested table using the TABLE construct 
  // and updating the nested table row 
  */
  public static void assignModule
         (int projId, String moduleName, String modOwner)  throws Exception 
  {
    System.out.println("Update:Assign '"+moduleName+"' to '"+ modOwner+"'");

    try {
      #sql {UPDATE TABLE(SELECT modules FROM projects WHERE id=:projId) m
             SET m.module_owner=(SELECT ref(p) 
                                 FROM participants p WHERE p.ename= :modOwner) 
             WHERE m.module_name = :moduleName };
    } catch(Exception e) {
      System.out.println("Error:insertModules");
      e.printStackTrace();
    }
  }

  /* deleteUnownedModules 
  // Demonstrates deletion of the Nested table element 
  */

  public static void deleteUnownedModules(int projId)
  throws Exception 
  {
    System.out.println("Deleting Unowned Modules for Project " + projId);
    try {
      #sql { DELETE TABLE(SELECT modules FROM projects WHERE id=:projId) m
             WHERE m.module_owner IS NULL };
    } catch(Exception e) {
      System.out.println("Error:deleteUnownedModules");
      e.printStackTrace();
    }
  }

  public static void getModules(int projId)
  throws Exception 
  {
    System.out.println("Display modules for project " + projId ) ;

    try {
      ModIter  miter1 ;
      #sql miter1={SELECT m.module_id, m.module_name, m.module_owner.ename 
                   FROM TABLE(SELECT modules 
                            FROM projects WHERE id=:projId) m };
        int mid=0;
        String mname =null;
        String mowner =null;
      while (miter1.next()) 
      {
        #sql { FETCH :miter1 INTO :mid, :mname, :mowner } ;
        System.out.println ( mid + " " + mname + " "+mowner) ;
      }
    } catch(Exception e) {
      System.out.println("Error:getModules");
      e.printStackTrace();
    }
  }

  public static void getModules2(int projId)
  throws Exception 
  {
    System.out.println("Display modules for project " + projId ) ;

    try {
      #sql {SELECT modules INTO :mymodules 
                            FROM projects  WHERE id=:projId };
      showArray(mymodules) ;
    } catch(Exception e) {
      System.out.println("Error:getModules2");
      e.printStackTrace();
    }
  }

  public static void showArray(ModuletblT a) 
  {
    try {
      if ( a == null )
        System.out.println( "The array is null" );
      else {
        System.out.println( "printing ModuleTable array object of size "
                            +a.length());
        ModuleT[] modules = a.getArray();

        for (int i=0;i<modules.length; i++) {
          ModuleT module = modules[i];
          System.out.println("module "+module.getModuleId()+
                ", "+module.getModuleName()+
                ", "+module.getModuleStartDate()+
                ", "+module.getModuleDuration());
        }
      }
    }
    catch( Exception e ) {
      System.out.println("Show Array") ;
      e.printStackTrace();
    }
  }
  /* clean up database from any previous runs of this program */
  private static void cleanupPreviousRuns()
  {
    try {
      #sql {delete from projects where id in (123, 600)};
    } catch (Exception e) {
      System.out.println("Exception at cleanup time!") ;
      e.printStackTrace();
    }
  }
}

Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj

Following is the source code for VarrayDemo1.sqlj and VarrayDemo2.sqlj. These use definitions from the SQL script in "Definition of Object and Collection Types".

Use of VARRAYs is discussed in "Strongly Typed Collections in SQLJ Executable Statements".

VarrayDemo1.sqlj

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

public class VarrayDemo1
{

/* Global variables */

static String uid      = "scott";              /* user id */
static String password = "tiger";              /* password */
static String url      = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */

public static void main(String [] args) throws SQLException
{

  System.out.println("*** SQLJ VARRAY DEMO #1 ***");

  try {

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(VarrayDemo1.class, "connect.properties");
      
    /* create a new VARRAY object and insert it into the DBMS */
    insertVarray();

    /* get the VARRAY object and print it */
    selectVarray();

  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ VARRAY DEMO #1 ***");
}

private static void selectVarray() throws SQLException
{
  PhoneArray ph;
  #sql {select phone_nums into :ph from employees where empnumber=2001};
  System.out.println(
    "there are "+ph.length()+" phone numbers in the PhoneArray.  They are:");

  String [] pharr = ph.getArray();
  for (int i=0;i<pharr.length;++i) 
    System.out.println(pharr[i]);

}

// creates a varray object of PhoneArray and inserts it into a new row
private static void insertVarray() throws SQLException
{
  PhoneArray phForInsert = consUpPhoneArray();

  // clean up from previous demo runs
  #sql {delete from employees where empnumber=2001};

  // insert the PhoneArray object
  #sql {insert into employees (empnumber, phone_nums)
        values(2001, :phForInsert)};
  
}

private static PhoneArray consUpPhoneArray()
{
  String [] strarr = new String[3];
  strarr[0] = "(510) 555.1111";
  strarr[1] = "(617) 555.2222";
  strarr[2] = "(650) 555.3333";
  return new PhoneArray(strarr);
}
}

VarrayDemo2.sqlj

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

#sql iterator StringIter (String s);
#sql iterator intIter(int value);

public class VarrayDemo2
{

/* Global variables */

static String uid      = "scott";              /* user id */
static String password = "tiger";              /* password */
static String url      = "jdbc:oracle:oci8:@"; /* Oracle's OCI8 driver */


public static void main(String [] args) throws SQLException
{
  System.out.println("*** SQLJ VARRAY  DEMO #2 ***");

  try {

    StringIter si = null;

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(VarrayDemo2.class, "connect.properties");
      
    #sql si = {select column_value s from
               table(select phone_nums from employees where empnumber=1001)};

    while(si.next())
      System.out.println(si.s());
  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ VARRAY DEMO #2 ***");
}
}

General Use of CustomDatum--BetterDate.java

This example shows a class that implements the CustomDatum interface to provide a customized representation of Java dates.

Note:

This is not a complete application--there is no main().   

import java.util.Date;
import oracle.sql.CustomDatum;
import oracle.sql.DATE;
import oracle.sql.CustomDatumFactory;
import oracle.jdbc.driver.OracleTypes;

// a Date class customized for user's preferences:
//      - months are numbers 1..12, not 0..11
//      - years are referred to via four-digit numbers, not two.

public class BetterDate extends java.util.Date
             implements CustomDatum, CustomDatumFactory {
  public static final int _SQL_TYPECODE = OracleTypes.DATE;
  
  String[]monthNames={"JAN", "FEB", "MAR", "APR", "MAY", "JUN",
                      "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"};
  String[]toDigit={"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"};

  static final BetterDate _BetterDateFactory = new BetterDate();

  public static CustomDatumFactory getFactory() { return _BetterDateFactory;}

  // the current time...
  public BetterDate() {
    super();
  }

  public oracle.sql.Datum toDatum(oracle.jdbc.driver.OracleConnection conn) {
    return new DATE(toSQLDate());
  }

  public oracle.sql.CustomDatum create(oracle.sql.Datum dat, int intx) {
    if (dat==null) return null;
    DATE DAT = ((DATE)dat);
    java.sql.Date jsd = DAT.dateValue();
    return new BetterDate(jsd);
  }
   
  public java.sql.Date toSQLDate() {
    java.sql.Date retval;
    retval = new java.sql.Date(this.getYear()-1900, this.getMonth()-1,
             this.getDate());
    return retval;
  }
  public BetterDate(java.sql.Date d) {
    this(d.getYear()+1900, d.getMonth()+1, d.getDate());
  }
  private static int [] deconstructString(String s) {
    int [] retval = new int[3];
    int y,m,d; char temp; int offset;
    StringBuffer sb = new StringBuffer(s);
    temp=sb.charAt(1);
    // figure the day of month
    if (temp < '0' || temp > '9') {
      m = sb.charAt(0)-'0';
      offset=2;
    } else {
      m = (sb.charAt(0)-'0')*10 + (temp-'0');
      offset=3;
    }

    // figure the month
    temp = sb.charAt(offset+1);
    if (temp < '0' || temp > '9') {
      d = sb.charAt(offset)-'0';
      offset+=2;
    } else {
      d = (sb.charAt(offset)-'0')*10 + (temp-'0');
      offset+=3;
    }

    // figure the year, which is either in the format "yy" or "yyyy"
    // (the former assumes the current century)
    if (sb.length() <= (offset+2)) {
      y = (((new BetterDate()).getYear())/100)*100 +
          (sb.charAt(offset)- '0') * 10 +
          (sb.charAt(offset+1)- '0');
    } else {
      y = (sb.charAt(offset)- '0') * 1000 +
          (sb.charAt(offset+1)- '0') * 100 +
          (sb.charAt(offset+2)- '0') * 10 +
          (sb.charAt(offset+3)- '0');
    }
    retval[0]=y;
    retval[1]=m;
    retval[2]=d;
//    System.out.println("Constructing date from string as: "+d+"/"+m+"/"+y);
    return retval;
  }
  private BetterDate(int [] stuff) {
    this(stuff[0], stuff[1], stuff[2]);
  }
  // takes a string in the format: "mm-dd-yyyy" or "mm/dd/yyyy" or
  // "mm-dd-yy" or "mm/dd/yy" (which assumes the current century)
  public BetterDate(String s) {
    this(BetterDate.deconstructString(s));
  }

  // years are as '1990', months from 1..12 (unlike java.util.Date!), date
  // as '1' to '31' 
  public BetterDate(int year, int months, int date) {
    super(year-1900,months-1,date);
  }
  // returns "Date: dd-mon-yyyy"
  public String toString() { 
    int yr = getYear();
    return getDate()+"-"+monthNames[getMonth()-1]+"-"+
      toDigit[(yr/1000)%10] + 
      toDigit[(yr/100)%10] + 
      toDigit[(yr/10)%10] + 
      toDigit[yr%10];
//    return "Date: " + getDate() + "-"+getMonth()+"-"+(getYear()%100);
  }
  public BetterDate addDays(int i) {
    if (i==0) return this;
    return new BetterDate(getYear(), getMonth(), getDate()+i);
  }
  public BetterDate addMonths(int i) {
    if (i==0) return this;
    int yr=getYear();
    int mon=getMonth()+i;
    int dat=getDate();
    while(mon<1) { 
      --yr;mon+=12;
    }
    return new BetterDate(yr, mon,dat);
  }
  // returns year as in 1996, 2007
  public int getYear() {
    return super.getYear()+1900;
  }
  // returns month as 1..12
  public int getMonth() {
    return super.getMonth()+1;
  }
  public boolean equals(BetterDate sd) {
    return (sd.getDate() == this.getDate() &&
            sd.getMonth() == this.getMonth() &&
            sd.getYear() == this.getYear());
  }
  // subtract the two dates; return the answer in whole years
  // uses the average length of a year, which is 365 years plus
  // a leap year every 4, except 100, except 400 years =
  // = 365 97/400 = 365.2425 days = 31,556,952 seconds
  public double minusInYears(BetterDate sd) {
    // the year (as defined above) in milliseconds
    long yearInMillis = 31556952L;
    long diff = myUTC()-sd.myUTC();
    return (((double)diff/(double)yearInMillis)/1000.0);
  }
  public long myUTC() {
    return Date.UTC(getYear()-1900, getMonth()-1, getDate(),0,0,0);
  }
  
  // returns <0 if this is earlier than sd
  // returns = if this == sd
  // else returns >0
  public int compare(BetterDate sd) {
    if (getYear()!=sd.getYear()) {return getYear()-sd.getYear();}
    if (getMonth()!=sd.getMonth()) {return getMonth()-sd.getMonth();}
    return getDate()-sd.getDate();
  }
}


Prev
Top
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index