Sample Applications

Creating Customized Java Classes for Oracle Objects

This section contains the following subsections:

This section contains examples of the code you must provide to create custom Java classes for Oracle objects. You create the custom classes by implementing either the SQLData or CustomDatum interface. These interfaces provide a way to create and populate the custom Java class for the Oracle object and its attributes.

Although both SQLData and CustomDatum both populate a Java object from a SQL object, the CustomDatum interface is far more powerful. In addition to letting you populate Java objects, CustomDatum lets you materialize objects from SQL types that are not necessarily objects. Thus, you can create a CustomDatum object from any datatype found in an Oracle database. This is particularly useful in the case of RAW data that can be a serialized object.

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. You can write your own code to create custom Java classes that implement this interface, but you will find it easier to let the Oracle utility JPublisher create the custom classes for you. The custom classes created by JPublisher implement the CustomDatum interface.

For more information on the CustomDatum interface, see "Understanding the CustomDatum Interface". See the Oracle8i JPublisher User's Guide for more information on the JPublisher utility.

SQLData Sample

This section contains a code sample that illustrates how you can create a custom Java type to correspond to a given SQL type. It then demonstrates how you can use the custom Java class in the context of a sample program. The sample also contains the code to map the SQL type to the custom Java type.

Creating the SQL Object Definition

Following is the SQL definition of an EMPLOYEE object. The object has two attributes: a string EmpName (employee name) attribute and an integer EmpNo (employee number) attribute.

 -- SQL definition 
CREATE TYPE EMPLOYEE AS OBJECT
(
     EmpName VARCHAR2(50),
     EmpNo   INTEGER,
);

Creating the Custom Java Class

The following program implements the custom Java class EmployeeObj to correspond to the SQL type EMPLOYEE. Notice that the implementation 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.jdbc2.*;

public class EmployeeObj implements SQLData
 {
  private String sql_type;
 
  public String empName;
  public int empNo;

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

  ////// implements SQLData //////
 
  // define a get method to return the SQL type of the object  line 24
  public String getSQLTypeName() throws SQLException
  { 
    return sql_type; 
  }                                                         // line 28
 
  // define the required readSQL() method                      line 30
  public void readSQL(SQLInput stream, String typeName)
    throws SQLException
  {
    sql_type = typeName;
  
    empName = stream.readString();
    empNo = stream.readInt();
  }  
  // define the required writeSQL() method                     line 39
  public void writeSQL(SQLOutput stream)
    throws SQLException
  { 
    stream.writeString(empName);
    stream.writeInt(empNo);
  }
}

Lines 1-14:

Import the needed java.* and oracle.* packages. Define the custom Java class EmployeeObj to implement the SQLData interface. EmployeeObj is the class to which you will later map your EMPLOYEE SQL object type. The EmployeeObj object has three attributes: a SQL type name, an employee name, and an employee number. The SQL type name is a Java string that represents the fully qualified SQL type name (schema.sql_type_name) of the Oracle object that the custom Java class represents.

Lines 24-28:

Define a getSqlType() method to return the SQL type of the custom Java object.

Lines 30-38:

Define a readSQL() method as required by the definition of the SQLData interface. The readSQL() method takes a stream SQLInput object and the SQL type name of the object data that it is reading.

Lines 39-45:

Define a writeSQL() method as required by the definition of the SQLData interface. The writeSQL() method takes a stream SQLOutput object and the SQL type name of the object data that it is reading.

Using the Custom Java Class

After you create your 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.

Except for some changes to the comments, the following sample is similar to the SQLDataExample.java program in the Demo/samples/oci8/object-samples directory.

