• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

Passing Nested Type from Java to DB

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 = "jdbc:oracle: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.....
 
No matter how many women are assigned to the project, a pregnancy takes nine months. Much longer than this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!