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

## Samples for Custom Object Classes

This section demonstrates the functionality of custom Java classes to map from SQL structured objects, providing examples of both a standard `SQLData` implementation and an Oracle `CustomDatum` implementation:

- SQLData Implementation--SQLDataExample.java

- CustomDatum Implementation--CustomDatumExample.java

This includes examples of the code you must provide to define custom Java classes for Oracle objects, and sample applications that make use of these custom Java class definitions. You create the custom classes by implementing either the standard `java.sql.SQLData` interface or the Oracle `oracle.sql.CustomDatum` interface. These interfaces provide a way to create and populate the custom Java class for the Oracle object and its attributes.

`SQLData` and `CustomDatum` both populate a Java object from a SQL object, with the SQLData interface providing more portability and the `CustomDatum` interface providing more utility and flexibility in how you present the data.

The `SQLData` interface is a JDBC standard. For more information on this interface, see "Understanding the SQLData Interface".

The `CustomDatum` interface is provided by Oracle. For more information on the `CustomDatum` interface, see "Understanding the CustomDatum Interface".

You can write your own code to create custom Java classes that implement either interface, but the Oracle JPublisher utility can generate classes to implement either interface as well.

For more information about JPublisher, see "Using JPublisher to Create Custom Object Classes" and the Oracle8i JPublisher User's Guide.

The sample applications and custom Java class definitions in this section are located in the following directory on the product CD:

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

### SQLData Implementation--SQLDataExample.java

This section contains code that illustrates how to define and use a custom Java type corresponding to a given SQL object type, using a `SQLData` implementation.

#### SQL Object Definition

Following is the SQL definition of an `EMPLOYEE` object. The object has two attributes: a `VARCHAR2` attribute `EMPNAME` (employee name) and an `INTEGER` attribute `EMPNO` (employee number).

     -- SQL definition
    CREATE TYPE employee AS OBJECT
    (
         empname VARCHAR2(50),
         empno   INTEGER
    );

#### Custom Object Class--SQLData Implementation

The following code defines the custom Java class `EmployeeObj` (defined in `EmployeeObj.java`) to correspond to the SQL type `EMPLOYEE`. Notice that the definition of `EmployeeObj` contains a string `empName` (employee name) attribute and an integer `empNo` (employee number) attribute. Also notice that the Java definition of the `EmployeeObj` custom Java class implements the `SQLData` interface and includes the implementations of a `get` method and the required `readSQL()` and `writeSQL()` methods.

    import java.sql.*;
    import oracle.jdbc.*;

    public class EmployeeObj implements SQLData
    {
      private String sql_type;

      public String empName;
      public int empNo;

      public EmployeeObj()
      {
      }

      public EmployeeObj (String sql_type, String empName, int empNo)
      {
        this.sql_type = sql_type;
        this.empName = empName;
        this.empNo = empNo;
      }

      ////// implements SQLData //////

      public String getSQLTypeName() throws SQLException
      {
        return sql_type;
      }

      public void readSQL(SQLInput stream, String typeName)
        throws SQLException
      {
        sql_type = typeName;

        empName = stream.readString();
        empNo = stream.readInt();
      }

      public void writeSQL(SQLOutput stream)
        throws SQLException
      {
        stream.writeString(empName);
        stream.writeInt(empNo);
      }
    }

#### Sample Application Using SQLData Custom Object Class

After you create the `EmployeeObj` Java class, you can use it in a program. The following program creates a table that stores employee name and number data. The program uses the `EmployeeObj` object to create a new employee object and insert it in the table. It then applies a `SELECT` statement to get the contents of the table and prints its contents.

For information about using `SQLData` implementations to access and manipulate SQL object data, see "Reading and Writing Data with a SQLData Implementation".

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

    public class SQLDataExample
    {

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

        // Connect
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
        OracleConnection conn = (OracleConnection)
          DriverManager.getConnection("jdbc:oracle:oci8:@",
                                      "scott", "tiger");

        Dictionary map = (Dictionary)conn.getTypeMap();
        map.put("EMPLOYEE", Class.forName("EmployeeObj"));

        // Create a Statement
        Statement stmt = conn.createStatement ();
        try
        {
          stmt.execute ("drop table EMPLOYEE_TABLE");
          stmt.execute ("drop type EMPLOYEE");
        }
        catch (SQLException e)
        {
          // An error is raised if the table/type does not exist. Just ignore it.
        }

        // Create and populate tables
        stmt.execute ("CREATE TYPE EMPLOYEE AS OBJECT
                       (EmpName VARCHAR2(50),EmpNo INTEGER)");
        stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)");
        stmt.execute ("INSERT INTO EMPLOYEE_TABLE VALUES
                       (EMPLOYEE('Susan Smith', 123))");
        stmt.close();

        // Create a SQLData object
        EmployeeObj e = new EmployeeObj("SCOTT.EMPLOYEE", "George Jones", 456);

        // Insert the SQLData object
        PreparedStatement pstmt
          = conn.prepareStatement ("insert into employee_table values (?)");

        pstmt.setObject(1, e, OracleTypes.STRUCT);
        pstmt.executeQuery();
        System.out.println("insert done");
        pstmt.close();

        // Select now
        Statement s = conn.createStatement();
        OracleResultSet rs = (OracleResultSet)
          s.executeQuery("select * from employee_table");

        while(rs.next())
        {
           EmployeeObj ee = (EmployeeObj) rs.getObject(1);
           System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo);
        }
        rs.close();
        s.close();

        if (conn != null)
        {
          conn.close();
        }
      }
    }

