java与oracle type间传递数据

写一点东西，以便以后查看，省的忘记了。

1.建一个oracle  type ,对应java的类

create or replace type QUERYVO as object{

id varchar2(20),

name varchar2(30)

}

public class QueryUtilVo {

private String id;

private String name;

}

创建包,声明一个游标类型（返回java数据集）

create or replace package pak_query_util is

  type resultcursor is ref cursor;

end pak_query_util;

 

2.建议存储过程，

create or replace procedure pro_query_util(param in QUERYVO,outcursor in out pak_query_util.resultcursor)  is

  begin

........

end pro_query_util;

3.java往数据库传递数据,,需要做JAVA数据类型和存储过程类型type的映射,下面的StructDescriptor是mapping Oracle端AS OBJECT类型,

Connection conn = null;

CallableStatement cs = null;

try {

      conn = dao.getConn();

     QueryUtilVo vo = new QueryUtilVo("150125113", "张三");

    String sql = "{call pro_query_util(?,?)}";
    cs = conn.prepareCall(sql);

StructDescriptor recDesc = StructDescriptor.createDescriptor( "QUERYVO", conn);//对应的数据库type名称，一定要大写
  Object[] record = new Object[2];
  record[0] = this.getId();
  record[1] = this.getName();
  STRUCT struct = new STRUCT(recDesc, conn, record);

cs.setObject(1, struct, OracleTypes.STRUCT);

cs.registerOutParameter(2, OracleTypes.CURSOR);
    cs.execute();

rs = (ResultSet) cs.getObject(2);

while (rs.next()) {
     System.out.println(rs.getString("name") + "    "
       + rs.getString("id") ;
    }

}

4.如果每次调用都需要做Java bean的到Oracle的"AS OBJECT"类型的mapping,则很繁琐,可以然Java bean实现oracle.sql.ORAData,这样就不用在调用时候在做mapping了.

import oracle.jdbc.OracleTypes;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.Datum;
import oracle.sql.ORAData;
import oracle.sql.ORADataFactory;

public class QueryUtilVo implements ORAData{

public static final String _ORACLE_TYPE_NAME = "QUERYVO";//类对应的oracle type （名称必须大写）
 protected MutableStruct _struct;
 static int[] _sqlType;//字段参数对应的oracle type 中参数的类型
 static int _paramNum = 2;//参数数量
 static ORADataFactory[] _factory = new ORADataFactory[_paramNum];

static {
  _sqlType = new int[_paramNum];
  for (int i = 0; i < _paramNum; i++) {
   _sqlType[i] = OracleTypes.VARCHAR;
  }
 }

@Override
 public Datum toDatum(Connection conn) throws SQLException {
  _struct.setAttribute(0, this.id);
  _struct.setAttribute(1, this.name);
  return _struct.toDatum(conn, _ORACLE_TYPE_NAME);
 }

public QueryUtilVo() {
  super();
  _struct = new MutableStruct(new Object[_paramNum], _sqlType, _factory);
 }

private String id;

private String name;

......

}

 java调用时，就不需要再做Java bean 到Oracle "AS OBJECT"数据类型的mapping

QueryUtilVo vo = new QueryUtilVo("150125113", "张三");

String sql = "{call pro_query_util(?,?)}";
    cs = conn.prepareCall(sql);
    cs.setObject(1, vo);
    cs.registerOutParameter(2, OracleTypes.CURSOR);
    cs.execute();
    rs = (ResultSet) cs.getObject(2);
    while (rs.next()) {
	System.out.println(rs.getString("name") + " "
	+ rs.getString("id") ;

    }
}

 

 注意：这种传递方法，会出现乱码，我就被这个问题纠结了好久，需要一个jar包。(orai18n.jar,ojdbc14.jar)

oracle11g关于字符集的jdbc驱动,orai18n.jar 支持集合的驱动程序 将java的集合转换为数据库集合类型

http://download.csdn.net/detail/oshusheng1/6425995

转自     http://blog.csdn.net/kkdelta/article/details/7226331