java jdbc 传参_java – 使用简单的jdbc调用将数组作为输入参数传递给oracle存储过程...

这是我的oracle程序规范

CREATE OR REPLACE PACKAGE PKG_RE_FI AS

PROCEDURE PRC_RE_FI_DETAILS(P_FAN_NO IN VARCHAR2,

P_REF_ID IN TY_APP_REF_ID,

P_COMMENTS IN VARCHAR2,

P_BILLING_FLAG IN VARCHAR2,

P_STATUS OUT VARCHAR2);

END PKG_RE_FI;

TY_APP_REF_ID是

CREATE OR REPLACE TYPE ty_app_REF_ID as varray(500) of obj_array_ref_id

CREATE OR REPLACE TYPE obj_array_ref_id AS OBJECT(

app_ref_id VARCHAR2(100)

)

我使用Spring JDBC Framework(SimpleJdbcCall对象)来执行上述过程.下面是我声明的代码片段

this.reFIJdbcCall = new SimpleJdbcCall(dataSource).withCatalogName("PKG_RE_FI").

withProcedureName("PRC_RE_FI_DETAILS").declareParameters(new SqlParameter("P_FAN_NO", Types.VARCHAR),

new SqlParameter("P_REF_ID", Types.ARRAY),

new SqlParameter("P_COMMENTS", Types.VARCHAR),

new SqlParameter("P_BILLING_FLAG", Types.VARCHAR),

new SqlOutParameter("P_STATUS", Types.VARCHAR)

);

我应该如何将数组传递给

new SqlParameter("P_REF_ID", Types.ARRAY),

到MapSqlParameterSource

MapSqlParameterSource in = new MapSqlParameterSource();

解决方法:

PeudoCode与我的实现方式相同.

# 1.You will require a structDescriptor object for an object equivalent in pl sql like :

StructDescriptor structDes= new StructDescriptor(".", connectionObject);

# 2. You will need to pass one object values such name, class, id to an object array in order and accordance to 'sql_object_name' object.

For exmaple:

STRUCT[] structArray=new STRUCT[.size()];

int index=0;

for (a in ListObj){

Object[] object=new Object[]{a.getName(),a.getId()};

STRUCT struct=new STRUCT(structDes ,connectionObject,object);

structArray[index]=struct;

index++;

}

ArrayDescriptor arrayDes=ArrayDescriptor.createDescriptor(

".

ARRAY array=new ARRAY(arrayDes,connectionObject, structArray);

then pass it to proc

.declareParameters(

new SqlInOutParameter("",OracleTypes.ARRAY,"

."))

like

Hashmap map= new HashMap<>();

map.put("",array);

psStatement.execute(map);

希望能帮助到你.此序列可能根据所使用的sql数据库的要求和类型而有所不同,但基数相同.

标签：spring-jdbc,java,spring,oracle,stored-procedures

来源： https://codeday.me/bug/20191008/1873099.html