Java处理Oracle中的用户自定义类型

Java对于PL/SQL中package下定义的类型并不支持（找不到，抛出invalid name pattern异常信息），需要定义schema级的类型。

 

Oracle的API:

http://www.rakeshv.org/docs/oracle/jdbc3.0/index.html

 

代码如下：

Schema Level Type:

create or replace type Guy as object
(
       name varchar2(50),
       age  number
)

create or replace type GUYS as Table of GUY

PL/SQL:

create or replace package ArrayNesta is

  PROCEDURE insert_guys(
      guys_in IN guys
  );

end ArrayNesta;

create or replace package body ArrayNesta is

  PROCEDURE insert_guys(
      guys_in IN guys
  )
  IS
  BEGIN
      for i in guys_in.first .. guys_in.last
      LOOP
        insert into ASSOCIATEARRAYNESTA(name, age) values(guys_in(i).name, guys_in(i).age);
      END LOOP;
 
  END insert_guys;
 
end ArrayNesta;

Database Table:

create table ASSOCIATEARRAYNESTA(
 name varchar2(50),
 age number
)

Java:
/**
 *
 */
package array;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;

import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

/**
 * @author nfeng
 *
 */
public class ArrayTest {

 /**
  * @param args
  */
 public static void main(String[] args) throws Exception {
  OracleDataSource ods = new OracleDataSource();
  ods.setURL("jdbc:oracle:thin:username/password@host:port:database");
  Connection conn = ods.getConnection();
  System.out.println(conn);
  StructDescriptor sd = StructDescriptor.createDescriptor("GUY", conn);
  Object[] nesta = new Object[] {"nesta", 29};
  STRUCT snesta = new STRUCT(sd, conn, nesta);
  
  Object[] kisey = new Object[] {"kisey", 30};  
  STRUCT skisey = new STRUCT(sd, conn, kisey);
  
  ArrayDescriptor ad = ArrayDescriptor.createDescriptor("GUYS", conn);
  ARRAY guysin = new ARRAY(ad, conn, new STRUCT[] {snesta, skisey});
  
  CallableStatement cs = conn.prepareCall("{ call ArrayNesta.insert_guys(?) }");
  cs.setArray(1, guysin);
  cs.execute();
  
  conn.close();
 }

}