import java.sql.*;                                           // line 1
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 to the database
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
    OracleConnection conn = (OracleConnection)
      DriverManager.getConnection("jdbc:oracle:oci8:@",
                                  "scott", "tiger");        // line 16
 
    // in the type map, add the mapping of EMPLOYEE SQL     // line 18
    // type to the EmployeeObj custom Java type 
    Dictionary map = conn.getTypeMap();
    map.put("EMPLOYEE", Class.forName("EmployeeObj"));      // line 21

    // Create a Statement                                      line 23
    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                           // line 35
    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))");                                 // line 40
 
    // Create a SQLData object EmployeeObj in the SCOTT schema
    EmployeeObj e = new EmployeeObj("SCOTT.EMPLOYEE", "George Jones", 456);
 
    // Insert the SQLData object into the database          // line 45
    PreparedStatement pstmt
      = conn.prepareStatement ("INSERT INTO employee_table VALUES (?)");
 
    pstmt.setObject(1, e, OracleTypes.STRUCT);
    pstmt.executeQuery();
    System.out.println("insert done");
    pstmt.close();                                          // line 52
 
    // Select the contents of the employee_table            // line 54
    Statement s = conn.createStatement();
    OracleResultSet rs = (OracleResultSet) 
      s.executeQuery("SELECT * FROM employee_table");       // line 57
    
    // print the contents of the table                               // line 59
    while(rs.next())
    {
       EmployeeObj ee = (EmployeeObj) rs.getObject(1);
       System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo);
    }                                                       // line 64
 
    // close the result set, statement, and connection      // line 66
    rs.close();
    s.close();
 
    if (conn != null)
    {
      conn.close();                                         // line 72
    }
   } 
}

Lines 1-16:

Import needed java.* and oracle.* packages. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user scott with password tiger. You can optionally enter a database name following the @ symbol.

Lines 18-21:

Use the getTypeMap() method to get the type map associated with this connection. Use the map object's put() method to add the mapping of the SQL EMPLOYEE object to the EmployeeObj custom Java type.

Lines 23-33:

Create a statement object and drop any pre-existing tables and types named EMPLOYEE_TABLE and EMPLOYEE.

Lines 35-40:

Use SQL statements to:

create an EMPLOYEE object with employee name and employee number attributes
create a table of employee objects (EMPLOYEE_TABLE) having a single EMPLOYEE column
insert initial data values into the table

Lines 42, 43:

Create a new EmployeeObj object (which is a SQLData object). Identify the schema name (SCOTT), SQL type name (EMPLOYEE), an employee name (George Jones) and an employee number (456). Note that the schema name is the same as the user name in the getConnection() call. If you change the user name, you must also change the schema name.

Lines 45-52:

Prepare a statement to insert the new EMPLOYEE object into the employee table. The setObject() method indicates that the object will be inserted into the first index position and that the underlying type of the EMPLOYEE object is oracle.sql.STRUCT.

Lines 54-57:

Select the contents of the EMPLOYEE_TABLE. Cast the results to an OracleResultSet so that you can retrieve the custom Java object data from it.

Lines 59-62:

Iterate through the result set, getting the contents of the EMPLOYEE objects and printing the employee names and employee numbers.

Lines 66-72:

Close the result set, statement, and connection objects.

CustomDatum Sample

This section describes a Java class, written by a user, that implements the CustomDatum and CustomDatumFactory interfaces. The custom Java class of type 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.

The following example illustrates a Java class definition that can be written by a user, given the SQL definition of an EMPLOYEE object.

SQL Definition of EMPLOYEE Object

The following SQL code defines the EMPLOYEE object. The EMPLOYEE object consists of the employee's name (EmpName) and the employee's associated number (EmpNo).

create type EMPLOYEE as object 
     ( 
          EmpName VARCHAR2(50), 
          EmpNo   INTEGER 
     );

Java Class Definitions for a Custom Java Object

Below are the contents of the Employee.java file.

import java.math.BigDecimal; 
import java.sql.SQLException; 
import oracle.jdbc.driver.OracleConnection; 
import oracle.sql.CustomDatum; 
import oracle.sql.CustomDatumFactory; 
import oracle.sql.Datum; 
import oracle.sql.STRUCT; 
import oracle.sql.StructDescriptor; 
 
public class Employee implements CustomDatum, CustomDatumFactory // line 10
{ 
 
  static final Employee _employeeFactory = new Employee(null, null); //line 13
 
  public static CustomDatumFactory getFactory() 
  { 
    return _employeeFactory; 
  }                                                         // line 18
 
  /* constructor */                                         // line 20
  public Employee(String empName, BigDecimal empNo) 
  { 
    this.empName = empName; 
    this.empNo = empNo; 
  }                                                         // line 25
 
