Sample Applications

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:

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