java如何将一组对象传入Oracle存储过程

太过爱你忘了你带给我的痛 2022-08-02 12:51 141阅读 0赞

java如何将一组对象传入Oracle存储过程，需要注意的是jar包是有要求的，如果使用不当会导致只有数字能传过去，字符串传不过去。如果是Oracle11g则需要选用如下的jar包，F:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\jlib\\orai18n.jar、

D:\\program\\weblogic\\oracle\_common\\modules\\oracle.jdbc\_11.2.0\\ojdbc6.jar

例子如下：

CREATE OR REPLACE TYPE TEST_OBJECT AS OBJECT 
    (
      id number,
      name varchar2(32)
    );
    CREATE OR REPLACE TYPE TABLES_ARRAY AS VARRAY(100) OF TEST_OBJECT;
    drop table test purge;
    create table test
    (
      id number,
      name varchar2(32)
    );
    create or replace procedure t_list_to_p(arr_t in tables_array) 
    is
    begin
      for i in 1..arr_t.count loop
          insert  into test values(arr_t(i).id,arr_t(i).name);
      end loop;
      commit;
    end t_list_to_p;

import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import oracle.sql.ARRAY;
    import oracle.sql.ArrayDescriptor;
    import oracle.sql.STRUCT;
    import oracle.sql.StructDescriptor;
    
    public class TestListToProcedure {
        static final String driver_class  = "oracle.jdbc.driver.OracleDriver";
        static final String connectionURL = "jdbc:oracle:thin:@10.150.15.150:1521:orcl";
        static final String userID        = "test";
        static final String userPassword  = "test";
        public void runTest() {
            Connection  con = null;
            CallableStatement stmt = null ;
            try {
                Class.forName (driver_class).newInstance();
                con = DriverManager.getConnection(connectionURL, userID, userPassword);
                StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con);
            	List<STRUCT> structs = new ArrayList<STRUCT>();
            	Object[] 	tObject  = null ;
            	//可以将系统中VO，DTO转化成Object对象,先创建struts
            	for(int i = 0; i<10; i++){
            		tObject = new Object[2];
            		tObject[0] = i;
            		tObject[1] = "name"+i;
                    STRUCT tStruct = new STRUCT(tDescriptor, con, tObject);
                    structs.add(tStruct);   		
            	}
            	ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("TABLES_ARRAY", con);
            	ARRAY tArray = new ARRAY(arrayDescriptor, con, structs.toArray());
                stmt = con.prepareCall("{call t_list_to_p(?)}");  
                stmt.setArray(1, 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();
    				}
            	}
            }
        }
        public static void main(String[] args) {
        	TestListToProcedure testListToProcedure = new TestListToProcedure();
        	testListToProcedure.runTest();
        }
    
    }