Java jdbcTemplate调用Oracle存储过程,入参为list的记录

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;
}
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值