  /* CustomDatum interface */                               // line 27
  public Datum toDatum(OracleConnection c) throws SQLException 
  { 
    StructDescriptor sd = 
       StructDescriptor.createDescriptor("SCOTT.EMPLOYEE", c); 
 
    Object [] attributes = { empName, empNo }; 
 
    return new STRUCT(sd, c, attributes); 
  }                                                         // line 36
 
  /* CustomDatumFactory interface */                        // line 38
  public CustomDatum create(Datum d, int sqlType) throws SQLException 
  { 
    if (d == null) return null; 
 
    System.out.println(d); 
 
    Object [] attributes = ((STRUCT) d).getAttributes(); 
 
    return new Employee((String) attributes[0], 
                        (BigDecimal) attributes[1]); 
  }                                                         // line 49
 
  /* fields */    
  public String empName; 
  public BigDecimal empNo; 
}

Line 10:

As required, the Employee class implements the CustomDatum and CustomDatumFactory interfaces.

Lines 13-18:

JPublisher defines a _employeeFactory object of class Employee, which will be returned by the getFactory() method and used to create new Employee objects. The getFactory() method returns an empty Employee object that you can use to create new Employee objects.

Lines 20-25:

JPublisher defines the Employee Java class to correspond to the SQL EMPLOYEE object. JPublisher creates the Employee class with two attributes: an employee name of type java.lang.String and an employee number of type java.math.BigDecimal.

Lines 27-36:

The toDatum() method of the CustomDatum interface transforms the EMPLOYEE SQL data into oracle.sql.* representation. To do this, toDatum() uses:

a STRUCT descriptor that takes the schema name, the SQL object or "type" name, and the connection object as arguments
an object array that stores the values of the object's employee name and employee number attributes

The toDatum() returns a STRUCT containing the STRUCT descriptor, the connection object and the object attributes into an oracle.sql.Datum.

Lines 38-49:

The CustomDatumFactory interface specifies a create() method that is analogous to the constructor of your Employee custom Java class. The create() method takes the Datum object and the SQL type code of the Datum object and returns a CustomDatum instance.

According to the definition, the create() method returns null if the value of the Datum object is null. Otherwise, it returns an instance of the Employee object with the employee name and employee number attributes.

Custom Java Class Usage Example

This code snippet presents a simple example of how you can use the Employee class that you created with 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.

Except for some changes to the comments, the following sample is similar to the CustomDatumExample.java program in the Demo/samples/oci8/object-samples directory.

import java.sql.*;                                           // line 1
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                                   // line 18
    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.
    }                                                       // line 28

    // Create and populate tables                           // line 30
    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))");          // line 35

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

    // Insert the CustomDatum object                        // line 40
    PreparedStatement pstmt
      = conn.prepareStatement ("INSERT INTO employee_table VALUES (?)");

    pstmt.setObject(1, e, OracleTypes.STRUCT);
    pstmt.executeQuery();
    System.out.println("insert done");
    pstmt.close();                                          // line 47
                     
    // Select now                                           // line 49
    Statement s = conn.createStatement();
    OracleResultSet rs = (OracleResultSet) 
      s.executeQuery("SELECT * FROM employee_table");

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

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

Lines 1-16:

Import needed java.* and oracle.* packages. Register the driver with the DriverManager.registerDriver() method and connect to the database with the getConnection() method. Use the database URL jdbc:oracle:oci8:@ and connect as user system with password manager. You can optionally enter a database name following the @ symbol.

Lines 18-28:

Create a statement object and drop any pre-existing tables and types named EMPLOYEE_TABLE and EMPLOYEE.

Lines 30-35:

Use SQL statements to:

create an Employee object with employee name and employee number attributes
create a table of employee objects having a single EMPLOYEE column
insert initial data values into the table

Lines 37, 38:

Create a new Employee object (which is a CustomDatum object) and define an employee name and employee number for it.

Lines 40-47:

Prepare a statement to insert the new Employee object into the database. The setObject() method indicates that the object will be inserted into the first index position and that the underlying type of the Employee object is oracle.sql.STRUCT.

Lines 49-54:

Select the contents of the employee_table. Cast the results to an OracleResultSet so that the getCustomDatum() method can be used on it.

Lines 54-58:

Iterate through the result set, getting the contents of the Employee objects and printing the employee names and employee numbers.

Lines 58-62:

Close the result set, statement, and connection objects.