Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index