java 调用mysql存储过程实例 java调用存储过程传入list

1. 当需要把插入多条数据的行为合并为一个事务时，可以考虑把一个list型数据作为参数传入存储过程，    
2. 可以调用jdbc内部实现类来实现，这些类在classes12.zip(oracle 8，别的版本可能是其他名字的zip包)。    
3.  如：    
4.   需要将一个list传入存储过程。    
5.  具体操作如下：    
6.   1,建立数据库对象来映射list数据类型。    
7.    --定义一个与list中各元素的数据类型相同的数据库对象    
8.    CREATE TYPE AOBJECT AS OBJECT(    
9.       aaaa          NUMBER(8),    
10.       bbbb         NUMBER(8)    
11.     )    
12.    /    
13.    --定义一个list数据库对象    
14.    CREATE TYPE ALIST AS VARRAY(100) OF AOBJECT    
15.    /    
16.    
17.   2,具体java代码如下：    
18.   import java.sql.CallableStatement;    
19.   import java.sql.Connection;    
20.   import java.sql.PreparedStatement;    
21.   import java.util.ArrayList;    
22.       
23.   /*   
24.    * 以下就是classes12.zip中的实现类   
25.   */   
26.   import oracle.jdbc.driver.OracleCallableStatement;    
27.   import oracle.sql.ARRAY;    
28.   import oracle.sql.ArrayDescriptor;    
29.   import oracle.sql.STRUCT;    
30.   import oracle.sql.StructDescriptor;     
31.       
32.   .......    
33.       
34.   public static long addRecords(ArrayList list) {    
35.    Connection con = null;    
36.    CallableStatement stmt = null;    
37.    int backVal = 0;    
38.    try {    
39.     con = pool.getConnection();    
40.     if (con != null) {    
41.      ARRAY aArray = getArray(con, "AOBJECT","ALIST", list);//该函数调用的第二三个参数必须大写    
42.      stmt = con.prepareCall("{call produce1(?,?)}"); //调用某个存储过程    
43.      ((OracleCallableStatement) stmt).setARRAY(1, aArray);    
44.      stmt.registerOutParameter(2, java.sql.Types.INTEGER);    
45.      stmt.execute();    
46.      backVal = stmt.getInt(2);    
47.     }     
48.    } catch (Exception e) {    
49.     ....    
50.    } finally {    
51.     ....//释放数据库连接    
52.    }    
53.    return backVal;    
54.   }    
55.    
56.   private static ARRAY getArray(Connection con, String OracleObj,    
57.    String Oraclelist, ArrayList objlist) throws Exception {    
58.    ARRAY list = null;    
59.    if (objlist != null && objlist.size() > 0) {    
60.     StructDescriptor structdesc = new StructDescriptor(OracleObj, con);    
61.     STRUCT[] structs = new STRUCT[objlist.size()];    
62.     Object[] result = new Object[0];    
63.     for (int i = 0; i < objlist.size(); i++) {    
64.      result = new Object[2];//数组大小应和你定义的数据库对象(AOBJECT)的属性的个数    
65.      result[0] = new Integer(..);  //将list中元素的数据传入result数组    
66.      result[1] = new Integer(..);  //    
67.          
68.      structs[i] = new STRUCT(structdesc, con, result);    
69.     }    
70.     ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,    
71.       con);    
72.     list = new ARRAY(desc, con, structs);    
73.    } // if    
74.    return list;    
75.   } // function  
//

因为上周末帮JAVA组的同事实现了一个输入数值->update记录->返回游标的存储过程，这些过程很简单。但当时就考虑到了一个问题，因为有在JAVA代码中调用这个存储过程之前先要从一个XLS文件中读取数据然后用INSERT插入ORACLE数据库表中。但发现在JAVA代码实现的方式是一条条记录地INSERT进ORACLE，当时就提出了这个问题，想在JAVA那些传个数组对象进ORACLE存储过程中，然后在ORACLE存储过程代码中分解这个数组的数据，再执行其他的操作。因此就有了下面的JAVA＋ORACLE实践日记了，嘿嘿，好久没搞JAVA了，ArrayList都不会用了。不多说，看代码：

 

首先创建两个ORACLE数据类型，目的就是为了存储结果集数据(数组等)：

 

 

CREATE OR REPLACE TYPE CDW_OBJECT AS OBJECT(
    FISCAL_MONTH                 VARCHAR2(10),
    CUSTOMER_NUMBER              VARCHAR2(10),
    CUSTOMER_NAME                VARCHAR2(50)
    );  CREATE OR REPLACE TYPE CDW_TABLE AS TABLE OF CDW_OBJECT;

表结果及数据：

 

