GeeCON Prague 2014*
The moose likes JDBC and the fly likes Passing Nested Type from Java to DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing Nested Type from Java to DB" Watch "Passing Nested Type from Java to DB" New topic
Author

Passing Nested Type from Java to DB

arpit kumar Dubey
Greenhorn

Joined: Sep 28, 2011
Posts: 1
I have following 2 types

CREATE OR REPLACE TYPE "GRSO_COMPETENCY" as object
(
COMPETENCY_ID NUMBER(5),
COMPETENCY_DESC VARCHAR2(250),
PROFICIENCY_ID NUMBER(3)
);

CREATE OR REPLACE TYPE grso_competency_array AS TABLE OF grso_competency;


CREATE OR REPLACE TYPE "MASTER_TYPE" as object
(
CANDIDATE_ID number,
COMPETENCY_DTLS grso_competency_array
);

CREATE OR REPLACE TYPE MASTER_TYPE_ARRAY AS TABLE
OF MASTER_TYPE
/


Now i have made 2 VO

1st VO - THis is Child VO


import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class ChildArrayVO implements Serializable,SQLData {

/**
*
*/
private String sql_type;
private Integer CompetencyID =null;
private String CompetencyDesc=null;
private Integer ProfId=null;
public ChildArrayVO() {
}

public ChildArrayVO(String sql_type,Integer CompetencyId,String CompetencyDesc,Integer ProfId) {
this.sql_type=sql_type;
this.CompetencyID=CompetencyId;
this.CompetencyDesc=CompetencyDesc;
this.ProfId=ProfId;
}

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

public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
this.CompetencyID=stream.readInt();
this.CompetencyDesc=stream.readString();
this.ProfId=stream.readInt();
}

public void writeSQL(SQLOutput stream) throws SQLException {

stream.writeInt(CompetencyID);
stream.writeString(CompetencyDesc);
stream.writeInt(ProfId);

}

public String getCompetencyDesc() {
return CompetencyDesc;
}

public void setCompetencyDesc(String competencyDesc) {
CompetencyDesc = competencyDesc;
}

public Integer getCompetencyID() {
return CompetencyID;
}

public void setCompetencyID(Integer competencyID) {
CompetencyID = competencyID;
}

public Integer getProfId() {
return ProfId;
}

public void setProfId(Integer profId) {
ProfId = profId;
}


}


2nd VO - THis is Parent VO


import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class ArrayVO implements Serializable,SQLData {

/**
*
*/
private String sql_type;
private Integer CandidateId =null;
private ChildArrayVO childRecord=null;

public ArrayVO() {
}

public ArrayVO(String sql_type,Integer CandidateId,ChildArrayVO childRecord) {
this.sql_type=sql_type;
this.CandidateId=CandidateId;
this.childRecord=childRecord;

}

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

public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
CandidateId = stream.readInt();
//childRecord.setCompetencyID(stream.readInt());
//childRecord.setCompetencyDesc(stream.readString());
//childRecord.setProfId(stream.readInt());
childRecord = (ChildArrayVO) stream.readObject();
}

public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(CandidateId);
//stream.writeInt(childRecord.getCompetencyID());
//stream.writeString(childRecord.getCompetencyDesc());
//stream.writeInt(childRecord.getProfId());

stream.writeObject(childRecord);

}

public Integer getCandidateId() {
return CandidateId;
}

public void setCandidateId(Integer candidateId) {
CandidateId = candidateId;
}

public ChildArrayVO getChildRecord() {
return childRecord;
}

public void setChildRecord(ChildArrayVO childRecord) {
this.childRecord = childRecord;
}


}


I want to pass the Nested Type Array

The Main code is as below
-----------------------------

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.util.Dictionary;


import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;


public class NestedArraytoDB {

public NestedArraytoDB() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbcracle:thin:@172.90.12.85:1521/TEST";
java.sql.Connection con = DriverManager.getConnection(url,"scott", "tiger123");

System.out.println("hello");

Dictionary map1 = (Dictionary)con.getTypeMap();
Dictionary map2 = (Dictionary)con.getTypeMap();
Thread thread = Thread.currentThread();
ClassLoader classLoader = thread.getContextClassLoader();
//Corresponding mapping java class to be used.
Class classComp1 = classLoader.loadClass("ArrayVO");
Class classComp2 = classLoader.loadClass("ChildArrayVO");

map1.put("MASTER_TYPE", classComp1);
map2.put("GRSO_COMPETENCY", classComp2);

ArrayDescriptor arrayDesc1 = ArrayDescriptor.createDescriptor( "MASTER_TYPE_ARRAY", con);
ArrayDescriptor arrayDesc2 = ArrayDescriptor.createDescriptor( "GRSO_COMPETENCY_ARRAY", con);


ArrayVO[] arrayList;
ChildArrayVO[] childList;

ChildArrayVO childArrayVO = new ChildArrayVO("GRSO_COMPETENCY",
11,
"Test12",
11

);

ArrayVO arrayVO = new ArrayVO("MASTER_TYPE",
1,
childArrayVO
);

arrayList = new ArrayVO[1];

arrayList[0]=arrayVO;

//childList=new ChildArrayVO[1];
//childList[0]=childArrayVO;


ARRAY arrComp = new ARRAY(arrayDesc1, con, arrayList);

System.out.println(arrayVO.getCandidateId());
System.out.println(arrayVO.getChildRecord().getCompetencyDesc());

}



public static void main(String[] args){
try{
new NestedArraytoDB();
}catch(Exception e ){e.printStackTrace();}


}
}


Can any one help please.....
 
GeeCON Prague 2014
 
subject: Passing Nested Type from Java to DB