Java jdbcTemplate调用Oracle存储过程,入参为list的记录
1、Oracle数据库类型定义
CREATE OR REPLACE TYPE xm_object AS OBJECT(
O_A NVARCHAR2( 10 ) ,
O_B NVARCHAR2( 2000 ) ,
) ;
CREATE OR REPLACE TYPE xm_table AS TABLE OF xm_object;
2、创建存储过程
create or replace procedure pro_xmgl_imp( xmList in xm_table, resultSet out NVARCHAR2) is
begin
for i in 1 . . xmList. count loop
insert into emacms_jjk_jjysgl( id, A, B, createdate, updatedate)
values
( sys_guid( ) , xmList( i) . O_A, xmList( i) . O_B, sysdate, sysdate) ;
end loop ;
commit ;
resultSet := '导入成功' ;
end pro_xmgl_imp;
3、java 实体
public class XmglExpEntity {
private String A ;
private String B ;
}
4、使用
public List < XmglExpEntity > getList ( ) {
List < XmglExpEntity > list = new ArrayList < XmglExpEntity > ( ) ;
for ( int i = 0 ; i < 10 ; i++ ) {
XmglExpEntity xm = new XmglExpEntity ( "A" + i, "B" + i) ;
list. add ( xm) ;
}
return list ;
}
public void importExcel ( ) {
final List < XmglExpEntity > list = getList ( ) ;
String resp = ( String ) jdbcTemplate. execute ( "{call pro_xmgl_imp(?, ?)}" , new CallableStatementCallback ( ) {
public String doInCallableStatement (
CallableStatement cstmt) throws SQLException , DataAccessException {
OracleConnection con = cstmt. getConnection ( ) . unwrap ( OracleConnection . class ) ;
ARRAY array = getArray ( list, "XM_OBJECT" , "XM_TABLE" , con) ;
cstmt. setArray ( 1 , array) ;
cstmt. registerOutParameter ( 2 , Types . VARCHAR) ;
cstmt. execute ( ) ;
return cstmt. getString ( 2 ) ;
}
} ) ;
System . out. println ( resp) ;
}
private ARRAY getArray ( List < XmglExpEntity > list, String row_object, String table_array, OracleConnection con) throws SQLException {
if ( list != null && list. size ( ) > 0 ) {
STRUCT[ ] struts = new STRUCT[ list. size ( ) ] ;
StructDescriptor st = new StructDescriptor ( row_object, con) ;
for ( int i = 0 ; i < list. size ( ) ; i++ ) {
XmglExpEntity xm = list. get ( i) ;
Object [ ] obs = { xm. getA ( ) , xm. getB ( ) } ; ;
struts[ i] = new STRUCT ( st, con, obs) ;
}
ArrayDescriptor arrayDept = ArrayDescriptor . createDescriptor ( table_array, con) ;
ARRAY deptArrayObject = new ARRAY ( arrayDept, con, struts) ;
return deptArrayObject;
}
return null ;
}