SQL> DESC CDW_AR_SA_EXPOSURE_T;
Name            Type         Nullable Default Comments 
--------------- ------------ -------- ------- -------- 
FISCAL_MONTH    VARCHAR2(10) Y                         
CUSTOMER_NUMBER VARCHAR2(10) Y                         
CUSTOMER_NAME   VARCHAR2(50) Y                        SQL> DESC CDW_AR_SA_EXPOSURE_FACT;
Name            Type         Nullable Default Comments 
--------------- ------------ -------- ------- -------- 
FISCAL_MONTH    VARCHAR2(10) Y                         
CUSTOMER_NUMBER VARCHAR2(10) Y                         
CUSTOMER_NAME   VARCHAR2(50) Y                        SQL>

 

表结构及测试数据代码：

CREATE TABLE CDW_AR_SA_EXPOSURE_FACT (FISCAL_MONTH VARCHAR2(10),CUSTOMER_NUMBER VARCHAR2(10),CUSTOMER_NAME VARCHAR2(50));
 
 
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200708','1001','XIEFENG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200705','1002','MANTISXF');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200706','2001','CHENGUOZHENG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200808','3001','XIAOFANG');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200809','1056','ZIMING');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200810','7701','BOSHI');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200811','1821','BOGE');
 INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200903','2431','FEIZAI');
 
 
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200811','3301','BOSHI_HAOREN');
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200812','1921','BOGE_HAOREN');
 INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200905','6666','FEIZAI_HAOREN');
 
 COMMIT;

 

存储过程代码：

 

CREATE OR REPLACE PROCEDURE SP_CARC_UPLOAD_FILE_TEST(OUT_CURSOR OUT SYS_REFCURSOR, V_ARRAY IN CDW_TABLE) AS
    -- DECLARE THE VARIABLE AND CURSOR TYPE
    V_STEP VARCHAR2(100);
    VCOUNTS NUMBER;
  
  
  BEGIN
    V_STEP := 'INSERT RECORDS FROM ARRAY';
    FOR I IN 1..V_ARRAY.COUNT LOOP
    INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES
    (
     V_ARRAY(I).FISCAL_MONTH,
     V_ARRAY(I).CUSTOMER_NUMBER,
     V_ARRAY(I).CUSTOMER_NAME
    );
    
    DBMS_OUTPUT.PUT_LINE('FISCAL_MONTH: '||V_ARRAY(I).FISCAL_MONTH);
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_NUMBER: '||V_ARRAY(I).CUSTOMER_NUMBER);
    DBMS_OUTPUT.PUT_LINE('CUSTOMER_NAME: '||V_ARRAY(I).CUSTOMER_NAME);
        
    IF MOD(I,1000) = 0 THEN
      COMMIT;
    END IF;
    END LOOP;
    COMMIT;
    
    V_STEP := 'GET THE INVALID CUSTOMER COUNT';
      SELECT COUNT(1)
        INTO VCOUNTS
        FROM CDW_AR_SA_EXPOSURE_T STG
       WHERE NOT EXISTS (SELECT 1
                FROM CDW_AR_SA_EXPOSURE_FACT FACT
               WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
                 AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
    
    --FETCH CUR_MATCH_USERID INTO RT_CUR_MATCH_USERID;
    
    IF VCOUNTS = 0 THEN
      V_STEP := 'UPDATE USER_INFORMATION';
          DBMS_OUTPUT.PUT_LINE('UPDATED SQL COUNT: ' || SQL%ROWCOUNT);
          OPEN OUT_CURSOR FOR SELECT DUMMY AS CUSTOMER_NUMBER FROM DUAL WHERE ROWNUM < 1;
          
    ELSE
          V_STEP     := 'RETURN CURSOR WHICH STORED ALL INVILD CUSTOMER_DETAILS';
          OPEN OUT_CURSOR FOR 
          SELECT CUSTOMER_NUMBER
            FROM CDW_AR_SA_EXPOSURE_T STG
           WHERE NOT EXISTS (SELECT 1
                    FROM CDW_AR_SA_EXPOSURE_FACT FACT
                   WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER
                     AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH);
    END IF;
    
    V_STEP := 'DELETE DATA FROM TABLE CDW_AR_SA_EXPOSURE_T';
    /*DELETE FROM CDW.CDW_AR_SA_EXPOSURE_T;
    COMMIT;*/
    
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        ROLLBACK;
        --EXECUTE IMMEDIATE 'TRUNCATE TABLE CDW_AR_SA_EXPOSURE_T REUSE STORAGE';
        DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP);
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM);
      END;
    
  END SP_CARC_UPLOAD_FILE_TEST;

JAVA 代码:

 

