java调用oracle存储过程 传递 自定义数组,对象

一, 引入jar包  nls_charset12.jar 、orai18n.jar

二、oracle 创建对象

       create or replace type questionAnswer as object(//自定义对象 题目答案
        answer_no  varchar2(10),
        answer_content varchar2(1000)
       )

        create or replace type questionAnswer_array  as array(10) of questionanswer //定义数组存放题目答案

        create or replace type SingleQuestion  as object  //题目对象
        (
        question_type varchar2(1),
        question_name varchar2(200),
        right_key  varchar2(10),
       questionAnswer_list  questionAnswer_array
       )

       create or replace type SINGLEQUESTION_ARRAY  as array(100) of SINGLEQUESTION //存放题目数组

三、Java代码

     //题目实体类

     import java.util.List;
     public class SingleQuestion {
    private String question_type;//题目类型
    private String question_name;//题目名称
    private String right_key;//正确答案
    private List<QuestionAnswer> questionAnswer;//答案内容
public String getQuestion_type() {
return question_type;
}
public void setQuestion_type(String question_type) {
this.question_type = question_type;
}
public String getQuestion_name() {
return question_name;
}
public void setQuestion_name(String question_name) {
this.question_name = question_name;
}
public String getRight_key() {
return right_key;
}
public void setRight_key(String right_key) {
this.right_key = right_key;
}
public List<QuestionAnswer> getQuestionAnswer() {
return questionAnswer;
}
public void setQuestionAnswer(List<QuestionAnswer> questionAnswer) {
this.questionAnswer = questionAnswer;
}
public SingleQuestion() {
super();
}
}

     //题目答案实体类

     public class QuestionAnswer {
    private String answer_no;//答案序号
    private String answer_content;//答案内容
public String getAnswer_no() {
return answer_no;
}
public void setAnswer_no(String answer_no) {
this.answer_no = answer_no;
}
public String getAnswer_content() {
return answer_content;
}
public void setAnswer_content(String answer_content) {
this.answer_content = answer_content;
}
public QuestionAnswer() {
super();
}
public QuestionAnswer(String answer_no, String answer_content) {
super();
this.answer_no = answer_no;
this.answer_content = answer_content;
}
@Override
public String toString() {
return "QuestionAnswer [answer_no=" + answer_no + ", answer_content=" + answer_content + "]";
}
 
} 

//调用oracle工具类

   public class ObjectConvert {
   static final String driver_class  = "oracle.jdbc.driver.OracleDriver";
   static final String connectionURL = "jdbc:oracle:thin:@localhost:1521:orcl";
   static final String userID        = "user";
   static final String userPassword  = "password";
   public static void runTest(String subject_no,List<SingleQuestion> single) {
       Connection  con = null;
       CallableStatement stmt = null ;
       try {
        Class.forName(driver_class);
           con = (Connection) DriverManager.getConnection(connectionURL, userID, userPassword);
        StructDescriptor tDescriptor1 = StructDescriptor.createDescriptor("SINGLEQUESTION", con);
        List<STRUCT> structs = new ArrayList<STRUCT>();
        Object[] tObject  = null ;
        for(int i = 0; i<single.size(); i++){
        tObject = new Object[4];
        tObject[0] = single.get(i).getQuestion_type();
        tObject[1] = single.get(i).getQuestion_name();
        tObject[2] = single.get(i).getRight_key();
        StructDescriptor tDescripto = StructDescriptor.createDescriptor("QUESTIONANSWER", con);
        List<STRUCT> struc = new ArrayList<STRUCT>();
        Object[] o=null;
        for(int j=0;j<single.get(i).getQuestionAnswer().size();j++){
        o=new Object[2];
        o[0]=single.get(i).getQuestionAnswer().get(j).getAnswer_no();
        o[1]=single.get(i).getQuestionAnswer().get(j).getAnswer_content();
        STRUCT tStruct1 = new STRUCT(tDescripto, con, o); 
        struc.add(tStruct1);   
        }
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("QUESTIONANSWER_ARRAY", con);
        ARRAY tArray = new ARRAY(arrayDescriptor, con, struc.toArray());
        tObject[3]=tArray;
        STRUCT tStruct = new STRUCT(tDescriptor1, con, tObject); 
               structs.add(tStruct);  
        }
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("SINGLEQUESTION_ARRAY", con);
        ARRAY tArray = new ARRAY(arrayDescriptor, con, structs.toArray());


           stmt = con.prepareCall("{call p_import_question(?,?)}");  
           stmt.setString(1, subject_no);
           stmt.setArray(2, tArray);
           stmt.execute();
       }  catch (SQLException e) {
           e.printStackTrace();
       } catch (Exception e) {
           e.printStackTrace();
       }finally{
        if(stmt != null){
        try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
        }
        if(con != null){
        try {
        con.close();
} catch (SQLException e) {
e.printStackTrace();
}
        }
       }
   }
  
}

参考文章:http://blog.csdn.net/stevendbaguo/article/details/46968219