Java Object Storage: SQL Types of Language Java or SQLJ Object Types

Java Object Storage: SQL Types of Language Java or SQLJ Object Types

The integration of Java in the Oracle database allowed one main function: accessing SQL object types from Java applications using JDBC or SQLJ. This was possible by mapping Java classes to the object types. Oracle9i introduced Java object persistence with SQLJ object types that enable the reverse mapping (i.e., SQL object types mapped to Java classes).

SQLJ object types are SQL object types of the Java language. You can use these types wherever you can use a SQL object type—for example, as the type of an object table, an attribute of a type, or a column of an object-relational table. You can query and manipulate schema objects of SQLJ object types using SQL.

You create SQLJ object types using the extended CREATE TYPE statement with the USING clause and the EXTERNAL clause.

To implement SQLJ object types, you need the following:

• 
  

  A mapping between the SQLJ object type attributes and member methods to the corresponding Java equivalents, and a mapping between the SQLJ object type as a whole to the Java class as a whole. You do this with the EXTERNAL clause.

  
  


• 
  

  An interface to use the SQLJ object type functionality. You do this with the USING clause. The USING clause can specify one of the interfaces java.sql.SQLData or oracle.sql.ORAData, along with oracle.sql.ORADataFactory, and the corresponding Java class must implement one of these interfaces.

  
  

Here are the steps involved in creating and using SQLJ object types:

1. 
   

   Create the custom Java class that the SQLJ object type maps to.

   
   


2. 
   

   Load the class into the Oracle9i database.

   
   


3. 
   

   Create a SQLJ object type specifying the mappings from the object type to the Java class.

   
   


4. 
   

   Use the SQLJ object type in PL/SQL and/or SQL just like any object type.

   
   

The following sections describe how to perform each of these steps.

Create the Custom Java Class That the SQLJ Object Type Maps To

This class should implement the java.sql.SQLData or oracle.sql.ORAData interface. I use the definition similar to that of the address object type described earlier. Here's the code for the custom Java class:

import java.sql.*;
import oracle.sql.*;
public class Address implements SQLData {
  public String line1;
  public String line2;
  public String city;
  public String state_code;
  public String zip;
  String sql_type = "ADDRESS_SQLJ";

  public Address() {
  }

  public Address (String iline1, String iline2, String icity,
                              String istate, String izip) {
    this.line1 = iline1;
    this.line2 = iline2;
    this.city = icity;
    this.state_code = istate;
    this.zip = izip;
  }

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

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

    line1 = stream.readString();
    line2 = stream.readString();
    city = stream.readString();
    state_code = stream.readString();
    zip = stream.readString();
  }

  public void writeSQL(SQLOutput stream)
    throws SQLException
  {
    stream.writeString(line1);
    stream.writeString(line2);
    stream.writeString(city);
    stream.writeString(state_code);
    stream.writeString(zip);
  }

  public static Address setAddress (String iline1, String iline2,
                                       String icity, String istate, String zip) {
     return new Address(iline1, iline2, icity, istate, izip);
  }

  public String getAddress() {
    return this.line1 + " " + this.line2 + " " + this.city + ", " +
                this.state_code + " " + this.zip;
  }
}

Once you've defined the Java source, you have to compile into a .class file using the command

javac Address.java

Load the Class into the Oracle9i Database

You do this using the loadjava utility provided by Oracle. Refer to the "Oracle9i Java Stored Procedures Developers Guide" and the "Oracle9i Java Developers Guide" in the Oracle documentation for a description of the loadjava utility.

Here's how the Address.class Java class file is loaded into the plsql9i/plsql9I schema:

loadjava -u -plsql9i/plsql9i -r -oci8 Address.class

The –r option resolves external references inside in the schema.

To verify that the Address.class file is loaded into the database, use the following query:

SQL> column object_name format a30;
SQL> select object_name, object_type
  2  from user_objects
  3  where object_type like '%JAVA%';

OBJECT_NAME                      OBJECT_TYPE
-------------------------------- -----------
Address                          JAVA CLASS

Create a SQLJ Object Type Specifying the Mappings from the Object Type to the Java Class

Once you've defined the custom Java class and compiled and loaded it into the database, the next step is to define the object type based on the Java class. You do this with the CREATE TYPE statement using the EXTERNAL and USING clauses. Here's an example that defines an object type based on the Address class:

