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

Java对于PL/SQL中package下定义的类型并不支持(找不到,抛出invalid name pattern异常信息),需要定义schema级的类型。
 
Oracle的API:
 
代码如下:
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();
 }
}
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值