写一点东西,以便以后查看,省的忘记了。
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