CREATE TYPE address_sqlj AS OBJECT
  EXTERNAL NAME 'Address' LANGUAGE JAVA
  USING SQLData(
    line1_sqlj varchar2(20) EXTERNAL NAME 'line1',
    line2_sqlj varchar2(20) EXTERNAL NAME 'line2',
    city_sqlj  varchar2(20) EXTERNAL NAME 'city',
    state_code_sqlj varchar2(2) EXTERNAL NAME 'state_code',
    zip_sqlj varchar2(13) EXTERNAL NAME 'zip',
    STATIC FUNCTION set_address (p_line1 VARCHAR2, p_line2 VARCHAR2,
        p_city VARCHAR2, p_state_code VARCHAR2, p_zip VARCHAR2)
      RETURN address_sqlj EXTERNAL NAME 'setAddress (java.lang.String, java.lang.String,
        java.lang.String, java.lang.String, java.lang.String) return Address',
    MEMBER FUNCTION get_address RETURN VARCHAR2
      EXTERNAL NAME 'Address.getAddress() return java.lang.String'
  )
NOT FINAL;
/

Here's how you can verify the output of this statement:

SQL> desc address_sqlj

Here's the output:

address_sqlj is NOT FINAL
Name                                                     Null?  Type
-------------------------------------------------------- ------ ---------
LINE1_SQLJ                                                      VARCHAR2(20)
LINE2_SQLJ                                                      VARCHAR2(20)
CITY_SQLJ                                                       VARCHAR2(20)
STATE_CODE_SQLJ                                                 VARCHAR2(2)
ZIP_SQLJ                                                        VARCHAR2(13)

METHOD
------
STATIC FUNCTION SET_ADDRESS RETURNS ADDRESS_SQLJ
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ---------------
P_LINE1                        VARCHAR2                IN
P_LINE2                        VARCHAR2                IN
P_CITY                         VARCHAR2                IN
P_STATE_CODE                   VARCHAR2                IN
P_ZIP                          VARCHAR2                IN

METHOD
------
MEMBER FUNCTION GET_ADDRESS RETURNS VARCHAR2

The USING clause specifies the interface that the SQLJ object type implements. It's one of the interfaces of java.sql.SQLData or oracle.sql.ORAData, along with oracle.sql.ORADataFactory, and the corresponding Java class must implement one of these interfaces.

The EXTERNAL clause specifies the mapping between each SQLJ object type attribute and member methods to the corresponding Java equivalents, and a mapping between the SQLJ object type as a whole to the Java class as a whole.

Use the SQLJ Object Type in PL/SQL and/or SQL Just Like Any Object Type

Once you've created the SQLJ object type, you can use it just like any other object type. For example, you can use it to build an object table or you can use it as the data type of a column in an object-relational table. Here's an example to illustrate this.

First, I create an object table based on the SQLJ object type address_sqlj. Here's the code:

CREATE TABLE address_master_sqlj OF address_sqlj;

Then I use an INSERT statement with the set_address member method invoked on the object type to insert a row into this table. Here's the code:

insert into address_master_sqlj
values(address_sqlj.set_address('1 Oracle parkway',null,'Redwood Shores',
                                                         'CA ', '41246 '));

Next, I query the object table to select columns that are mapped to the attributes of the SQLJ object type. Here's the code:

SELECT a.line1_sqlj, a.line2_sqlj FROM address_master_sqlj a;

Here's the output of this query:

SQL> SELECT a.line1_sqlj, a.line2_sqlj FROM address_master_sqlj a;
LINE1_SQLJ           LINE2_SQLJ
-------------------- ----------
1 Oracle parkway

Also, I can invoke member methods of the SQLJ object type as follows:

SELECT a.get_address() FROM address_master_sqlj;

Here's the output of this query:

SQL> select a.get_address() from address_master_sqlj a;

A.GET_ADDRESS()
----------------------------------------------
1 Oracle parkway null Redwood Shores, CA 41246

Tip�

You can declare member methods of a SQLJ object type as STATIC. STATIC member functions can map to static Java methods of the corresponding mapping Java class, and they're used in INSERT statements to invoke either a user-defined constructor of the Java class or a static Java method that returns the class type.