java.sql.SQLData - Oracle object mapping problem

Question

Hi guys,

I am using java.sql.SQLData interface to map my java objects to Oracle database types.

For example, I have an object type Person in Oracle DB defined as:

CREATE OR REPLACE TYPE PERSON AS OBJECT
(
      PERSON_ID NUMBER,
      PERSON_NAME VARCHAR2(100)
);

Corresponding Java type is:

public class Person implements SQLData {

 private String sql_type = "PERSON";
 private int personId;
 private String personName;

 public int getPersonId() {
  return personId;
 }

 public void setPersonId(int personId) {
  this.personId = personId;
 }

 public String getPersonName() {
  return personName;
 }


 public void setPersonName(String personName) {
    this.personName = personName;
 }

 public void readSQL(SQLInput stream, String typeName) throws SQLException 
 {
     this.sql_type=typeName;
     this.personId = stream.readLong();
     this.personName = stream.readString();
 }

 public void writeSQL(SQLOutput stream) throws SQLException 
 {
       stream.writeLong(this.personId);
        stream.writeString(this.personName);
 }
}

This works fine currently and populates Person Objects from database type.

Now, I have a another type and it's corresponding collection as follows:

CREATE OR REPLACE TYPE SUBJECT AS OBJECT
    (
          SUBJECT_ID NUMBER,
          SUBJECT_NAME VARCHAR2(100)
    );
 -- Corresponding List
 CREATE OR REPLACE TYPE SUBJECT_LIST IS TABLE OF SUBJECT;

I have to create a new entry in type PERSON with this collection as follows:

CREATE OR REPLACE TYPE PERSON AS OBJECT
(
      PERSON_ID NUMBER,
      PERSON_NAME VARCHAR2(100),
      SUBJECT_LIST TYPE SUBJECT_LIST
);

To make this change I have to change my java Person class. I tried adding java.sql.Array parameter but it is not working.

Can you please help here to map the new PERSON Object type to Java type?

Thanks in advance.

--Siddharth

Answer 1

The documentation of SQLInput (link below) has this on the first line...

"This interface [ie SQLInput] ... is used by the driver behind the scenes, and a programmer never directly invokes SQLInput methods."

Are you sure you should be using SQLInput directly? Is there an example you're following?

Ref: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/SQLInput.html#readObject%28%29

Answer 2

hi sidhart, can u please show how you have solved this particular problem. I am also facing problem while passing a two level of object in oracle stored procedure.

Answer 3

Hey I did not access this so sorry I could not reply. What I did was to have a java.sql.Array defined in my class. This maps to the the nested type in Oracle database. So in this case my Person class will have an instance variable :

java.sql.Array subjectList;

To set the value you will need to do the following: Subject[] subjectListArray=null; Person p = new Person(); p.setSubjectList(new oracle.sql.ARRAY(getOracleArray(typeName, connection, subjectListArray)));

The getOracleArray method will be something like this:

public static oracle.sql.ArrayDescriptor getOracleArray(final String typeName)throws SQLException { if(typeName==null)return null; final oracle.sql.ArrayDescriptor arrayDescriptor = new oracle.sql.ArrayDescriptor( typeName, con); return arrayDescriptor; }