import oracle.sql.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import java.util.*;public class ArrayTest {
 static public Connection conn;
 static public OracleCallableStatement stmt = null;
 public Connection getConnectionDB()throws SQLException, ClassNotFoundException{
  Class.forName("oracle.jdbc.driver.OracleDriver");     // B. 创新新数据库连接
     conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oraxf", "scott", "tiger");
     DatabaseMetaData md = conn.getMetaData();
     System.out.println("数据库版本：");
     System.out.println("------------------------------------------------");
     System.out.println(md.getDatabaseProductVersion());
     System.out.println();
     System.out.println("驱动程序名称与版本：");
     System.out.println("------------------------------------------------");
     System.out.print(md.getDriverName() + " " + md.getDriverVersion());
     
     return conn;
     
 }
 
  public static void main(String[] my)throws SQLException, ClassNotFoundException{
   try{
      
   ArrayTest at = new ArrayTest();
   at.getConnectionDB();
   
   ArrayList myArray = new ArrayList();
   String[][] values = {
     {"200811","3301","BOSHI_HAOREN"},
     {"200812","1921","BOGE_HAOREN"},
     {"200905","6666","FEIZAI_HAOREN"}
   };
   myArray.add(values);
      ARRAY array = getArray("CDW_OBJECT","CDW_TABLE",myArray);
      stmt = (OracleCallableStatement)conn.prepareCall("begin SP_CARC_UPLOAD_FILE_TEST(?,?); end;");
      stmt.registerOutParameter(1, OracleTypes.CURSOR,"OUT_CURSOR");
      stmt.setArray(2, array);
      stmt.execute();
      
      ResultSet rs = (ResultSet)stmt.getObject(1);
      
      while(rs.next()){
       String CUSTOMER_NUMBER = rs.getString("CUSTOMER_NUMBER");
       System.out.println("CUSTOMER_NUMBER: "+CUSTOMER_NUMBER);
      }
      
      at.closeConnectionDB();
   }
   catch(Exception e){
    e.printStackTrace();
   }
  }
  
  public static ARRAY getArray(String OracleObj,String OracleTbl, ArrayList alist) throws Exception{
   
   // my code
   ARRAY list = null;
   
   if (alist != null && alist.size() > 0){
    StructDescriptor structdesc = new StructDescriptor(OracleObj,conn);
    //STRUCT[] structs = new STRUCT[alist.size()];
    Object[] result = null;
    //String[][] myArray = new String[alist.size()][3];
    //alist.toArray(myArray);
   /* for(int i=0;i<alist.size();i++){
     String obj[] = (String [])alist.get(i);
     result = new Object[3];
     result[0] = obj[0].toString();
     result[1] = obj[1].toString();
     result[2] = obj[2].toString();
     
     structs[i] = new STRUCT(structdesc,conn,result);
    }
    for(int i=0;i<alist.size();i++){
     ResultSet rs = (ResultSet)alist.get(i);
     for(int j=0;j<3;j++){
      result = new Object[3];
      result[0] = rs.getObject(1).toString();
      result[1] = rs.getObject(2).toString();
      result[2] = rs.getObject(3).toString();
     }
     structs[i] = new STRUCT(structdesc,conn,result);
    }*/
    
    Object[] o1 = new Object[]{"200811","3301","BOSHI_HAOREN"};
    Object[] o2 = new Object[]{"200812","1921","BOGE_HAOREN"};
    Object[] o3 = new Object[]{"200905","6666","FEIZAI_HAOREN"};
    
    STRUCT s1 = new STRUCT(structdesc, conn, o1);
    STRUCT s2 = new STRUCT(structdesc, conn, o2);
    STRUCT s3 = new STRUCT(structdesc, conn, o3);
        
    STRUCT[] structs = {s1,s2,s3};
    /*
    for(int i=0;i<alist.size();i++){
     result = new Object[3];
     result[0] = myArray[i][0].toString();
     result[1] = myArray[i][1].toString();
     result[2] = myArray[i][2].toString();
     
     structs[i] = new STRUCT(structdesc,conn,result);
    }*/
    ArrayDescriptor arraydesc = new ArrayDescriptor(OracleTbl,conn);
    list = new ARRAY(arraydesc,conn,structs);
   }
   return list;
   
  }
  
  public void closeConnectionDB()throws SQLException{
   conn.close();
  }
}

 

输出结果：

-- JAVA 控制台

数据库版本：

------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

驱动程序名称与版本：

------------------------------------------------
Oracle JDBC driver 10.1.0.2.0CUSTOMER_NUMBER: 6666
CUSTOMER_NUMBER: 1921
CUSTOMER_NUMBER: 3301

-- 数据库SQLPLUS

SQL> SELECT * FROM CDW_AR_SA_EXPOSURE_T;
FISCAL_MONTH CUSTOMER_NUMBER CUSTOMER_NAME
------------ --------------- --------------------------------------------------
200811       3301            BOSHI_HAOREN
200812       1921            BOGE_HAOREN
200905       6666            FEIZAI_HAORENSQL>

在跑JAVA代码时可能会出现相关问题：

java程序中使用Oracle的对象：

StructDescriptor structdesc = new StructDescriptor(OracleObj,conn);

出现这个错误：

java.sql.SQLException: Fail to construct descriptor: Invalid arguments
java.sql.SQLException: 无法构造描述符: Invalid arguments

原因为数据库连接dbConn为空，可是这种Exception着实有点让人迷糊。切记：友好的提示信息对问题的定位和解决非常重要。