java.sql.SQLData - Oracle object mapping problem

Question

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

cagcowboy · Accepted Answer · 2010-07-27 13:35:18Z

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

answered Jul 27, 2010 at 13:35

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sid Over a year ago

The example above is the one I am using right now. I am not using SQLInput directly. I am simply implementing the SQLData interface and the methods readSQL and writeSQL. The methods are called internally by SQLInput and SQLOutput. I am sorry if I haven't described the problem statement correctly. I need to have an Oracle collection type within an Oracle object type. I want to know how I can create the corresponding Java class. Do I need a java.sql.Array instance for the Collection type? Thanks.

cagcowboy Over a year ago

Yes, I think you'll need a java.sql.Array class, although we actually use oracle.sql.ARRAY since it already implements a lot of the methods for us.

Sid Over a year ago

So should I have an instance of java.sql.Array in person class, something like this: private java.sql.Array subjectList; In readSql... this.subjectList=stream.readArray(); In writeSQL... stream.writeArray(subjectList); Is this wat you mean?

Bilgehan Over a year ago

How can I do it type safe,SQLInput sqlInput.read depends an order how can I force items

Community · Accepted Answer · 2011-11-21 14:14:16Z

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

Collectives™ on Stack Overflow

java.sql.SQLData - Oracle object mapping problem

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related