Oracle預存程序及Java調用

Oracle預存程序及Java調用

初次研究出bug的地方非常多，所以注意點非常多，花了我三天時間除盡所有bug，我會把注意點都列出來，可能有落下的地方，還請多指正，相互探討。

首先上最終測試成功版預存程序代碼：（裡面代碼可能不盡對你都有用，借鑒參考吧，我全貼出來也是為了我以後好查）

說一下jar包用的是ojdbc14.jar，至於什麼class12.jar、ojdbc6.jar啊應該都可以，只要一種就可以了。

說說我的需求，以便讓大家更順利的看懂My Code，我的需求是：預存程序從Java端接收兩個參數userid（使用者）和topicid（話題），在預存程序進行迴圈查詢目前使用者對當前話題的點贊記錄,如果有記錄，則record為設定1，沒有則為0，最後返回一個結果集，是反應 使用者=>話題=>record相互對應的關係表。

好了不廢話了，上代碼吧，學習階段，所以難免情緒波動和囉嗦，也是希望以最直白能懂的方式敘述出來。

------------在資料庫建立一個type,對應JAVA端要傳入的對象結構 : 
create or replace type tp_arr3 as Object   
(   
  userid  nvarchar2(40),    --這裡從varchar2改成nvarchar2類型才能跟Java的String匹配上 
  topicid nvarchar2(40),
  record nvarchar2(4)   
)

---多次測試建立可能會出現“ORA-02303: 無法使用類型或表的相關性來刪除或取代一個類型”這個錯誤，這時只要換一個類型名字再建立就可以了
----------
CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3
--------------建立包 ，建立一個遊標類型用來放輸出參數 
create or replace package testpkg as
 type testcur is ref cursor;
 end testpkg;
------建立預存程序 ，定義兩個參數，一個入參，是一個物件類型數組（這種類型應該可以滿足大部分複雜需求了），一個出參，是用遊標存放查詢值 
 CREATE OR REPLACE  procedure findRecord(type_obj IN tp_arr_tbl3,result out testpkg.testcur) 
  as
  t tp_arr3;
  sql2 varchar2(500);
  sql3 varchar2(500);
  v_count varchar2(4);    --臨時中間變數，用來存放對應的record 
 BEGIN   
    sql2 :='drop table tb_temp';
    sql3 :='CREATE TABLE tb_temp( userid varchar2(40),topicid varchar2(40) primary key, record varchar2(4))';
      execute immediate sql2;
      execute immediate sql3;
     
      FOR i IN type_obj.first()..type_obj.last()
      LOOP
      t:= type_obj(i);
      select count(*) into v_count from scott.tb_praise_rel where userid=t.userid and topicid=t.topicid;
      dbms_output.put_line( t.userid || '=>'||t.topicid ||'=>' || v_count);
      insert into tb_temp values (t.userid,t.topicid, v_count);
      END LOOP;
      COMMIT;
     
      open result for select * from tb_temp;
 END;
--------------執行預存程序

declare
ta tp_arr_tbl3:=tp_arr_tbl3();      --對象的聲明 
t tp_arr3:=tp_arr3('0','0','0');    --聲明及賦初值（必要步驟）
begin
for i in 1..12 loop
ta.extend;
  t.userid:='1';
  t.topicid:=i;
  t.record:='0';
  ta(i):=t;
  findRecord(ta);
  end loop;
  end findRecord;

 ----------------------表查詢測試部分
 select * from tb_temp;
 
 select * from scott.tb_praise_rel;
 
 select userid from scott.tb_praise_rel where userid='1' and topicid='1';
-----------------------遊標測試，後來沒用，可以略過
 cursor testcur is select userid,topicid from scott.tb_praise_rel;
  cur testcur%rowtype;
 
  open testcur;
    loop
    fetch testcur into cur;
    exit when testcur%notfound;
    dbms_output.put_line( 'userid:' || cur.userid || ',topicid:' || cur.topicid );
    update tb_temp set record='1' where userid=cur.userid and topicid=cur.topicid;
    end loop;
    dbms_output.put_line('----------------------');
    close testcur;
      COMMIT;

注意點：1.測試使用者起初用的Scott，發現沒有執行許可權，對其進行賦予dba許可權還是不行，遂後來用的system；2.多次測試建立可能會出現“ORA-02303: 無法使用類型或表的相關性來刪除或取代一個類型”這個錯誤，這時只要換一個類型名字再建立就可以了；3.由於我的tb_temp表有唯一欄位約束，所以預存程序每次進來先刪表，再建表，再插入資料；4.記得該打分號的地方不要漏，不該打的地方不要多；5.執行預存程序的時候，要先聲明並賦初值，不然也會報錯；6.注意pl/sql裡執行預存程序測試賦值時候ta.extend不能少；6.自訂類型要注意的地方很多，比如nvarchar2和JavaString類型的定義；7.簡單說tp_arr3 類型是指一條記錄，tp_arr_tbl3是指多條記錄；

--點贊關係表
create table tb_praise_rel(
      id varchar2(40) primary key,
      userid varchar2(40),            --使用者id
      topicid  varchar2(40),            --話題id
      remarks1 varchar2(3000),        --備用欄位
      remarks2 varchar2(3000),
      remarks3 varchar2(3000)
);

再上Java調用代碼：

package com.lofter.svntesr;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import Oracle.jdbc.OracleTypes;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import com.lofter.bean.ProcedureBean;

