SQLData Implementation--SQLDataExample.java

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