Samples for JDBC 2.0 Types

This section contains sample code for the Oracle implementations of standard JDBC 2.0 types:

• BLOBs and CLOBs--LobExample.java

• Weakly Typed Objects--PersonObject.java

• Weakly Typed Object References--StudentRef.java

• Weakly Typed Arrays--ArrayExample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/object-samples

BLOBs and CLOBs--LobExample.java

This sample demonstrates basic JDBC support for LOBs. It illustrates how to create a table containing LOB columns and includes utility programs to read from a LOB, write to a LOB, and dump the LOB contents. For more information on LOBs, see "Working with BLOBs and CLOBs".

/* 
 * This sample demonstrate basic LOB support.
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;

//needed for new CLOB and BLOB classes
import oracle.sql.*;

public class LobExample
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table basic_lob_table");
    }
    catch (SQLException e)
    {
      // An exception could be raised here if the table did not exist already.
    }

    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table basic_lob_table 
                   (x varchar2 (30), b blob, c clob)");
    
    // Populate the table
    stmt.execute ("insert into basic_lob_table values 
         ('one', '010101010101010101010101010101', 'onetwothreefour')");
    stmt.execute ("insert into basic_lob_table values 
         ('two', '0202020202020202020202020202', 'twothreefourfivesix')");
    
    System.out.println ("Dumping lobs");

    // Select the lobs   
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);

      // Print the lob contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);

      // Change the lob contents
      fillClob (conn, clob, 2000);
      fillBlob (conn, blob, 4000);
    }

    System.out.println ("Dumping lobs again");

    rset = stmt.executeQuery ("select * from basic_lob_table");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);

      // Print the lobs contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
    }
    // Close all resources
    rset.close();
    stmt.close();
    conn.close(); 
  }

  // Utility function to dump Clob contents
  static void dumpClob (Connection conn, CLOB clob)
    throws Exception
  {
    // get character stream to retrieve clob data
    Reader instream = clob.getCharacterStream();

    // create temporary buffer for read
    char[] buffer = new char[10];

    // length of characters read
    int length = 0;

    // fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " chars: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]);
      System.out.println();
    }

    // Close input stream
    instream.close();
  }

  // Utility function to dump Blob contents
  static void dumpBlob (Connection conn, BLOB blob)
    throws Exception
  {
    // Get binary output stream to retrieve blob data
    InputStream instream = blob.getBinaryStream();

    // Create temporary buffer for read
    byte[] buffer = new byte[10];

    // length of bytes read
    int length = 0;

    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " bytes: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
      System.out.println();
    }

    // Close input stream
    instream.close();
  }

  // Utility function to put data in a Clob
  static void fillClob (Connection conn, CLOB clob, long length)
    throws Exception
  {
    Writer outstream = clob.getCharacterOutputStream();

    int i = 0;
    int chunk = 10;

    while (i < length)
    {
      outstream.write(i + "hello world", 0, chunk);

      i += chunk;
      if (length - i < chunk)
      chunk = (int) length - i;
    }
    outstream.close();
  }

  // Utility function to put data in a Blob
  static void fillBlob (Connection conn, BLOB blob, long length)
    throws Exception
  {
    OutputStream outstream = blob.getBinaryOutputStream();

    int i = 0;
    int chunk = 10;

    byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

    while (i < length)
    {
      data [0] = (byte)i;
      outstream.write(data, 0, chunk);

      i += chunk;
      if (length - i < chunk)
      chunk = (int) length - i;
    }
    outstream.close();
  }
}

Weakly Typed Objects--PersonObject.java

This sample demonstrates the functionality of the Oracle classes oracle.sql.STRUCT and oracle.sql.StructDescriptor for weakly typed support of SQL structured objects. It defines the SQL object types PERSON and ADDRESS (an attribute of PERSON).

For a complete discussion of weakly typed STRUCT class functionality, see "Using the Default STRUCT Class for Oracle Objects".

/* 
 * This sample demonstrate basic Object support
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class PersonObject
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an object of type "STUDENT",
    // materializes the object as an object of type ADT.
    // The Object is then modified and inserted back into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@",
                                   "scott", "tiger");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table people");
      stmt.execute ("drop type PERSON FORCE");
      stmt.execute ("drop type ADDRESS FORCE");
    }
    catch (SQLException e)
    {
      // the above drop and create statements will throw exceptions
      // if the types and tables did not exist before
    }

    stmt.execute ("create type ADDRESS as object 
                   (street VARCHAR (30), num NUMBER)");
    stmt.execute ("create type PERSON as object 
                   (name VARCHAR (30), home ADDRESS)");
    stmt.execute ("create table people (empno NUMBER, empid PERSON)");

    stmt.execute ("insert into people values 
                   (101, PERSON ('Greg', ADDRESS ('Van Ness', 345)))");
    stmt.execute ("insert into people values 
                   (102, PERSON ('John', ADDRESS ('Geary', 229)))");

    ResultSet rs = stmt.executeQuery ("select * from people");
    showResultSet (rs);
    rs.close();

    //now insert a new row

    // create a new STRUCT object with a new name and address
    // create the embedded object for the address
    Object [] address_attributes = new Object [2];
    address_attributes [0] = "Mission";
    address_attributes [1] = new BigDecimal (346);

    StructDescriptor addressDesc = 
      StructDescriptor.createDescriptor ("ADDRESS", conn);
    STRUCT address = new STRUCT (addressDesc, conn, address_attributes);

    Object [] person_attributes = new Object [2];
    person_attributes [0] = "Gary";
    person_attributes [1] = address;
    
    StructDescriptor personDesc = 
      StructDescriptor.createDescriptor("PERSON", conn);
    STRUCT new_person = new STRUCT (personDesc, conn, person_attributes);

    PreparedStatement ps = 
      conn.prepareStatement ("insert into people values (?,?)");
    ps.setInt (1, 102);
    ps.setObject (2, new_person);

    ps.execute ();
    ps.close();

    rs = stmt.executeQuery ("select * from people");
    System.out.println ();
    System.out.println (" a new row has been added to the people table");
    System.out.println ();
    showResultSet (rs);

    rs.close();
    stmt.close();
    conn.close();    
  }

  public static void showResultSet (ResultSet rs)
    throws SQLException
  {
    while (rs.next ())
    {
      int empno = rs.getInt (1);
      // retrieve the STRUCT 
      STRUCT person_struct = (STRUCT)rs.getObject (2);
      Object person_attrs[] = person_struct.getAttributes();

      System.out.println ("person name:  " + (String) person_attrs[0]);

      STRUCT address = (STRUCT) person_attrs[1];

      System.out.println ("person address: ");

      Object address_attrs[] = address.getAttributes();

      System.out.println ("street:  " + (String) address_attrs[0]);
      System.out.println ("number:  " + 
                         ((BigDecimal) address_attrs[1]).intValue());
      System.out.println ();
    }
  }
}

Weakly Typed Object References--StudentRef.java

This sample demonstrates the functionality of the Oracle class oracle.sql.REF for weakly typed support of SQL object references. It defines the SQL object type STUDENT and uses references to that object type.

For a complete discussion of weakly typed REF class functionality, see Chapter 9, "Working with Oracle Object References".

/* 
 * This sample demonstrate basic Ref support 
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class StudentRef
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an object of type "person",
    // materializes the object as an object of type ADT.
    // The Object is then modified and inserted back into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", 
                                   "scott", "tiger");
    
    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table student_table");
      stmt.execute ("drop type STUDENT");   
    }
    catch (SQLException e)
    {      
      // the above drop and create statements will throw exceptions
      // if the types and tables did not exist before
    }

    stmt.execute ("create type STUDENT as object 
                   (name VARCHAR (30), age NUMBER)");
    stmt.execute ("create table student_table of STUDENT");
    stmt.execute ("insert into student_table values ('John', 20)");

    ResultSet rs = stmt.executeQuery ("select ref (s) from student_table s");
    rs.next ();

    // retrieve the ref object
    REF ref = (REF) rs.getObject (1);

    //retrieve the object value that the ref points to in the
    // object table

    STRUCT student = (STRUCT) ref.getValue ();
    Object attributes[] = student.getAttributes();

    System.out.println ("student name: " + (String) attributes[0]);
    System.out.println ("student age:  " + ((BigDecimal)
                         attributes[1]).intValue());

    rs.close();
    stmt.close();
    conn.close();
  }
}

Weakly Typed Arrays--ArrayExample.java

This sample program uses JDBC to create a table with a VARRAY. It inserts a new array object into the table, then prints the contents of the table. For more information on arrays, see Chapter 10, "Working with Oracle Collections".

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.driver.*;
import java.math.BigDecimal;

public class ArrayExample
{
  public static void main (String args[])
    throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an varray of type "NUM_VARRAY",
    // materializes the object as an object of type ARRAY.
    // A new ARRAY is then inserted into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", 
                                   "scott", "tiger");
   
    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("DROP TABLE varray_table");
      stmt.execute ("DROP TYPE num_varray");     
    }
    catch (SQLException e)
    {
      // the above drop statements will throw exceptions
      // if the types and tables did not exist before. Just ingore it.
    }
 
    stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

    ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);

    //now insert a new row

    // create a new ARRAY object    
    int elements[] = { 300, 400, 500, 600 };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, elements);
    
    PreparedStatement ps = 
      conn.prepareStatement ("insert into varray_table values (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);

    // Close all the resources
    rs.close();
    ps.close();
    stmt.close();
    conn.close();

  }   

  public static void showResultSet (ResultSet rs)
    throws SQLException
  {       
    int line = 0;
    while (rs.next())
    {
      line++;
      System.out.println("Row "+line+" : ");
      ARRAY array = ((OracleResultSet)rs).getARRAY (1);

      System.out.println ("Array is of type "+array.getSQLTypeName());
      System.out.println 
                 ("Array element is of typecode "+array.getBaseType()); 
      System.out.println ("Array is of length "+array.length());

      // get Array elements            
      BigDecimal[] values = (BigDecimal[]) array.getArray();

      for (int i=0; i<values.length; i++) 
      {
        BigDecimal value = (BigDecimal) values[i];
        System.out.println(">> index "+i+" = "+value.intValue());
      }
    }
  }
}

---