public class ProcedureTest3 {

 /**
  * @param args
  */
 public static void main(String[] args) {
  try { 
   
            Class.forName("oracle.jdbc.driver.OracleDriver"); 
 
            String url = "jdbc:oracle:thin:@localhost:1521:orcl"; 
 
            //網上很多卡在擷取con這個地方的，我最初也是，說是什麼jar包問題，刪掉class12啊，什麼oracle與apache串連池衝突啊，但是我其實是預存程序沒寫對，最後繞了一圈回來還是用的這種方法測試通過，並沒有加((org.apache.commons.dbcp.PoolableConnection) conn).getInnermostDelegate()
            Connection con = DriverManager.getConnection(url, "system", "a"); 
 
      //      PreparedStatement pstmt = null; 
            CallableStatement cs = null;
            ResultSet rs=null;
           
            List<ProcedureBean> list = new ArrayList<ProcedureBean>();
      for (int i = 1; i <= 12; i++) {
       String r = i + "";
       list.add(new ProcedureBean("1", r, "0"));
      }
     // list.add(new ProcedureBean("1","5f60b0f0-03d9-4671-b945-936fe821fe19", "0"));
     
      //如果預存程序是用我這種對象數組as object類型，則java調用這一步必不可少，這是對之前在pl/sql中聲明的tp_arr3 類型的映射，表示在pl/sql中去匹配你自訂的類型
      //還有注意要大寫,不然可能會報“無效名稱模式”
      StructDescriptor recDesc = StructDescriptor.createDescriptor(
     "TP_ARR3", con);

      //這一步是將你自訂的類型轉化成oracle自己的類型，即STRUCT，相當於一個Object類，因為oracle的開發人員也不知道你會定義一個什麼名字的類型，反正只用提供一個規則，最後大家都照著這個規則來轉化就是了
   ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
   for (ProcedureBean pb : list) {
    System.out.println(pb);
    Object[] objs = new Object[3];
    objs[0] = pb.getUserid();
    objs[1] = pb.getTopicid();
    objs[2] = pb.getRecord();
    STRUCT item = new STRUCT(recDesc, con, objs);
    pstruct.add(item);
   }
   
   //這是第二步映射，映射我在oracle中自訂的tp_arr_tbl3類型，注意也要大寫，網上也有說要加包名，不是同一個使用者要加使用者首碼什麼的，我沒有加，測試也通過，可能不是極端情況吧
   oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con); 
   
            oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, pstruct.toArray());
           
            //也有說調用的時候要加包名的
            cs = con.prepareCall("{call findRecord(?,?)}");
           
            //設定參數這裡，1、2分別對應預存程序findRecord(?,?)中參數的位置，注意位置不要錯了
            cs.setArray(1, array);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.execute();
            rs=(ResultSet) cs.getObject(2); //取資料也是根據對應參數位置來的
           
            while( rs.next() ){
             System.out.println("result : \t" + rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
            }
            con.commit();
        } catch (Exception e) { 
 
            e.printStackTrace(); 
 
        } 
 
    }   
 }

Java調用注意：基本上注意事項都以注釋的方式寫在代碼裡了，也有可能沒想起來，很多bug資訊由於測試通過心切，沒能及時複製下來。注意不要導錯包

2.錯誤資訊“Message file 'oracle.jdbc.driver.Messages' is missing.”，可能是你寫錯了或類型與oracle中不匹配，不要去找什麼jar包啊什麼的，網上資訊也不多，我在這繞了好久，多檢查一下上面提到的加包名、大小寫、轉類型什麼的；

還有其他沒想起來或沒碰到的bug只有親們多結合錯誤資訊猜測，多動手測測，相信就會迎刃而解了。

測試的javaBean：

package com.lofter.bean;

import java.io.Serializable;

public class ProcedureBean implements Serializable {

 private static final long serialVersionUID = 809894604693791308L;
 private String userid;
 private String topicid;
 private String record;

 public ProcedureBean() {
  super();
 }

 public ProcedureBean(String userid, String topicid, String record) {
  super();
  this.userid = userid;
  this.topicid = topicid;
  this.record = record;
 }

 public String getUserid() {
  return userid;
 }

 public void setUserid(String userid) {
  this.userid = userid;
 }

 public String getTopicid() {
  return topicid;
 }

 public void setTopicid(String topicid) {
  this.topicid = topicid;
 }

 public String getRecord() {
  return record;
 }

 public void setRecord(String record) {
  this.record = record;
 }

 @Override
 public String toString() {
  return "ProcedureBean [userid=" + userid + ", topicid=" + topicid
    + ", record=" + record + "]";
 }

}

折騰了我好幾天研究這個東西，主要是要研究對象數群組類型的，從文法都不清楚，只能參照著能看懂大概的別人代碼揣測著寫，到最後測試通過，一把辛酸淚啊，兩天研究到淩晨四點，敲了不知多少遍斷行符號鍵，點了不知多少次運行（其實也沒多少，可能也是對之前調試所有bug過程的一次發泄），因為複雜類型的參數網上很多沒講清楚，也有很多bug，所以沒辦法就用。測試期間bug不斷，一直百度，也翻了下平時都沒認真看過的教材，最後終於打通從PL/SQL調用到Java調用，其實bug出最多在Java調用上，各種類型不符，只能說搜尋引擎真強大，互連網真強大，大資料真強大。最後我想說的是：“人就怕認真”。