### CustomDatum Implementation--CustomDatumExample.java

This section contains code that illustrates how to define and use a custom Java type corresponding to a given SQL object type, using a `CustomDatum` implementation.

#### SQL Object Definition

Following is the SQL definition of an `EMPLOYEE` object. The object has two attributes: a `VARCHAR2` attribute `EMPNAME` (employee name) and an `INTEGER` attribute `EMPNO` (employee number).

    CREATE TYPE employee AS OBJECT
    (
         empname VARCHAR2(50),
         empno   INTEGER
    );

#### Custom Object Class--CustomDatum Implementation

The following code defines the custom Java class `Employee` (defined in `Employee.java`) to correspond to the SQL type `EMPLOYEE`. Notice that the definition of `Employee` contains accessor methods for a string `empname` (employee name) and an integer `empno` (employee number). Also notice that the Java definition of the `Employee` custom Java class implements the `CustomDatum` and `CustomDatumFactory` interfaces. A custom Java class that implements `CustomDatum` has a static `getFactory()` method that returns a `CustomDatumFactory` object. The JDBC driver uses the `CustomDatumFactory` object's `create()` method to return a `CustomDatum` instance.

Note that instead of writing the custom Java class yourself, you can use the JPublisher utility to generate class definitions that implement the `CustomDatum` and `CustomDatumFactory` interfaces. In fact, the `Employee.java` code shown here was generated by JPublisher.

    import java.sql.SQLException;
    import oracle.jdbc.driver.OracleConnection;
    import oracle.jdbc.driver.OracleTypes;
    import oracle.sql.CustomDatum;
    import oracle.sql.CustomDatumFactory;
    import oracle.sql.Datum;
    import oracle.sql.STRUCT;
    import oracle.jpub.runtime.MutableStruct;

    public class Employee implements CustomDatum, CustomDatumFactory
    {
      public static final String _SQL_NAME = "SCOTT.EMPLOYEE";
      public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

      MutableStruct _struct;

      static int[] _sqlType =
      {
        12, 4
      };

      static CustomDatumFactory[] _factory = new CustomDatumFactory[2];

      static final Employee _EmployeeFactory = new Employee();
      public static CustomDatumFactory getFactory()
      {
        return _EmployeeFactory;
      }

      /* constructor */
      public Employee()
      {
        _struct = new MutableStruct(new Object[2], _sqlType, _factory);
      }

      /* CustomDatum interface */
      public Datum toDatum(OracleConnection c) throws SQLException
      {
        return _struct.toDatum(c, _SQL_NAME);
      }

      /* CustomDatumFactory interface */
      public CustomDatum create(Datum d, int sqlType) throws SQLException
      {
        if (d == null) return null;
        Employee o = new Employee();
        o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
        return o;
      }

      /* accessor methods */
      public String getEmpname() throws SQLException
      { return (String) _struct.getAttribute(0); }

      public void setEmpname(String empname) throws SQLException
      { _struct.setAttribute(0, empname); }

      public Integer getEmpno() throws SQLException
      { return (Integer) _struct.getAttribute(1); }

      public void setEmpno(Integer empno) throws SQLException
      { _struct.setAttribute(1, empno); }
    }

#### Sample Application Using CustomDatum Custom Object Class

This sample program shows how you can use the `Employee` class generated by JPublisher. The sample code creates a new `Employee` object, fills it with data, then inserts it into the database. The sample code then retrieves the `Employee` data from the database.

For information about using `CustomDatum` implementations to access and manipulate SQL object data, see "Reading and Writing Data with a CustomDatum Implementation".

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

    public class CustomDatumExample
    {

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

        // Connect
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
        OracleConnection conn = (OracleConnection)
          DriverManager.getConnection("jdbc:oracle:oci8:@",
                                      "scott", "tiger");

        // Create a Statement
        Statement stmt = conn.createStatement ();
        try
        {
          stmt.execute ("drop table EMPLOYEE_TABLE");
          stmt.execute ("drop type EMPLOYEE");
        }
        catch (SQLException e)
        {
          // An error is raised if the table/type does not exist. Just ignore it.
        }

        // Create and populate tables
        stmt.execute ("CREATE TYPE EMPLOYEE AS OBJECT
                       (EmpName VARCHAR2(50),EmpNo INTEGER)");
        stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)");
        stmt.execute ("INSERT INTO EMPLOYEE_TABLE VALUES
                       (EMPLOYEE('Susan Smith', 123))");
        stmt.close();

        // Create a CustomDatum object
        Employee e = new Employee("George Jones", new BigDecimal("456"));

        // Insert the CustomDatum object
        PreparedStatement pstmt
          = conn.prepareStatement ("insert into employee_table values (?)");

        pstmt.setObject(1, e, OracleTypes.STRUCT);
        pstmt.executeQuery();
        System.out.println("insert done");
        pstmt.close();

        // Select now
        Statement s = conn.createStatement();
        OracleResultSet rs = (OracleResultSet)
          s.executeQuery("select * from employee_table");

        while(rs.next())
        {
           Employee ee = (Employee) rs.getCustomDatum(1, Employee.getFactory());
           System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo);
        }
        rs.close();
        s.close();

        if (conn != null)
        {
          conn.close();
        }
      }
    }

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